Обсуждение: Partitioned Tables vs Vacuum+Reindex
Hello!
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
Hi Edgar,
After performing the partition we have to vacuum individual partitions.
Just vacuuming the parent table won't vacuum the child tables.
Thanks and Regards
Ajinkya Bangale
Database Administrator
Www.shreeyansh.com
On Friday, January 8, 2016, Edgar Delgado <edgdelgado@gmail.com> wrote:
On Friday, January 8, 2016, Edgar Delgado <edgdelgado@gmail.com> wrote:
Hello!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
On Fri, Jan 8, 2016 at 9:30 AM, Edgar Delgado <edgdelgado@gmail.com> wrote:
Hello!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.
I have a database the has a huge amount of new and old deleted records (3 million a day). I remember reading somewhere thata particular version/update of postgresql reused deleted index entries so that the size of the index on disk did notgrow indefinitely due to deleted index entries. Can anyone provide a link to such a reverence, or at let me know which version(s) addressed the index bloat? Thanks, Evan.
> I have a database the has a huge amount of new and old deleted records (3 > million a day). I remember reading somewhere that a particular > version/update of postgresql reused deleted index entries so that the size > of the index on disk did not grow indefinitely due to deleted index > entries. > > Can anyone provide a link to such a reverence, or at let me know which > version(s) addressed the index bloat? > Evan, you used vaccum?. Rgds, Gilberto Castillo ETECSA, La Habana, Cuba