Re: Table growing faster than autovacuum can vacuum - Mailing list pgsql-general

On 02/15/12 8:46 AM, Asher Hoskins wrote:
> I've got a database with a very large table (currently holding 23.5
> billion rows,

a table that large should probably be partitioned, likely by time.
maybe a partition for each month.  as each partition is filled, it can
be VACUUM FREEZE'd since it will never be modified again.   if you're
doing your batch inserts of 1-2 billion rows once a month or so, perhaps
that should be the partition right there.

since you're inserting the new data sequentially, and not doing UPDATEs,
there's no point in doing VACUUM FULL, just a plain vacuum.....

so, your ~ monthly batch run could be something like...

     create new partition table
     copy/insert your 1-2 billion rows
     vacuum analyze (NOT full) new table
     vacuum freeze new table
     update master partition table rules

this way, autovacuum won't even bother with these tables.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast