Re: Vacuuming strategy

On Wed, Apr 30, 2014 at 10:49 AM, Elanchezhiyan Elango <elanelango(at)gmail(dot)com
> wrote:

>
>> Each table has 4 indices. The updates are to the indexed columns.
>>
>> Here is the schema of a table: http://pastebin.com/ffu0dUjS All tables
>> have this same schema except that some tables don't have a port column and
>> so will have one less index
>
> What indexes exist? Are the updates to indexed columns?
>
>
> Sorry I was wrong when I said the updates are to the indexed columns. The
> updates are to the 'data[]' column which doesn't have any index.
>

In this case your updates might qualify for Heap-Only-Tuple (HOT) updates.
Those are nice because they don't bloat the indexes and because the space
within the tables can be re-used faster, not needing an intervening vacuum
in order to reuse it. But to get that benefit, you do have to have enough
space in the page to record the new tuple, and so it would benefit from a
lower fillfactor. But once that part of the table goes cold, then the
lower fillfactor no longer is a benefit and justs wastes space. That is
where partitioning could come in handy. Once the tuples have become old
enough that they can't be updated any more by the app, just move them from
the live partition to the cold partition and they get repacked in the
process

But so far there is no indication you need to resort to such things.

Cheers,

Jeff