Vacuum settings for mostly append-only data

Question

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?

Answer 1

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"

Answer 2

Don't disable autovacuum unless you have a very good reason. At the absolute most, set per-table vacuum parameters to limit how it runs on a particular table.

Autovacuum maintains table statistics and you actually want it to run very often on append-only tables to make sure you get good query plans; that's a known problem area for statistics- based planners, since the "maximum" value in the stats is always out of date.

The visibility map should ensure that autovacuum doesn't bother to scan clean rows so the cost of each ordinary vacuum should be very small.

Answer 3

Vacuum isn't just useful after updating. It reclaims the hard disk section and optimizes the table for that data. That being said, your data should be laid out in a fairly linear fashion, so while it is still beneficial, it probably isn't necessary to do it as often.

I'd try cutting back the Vacuum like you suggested, or even try it on a monthly basis.