Обсуждение: Release plans for improvements to 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
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.
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
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 somevery > 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