From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Table growing faster than autovacuum can vacuum |
Date: | 2012-02-15 19:38:42 |
Message-ID: | 4F3C09C2.2060300@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2012-02-15 19:49:14 | Re: Table growing faster than autovacuum can vacuum |
Previous Message | John R Pierce | 2012-02-15 19:32:20 | Re: postgresql-9.0 |