Thread: Strong slowdown on huge tables

Strong slowdown on huge tables

From
Job
Date:
Hello,
 
i use PostgreSQL 8.4.8 on Centos 5.x and i have some table where i load with pg_bulkload webtraffic logs, every day.
After loading new data, i delete with a query 30-days older logs.
Autovacuum is on.
 
In some systems, i experience a very huge slowdown of this table in queries, expecially update and delete; the database size grows up a lots.
I tried to rebuild indexes and make a manual vacuum; database size reduces, but slowdown problems remains.
 
Are there particular things to know after using pg_bulkload?
In this case, is preferred to set autovacuum off and launch one time a week, as example, the autovacuum full manually?

Thank you!
Francesco

Re: Strong slowdown on huge tables

From
Tom Lane
Date:
Job <Job@colliniconsulting.it> writes:
> i use PostgreSQL 8.4.8 on Centos 5.x and i have some table where i load with pg_bulkload webtraffic logs, every day.
> After loading new data, i delete with a query 30-days older logs.

Since the deletion pattern is so predictable, you should consider
setting this table up as a partitioned table, so that you can just drop
the oldest partition instead of having to rely on DELETE+VACUUM to
reclaim space.  See
http://www.postgresql.org/docs/8.4/static/ddl-partitioning.html

Alternatively, look into whether including a manual VACUUM in your daily
update script helps any.

            regards, tom lane