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 CB74D563A0 for ; Sat, 19 Mar 2005 22:52:45 +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 80989-08 for ; Sat, 19 Mar 2005 22:52:42 +0000 (GMT) Received: from sccrmhc13.comcast.net (sccrmhc13.comcast.net [204.127.202.64]) by svr1.postgresql.org (Postfix) with ESMTP id 46C3852A64 for ; Sat, 19 Mar 2005 22:52:41 +0000 (GMT) Received: from grownups (c-24-21-166-228.client.comcast.net[24.21.166.228]) by comcast.net (sccrmhc13) with SMTP id <20050319225243016007mqooe>; Sat, 19 Mar 2005 22:52:43 +0000 Message-ID: <001d01c52cd6$9702be10$0200a8c0@grownups> From: "Stacy White" To: References: <200503191202.38089.josh@agliodbs.com> <18168.1111265368@sss.pgh.pa.us> Subject: Re: What needs to be done for real Partitioning? Date: Sat, 19 Mar 2005 14:54:20 -0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.249 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200503/374 X-Sequence-Number: 11079 From: "Tom Lane" > Josh Berkus writes: > > -- INSERT INTO should automatically create new partitions where necessary > > -- DELETE FROM should automatically drop empty partitions > > I am not sure I agree with either of those, and the reason is that they > would turn low-lock operations into high-lock operations. I second this. We're current using an inheritance based partitioning scheme with automatic partition creation in the application code, and have seen at least one case of deadlock due to partition creation. Other phase II/III items might include: - Modify the partitioning scheme of a table. In the above example, adding a '200504' partition, and moving the '200502' orders into 'ARCHIVE' - The ability to place a partition in a tablespace. In the example above, it would be nice to put the 'ARCHIVE' partition would likely be placed on a slower set of disks than the most recent month's partition. - Global indexes (that is to say, an index spanning the the table rather than an individual partition). This seems counterintuitive, but they've dramatically increased performance on one of our Oracle systems and should at least be worth considering.