Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 262A79FB935 for ; Tue, 19 Jun 2007 11:11:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 05000-03 for ; Tue, 19 Jun 2007 11:10:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 3D0B19FB978 for ; Tue, 19 Jun 2007 11:10:55 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Tue, 19 Jun 2007 09:10:51 -0500 Message-Id: <46779D96.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Tue, 19 Jun 2007 09:10:47 -0500 From: "Kevin Grittner" To: "Chander Ganesan" Cc: Subject: Re: Explained by known hardware failures, or keep looking? References: <467699B7.EE98.0025.0@wicourts.gov> <4677D511.60802@otg-nc.com> In-Reply-To: <4677D511.60802@otg-nc.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/169 X-Sequence-Number: 25837 >>> On Tue, Jun 19, 2007 at 8:07 AM, in message <4677D511.60802@otg-nc.com= >, Chander Ganesan wrote:=20 > Kevin Grittner wrote: >> =20 >> weekly maintenance process which builds a new version of a table based = on=20 >> records retention rules. It is built under a temporary name; then = the=20 >> previous version of the table is dropped and the new table is renamed. = This=20 >> leaves a fraction of a second during which queries may fail on the = missing=20 >> table, but it seems to be better than the alternatives. (If a query = doesn't=20 >> complete within 20 seconds, it is an error for the users of these = tables,=20 >> since our web app times out. The alternatives which blocked rather = than=20 >> giving outright errors blocked for more than 20.25 seconds, so this=20 >> alternative generates the fewest errors from a user perspective.) >=20 > With PostgreSQL 8.2 you have the ability to dynamically add and = remove=20 > child tables, so you could create a "blank" parent table and make the=20 > data table its child, built the new data table, and then simply = change=20 > the inheritance (remove the old child and add the new child). =20 > Applications would not need to change (since PostgreSQL's inheritance=20 > rules would have the same "parent" table name, and only the child = would=20 > change). >=20 > That would give you a much, much, much smaller window of unavailability= =20 =20 I'm curious what the "much, much, much smaller" amount of time would be. Our current technique seems to result in between 80 ms and 250 ms of "down time" around our weekly maintenance. Even though our site gets about two million hits a day, we usually don't see any queries trying to touch this table during the replacement. On a bad day we might see five errors, which would result in the users getting a "try again" sort of = message in their browsers. It would be totally unacceptable, by the way, for there to be any window = of time during which the table appeared empty -- an error would be much preferred. This means that we would either need to bracket the inheritance= changes within a transaction or add the new table as a child (with mostly duplicate rows) before dropping the old one. Either would be OK -- does one sound more promising than the other? Is there any chance that using this technique would have a negative impact on performance? (Many of these queries join a large number of tables and also use several correlated= subqueries.) =20 -Kevin =20