Vacuum helps in a number of cases out of which 2 important are:
- recover or reuse disk space occupied by deleted or updated rows
- update statistics for query engine
Some other use cases are:
- update visibility map for index-only scans
- prevent transaction-id wrap around failures
Details are present: http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html
Apigee Scenario:
- No deletion to fact-data tables
- Purge happens through drop tables [parent-child]
- Only update happens in aggregation driver table - which is not beyond 20-25 records
- Index is only timestamp column - and all queries are driven by timestamp predicates
So in our case:
- Reuse of disk space occupied by deleted or updated rows are minimal - that they can be ignored.
- When it comes to query planner, the only driving index is timestamp [client_received_start_timestamp / timestamp], so the query path hardly changes even if the stats change.
Traditionally the TPS or traffic rate for private customers had not been very high. So the issue of "transaction-id wrap-around" was hardly there, rather we drop the child-fact tables, before even we hit the problem. For high TPS customers, this can be a problem, and auto-vacuum must be enabled. Because the percentage of such customers are way-less, and the cost incurred at times for running auto-vacuum is sometimes high, we had traditionally switched it off by default.
So depending on the data retention period and traffic rate, it should be prudent to switch it on - if we are in doubt.
LinkedIn
Twitter
