Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

3 Answers 3

up vote 2 down vote accepted

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"
share|improve this answer
    
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 '13 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 '13 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 '13 at 20:50
    
Very helpful. Thanks @bma! –  Larsenal Aug 13 '13 at 20:56
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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