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@xxxxxxxxxxxxxx#CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@xxxxxxxxxxxxxx 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
