Postgresql – Vacuum settings for mostly append-only data

postgresqlpostgresql-9.0vacuum

I have a table with the following characteristics:

  1. We INSERT a few 100k rows each day
  2. We never UPDATE the rows
  3. We DELETE "old" data once a week

From my shallow knowledge of Postgres VACUUM, it seems like this table might be a good candidate for a non-standard VACUUM strategy. A few questions I have…

  1. Apart from the weekly purging of old data, will a VACUUM do anything? Seems to me there is nothing to compact/clean if we're only doing INSERTs.
  2. Would it be a horrible idea to simply disable AUTOVACUUM on that table and manually VACUUM FULL after our weekly DELETE?

Best Answer

Autovacuum won't run against the table if it not changing. In your case, you are appending data to the table so autovacuum will run, but it is the ANALYZE portion that is worth mentioning. The DELETEs will kick of autovacuum runs of course. Are you seeing performance problems when autovacuum runs? If not, I would recommend leaving it enabled. After your loading, you could issue a manual VACUUM FREEZE <your table>, but again, I would not recommend disabling autovacuum unless there is a compelling reason to do so.

Note: An unadorned VACUUM does not reclaim space, it marks it as reusable. A VACUUM FULL or CLUSTER will reclaim space however.

-- To show the current vacuum settings:

SELECT  "name", 
        current_setting("name") AS current_setting, 
        source
FROM pg_settings
WHERE "name" ~* 'vac'
ORDER BY "name"
Related Question