Re: Table growing faster than autovacuum can vacuum

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