>>> On Tue, Jun 19, 2007 at 8:07 AM, in message <4677D511.60802@xxxxxxxxxx>, Chander Ganesan <chander@xxxxxxxxxx> 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
