X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 26EDE9DC86F for ; Mon, 13 Feb 2006 07:49:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94800-10 for ; Mon, 13 Feb 2006 07:49:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (serval.logix-tt.com [213.239.221.42]) by postgresql.org (Postfix) with ESMTP id B453F9DC817 for ; Mon, 13 Feb 2006 07:49:09 -0400 (AST) Received: from kingfisher.intern.logix-tt.com (Mf755.m.pppool.de [89.49.247.85]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id E5B8D24407F; Mon, 13 Feb 2006 12:49:16 +0100 (CET) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id C3768184B3AD8; Mon, 13 Feb 2006 12:49:15 +0100 (CET) Message-ID: <43F0723B.3040505@logix-tt.com> Date: Mon, 13 Feb 2006 12:49:15 +0100 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: david drummard Cc: pgsql-performance@postgresql.org Subject: Re: help required in design of database References: <9c89e2490602101220i796e6913pdfa05f40bb894b97@mail.gmail.com> In-Reply-To: <9c89e2490602101220i796e6913pdfa05f40bb894b97@mail.gmail.com> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.121 required=5 tests=[AWL=0.121] X-Spam-Score: 0.121 X-Spam-Level: X-Archive-Number: 200602/142 X-Sequence-Number: 17128 Hi, David, david drummard wrote: > 1) create a new table every time a new feed file comes in. Create table > with indexes. Use the copy command to dump the data into the table. Its faster to obey the following order: - Create the table - COPY the data into the table - Create the indices - ANALYZE the table. and probably CLUSTER the table on the most-used index, between index creation and ANALYZE. You also might want to increase the statistics target on some columns before ANALYZE, depending on your data. > 2) rename the current table to some old table name and rename the new > table to current table name so that applications can access them directly. You can also use a view, and then use CREATE OR REPLACE VIEW to switch between the tables. But two table renames inside a transaction should do as well, and shorten the outage time, as with the transaction encapsulation, no external app should see the change inside their transaction. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org