I've a question about partitioned tables (never used before).
I got a table with 100gb and data been added and removed all the time so once I week I run vacuum on it, the problem is the vacuum is taking 1 n 1/2 hour to finish.
Only newest data on this table keep been added/removed, data from 2-3 months in the past is only used for historical needs.
If I partition my table will be fast the vacuum process? Can I only vacuum the main partitioned table and the the historical out of the maintenance?
Thanks.
Edgar
You may still have to vacuum the entire partition set, although you could split it up to vacuum the individual children instead of the whole thing, so it's not one long transaction.
In fact, partitioning potentially increases the need for manual vacuuming. Since autovacuum kicking off is determined by the number of writes to a table, if your older partitions rarely get writes anymore, that could cause their xid to increase indefinitely. See the following settings: autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, autovacuum_analyze_scale_factor, autovacuum_analyze_threshold. Note that autovac will kick in once autovacuum_freeze_max_age is reached for the cluster xid value. The downside of relying on that is typically when that is hit, many tables all hit it at the same time and cause a huge number of vacuum jobs to kick off in a row which could greatly impact performance and storage concerns. And depending on your activity level, it may never catch up if left on its own which could put you into xid wraparound territory.
If the old tables really are not getting anymore writes, you can look at running VACUUM FREEZE on them, and that will keep their xid values from increasing. However, if even a single write hits those older tables, you have to vacuum it again.
This means you will have to either schedule manual vacuums on these tables, work out a process to vacuum freeze the old tables, or look at archiving the older tables out if they are no longer needed live in the database.
If your partitioning is based on time or ID, I've written a tool that helps manage that. It can also manage dropping/dumping out old tables as well.