1

I have created a table where I store serialized Java objects. They are frequently accessed and costly to create, so that is the reason. Solution works fine to me, however table size grows to 25GB inside few days! The objects are removed after few mins, so it's not case the case of amount of data really held, but simply vacuum does not do its job. After full vacuum table size drops to approx. 150 MB. In the logs I see that vacuuming process is running, but as table grows in size, it takes over 5 mins and does not help a lot.

I wonder how can I tune the vacuum to make its job better for this table?

  • 2
    Do you have any connections that are in the "<IDLE> in transaction" state? If those accessed the table, they will prevent vacuum from cleaning up. Another option might be to make vacuum more aggressive to kick in more often. – a_horse_with_no_name Nov 8 '12 at 7:54
  • 2
    PostgreSQL version? PostgreSQL's autovacuum has improved a lot, so the version is crucial. If it isn't at least 8.3 or 8.4, upgrade. Also, what's the output of SHOW max_prepared_transactions; and SELECT count(*) FROM pg_prepared_xacts;? Also, is there any chance table-specific autovacuum settings were ever applied? – Craig Ringer Nov 8 '12 at 8:06
  • 1
    Also, if you're on a Pg older than 8.4, what's your max_fsm_pages and are you getting any warnings about it in the logs? – Craig Ringer Nov 8 '12 at 8:28
  • It's Postgres 9.1 Yes, there are connections in transaction, but not all the time, so there should be plenty of time for vacuum. I never applied any table-specific autovacuum settings. SHOW max_prepared_transactions = 0 SELECT count(*) FROM pg_prepared_xacts = 0 – Michal Nov 8 '12 at 15:56
1

First, take a long, careful look at Aggressive Autovacuum on PostgreSQL as it may very well describe your problem, and check pg_locks to see if there is a locking issue on the table (there may well be one) and also look carefully at Josh Berkus's recommended autovacuum settings for aggressively autovacuuming.

To recover from a bloated table you can run the cluster command from psql (exclusively locks a table, but much faster than vacuum full).

Third, if this is a locking issue, you may try to use a cron job to run a vacuum or even vacuum full (or cluster) during low-utilization times.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.