Autovacuum on partitioned tables in version 9.1 - Mailing list pgsql-general

Hello People,

Before the question, this is the scenario:

I have a postgresql 9.1 cluster with a size of 1.5 TB and composed of 70
databases.

In every database I have 50 tables (master partition), each one have an
associated trigger that insert the record into a child table of its own.

The partition is based on a week period, so every newly created tables
is of the form: tablename_YYYYwWW

The above configuration works great under postgres version 8.4 and
postgres version 9.1, except for one thing in the case of 9.1:

The autovacuum process on version 9.1 keeps vacuuming the master tables
and that takes a lot of time considering the master table have no
records of its own.

The trigger itself insert into the master table, then into the child and
then remove the record from the master, we do that way because we need
to get the inserted row info.

Actually we don't use any features of version 9.1 that are not available
under version 8.4, however I don't want to downgrade to version 8.4 as I
consider that I still have not understood completely how auto vacuuming
process works, and I need to improve on this.

Earlier in this list a user experienced the same behavior:
http://www.postgresql.org/message-id/flat/CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@mail.gmail.com

On the same thread another user wrote it could be an issue that is to be
resolved at minor version 9.1.8
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5454344b968d6a189219cfd49af609a3e7d6af33

I currently have  PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled
by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit running on an eight-core
processor and 24 GB RAM with the following options:

maintenance_work_mem = 1GB # pgtune wizard 2014-08-29
checkpoint_completion_target = 0.7 # pgtune wizard 2014-08-29
effective_cache_size = 15GB # pgtune wizard 2014-08-29
work_mem = 40MB # pgtune wizard 2014-08-29
wal_buffers = 4MB # pgtune wizard 2014-08-29
checkpoint_segments = 8 # pgtune wizard 2014-08-29
shared_buffers = 5GB # pgtune wizard 2014-08-29
max_connections = 500 # pgtune wizard 2014-08-29

In order to alleviate the I/O problem I disable autovacuum on all the
master tables like so:

ALTER TABLE public.tablename SET ( autovacuum_enabled = false,
toast.autovacuum_enabled = false);

But I know I can't left the database without vacuuming enabled because
of transaction ID wraparround.

So I need to set up a cron script for this; for every master table there
is a new child table every week then I can start a vacuum process via
cron for the table before the newly created, these tables are only used
for reading after a week.

But I need some clarification on this:

Why is postgresql starting a vacuum on master tables too often ? Why it
takes too much time on version 9.1 ? I guess because it needs to reclaim
unused space due to the insert/remove process, but in version 8.4 that
is unnoticeable.

How do I know which tables needs to be vacuumed ? any sql recipe ?

How do I check when I am near the limit of the transaction ID ?

Do I need to vacuum tables that haven't change a long time ago ?

Anybody have experienced the same behavior and would like to comment on
this ?

Slds.

--
Nestor A. Diaz