X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6E012545F2 for ; Tue, 1 Mar 2005 01:41:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25457-01 for ; Tue, 1 Mar 2005 01:41:25 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 806145647F for ; Tue, 1 Mar 2005 01:41:24 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.2/8.12.9) with ESMTP id j211fK0F000323; (envelope-from ) Mon, 28 Feb 2005 19:41:20 -0600 (CST) Received: from [192.168.1.13] (12-217-241-0.client.mchsi.com [12.217.241.0]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j211fJWI012293 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Mon, 28 Feb 2005 19:41:20 -0600 (CST) Message-ID: <4223C840.1060109@arbash-meinel.com> Date: Mon, 28 Feb 2005 19:41:20 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Sven Willenberger Cc: Postgresql Performance list Subject: Re: Inheritence versus delete from References: <1109635153.4620.44.camel@lanshark.dmv.com> In-Reply-To: <1109635153.4620.44.camel@lanshark.dmv.com> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig3A79B20BA3BA1756EE8BC867" X-Virus-Scanned: ClamAV 0.83/723/Thu Feb 24 05:54:24 2005 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV 0.80/706/Sun Feb 13 18:14:02 2005, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/2 X-Sequence-Number: 10707 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig3A79B20BA3BA1756EE8BC867 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Sven Willenberger wrote: >Trying to determine the best overall approach for the following >scenario: > >Each month our primary table accumulates some 30 million rows (which >could very well hit 60+ million rows per month by year's end). Basically >there will end up being a lot of historical data with little value >beyond archival. > > > If this statement is true, then 2 seems the best plan. >2) Each month: >SELECT * INTO 3monthsago_dynamically_named_table FROM bigtable WHERE >targetdate < $3monthsago; >DELETE FROM bigtable where targetdate < $3monthsago; >VACUUM ANALYZE bigtable; >pg_dump 3monthsago_dynamically_named_table for archiving; > > > It seems like this method would force the table to stay small, and would keep your queries fast. But if you ever actually *need* the old data, then you start having problems. ... I think (3) would tend to force a whole bunch of joins (one for each child table), rather than just one join against 3months of data. >Any takes on which approach makes most sense from a performance and/or >maintenance point of view and are there other options I may have missed? > >Sven Willenberger > > If you can get away with it 2 is the best. John =:-> --------------enig3A79B20BA3BA1756EE8BC867 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCI8hAJdeBCYSNAAMRAsCmAJkB7KPfpzIy50LAEU82HdXssfPGfACeNBGx FdJJ9C5mZAIv2dhEWcGYB4s= =qhfR -----END PGP SIGNATURE----- --------------enig3A79B20BA3BA1756EE8BC867--