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

TL;DR - the interaction of ANALYZE and inheritance hierarchies seems to be
broken for the uncommon use case where the inserts temporarily remain on the
master table in order to allow RETURNING to work.

Note - I have not played with this scenario personally but


http://www.postgresql.org/message-id/flat/CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@mail.gmail.com#CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@mail.gmail.com


combined with this post leads me to that conclusion.


Nestor A. Diaz wrote
> 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.

If you say so...but the second paragraph makes your conclusion in the first
paragraph false.

You may wish to read up on Multi-Version Concurrency Control (MVCC)


> 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.

Well, you are actually using one right now - community support :)


> Earlier in this list a user experienced the same behavior:
> http://www.postgresql.org/message-id/flat/

> CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@.gmail

>
> 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:

Given that 9.1.14 is current being on 9.1.9 is going to be a problem.

The referenced thread never came to conclusion - the OP never affirmed the
patch fixed their problem - but the patch mentioned affected vacuum while
the preceding conclusion in the post was that ANALYZE was the probable
culprit - specifically that the ANALYZE on the master table cascaded to all
of the children and so took however long it would take to analyze the entire
partition hierarchy.


> 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.

If things get to a point where this is required it will run regardless of
your configuration.


> 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.

I'd be more concerned with the difference between 8.4 and 9.1 but if you are
indeed physically inserting and the deleting from the master table you need
some kind of vacuum if you want to reclaim that wasted space.

As noted above the ANALYZE is a probable culprit here - and its interaction
with inheritance seems under-documented and incompletely implemented.  I
think this would be more obvious but apparently most people do not write
their trigger sets to leave the inserted record in the master table so as
not to break RETURNING and then delete the record shortly thereafter.
Someone from -hackers needs to comment on this use case and whether
something can and should be done to accommodate it.


> 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 ?

If a vacuum freeze has been run on a table then in the absence of subsequent
updates it will not require vacuuming.


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

David J.




--
View this message in context:
http://postgresql.nabble.com/Autovacuum-on-partitioned-tables-in-version-9-1-tp5826595p5826603.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.