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?
  • For anyone finding this in 2022 Postgres now has a way to manage append only tables being vacuum. autovacuum_vacuum_insert_scale_factor
    – Gurmokh
    Mar 28, 2022 at 16:38

3 Answers 3


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, 
FROM pg_settings
WHERE "name" ~* 'vac'
ORDER BY "name"
  • I'm seeing autovacuum runs take 15 minutes. Total relation size is in the 2-4 GB range. It doesn't hurt anything as far as we can tell, other than tripping a few alerts for long-running queries. If my weekly volume was fairly stable, is there any advantage to intentionally avoiding a VACUUM FULL so that we continue to reuse the space in an orderly fashion? By reusing the same space do we help avoid fragmentation of some kind?
    – Larsenal
    Aug 13, 2013 at 19:49
  • 15 minutes seems a bit long for a table that small. Somewhat counterintuitively, setting the vacuum/autovacuum settings more aggressive can reduce that running time. In your case, since you know when you are doing the DELETEs I would be inclined to issue a manual VACUUM ANALYZE after the DELETE. If you can afford the blocked access to your table for a few minutes, a CLUSTER of the table after your DELETE would be even more effective, as it reclaims space and reorders your data according to the index you CLUSTER on. CLUSTER is the recommended method instead of VACUUM FULL.
    – bma
    Aug 13, 2013 at 20:49
  • Reusing the empty space (using VACUUM) does act as a defragmenter of sorts, but the data will generally not be as contiguous on disk as what the results of a CLUSTER would do.
    – bma
    Aug 13, 2013 at 20:50

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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