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 0E64656478 for ; Sun, 20 Mar 2005 19:58:01 +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 96227-09 for ; Sun, 20 Mar 2005 19:57:51 +0000 (GMT) Received: from rwcrmhc11.comcast.net (rwcrmhc11.comcast.net [204.127.198.35]) by svr1.postgresql.org (Postfix) with ESMTP id E54DC56480 for ; Sun, 20 Mar 2005 19:57:49 +0000 (GMT) Received: from grownups (c-24-21-166-228.client.comcast.net[24.21.166.228]) by comcast.net (rwcrmhc11) with SMTP id <2005032019574901300eh125e>; Sun, 20 Mar 2005 19:57:50 +0000 Message-ID: <002a01c52d87$543d1010$0200a8c0@grownups> From: "Stacy White" To: "Alvaro Herrera" , "Tom Lane" Cc: "PFC" , "Josh Berkus" , References: <200503191202.38089.josh@agliodbs.com> <20050319225624.GA8467@dcc.uchile.cl> <3404.1111277153@sss.pgh.pa.us> Subject: Re: What needs to be done for real Partitioning? Date: Sun, 20 Mar 2005 11:59:29 -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.255 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200503/389 X-Sequence-Number: 11094 From: "Tom Lane" > Alvaro Herrera writes: > > We probably also need multi-table indexes. > As Josh says, that seems antithetical to the main point of partitioning, > which is to be able to rapidly remove (and add) partitions of a table. > If you have to do index cleaning before you can drop a partition, what's > the point of partitioning? Global indexes (as opposed to partition local indexes) are useful in cases where you have a large number of partitions, index columns different than the partition key, and index values that limit the query to just a subset of the partitions. The two domains that I'm most familiar with are warehouse management, and the film industry. In both these cases it's logical to partition on day/week/month, it's frequently important to keep a lot of history, and it's common to have products that only show activity for a few months. In one of our production systems we have 800 partitions (by week, with a lot of history), but a popular product might have only 20 weeks worth of activity. Selecting records for the product requires at least 800 random-access reads if you have local indexes on 'product_no', 780 of which just tell the executor that the partition doesn't include any information on the product. This is definitely a phase II item, but as I said before it's worth considering since good DBAs can do a lot with global indexes. FWIW, we see large benefits from partitioning other than the ability to easily drop data, for example: - We can vacuum only the active portions of a table - Postgres automatically keeps related records clustered together on disk, which makes it more likely that the blocks used by common queries can be found in cache - The query engine uses full table scans on the relevant sections of data, and quickly skips over the irrelevant sections - 'CLUSTER'ing a single partition is likely to be significantly more performant than clustering a large table In fact, we have yet to drop a partition on any of our Oracle or Postgres production systems.