pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Release plans for improvements to partitioning
3+ messages / 2 participants
[nested] [flat]

* Release plans for improvements to partitioning
@ 2006-03-29 21:11  Mark Liberman <mliberman@mixedsignals.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Mark Liberman @ 2006-03-29 21:11 UTC (permalink / raw)
  To: pgsql-admin

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.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Release plans for improvements to partitioning
@ 2006-03-31 00:04  Mark Liberman <mliberman@mixedsignals.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Mark Liberman @ 2006-03-31 00:04 UTC (permalink / raw)
  To: pgsql-admin

Re-sending:  for some reason original post was truncated and did not have carriage returns.

----------------------------------------

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.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Release plans for improvements to partitioning
@ 2006-03-31 22:35  Jim C. Nasby <jnasby@pervasive.com>
  parent: Mark Liberman <mliberman@mixedsignals.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Jim C. Nasby @ 2006-03-31 22:35 UTC (permalink / raw)
  To: Mark Liberman <mliberman@mixedsignals.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-03-31 22:35 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-29 21:11 Release plans for improvements to partitioning Mark Liberman <mliberman@mixedsignals.com>
2006-03-31 00:04 Re: Release plans for improvements to partitioning Mark Liberman <mliberman@mixedsignals.com>
2006-03-31 22:35 ` Jim C. Nasby <jnasby@pervasive.com>

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