Re: Partitioned Tables vs Vacuum+Reindex

On Fri, Jan 8, 2016 at 9:30 AM, Edgar Delgado <edgdelgado(at)gmail(dot)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.
https://github.com/keithf4/pg_partman

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com