From: Jim C. Nasby <jnasby@pervasive.com>
To: Mark Liberman <mliberman@mixedsignals.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Release plans for improvements to partitioning
Date: Fri, 31 Mar 2006 16:35:25 -0600
Message-ID: <20060331223525.GW49405@pervasive.com> (raw)
In-Reply-To: <9D938282F8C6EE43B748B910386DE93E0138B420@srvgpimail1.GPI.local>
References: <9D938282F8C6EE43B748B910386DE93E0138B420@srvgpimail1.GPI.local>
On Thu, Mar 30, 2006 at 04:04:04PM -0800, Mark Liberman wrote:
> Re-sending: for some reason original post was truncated and did not have carriage returns.
If you're going to hard-code CR/LF in, you should do it at the
traditional width of 72 characters. Your second email looks way worse on
my client than your first.
Anyway, someone (Simon maybe?) recently mentioned on -hackers that
they're intending to submit a patch for 8.2 with partitioning syntax,
which would presumably handle all the mundane stuff you need to do to
setup partitioning.
> ----------------------------------------
>
> I was wondering if anyone has any insight into if/when future improvements to the 8.1 partitioning capabilities are planned.
>
> The current implementation of partitioning in postgres 8.1 appears to be just a first step. While it would provide some very
> nice benefits (such as the absence of vacuum processes after dropping partitions), the additional burden it places on
> administrative DDL whenever you add a partition might be a little too daunting to make it a viable solution for us.
>
> Currently, a single partition, of which we would like to create one per table per day, involves the following definitions:
>
> 1) Create table definition. This is rather straight-forward ... e.g. CREATE TABLE programs_20060101 INHERITS (programs_1min)
>
> 2) Check condition. This tells postgres which range of data resides in this partition ...
> e.g. CHECK ( end_time >= '2006-01-01' AND end_time < '2006-01-02' ).
>
> Note: there are no checks to guarantee that these are non-overlapping.
>
> 3) Insert rule. This tells postgres which partition to insert into for inserts into the master ... e.g.
>
> CREATE RULE pgm_1min_insert_20060101 AS ON INSERT TO programs_1min
> WHERE ( end_time >= '2006-01-01' AND end_time < '2006-01-02' )
> DO INSTEAD INSERT INTO programs_1min_20060101 VALUES ( NEW.programs_id, NEW.begin_time, NEW.end_time);
>
> Again, there are no guarantees that these are non-overlapping.
>
> 4) All Indexes. Indexes must be created seperately on each partition. The existance of an index on the master table
> does not imply such an index on the underlying partitions.
>
> This is the major area that would involve too much effort. Whenever a new index is added we would need to write a script
> that dynamically added that new index to all partitions of a given table. While this is certainly achievable with scripting,
> it simply adds too much margin for error if we are trying to build an automated solution for all of our customers.
>
> From my understanding, there are other limitations as well, such as:
>
> 1) No delete rules for deleting across partitions
> 2) No update rules for updating across partitions
>
> In an ideal solution the addition of a new partition would be a single (ALTER TABLE ADD PARTITION) statement
> (much like Oracle). This should take care of the table definition, the acceptable ranges (which cannot be
> overlapping) and any insert rules. Additionally, the index definition that applies to the table should apply to
> all underlying partitions so that any create index statement on the master table would be sufficient to index each
> underlying partition. Once created, the partitions should then be "invisible" to the sql writer, such that all
> inserts, updates, and deletes appropriately hit only the required partitions.
>
> Hopefully, some/much of this is underway, but any insight would be appreciated because we need to determine whether we
> want to go down the path of implementing a solution based on the current partitioning, or waiting for the next generation
> of PG partitioning.
>
> Thanks,
>
> Mark
>
>
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: jnasby@pervasive.com, mliberman@mixedsignals.com
Subject: Re: Release plans for improvements to partitioning
In-Reply-To: <20060331223525.GW49405@pervasive.com>
References: <9D938282F8C6EE43B748B910386DE93E0138B420@srvgpimail1.GPI.local> <20060331223525.GW49405@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox