Re: Explained by known hardware failures, or keep looking?

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Chander Ganesan" <chander(at)otg-nc(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Explained by known hardware failures, or keep looking?
Date: 2007-06-19 14:10:47
Message-ID: 46779D96.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

>>> On Tue, Jun 19, 2007 at 8:07 AM, in message <4677D511(dot)60802(at)otg-nc(dot)com>,
Chander Ganesan <chander(at)otg-nc(dot)com> wrote:
> Kevin Grittner wrote:
>>
>> weekly maintenance process which builds a new version of a table based on
>> records retention rules. It is built under a temporary name; then the
>> previous version of the table is dropped and the new table is renamed. This
>> leaves a fraction of a second during which queries may fail on the missing
>> table, but it seems to be better than the alternatives. (If a query doesn't
>> complete within 20 seconds, it is an error for the users of these tables,
>> since our web app times out. The alternatives which blocked rather than
>> giving outright errors blocked for more than 20.25 seconds, so this
>> alternative generates the fewest errors from a user perspective.)
>
> With PostgreSQL 8.2 you have the ability to dynamically add and remove
> child tables, so you could create a "blank" parent table and make the
> data table its child, built the new data table, and then simply change
> the inheritance (remove the old child and add the new child).
> Applications would not need to change (since PostgreSQL's inheritance
> rules would have the same "parent" table name, and only the child would
> change).
>
> That would give you a much, much, much smaller window of unavailability

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

-Kevin

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Mario Splivalo 2007-06-19 14:31:12 Postgres8.2 - turning off BINGLOG/PARSELOG
Previous Message Chris Browne 2007-06-19 13:49:45 Re: [pgsql-advocacy] [PERFORM] Postgres VS Oracle