From simon@2ndquadrant.com Fri Jun 5 04:52:19 2026 X-Original-To: pgsql-patches-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 378C9DA860 for ; Sun, 30 Oct 2005 21:48:43 -0400 (AST) 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 54273-07 for ; Mon, 31 Oct 2005 01:48:39 +0000 (GMT) Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by svr1.postgresql.org (Postfix) with ESMTP id 3F5E6D7011 for ; Sun, 30 Oct 2005 21:48:39 -0400 (AST) Received: from 192.168.0.3 (unknown [84.12.200.148]) by smtp.nildram.co.uk (Postfix) with ESMTP id C5C6B2507EC for ; Mon, 31 Oct 2005 01:48:41 +0000 (GMT) Subject: Partitioning docs WIP From: Simon Riggs To: pgsql-patches@postgresql.org Content-Type: multipart/mixed; boundary="=-gKXD0L5uSf8MOFkr4IsO" Organization: 2nd Quadrant Date: Mon, 31 Oct 2005 02:46:56 +0000 Message-Id: <1130726816.8300.1377.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.014 required=5 tests=[AWL=0.014] X-Spam-Score: 0.014 X-Spam-Level: X-Archive-Number: 200510/239 X-Sequence-Number: 17735 --=-gKXD0L5uSf8MOFkr4IsO Content-Type: text/plain Content-Transfer-Encoding: 7bit I've been working on some docs for Constraining Exclusion & Partitioning for some time now. Deadlines seem to be looming, or may even have passed, so it seems sensible to submit what I have now. It's still a WIP: The final section on queries is not yet complete, but the overall structure and flow makes sense now. Many thanks to Josh Berkus for providing the numbered section on implementation process, which was the starting point I'd been looking for to describe everything else. Any comments welcome now... flames expected for lateness. Best Regards, Simon Riggs --=-gKXD0L5uSf8MOFkr4IsO Content-Disposition: attachment; filename=ce2.patch Content-Type: text/x-patch; name=ce2.patch; charset=UTF-8 Content-Transfer-Encoding: 7bit Index: ddl.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.45 diff -c -c -r1.45 ddl.sgml *** ddl.sgml 23 Oct 2005 19:29:49 -0000 1.45 --- ddl.sgml 31 Oct 2005 01:38:26 -0000 *************** *** 398,403 **** --- 398,410 ---- ensure that a column does not contain null values, the not-null constraint described in the next section can be used. + + + Check constraints can also be used to enhance performance with + very large tables, when used in conjunction with the + parameter. + This is discussed in more detail in + *************** *** 1040,1052 **** Inheritance ! This section needs to be rethought. Some of the ! information should go into the following chapters. ! Let's create two tables. The capitals table contains ! state capitals which are also cities. Naturally, the ! capitals table should inherit from cities. CREATE TABLE cities ( --- 1047,1081 ---- Inheritance ! ! not-null constraint ! ! ! ! constraint ! NOT NULL ! ! PostgreSQL was the first DBMS to introduce ! inheritance, as one its object-relational features. ! PostgreSQL implements table ! inheritance, a useful tool for database designers. The SQL:2003 standard ! also allows inheritance, but this is type inheritance rather than the ! features described here. ! ! ! ! Let's start with an example: ! We're trying to build a data model for cities, but we have a problem. ! Each state has many cities, but only one capital. We want to be able ! to quickly retrieve the capital city for any particular state. We ! can solve this problem by creating two tables. The capitals table contains ! state capitals, then we have another table for cities that aren't capitals. ! What happens when we want to ask for data about a city, regardless of ! whether it is a capital or not? We can use the inheritance feature to ! help resolve this problem for us. We define the capitals table so that ! it inherits from cities. CREATE TABLE cities ( *************** *** 1062,1077 **** In this case, a row of capitals inherits all attributes (name, population, and altitude) from its parent, cities. State ! capitals have an extra attribute, state, that shows their state. In ! PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendants. - - - - The inheritance hierarchy is actually a directed acyclic graph. - - --- 1091,1102 ---- In this case, a row of capitals inherits all attributes (name, population, and altitude) from its parent, cities. State ! capitals have an extra attribute, state, that shows their state. ! ! ! In PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendants. *************** *** 1133,1163 **** ! ! Deprecated ! ! In previous versions of PostgreSQL, the ! default behavior was not to include child tables in queries. This was ! found to be error prone and is also in violation of the SQL:2003 ! standard. Under the old syntax, to get the sub-tables you append ! * to the table name. ! For example ! ! SELECT * from cities*; ! ! You can still explicitly specify scanning child tables by appending ! *, as well as explicitly specify not scanning child tables by ! writing ONLY. But beginning in version 7.1, the default ! behavior for an undecorated table name is to scan its child tables ! too, whereas before the default was not to do so. To get the old ! default behavior, set the configuration option ! SQL_Inheritance to off, e.g., ! ! SET SQL_Inheritance TO OFF; ! ! or add a line in your postgresql.conf file. ! ! In some cases you may wish to know which table a particular row --- 1158,1180 ---- ! ! Inheritance does not automatically propogate data from INSERT or COPY ! commands to other tables in the inheritance hierarchy. Inheritance ! does not influence these commands. ! In our example, the following INSERT statement will fail. ! ! INSERT INTO cities ! (name, population, altitude, state) ! VALUES ('New York', NULL, NULL, 'NY'); ! ! We might hope that the data would be somehow routed to the capitals table, ! though this does not happen. If the child has no locally defined ! columns, then it is possible to route data from the parent to the child ! using a RULE, see . ! This is not possible with the above INSERT statement ! because the state column does not exist on both tables. ! In some cases you may wish to know which table a particular row *************** *** 1204,1220 **** ! A table can inherit from more than one parent table, in which case it has ! the union of the columns defined by the parent tables (plus any columns ! declared specifically for the child table). ! A serious limitation of the inheritance feature is that indexes (including ! unique constraints) and foreign key constraints only apply to single ! tables, not to their inheritance children. This is true on both the ! referencing and referenced sides of a foreign key constraint. Thus, ! in the terms of the above example: --- 1221,1287 ---- ! As shown above, a child table may locally define columns as ! well as inheriting them from their parents. ! However, polymorphism of table datatypes is not supported: a locally defined ! column cannot override the datatype of an inherited column of the same name. ! ! ! ! A table can inherit from a table that has itself inherited from ! other tables. A table can also inherit from more than one parent ! table, in which case it has the union of the columns defined by the parent ! tables (plus any columns declared locally for the child table). ! Inherited columns with duplicate names and datatypes will be merged so ! that only a single column is stored. An inheritance definition that ! results in a column with more than one datatype would not succeed. ! The full inheritance hierarchy is actually a directed acyclic graph of ! arbitrary complexity. You should note that complex inheritance hierarchies ! will cause a corresponding increase in parsing time for your queries. ! Direct access to the specific table your query requires will always ! provide the best performance. ! ! ! ! Table inheritance can currently only be defined using the ! statement. ! The related statement CREATE TABLE ... AS SELECT does ! not allow the specification of an inheritance link. The ALTER TABLE statement ! does not currently support the INHERITS() clause. There is no ! way to add an inheritance link to make an existing table into a child ! table. Similarly, there is no way to remove an inheritance link from a ! child table, once defined, other than using DROP TABLE on the child table. ! A parent table cannot be dropped while any of its children remain. If you ! wish to remove a table and all of its descendants, then you can do so ! using the CASCADE option of the statement. ! ! ! ! Check constraints can be defined on tables within an inheritance ! hierarchy. All check constraints on a parent table are automatically ! inherited by all of their children. Note that is currently possible to ! inherit mutually exclusive check constraints, but that definition quickly ! shows itself since all attempted row inserts will be rejected. ! ! ! ! will propogate any changes in data ! definition on columns or check constraints down the inheritance hierarchy. ! Again, dropping columns or constraints on parent tables is only possible ! when using the CASCADE option. ALTER TABLE follows the same rules for ! duplicate column merging and rejection that apply during CREATE TABLE. ! Both parent and child tables can have primary and foreign keys ! defined for them, so that they can take part normally on both the ! referencing and referenced sides of a foreign key constraint. Indexes ! may be defined on any of these columns whether or not they are inherited. ! However, a serious current limitation of the inheritance feature is that ! indexes (including unique constraints) and foreign key constraints only ! apply to single tables and do not also index their inheritance children. ! This is true on both sides of a foreign key constraint. ! Thus, in the terms of the above example: *************** *** 1236,1244 **** Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to ! capitals. In this case you could work around it by ! manually adding the same REFERENCES constraint to ! capitals. --- 1303,1313 ---- Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to ! capitals. However, it is possible to set up a ! foreign key such as capitals.name ! REFERENCES states.name. ! So it is possible to workaround this restriction by manually adding ! foreign keys to each child table. *************** *** 1255,1260 **** --- 1324,1722 ---- but in the meantime considerable care is needed in deciding whether inheritance is useful for your problem. + + Deprecated + + In previous versions of PostgreSQL, the + default behavior was not to include child tables in queries. This was + found to be error prone and is also in violation of the SQL:2003 + standard. Under the old syntax, to get the sub-tables you append + * to the table name. + For example + + SELECT * from cities*; + + You can still explicitly specify scanning child tables by appending + *, as well as explicitly specify not scanning child tables by + writing ONLY. But beginning in version 7.1, the default + behavior for an undecorated table name is to scan its child tables + too, whereas before the default was not to do so. To get the old + default behavior, set the configuration option + SQL_Inheritance to off, e.g., + + SET SQL_Inheritance TO OFF; + + or add a line in your postgresql.conf file. + + + + + + + Constraint Exclusion and Partitioning + + + partitioning + + + + constraint exclusion + + + + PostgreSQL provides features required + to support basic table partitioning. This section describes why and how + you implement this as part of your database design. + + + + In &version; the following ways of partitioning are supported: + + + + "Range Partitioning" where the table is partitioned along a "range" + defined by a single column or set of columns, with no overlap between + partitions. Examples might be a date range (e.g. LogDate) or a range of + identifiers for particular business objects (e.g. OrderId). + + + + + + "List Partitioning" where the table is partitioned by explicitly listing + which values relate to each partition. + + + + + + Mixed range and list partitioning. No restrictions are placed on the + number or complexity of the partitioning constraints. + + + + + + Hash partitioning is not currently supported. + + + + + + + Currently, partitioning is implemented in conjunction with table + inheritance only, though using fully SQL:2003 compliant syntax. + Table inheritance gives us the ability to split up a table into partitions + and the Constraint Exclusion feature provides the ability to selectively + bring partitions back together in response to particular SQL. + You should be familiar with Inheritance (see ) + before attempting to implement partitioning. + + + + + Implementing Partitioning + + + Partitioning a table is a straightforward process. The + reasons and ways to use partitioning are a rather more complex topic + and will be addressed primarily through the examples in this section. + + + + + + + Create a the "master" table, from which all of the partitions will + inherit. + + + This table will contain no data. Do not define any + constraints or keys on this table at this time, unless you intend them + to be applied equally to all partitions. + + + + + + Create several "child" tables using table inheritance. These child + tables are the partitions. It is advisable to devise a progressive naming + scheme for the partitions. + + + + + + Add table constraints to define the allowed values in each partition. + + + Only clauses of the form [COLUMN] [OPERATOR] [CONSTANT(s)] will be used + for constraint exclusion. Simple examples would be: + + CHECK ( x = 1 ) + CHECK ( county IN ('Oxfordshire','Buckinghamshire','Warwickshire')) + CHECK ( outletID BETWEEN 1 AND 99 ) + + + These can be linked together with boolean operators AND and OR to + form complex constraints. Note that there is no difference in syntax + between Range and List Partitioning mechanisms; those terms are + descriptive only. Ensure that the set of values in each child table + do not overlap. + + + + + + Add any other indexes you want on the partitions, bearing in mind + that it is always more efficient to add them after data has been + loaded into the partition. + + + + + + (optional) Define a RULE or trigger which tells the database which + partition in which to put new data. + + + + + + + + Note that partitions are normal PostgreSQL tables, so all the normal + conditions apply as specified in the rest of this manual. + + + + Now lets look at the process again using a worked example of a database + for an ice cream company. The company measures peak temperatures every day + as well as ice cream sales in each region. They have two tables: + + + CREATE TABLE cities ( + id int not null, + name text not null, + altitude int -- (in ft) + ); + + CREATE TABLE measurement ( + city_id int not null + logdate date not null, + peaktemp int + unitsales int + ); + + + As time goes on, sales data is entered. Storing data costs money, + yet older data has less value as ice cream fashions change, so we decide + to keep only 3 years data on a rolling 36 months. Each month we remove + the oldest month's data. + + + + Most queries just access the last week, month or quarter's data, + since we need to keep track of sales. As a result we have a large table, + yet only the most frequent 10% is accessed. Most of these queries + are online reports for various levels of management. These queries access + much of the table, so it is difficult to build enough indexes and at + the same time allow us to keep loading all of the data fast enough. + Yet, the reports are online so we need to respond quickly. + + + + In this situation we can use partitioning to help us optimize the design + to cope with the demands placed upon the measurement table. + + + + (Step 1) The measurement table is our master table. + + + + (Step 2) Next we create 36 month-size partitions, using the PostgreSQL + inheritance feature like this: + + + CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement); + ... + CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement); + + + Each of the tables are complete tables in their own right, but they + inherit their definition from the measurement table. + + + + This solves one of our problems: deleting older data. Each month, all + we need to do is perform a DROP TABLE on the oldest table and create + a new table to insert into. + + + + (Step 3) We now add non-overlapping table constraints, so that our table + creation script becomes: + + + CREATE TABLE measurement_yy04mm02 ( + CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy04mm03 ( + CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' ) + ) INHERITS (measurement); + ... + CREATE TABLE measurement_yy05mm11 ( + CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy05mm12 ( + CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy06mm01 ( + CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) + ) INHERITS (measurement); + + + Note that we use constants in the CHECK constraints rather than a + calculation involving INTERVALS. + + + + (Step 4) We choose not to add further indices at this time. + + + + (Step 5) Data will be added each day to the latest partition. This allows + us to set up a very simple RULE to insert data, which we change once per + month so that it always points to the current partition. + + + CREATE RULE measurement_current_partition AS + ON INSERT + TO MEASUREMENT + DO INSTEAD + INSERT INTO measurement_yy05mm11 VALUES ( NEW.city_id + ,NEW.logdate + ,NEW.peaktemp + ,NEW.unitsales ); + + + We might want to insert data and have the server automatically + locate the partition into which the row should be added. We could do this + with a more complex set of RULEs as shown below. + + + CREATE RULE measurement_insert_yy04mm02 AS + ON INSERT + TO MEASUREMENT WHERE + ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) + DO INSTEAD + INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id + ,NEW.logdate + ,NEW.peaktemp + ,NEW.unitsales ); + CREATE RULE measurement_insert_yy04mm03 AS + ON INSERT + TO MEASUREMENT WHERE + ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' ) + DO INSTEAD + INSERT INTO measurement_yy04mm03 VALUES ( NEW.city_id + ,NEW.logdate + ,NEW.peaktemp + ,NEW.unitsales ); + ... + CREATE RULE measurement_insert_yy06mm01 AS + ON INSERT + TO MEASUREMENT WHERE + ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) + DO INSTEAD + INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id + ,NEW.logdate + ,NEW.peaktemp + ,NEW.unitsales ); + + + Note that the WHERE clauses in each RULE exactly match those used + for the CHECK constraints on each partition. + + + + As we can see, a complex partitioning scheme could require a substantial + amount of DDL. In the above example we would be creating a new partition + each month, so it would be advisable to create a script/program that + generates the SQL DDL required automatically rather than manually + maintaining all of the required code. + + + + The following caveats apply: + + + + There is currently no way to specify that all of the CHECK constraints + are mutually exclusive. Care is required by the database designer. + + + + + + There is currently no way to specify that rows may not be inserted + into the master table. If you define a CHECK constraint on the master + table, then this constraint will be inherited by all child tables. All + queries will then return no rows. + + + + + + For some datatypes you must explicitly coerce the constant values + into the datatype of the column. The following constraint will + work if x is an INTEGER datatype, but not if x is BIGINT datatype. + + CHECK ( x = 1) + + For BIGINT we must use a constraint like: + + CHECK ( x = 1::bigint) + + + + + + + + + + + Constraint Exclusion in Queries + + + The following caveats apply: + + + + UPDATE/DELETE + + + + + + + + + + + + + + + + + + --=-gKXD0L5uSf8MOFkr4IsO-- From simon@2ndquadrant.com Fri Jun 5 04:52:19 2026 X-Original-To: pgsql-patches-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8AF27DB490; Mon, 31 Oct 2005 18:44:04 -0400 (AST) 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 65425-08; Mon, 31 Oct 2005 22:44:00 +0000 (GMT) Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by svr1.postgresql.org (Postfix) with ESMTP id 4DE4DDAF3C; Mon, 31 Oct 2005 18:43:58 -0400 (AST) Received: from 192.168.0.3 (unknown [84.12.200.148]) by smtp.nildram.co.uk (Postfix) with ESMTP id 7B18424C844; Mon, 31 Oct 2005 22:43:55 +0000 (GMT) Subject: Re: Partitioning docs From: Simon Riggs To: pgsql-patches@postgresql.org, pgsql-docs@postgresql.org In-Reply-To: <1130726816.8300.1377.camel@localhost.localdomain> References: <1130726816.8300.1377.camel@localhost.localdomain> Content-Type: multipart/mixed; boundary="=-cSH6kZT8cXEv0bzaLBMq" Organization: 2nd Quadrant Date: Mon, 31 Oct 2005 22:41:49 +0000 Message-Id: <1130798509.8300.1536.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.013 required=5 tests=[AWL=0.013] X-Spam-Score: 0.013 X-Spam-Level: X-Archive-Number: 200510/252 X-Sequence-Number: 17748 --=-cSH6kZT8cXEv0bzaLBMq Content-Type: text/plain Content-Transfer-Encoding: 7bit On Mon, 2005-10-31 at 02:46 +0000, Simon Riggs wrote: > I've been working on some docs for Constraining Exclusion & Partitioning > for some time now. Deadlines seem to be looming, or may even have > passed, so it seems sensible to submit what I have now. > Many thanks to Josh Berkus for providing the numbered section on > implementation process, which was the starting point I'd been looking > for to describe everything else. I believe this is now complete and ready for application. - passes sgml make against cvstip - spellchecked - all code executed correctly against RC1 Comments please? Apart from the obvious, so why did it take you so long. Apologies to the translators. Best Regards, Simon Riggs --=-cSH6kZT8cXEv0bzaLBMq Content-Disposition: attachment; filename=ce4.patch Content-Type: text/x-patch; name=ce4.patch; charset=UTF-8 Content-Transfer-Encoding: 7bit Index: ddl.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.45 diff -c -c -r1.45 ddl.sgml *** ddl.sgml 23 Oct 2005 19:29:49 -0000 1.45 --- ddl.sgml 31 Oct 2005 22:36:26 -0000 *************** *** 398,403 **** --- 398,410 ---- ensure that a column does not contain null values, the not-null constraint described in the next section can be used. + + + Check constraints can also be used to enhance performance with + very large tables, when used in conjunction with the + parameter. + This is discussed in more detail in + *************** *** 1040,1052 **** Inheritance ! This section needs to be rethought. Some of the ! information should go into the following chapters. ! Let's create two tables. The capitals table contains ! state capitals which are also cities. Naturally, the ! capitals table should inherit from cities. CREATE TABLE cities ( --- 1047,1081 ---- Inheritance ! ! not-null constraint ! ! ! ! constraint ! NOT NULL ! ! ! ! PostgreSQL was the first DBMS to introduce ! inheritance, one of its many object-relational features. ! PostgreSQL implements table ! inheritance which can be a useful tool for database designers. ! The SQL:2003 standard optionally defines type inheritance which differs ! in many respects from the features described here. ! ! Let's start with an example: ! We're trying to build a data model for cities, but we have a problem. ! Each state has many cities, but only one capital. We want to be able ! to quickly retrieve the capital city for any particular state. We ! can solve this problem by creating two tables. The capitals table contains ! state capitals, then we have another table for cities that aren't capitals. ! What happens when we want to ask for data about a city, regardless of ! whether it is a capital or not? We can use the inheritance feature to ! help resolve this problem for us. We define the capitals table so that ! it inherits from cities. CREATE TABLE cities ( *************** *** 1062,1077 **** In this case, a row of capitals inherits all attributes (name, population, and altitude) from its parent, cities. State ! capitals have an extra attribute, state, that shows their state. In ! PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendants. - - - - The inheritance hierarchy is actually a directed acyclic graph. - - --- 1091,1102 ---- In this case, a row of capitals inherits all attributes (name, population, and altitude) from its parent, cities. State ! capitals have an extra attribute, state, that shows their state. ! ! ! In PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendants. *************** *** 1133,1163 **** ! ! Deprecated ! ! In previous versions of PostgreSQL, the ! default behavior was not to include child tables in queries. This was ! found to be error prone and is also in violation of the SQL:2003 ! standard. Under the old syntax, to get the sub-tables you append ! * to the table name. ! For example ! ! SELECT * from cities*; ! ! You can still explicitly specify scanning child tables by appending ! *, as well as explicitly specify not scanning child tables by ! writing ONLY. But beginning in version 7.1, the default ! behavior for an undecorated table name is to scan its child tables ! too, whereas before the default was not to do so. To get the old ! default behavior, set the configuration option ! SQL_Inheritance to off, e.g., ! ! SET SQL_Inheritance TO OFF; ! ! or add a line in your postgresql.conf file. ! ! In some cases you may wish to know which table a particular row --- 1158,1180 ---- ! ! Inheritance does not automatically propogate data from INSERT or COPY ! commands to other tables in the inheritance hierarchy. Inheritance ! does not influence these commands. ! In our example, the following INSERT statement will fail. ! ! INSERT INTO cities ! (name, population, altitude, state) ! VALUES ('New York', NULL, NULL, 'NY'); ! ! We might hope that the data would be somehow routed to the capitals table, ! though this does not happen. If the child has no locally defined ! columns, then it is possible to route data from the parent to the child ! using a RULE, see . ! This is not possible with the above INSERT statement ! because the state column does not exist on both parent and child tables. ! In some cases you may wish to know which table a particular row *************** *** 1204,1220 **** ! A table can inherit from more than one parent table, in which case it has ! the union of the columns defined by the parent tables (plus any columns ! declared specifically for the child table). ! A serious limitation of the inheritance feature is that indexes (including ! unique constraints) and foreign key constraints only apply to single ! tables, not to their inheritance children. This is true on both the ! referencing and referenced sides of a foreign key constraint. Thus, ! in the terms of the above example: --- 1221,1287 ---- ! As shown above, a child table may locally define columns as ! well as inheriting them from their parents. ! However, polymorphism of table datatypes is not supported: a locally defined ! column cannot override the datatype of an inherited column of the same name. ! ! ! ! A table can inherit from a table that has itself inherited from ! other tables. A table can also inherit from more than one parent ! table, in which case it has the union of the columns defined by the parent ! tables (plus any columns declared locally for the child table). ! Inherited columns with duplicate names and datatypes will be merged so ! that only a single column is stored. An inheritance definition that ! results in a column with more than one datatype would not succeed. ! The full inheritance hierarchy is actually a directed acyclic graph of ! arbitrary complexity. You should note that complex inheritance hierarchies ! will cause a corresponding increase in parsing time for your queries. ! Direct access to the specific table your query requires will always ! provide the best performance. ! Table inheritance can currently only be defined using the ! statement. ! The related statement CREATE TABLE ... AS SELECT does ! not allow the specification of an inheritance link. The ALTER TABLE statement ! does not currently support the INHERITS() clause. There is no ! way to add an inheritance link to make an existing table into a child ! table. Similarly, there is no way to remove an inheritance link from a ! child table, once defined, other than using DROP TABLE on the child table. ! A parent table cannot be dropped while any of its children remain. If you ! wish to remove a table and all of its descendants, then you can do so ! using the CASCADE option of the statement. ! ! ! ! Check constraints can be defined on tables within an inheritance ! hierarchy. All check constraints on a parent table are automatically ! inherited by all of their children. Note that is currently possible to ! inherit mutually exclusive check constraints, but that definition quickly ! shows itself since all attempted row inserts will be rejected. ! ! ! ! will propogate any changes in data ! definition on columns or check constraints down the inheritance hierarchy. ! Again, dropping columns or constraints on parent tables is only possible ! when using the CASCADE option. ALTER TABLE follows the same rules for ! duplicate column merging and rejection that apply during CREATE TABLE. ! ! ! ! Both parent and child tables can have primary and foreign keys ! defined for them, so that they can take part normally on both the ! referencing and referenced sides of a foreign key constraint. Indexes ! may be defined on any of these columns whether or not they are inherited. ! However, a serious current limitation of the inheritance feature is that ! indexes (including unique constraints) and foreign key constraints only ! apply to single tables and do not also index their inheritance children. ! This is true on both sides of a foreign key constraint. ! Thus, in the terms of the above example: *************** *** 1236,1244 **** Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to ! capitals. In this case you could work around it by ! manually adding the same REFERENCES constraint to ! capitals. --- 1303,1313 ---- Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to ! capitals. However, it is possible to set up a ! foreign key such as capitals.name ! REFERENCES states.name. ! So it is possible to workaround this restriction by manually adding ! foreign keys to each child table. *************** *** 1251,1260 **** --- 1320,1922 ---- + Some statements are not optimised in the same way for inherited tables + as they are for normal tables. Examples include: + + + + MIN/MAX aggregates with no GROUP BY + + SELECT MIN(foo) FROM bar; + + + + + + Retrieving rows from an ordered result set using an index + + SELECT foo FROM bar ORDER BY foo LIMIT 5; + + + + + + These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your problem. + + + + Deprecated + + In previous versions of PostgreSQL, the + default behavior was not to include child tables in queries. This was + found to be error prone and is also in violation of the SQL:2003 + standard. Under the old syntax, to get the sub-tables you append + * to the table name. + For example + + SELECT * from cities*; + + You can still explicitly specify scanning child tables by appending + *, as well as explicitly specify not scanning child tables by + writing ONLY. But beginning in version 7.1, the default + behavior for an undecorated table name is to scan its child tables + too, whereas before the default was not to do so. To get the old + default behavior, set the configuration option + SQL_Inheritance to off, e.g., + + SET SQL_Inheritance TO OFF; + + or add a line in your postgresql.conf file. + + + + + + + Constraint Exclusion and Partitioning + + + partitioning + + + + constraint exclusion + + + + PostgreSQL provides features required + to support basic table partitioning. This section describes why and how + you can implement this as part of your database design. + + + + Partitioning Overview + + + Currently, partitioning is implemented in conjunction with table + inheritance only, though using fully SQL:2003 compliant syntax. + Table inheritance gives us the ability to split up a table into partitions + and the Constraint Exclusion feature provides the ability to selectively + bring partitions back together in response to particular SELECT statements. + You should be familiar with Inheritance (see ) + before attempting to implement partitioning. + + + + Partitioning can provide a number of benefits. + + + + Query performance can be improved dramatically for certain kinds + of query without the need for maintaining costly indexes. + + + + + + Insert performance can be improved by breaking down a large + index into multiple pieces. When an index no longer fits easily in memory, + both read and write operations on the index take progressively more + disk accesses. + + + + + + Bulk deletes may be avoided altogether by simply removing one of the + partitions, if that requirement is planned into the partitioning design. + + + + + + Seldom used data can be migrated to cheaper and slower storage media. + + + + + + The benefits will normally be worthwhile only when a data table would + otherwise be very large. That is for you to judge, though would not + usually be lower than the size of physical RAM on the database server. + Partitioning can be of use in any type of application. Examples range from + data warehousing applications with large historical data stores through + to line-of-business applications such as order-entry, CRM or ERP systems. + + + + In &version; the following partitioning types are supported: + + + + "Range Partitioning" where the table is partitioned along a "range" + defined by a single column or set of columns, with no overlap between + partitions. Examples might be a date range (e.g. LogDate) or a range of + identifiers for particular business objects (e.g. OrderId). + + + + + + "List Partitioning" where the table is partitioned by explicitly listing + which values relate to each partition. + + + + + + Mixed range and list partitioning. No restrictions are placed on the + number or complexity of the partitioning constraints. + + + + + + Hash partitioning is not currently supported. + + + + + + + In the next section we + discuss how to create the DDL required for partitioning. We then move on + to a discussion of the Constraint Exclusion feature which allows + query performance to be improved, in + + + + + + + + Implementing Partitioning + + + Partitioning a table is a straightforward process. There + are a wide range of options for you to consider, so judging exactly + when and how to implement partitioning is a more complex topic. We + will address that complexity primarily through the examples in this + section. + + + + + + + Create the "master" table, from which all of the partitions of that + will master will inherit. + + + This table will contain no data. Do not define any + constraints or keys on this table at this time, unless you intend them + to be applied equally to all partitions. + + + + + + Create several "child" tables using table inheritance to create + identical copies of the master table. + + + We will refer to the child tables as partitions, though they are in + every way just normal PostgreSQL data tables. It is advisable to + devise a progressive naming scheme for the partitions. + + + + + + Add table constraints to define the allowed values in each partition. + + + Only clauses of the form [COLUMN] [OPERATOR] [CONSTANT(s)] will be used + for constraint exclusion. Simple examples would be: + + CHECK ( x = 1 ) + CHECK ( county IN ('Oxfordshire','Buckinghamshire','Warwickshire')) + CHECK ( outletID BETWEEN 1 AND 99 ) + + + These can be linked together with boolean operators AND and OR to + form complex constraints. Note that there is no difference in syntax + between Range and List Partitioning mechanisms; those terms are + descriptive only. Ensure that the set of values in each child table + do not overlap. + + + + + + Add any other indexes you want on the partitions, bearing in mind + that it is always more efficient to add them after data has been + loaded into the partition. + + + + + + (optional) Define a RULE or trigger which tells the database which + partition in which to put new data. + + + + + + + + + Now lets look at the process again using a worked example of a database + for a large ice cream company. The company measures peak temperatures + every day as well as ice cream sales in each region. They have two tables: + + + CREATE TABLE cities ( + id int not null, + name text not null, + altitude int -- (in ft) + ); + + CREATE TABLE measurement ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int + ); + + + As time goes on, sales data is entered. Storing data costs money, + yet older data has less value as ice cream fashions change, so we decide + to keep only 3 years data on a rolling 36 months. Each month we remove + the oldest month's data. + + + + Most queries just access the last week, month or quarter's data, + since we need to keep track of sales. As a result we have a large table, + yet only the most frequent 10% is accessed. Most of these queries + are online reports for various levels of management. These queries access + much of the table, so it is difficult to build enough indexes and at + the same time allow us to keep loading all of the data fast enough. + Yet, the reports are online so we need to respond quickly. + + + + In this situation we can use partitioning to help us meet all of our + different requirements for the measurements table. + + + + (Step 1) The measurement table is our master table. + + + + (Step 2) Next we create 36 month-size partitions, using the PostgreSQL + inheritance feature like this: + + + CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement); + ... + CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement); + + + Each of the tables are complete tables in their own right, but they + inherit their definition from the measurement table. + + + + This solves one of our problems: deleting older data. Each month, all + we need to do is perform a DROP TABLE on the oldest table and create + a new table to insert into. + + + + (Step 3) We now add non-overlapping table constraints, so that our table + creation script becomes: + + + CREATE TABLE measurement_yy04mm02 ( + CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy04mm03 ( + CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' ) + ) INHERITS (measurement); + ... + CREATE TABLE measurement_yy05mm11 ( + CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy05mm12 ( + CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy06mm01 ( + CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) + ) INHERITS (measurement); + + + Note that we use constants in the CHECK constraints rather than a + calculation involving INTERVALS. + + + + (Step 4) We choose not to add further indices at this time. + + + + (Step 5) Data will be added each day to the latest partition. This allows + us to set up a very simple RULE to insert data. We must + redefine this each month so that it always points to the current partition. + + + CREATE OR REPLACE RULE measurement_current_partition AS + ON INSERT + TO measurement + DO INSTEAD + INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id + ,NEW.logdate + ,NEW.peaktemp + ,NEW.unitsales ); + + + We might want to insert data and have the server automatically + locate the partition into which the row should be added. We could do this + with a more complex set of RULEs as shown below. + + + CREATE RULE measurement_insert_yy04mm02 AS + ON INSERT + TO MEASUREMENT WHERE + ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) + DO INSTEAD + INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id + ,NEW.logdate + ,NEW.peaktemp + ,NEW.unitsales ); + ... + CREATE RULE measurement_insert_yy05mm12 AS + ON INSERT + TO MEASUREMENT WHERE + ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) + DO INSTEAD + INSERT INTO measurement_yy05mm12 VALUES ( NEW.city_id + ,NEW.logdate + ,NEW.peaktemp + ,NEW.unitsales ); + CREATE RULE measurement_insert_yy06mm01 AS + ON INSERT + TO MEASUREMENT WHERE + ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) + DO INSTEAD + INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id + ,NEW.logdate + ,NEW.peaktemp + ,NEW.unitsales ); + + + Note that the WHERE clauses in each RULE exactly match those used + for the CHECK constraints on each partition. + + + + As we can see, a complex partitioning scheme could require a substantial + amount of DDL. In the above example we would be creating a new partition + each month, so it would be advisable to create a script/program that + generates the SQL DDL required automatically rather than manually + maintaining all of the required code. + + + + The following caveats apply: + + + + There is currently no way to specify that all of the CHECK constraints + are mutually exclusive. Care is required by the database designer. + + + + + + There is currently no way to specify that rows may not be inserted + into the master table. Any CHECK constraint on the master + table will be inherited by all child tables, so could not be used for + that purpose. + + + + + + For some datatypes you must explicitly coerce the constant values + into the datatype of the column. The following constraint will + work if x is an INTEGER datatype, but not if x is BIGINT datatype. + + CHECK ( x = 1) + + For BIGINT we must use a constraint like: + + CHECK ( x = 1::bigint) + + The issue is not restricted to BIGINT datatypes but can occur whenever + the default datatype of the constant does not match the datatype of + the column to which it is being compared. + + + + + There is currently no way to specify that rows may not be inserted + into the master table. If you define a CHECK constraint on the master + table, then this constraint will be inherited by all child tables. All + queries will then return no rows. + + + + + + Partitioning can also be arranged using a UNION ALL view, such as the + following view. + + + CREATE VIEW measurement AS + SELECT * FROM measurement_yy04mm02 + UNION ALL SELECT * FROM measurement_yy04mm03 + ... + UNION ALL SELECT * FROM measurement_yy05mm11 + UNION ALL SELECT * FROM measurement_yy05mm12 + UNION ALL SELECT * FROM measurement_yy06mm01; + + + However, in this release this means of specifying a + partitioned table does not support Constraint Exclusion and is not + recommended. + + + + + + + + + + Constraint Exclusion in Queries + + + Partitioning can be used to improve query performance when used in + conjunction with the Constraint Exclusion (CE) feature. As an example: + + + SET constraint_exclusion=true; + SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + + + Without Constraint Exclusion the above query would scan each of the + partitions of the measurement table. With Constraint Exclusion, the + planner will examine each of the constraints and try to prove that + each of the partitions needs to be involved in the query. If the planner + is able to refute that for any partition, it excludes the partition + from the query plan. + + + + You can use the EXPLAIN command to show the difference + between a plan with constraint_exclusion on and a plan with it off. + + + SET constraint_exclusion=false; + EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + + QUERY PLAN + ----------------------------------------------------------------------------------------------- + Aggregate (cost=158.66..158.68 rows=1 width=0) + -> Append (cost=0.00..151.88 rows=2715 width=0) + -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + ... + -> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + + + Now when we enable CE, we get a significantly reduced plan, yet one that + returns exactly the same answer (provably). + + + SET constraint_exclusion=true; + EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + QUERY PLAN + ----------------------------------------------------------------------------------------------- + Aggregate (cost=63.47..63.48 rows=1 width=0) + -> Append (cost=0.00..60.75 rows=1086 width=0) + -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + + + Don't forget that you still need to run ANALYZE on each partition + individually. A simple command like this + + ANALYZE measurement; + + + does not ANALYZE each of the partitions, only the master table. + + + + No indexes are required to make CE function. The partitions should be + defined with appropriate CHECK constraints + and these are able to be compared against clauses on the query. + + + + The following caveats apply to this release: + + + + CE only works when the query directly matches a constant. A + constant bound to a parameterised query will not work in the same way + since the plan is fixed and would need to vary with each execution. + Also, stable constants such as CURRENT_DATE may not be used, since + these are constant only for during the execution of a single query. + Joins conditions will not allow CE to work either. + + + + + + UPDATEs and DELETEs against the partitioning master table do + not perform constraint exclusion. One of the purposes of + partitioning is to avoid bulk DELETE statements so you are advised + to rethink your design if you attempting this. + + + + + + All constraints on all partitions of the master table are considered for + Constraint Exclusion, so large numbers of partitions are likely to + increase query parse time considerably. + + + + + + + + --=-cSH6kZT8cXEv0bzaLBMq-- From neilc@samurai.com Fri Jun 5 04:52:19 2026 X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EAC99DB5C3; Mon, 31 Oct 2005 21:02:04 -0400 (AST) 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 37070-10; Tue, 1 Nov 2005 01:02:01 +0000 (GMT) Received: from mailbox.samurai.com (mailbox.samurai.com [205.207.28.82]) by svr1.postgresql.org (Postfix) with ESMTP id 30264DB5BC; Mon, 31 Oct 2005 21:02:03 -0400 (AST) Received: from localhost (mailbox.samurai.com [205.207.28.82]) by mailbox.samurai.com (Postfix) with ESMTP id 19D872399E1; Mon, 31 Oct 2005 20:02:07 -0500 (EST) Received: from mailbox.samurai.com ([205.207.28.82]) by localhost (mailbox.samurai.com [205.207.28.82]) (amavisd-new, port 10024) with LMTP id 58108-01-10; Mon, 31 Oct 2005 20:02:05 -0500 (EST) Received: from [192.168.1.104] (d226-86-55.home.cgocable.net [24.226.86.55]) by mailbox.samurai.com (Postfix) with ESMTP id 830F42399BF; Mon, 31 Oct 2005 20:02:05 -0500 (EST) Subject: Re: [PATCHES] Partitioning docs From: Neil Conway To: Simon Riggs Cc: pgsql-patches@postgresql.org, pgsql-docs@postgresql.org In-Reply-To: <1130798509.8300.1536.camel@localhost.localdomain> References: <1130726816.8300.1377.camel@localhost.localdomain> <1130798509.8300.1536.camel@localhost.localdomain> Content-Type: text/plain Date: Mon, 31 Oct 2005 20:02:07 -0500 Message-Id: <1130806927.8561.16.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at mailbox.samurai.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200510/65 X-Sequence-Number: 3310 On Mon, 2005-31-10 at 22:41 +0000, Simon Riggs wrote: > I believe this is now complete and ready for application. The changes need a fair bit of copy editing and SGML policy work, but that is probably easier to do once it has been applied. Barring any objections I'll apply the patch within 24 hours. -Neil From tgl@sss.pgh.pa.us Fri Jun 5 04:52:19 2026 X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 468A2DB5C4 for ; Tue, 1 Nov 2005 00:15:29 -0400 (AST) 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 37255-10 for ; Tue, 1 Nov 2005 04:15:28 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id BF0EDDB5CD for ; Tue, 1 Nov 2005 00:15:27 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jA14FNmt021548; Mon, 31 Oct 2005 23:15:23 -0500 (EST) To: Neil Conway Cc: Simon Riggs , pgsql-patches@postgresql.org, pgsql-docs@postgresql.org Subject: Re: [PATCHES] Partitioning docs In-reply-to: <1130806927.8561.16.camel@localhost.localdomain> References: <1130726816.8300.1377.camel@localhost.localdomain> <1130798509.8300.1536.camel@localhost.localdomain> <1130806927.8561.16.camel@localhost.localdomain> Comments: In-reply-to Neil Conway message dated "Mon, 31 Oct 2005 20:02:07 -0500" Date: Mon, 31 Oct 2005 23:15:23 -0500 Message-ID: <21547.1130818523@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006] X-Spam-Score: 0.006 X-Spam-Level: X-Archive-Number: 200511/1 X-Sequence-Number: 3311 Neil Conway writes: > On Mon, 2005-31-10 at 22:41 +0000, Simon Riggs wrote: >> I believe this is now complete and ready for application. > The changes need a fair bit of copy editing and SGML policy work, but > that is probably easier to do once it has been applied. Barring any > objections I'll apply the patch within 24 hours. I'd argue for editing first and then applying. I'll take up the job if you don't have time for the editing part... I'm hoping to spend most of this week on docs editing anyway, since anything else will raise Marc's hackles about whether we need another RC ;-) regards, tom lane From neilc@samurai.com Fri Jun 5 04:52:19 2026 X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EFF27DB5CF; Tue, 1 Nov 2005 00:27:15 -0400 (AST) 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 25330-05; Tue, 1 Nov 2005 04:27:13 +0000 (GMT) Received: from mailbox.samurai.com (mailbox.samurai.com [205.207.28.82]) by svr1.postgresql.org (Postfix) with ESMTP id 2B520DB5AB; Tue, 1 Nov 2005 00:27:13 -0400 (AST) Received: from localhost (mailbox.samurai.com [205.207.28.82]) by mailbox.samurai.com (Postfix) with ESMTP id BA3652398D5; Mon, 31 Oct 2005 23:27:12 -0500 (EST) Received: from mailbox.samurai.com ([205.207.28.82]) by localhost (mailbox.samurai.com [205.207.28.82]) (amavisd-new, port 10024) with LMTP id 41734-01-6; Mon, 31 Oct 2005 23:27:11 -0500 (EST) Received: from [192.168.1.104] (d226-86-55.home.cgocable.net [24.226.86.55]) by mailbox.samurai.com (Postfix) with ESMTP id 022B72398D3; Mon, 31 Oct 2005 23:27:08 -0500 (EST) Subject: Re: [PATCHES] Partitioning docs From: Neil Conway To: Tom Lane Cc: Simon Riggs , pgsql-patches@postgresql.org, pgsql-docs@postgresql.org In-Reply-To: <21547.1130818523@sss.pgh.pa.us> References: <1130726816.8300.1377.camel@localhost.localdomain> <1130798509.8300.1536.camel@localhost.localdomain> <1130806927.8561.16.camel@localhost.localdomain> <21547.1130818523@sss.pgh.pa.us> Content-Type: text/plain Date: Mon, 31 Oct 2005 23:27:36 -0500 Message-Id: <1130819256.8561.24.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at mailbox.samurai.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/2 X-Sequence-Number: 3312 On Mon, 2005-31-10 at 23:15 -0500, Tom Lane wrote: > I'd argue for editing first and then applying. I'll take up the job > if you don't have time for the editing part Okay. I'll do a round of copy editing and then commit to CVS -- there will likely be room for additional improvements, so once it's in CVS anyone else who's interested can have at it. -Neil From simon@2ndquadrant.com Fri Jun 5 04:52:19 2026 X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8EE77DB608; Tue, 1 Nov 2005 03:22:11 -0400 (AST) 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 13102-02; Tue, 1 Nov 2005 07:22:06 +0000 (GMT) Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by svr1.postgresql.org (Postfix) with ESMTP id 8FD7DDB623; Tue, 1 Nov 2005 03:22:06 -0400 (AST) Received: from 192.168.0.3 (unknown [84.12.200.148]) by smtp.nildram.co.uk (Postfix) with ESMTP id DFD1F24D57E; Tue, 1 Nov 2005 07:22:04 +0000 (GMT) Subject: Re: [PATCHES] Partitioning docs From: Simon Riggs To: Neil Conway Cc: Tom Lane , pgsql-patches@postgresql.org, pgsql-docs@postgresql.org In-Reply-To: <1130819256.8561.24.camel@localhost.localdomain> References: <1130726816.8300.1377.camel@localhost.localdomain> <1130798509.8300.1536.camel@localhost.localdomain> <1130806927.8561.16.camel@localhost.localdomain> <21547.1130818523@sss.pgh.pa.us> <1130819256.8561.24.camel@localhost.localdomain> Content-Type: text/plain Organization: 2nd Quadrant Date: Tue, 01 Nov 2005 07:19:51 +0000 Message-Id: <1130829591.8300.1552.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.012 required=5 tests=[AWL=0.012] X-Spam-Score: 0.012 X-Spam-Level: X-Archive-Number: 200511/3 X-Sequence-Number: 3313 On Mon, 2005-10-31 at 23:27 -0500, Neil Conway wrote: > On Mon, 2005-31-10 at 23:15 -0500, Tom Lane wrote: > > I'd argue for editing first and then applying. I'll take up the job > > if you don't have time for the editing part > > Okay. I'll do a round of copy editing and then commit to CVS -- there > will likely be room for additional improvements, so once it's in CVS > anyone else who's interested can have at it. Thanks guys. Best Regards, Simon Riggs From neilc@samurai.com Fri Jun 5 04:52:19 2026 X-Original-To: pgsql-patches-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 022AADB717; Tue, 1 Nov 2005 19:19:42 -0400 (AST) 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 30740-01; Tue, 1 Nov 2005 23:19:37 +0000 (GMT) Received: from mailbox.samurai.com (mailbox.samurai.com [205.207.28.82]) by svr1.postgresql.org (Postfix) with ESMTP id EFDD7DB684; Tue, 1 Nov 2005 19:19:37 -0400 (AST) Received: from localhost (mailbox.samurai.com [205.207.28.82]) by mailbox.samurai.com (Postfix) with ESMTP id 72CAC23985B; Tue, 1 Nov 2005 18:19:40 -0500 (EST) Received: from mailbox.samurai.com ([205.207.28.82]) by localhost (mailbox.samurai.com [205.207.28.82]) (amavisd-new, port 10024) with LMTP id 48543-01; Tue, 1 Nov 2005 18:19:38 -0500 (EST) Received: from [192.168.1.104] (d226-86-55.home.cgocable.net [24.226.86.55]) by mailbox.samurai.com (Postfix) with ESMTP id 96B9523985A; Tue, 1 Nov 2005 18:19:36 -0500 (EST) Subject: Re: Partitioning docs From: Neil Conway To: Simon Riggs Cc: pgsql-patches@postgresql.org, pgsql-docs@postgresql.org In-Reply-To: <1130798509.8300.1536.camel@localhost.localdomain> References: <1130726816.8300.1377.camel@localhost.localdomain> <1130798509.8300.1536.camel@localhost.localdomain> Content-Type: multipart/mixed; boundary="=-3E2RifVd8aZiK+zRBBgu" Date: Tue, 01 Nov 2005 18:19:37 -0500 Message-Id: <1130887177.6884.38.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 X-Virus-Scanned: by amavisd-new at mailbox.samurai.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/7 X-Sequence-Number: 17756 --=-3E2RifVd8aZiK+zRBBgu Content-Type: text/plain Content-Transfer-Encoding: 7bit On Mon, 2005-31-10 at 22:41 +0000, Simon Riggs wrote: > I believe this is now complete and ready for application. Comments: - INSERT, UPDATE, etc. should be marked with , unless would be more appropriate - The names of GUC variables should be marked up with , unless would be more appropriate - tags that link to the reference page of an SQL command should be of the form: -- the endterm attribute should not be omitted. - "PostgreSQL" should be marked-up with - In text like "You can use RULEs to ...", "rules" would be better. - The word following a colon should not be capitalized - "—" is an em dash, "--" and "---" are not - "indexes", not "indices" - Why "Constraint Exclusion" (or worse, "the Constraint Exclusion feature") rather than simply "constraint exclusion"? (I'm not even sure it's a good idea to mention this term in end-user documentation.) - I removed a few statements and paragraphs I thought were unnecessary (e.g. Postgres was the first DBMS to have inheritance, some vague and IMHO useless advice about query optimization differences with inherited tables, etc.). Feel free to resubmit them if you disagree (although perhaps not for 8.1.0). + All constraints on all partitions of the master table are considered for + Constraint Exclusion, so large numbers of partitions are likely to + increase query parse time considerably. Wouldn't it primarily increase planning time, not parsing time? + + CE only works when the query directly matches a constant. A + constant bound to a parameterised query will not work in the same way + since the plan is fixed and would need to vary with each execution. + Also, stable constants such as CURRENT_DATE may not be used, since + these are constant only for during the execution of a single query. + Joins conditions will not allow CE to work either. + I'm not sure what the last sentence is intended to mean. Revised patch attached and applied. There are at least a few more things that need cleaning up -- if no one beats me to it I'll do that shortly. -Neil --=-3E2RifVd8aZiK+zRBBgu Content-Disposition: attachment; filename=ce5.patch Content-Type: text/x-patch; name=ce5.patch; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Index: doc/src/sgml/config.sgml =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.33 diff -c -r1.33 config.sgml *** doc/src/sgml/config.sgml 26 Oct 2005 12:55:07 -0000 1.33 --- doc/src/sgml/config.sgml 1 Nov 2005 22:54:16 -0000 *************** *** 1974,1984 **** ! When this parameter is on, the planner compares query ! conditions with table CHECK constraints, and omits scanning tables ! where the conditions contradict the constraints. (Presently ! this is done only for child tables of inheritance scans.) For ! example: CREATE TABLE parent(key integer, ...); --- 1974,1984 ---- ! When this parameter is on, the planner compares ! query conditions with table CHECK constraints, and ! omits scanning tables where the conditions contradict the ! constraints. (Presently this is done only for child tables of ! inheritance scans.) For example: CREATE TABLE parent(key integer, ...); *************** *** 1988,2010 **** SELECT * FROM parent WHERE key = 2400; ! With constraint exclusion enabled, this SELECT will not scan ! child1000 at all. This can improve performance when ! inheritance is used to build partitioned tables. ! Currently, constraint_exclusion defaults to ! off, because it risks incorrect results if ! query plans are cached --- if a table constraint is changed or dropped, ! the previously generated plan might now be wrong, and there is no ! built-in mechanism to force re-planning. (This deficiency will ! probably be addressed in a future ! PostgreSQL release.) Another reason ! for keeping it off is that the constraint checks are relatively expensive, and in many circumstances will yield no savings. ! It is recommended to turn this on only if you are actually using ! partitioned tables designed to take advantage of the feature. --- 1988,2017 ---- SELECT * FROM parent WHERE key = 2400; ! With constraint exclusion enabled, this SELECT ! will not scan child1000 at all. This can ! improve performance when inheritance is used to build ! partitioned tables. ! Currently, constraint_exclusion is disabled by ! default because it risks incorrect results if query plans are ! cached — if a table constraint is changed or dropped, ! the previously generated plan might now be wrong, and there is ! no built-in mechanism to force re-planning. (This deficiency ! will probably be addressed in a future ! PostgreSQL release.) Another reason for ! keeping it off is that the constraint checks are relatively expensive, and in many circumstances will yield no savings. ! It is recommended to turn this on only if you are actually ! using partitioned tables designed to take advantage of the ! feature. ! ! ! ! Refer to for more information ! on using constraint exclusion and partitioning. Index: doc/src/sgml/ddl.sgml =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.45 diff -c -r1.45 ddl.sgml *** doc/src/sgml/ddl.sgml 23 Oct 2005 19:29:49 -0000 1.45 --- doc/src/sgml/ddl.sgml 1 Nov 2005 23:10:19 -0000 *************** *** 398,403 **** --- 398,410 ---- ensure that a column does not contain null values, the not-null constraint described in the next section can be used. + + + Check constraints can also be used to enhance performance with + very large tables, when used in conjunction with the parameter. This is discussed + in more detail in . + *************** *** 1040,1058 **** Inheritance ! This section needs to be rethought. Some of the ! information should go into the following chapters. ! Let's create two tables. The capitals table contains ! state capitals which are also cities. Naturally, the ! capitals table should inherit from cities. CREATE TABLE cities ( name text, population float, ! altitude int -- (in ft) ); CREATE TABLE capitals ( --- 1047,1085 ---- Inheritance ! ! not-null constraint ! ! ! ! constraint ! NOT NULL ! ! ! ! PostgreSQL implements table inheritance ! which can be a useful tool for database designers. The SQL:2003 ! standard optionally defines type inheritance which differs in many ! respects from the features described here. ! ! Let's start with an example: suppose we are trying to build a data ! model for cities. Each state has many cities, but only one ! capital. We want to be able to quickly retrieve the capital city ! for any particular state. This can be done by creating two tables, ! one for state capitals and one for cities that are not ! capitals. However, what happens when we want to ask for data about ! a city, regardless of whether it is a capital or not? The ! inheritance feature can help to resolve this problem. We define the ! capitals table so that it inherits from ! cities: CREATE TABLE cities ( name text, population float, ! altitude int -- in feet ); CREATE TABLE capitals ( *************** *** 1060,1083 **** ) INHERITS (cities); ! In this case, a row of capitals inherits all ! attributes (name, population, and altitude) from its parent, cities. State ! capitals have an extra attribute, state, that shows their state. In ! PostgreSQL, a table can inherit from zero or ! more other tables, and a query can reference either all rows of a table or ! all rows of a table plus all of its descendants. ! ! ! ! The inheritance hierarchy is actually a directed acyclic graph. ! ! ! For example, the following query finds the names of all cities, ! including state capitals, that are located at an altitude ! over 500ft: SELECT name, altitude --- 1087,1105 ---- ) INHERITS (cities); ! In this case, a row of capitals inherits ! all the columns of its parent table, cities. State ! capitals have an extra attribute, state, that shows ! their state. ! In PostgreSQL, a table can inherit from ! zero or more other tables, and a query can reference either all ! rows of a table or all rows of a table plus all of its descendants. ! For example, the following query finds the names of all cities, ! including state capitals, that are located at an altitude over ! 500ft: SELECT name, altitude *************** *** 1097,1105 **** ! On the other hand, the following query finds ! all the cities that are not state capitals and ! are situated at an altitude over 500ft: SELECT name, altitude --- 1119,1126 ---- ! On the other hand, the following query finds all the cities that ! are not state capitals and are situated at an altitude over 500ft: SELECT name, altitude *************** *** 1110,1169 **** -----------+---------- Las Vegas | 2174 Mariposa | 1953 ! ! Here the ONLY before cities indicates that the query should ! be run over only cities and not tables below cities in the ! inheritance hierarchy. Many of the commands that we ! have already discussed -- SELECT, ! UPDATE and DELETE -- ! support this ONLY notation. - - - Inheritance and Permissions - - Because permissions are not inherited automatically a user attempting to access - a parent table must either have at least the same permission for the child table - or must use the ONLY notation. If creating a new inheritance - relationship in an existing system be careful that this does not create problems. - - - ! Deprecated ! In previous versions of PostgreSQL, the ! default behavior was not to include child tables in queries. This was ! found to be error prone and is also in violation of the SQL:2003 ! standard. Under the old syntax, to get the sub-tables you append ! * to the table name. ! For example ! ! SELECT * from cities*; ! ! You can still explicitly specify scanning child tables by appending ! *, as well as explicitly specify not scanning child tables by ! writing ONLY. But beginning in version 7.1, the default ! behavior for an undecorated table name is to scan its child tables ! too, whereas before the default was not to do so. To get the old ! default behavior, set the configuration option ! SQL_Inheritance to off, e.g., ! ! SET SQL_Inheritance TO OFF; ! ! or add a line in your postgresql.conf file. ! In some cases you may wish to know which table a particular row ! originated from. There is a system column called ! tableoid in each table which can tell you the ! originating table: SELECT c.tableoid, c.name, c.altitude --- 1131,1185 ---- -----------+---------- Las Vegas | 2174 Mariposa | 1953 ! ! Here the ONLY keyword indicates that the query ! should apply only to cities, and not any tables ! below cities in the inheritance hierarchy. Many ! of the commands that we have already discussed — ! SELECT, UPDATE and ! DELETE — support the ! ONLY keyword. ! Inheritance and Permissions ! Because permissions are not inherited automatically, a user ! attempting to access a parent table must either have at least the ! same permission for the child table or must use the ! ONLY notation. If creating a new inheritance ! relationship in an existing system be careful that this does not ! create problems. ! Inheritance does not automatically propogate data from ! INSERT or COPY commands to ! other tables in the inheritance hierarchy. In our example, the ! following INSERT statement will fail: ! ! INSERT INTO cities ! (name, population, altitude, state) ! VALUES ('New York', NULL, NULL, 'NY'); ! ! We might hope that the data would be somehow routed to the ! capitals table, though this does not happen. If ! the child has no locally defined columns, then it is possible to ! route data from the parent to the child using a rule, see . This is not possible with the above ! INSERT statement because the state column ! does not exist on both parent and child tables. ! ! ! ! In some cases you may wish to know which table a particular row ! originated from. There is a system column called ! tableoid in each table which can tell you the ! originating table: SELECT c.tableoid, c.name, c.altitude *************** *** 1200,1220 **** cities | Mariposa | 1953 capitals | Madison | 845 - ! A table can inherit from more than one parent table, in which case it has ! the union of the columns defined by the parent tables (plus any columns ! declared specifically for the child table). ! A serious limitation of the inheritance feature is that indexes (including ! unique constraints) and foreign key constraints only apply to single ! tables, not to their inheritance children. This is true on both the ! referencing and referenced sides of a foreign key constraint. Thus, ! in the terms of the above example: --- 1216,1279 ---- cities | Mariposa | 1953 capitals | Madison | 845 ! As shown above, a child table may locally define columns as well as ! inheriting them from their parents. However, a locally defined ! column cannot override the datatype of an inherited column of the ! same name. A table can inherit from a table that has itself ! inherited from other tables. A table can also inherit from more ! than one parent table, in which case it inherits the union of the ! columns defined by the parent tables. Inherited columns with ! duplicate names and datatypes will be merged so that only a single ! column is stored. ! ! ! ! Table inheritance can currently only be defined using the ! statement. The related statement CREATE TABLE ... AS ! SELECT does not allow inheritance to be specified. There ! is no way to add an inheritance link to make an existing table into ! a child table. Similarly, there is no way to remove an inheritance ! link from a child table once it has been defined, other than using ! DROP TABLE. A parent table cannot be dropped ! while any of its children remain. If you wish to remove a table and ! all of its descendants, then you can do so using the ! CASCADE option of the statement. ! ! ! ! Check constraints can be defined on tables within an inheritance ! hierarchy. All check constraints on a parent table are ! automatically inherited by all of their children. It is currently ! possible to inherit mutually exclusive check constraints, but that ! definition quickly shows itself since all attempted row inserts ! will be rejected. ! ! ! ! will ! propogate any changes in data definition on columns or check ! constraints down the inheritance hierarchy. Again, dropping ! columns or constraints on parent tables is only possible when using ! the CASCADE option. ALTER ! TABLE follows the same rules for duplicate column merging ! and rejection that apply during CREATE TABLE. ! Both parent and child tables can have primary and foreign keys, so ! that they can take part normally on both the referencing and ! referenced sides of a foreign key constraint. Indexes may be ! defined on any of these columns whether or not they are inherited. ! However, a serious current limitation of the inheritance feature is ! that indexes (including unique constraints) and foreign key ! constraints only apply to single tables and do not also index their ! inheritance children. This is true on both sides of a foreign key ! constraint. Thus, in the terms of the above example: *************** *** 1236,1244 **** Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to ! capitals. In this case you could work around it by ! manually adding the same REFERENCES constraint to ! capitals. --- 1295,1305 ---- Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to ! capitals. However, it is possible to set up a ! foreign key such as capitals.name ! REFERENCES states.name. ! So it is possible to workaround this restriction by manually adding ! foreign keys to each child table. *************** *** 1254,1260 **** --- 1315,1870 ---- These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your problem. + + + + Deprecated + + In previous versions of PostgreSQL, the + default behavior was not to include child tables in queries. This was + found to be error prone and is also in violation of the SQL:2003 + standard. Under the old syntax, to get the sub-tables you append + * to the table name. For example: + + SELECT * from cities*; + + You can still explicitly specify scanning child tables by + appending *, as well as explicitly specify not + scanning child tables by writing ONLY. But + beginning in version 7.1, the default behavior for an undecorated + table name is to scan its child tables too, whereas before the + default was not to do so. To get the old default behavior, + disable the configuration + option. + + + + + + + Constraint Exclusion and Partitioning + + + partitioning + + + + constraint exclusion + + + + PostgreSQL supports basic table + partitioning. This section describes why and how you can implement + this as part of your database design. + + + + Overview + + + Currently, partitioning is implemented in conjunction with table + inheritance only, though using fully SQL:2003 compliant syntax. + Table inheritance allows tables to be split into partitions, and + constraint exclusion allows partitions to be selectively combined + as needed to satisfy a particular SELECT + statement. You should be familiar with inheritance (see ) before attempting to implement + partitioning. + + + + Partitioning can provide several benefits: + + + + Query performance can be improved dramatically for certain kinds + of queries without the need to maintain costly indexes. + + + + + + Insert performance can be improved by breaking down a large + index into multiple pieces. When an index no longer fits easily + in memory, both read and write operations on the index take + progressively more disk accesses. + + + + + + Bulk deletes may be avoided altogether by simply removing one of the + partitions, if that requirement is planned into the partitioning design. + + + + + + Seldom-used data can be migrated to cheaper and slower storage media. + + + + + The benefits will normally be worthwhile only when a data table would + otherwise be very large. That is for you to judge, though would not + usually be lower than the size of physical RAM on the database server. + + + + In PostgreSQL &version;, the following + partitioning types are supported: + + + + + "Range Partitioning" where the table is partitioned along a + "range" defined by a single column or set of columns, with no + overlap between partitions. Examples might be a date range or a + range of identifiers for particular business objects. + + + + + + "List Partitioning" where the table is partitioned by + explicitly listing which values relate to each partition. + + + + + Hash partitioning is not currently supported. + + + + + Implementing Partitioning + + + Partitioning a table is a straightforward process. There + are a wide range of options for you to consider, so judging exactly + when and how to implement partitioning is a more complex topic. We + will address that complexity primarily through the examples in this + section. + + + + To use partitioning, do the following: + + + + Create the master table, from which all of the + partitions will inherit. + + + This table will contain no data. Do not define any + constraints or keys on this table, unless you intend them to + be applied equally to all partitions. + + + + + + Create several child tables that inherit from + the master table. + + + + We will refer to the child tables as partitions, though they + are in every way just normal PostgreSQL + tables. + + + + + + Add table constraints to define the allowed values in each partition. + + + Only clauses of the form [COLUMN] [OPERATOR] [CONSTANT(s)] will be used + for constraint exclusion. Simple examples would be: + + CHECK ( x = 1 ) + CHECK ( county IN ('Oxfordshire','Buckinghamshire','Warwickshire')) + CHECK ( outletID BETWEEN 1 AND 99 ) + + + These can be linked together with boolean operators AND and OR to + form complex constraints. Note that there is no difference in syntax + between Range and List Partitioning mechanisms; those terms are + descriptive only. Ensure that the set of values in each child table + do not overlap. + + + + + + Add any other indexes you want to the partitions, bearing in + mind that it is always more efficient to add indexes after + data has been bulk loaded. + + + + + + Optionally, define a rule or trigger to redirect modifications + of the master table to the appropriate partition. + + + + + + + + For example, suppose we are constructing a database for a large + ice cream company. The company measures peak temperatures every + day as well as ice cream sales in each region. They have two + tables: + + + CREATE TABLE cities ( + id int not null, + name text not null, + altitude int -- in feet + ); + + CREATE TABLE measurement ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int + ); + + + To reduce the amount of old data that needs to be stored, we + decide to only keep the most recent 3 years worth of data. At the + beginning of each month we remove the oldest month's data. + + + + Most queries just access the last week, month or quarter's data, + since we need to keep track of sales. As a result we have a large table, + yet only the most frequent 10% is accessed. Most of these queries + are online reports for various levels of management. These queries access + much of the table, so it is difficult to build enough indexes and at + the same time allow us to keep loading all of the data fast enough. + Yet, the reports are online so we need to respond quickly. + + + + In this situation we can use partitioning to help us meet all of our + different requirements for the measurements table. Following the + steps outlined above, partitioning can be enabled as follows: + + + + + + + The measurement table is our master table. + + + + + + Next we create one partition for each month using inheritance: + + + CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement); + ... + CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement); + + + Each of the partitions are complete tables in their own right, + but they inherit their definition from the measurement table. + + + + This solves one of our problems: deleting old data. Each + month, all we need to do is perform a DROP + TABLE on the oldest table and create a new table to + insert into. + + + + + + We now add non-overlapping table constraints, so that our + table creation script becomes: + + + CREATE TABLE measurement_yy04mm02 ( + CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy04mm03 ( + CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' ) + ) INHERITS (measurement); + ... + CREATE TABLE measurement_yy05mm11 ( + CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy05mm12 ( + CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy06mm01 ( + CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) + ) INHERITS (measurement); + + + + + + + We choose not to add further indexes at this time. + + + + + + Data will be added each day to the latest partition. This + allows us to set up a very simple rule to insert data. We must + redefine this each month so that it always points to the + current partition. + + + CREATE OR REPLACE RULE measurement_current_partition AS + ON INSERT + TO measurement + DO INSTEAD + INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id, + NEW.logdate, + NEW.peaktemp, + NEW.unitsales ); + + + We might want to insert data and have the server automatically + locate the partition into which the row should be added. We + could do this with a more complex set of rules as shown below. + + + CREATE RULE measurement_insert_yy04mm02 AS + ON INSERT + TO measurement WHERE + ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) + DO INSTEAD + INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id, + NEW.logdate, + NEW.peaktemp, + NEW.unitsales ); + ... + CREATE RULE measurement_insert_yy05mm12 AS + ON INSERT + TO measurement WHERE + ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) + DO INSTEAD + INSERT INTO measurement_yy05mm12 VALUES ( NEW.city_id, + NEW.logdate, + NEW.peaktemp, + NEW.unitsales ); + CREATE RULE measurement_insert_yy06mm01 AS + ON INSERT + TO measurement WHERE + ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) + DO INSTEAD + INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id, + NEW.logdate, + NEW.peaktemp, + NEW.unitsales ); + + + Note that the WHERE clause in each rule + exactly matches those used for the CHECK + constraints on each partition. + + + + + + + As we can see, a complex partitioning scheme could require a + substantial amount of DDL. In the above example we would be + creating a new partition each month, so it may be wise to write a + script that generates the required DDL automatically. + + + + The following caveats apply: + + + + There is currently no way to specify that all of the + CHECK constraints are mutually + exclusive. Care is required by the database designer. + + + + + + There is currently no way to specify that rows may not be + inserted into the master table. A CHECK + constraint on the master table will be inherited by all child + tables, so that cannot not be used for this purpose. + + + + + + For some datatypes you must explicitly coerce the constant values + into the datatype of the column. The following constraint will + work if x is an INTEGER datatype, but not if x is BIGINT datatype. + + CHECK ( x = 1 ) + + For BIGINT we must use a constraint like: + + CHECK ( x = 1::bigint ) + + The issue is not restricted to BIGINT datatypes but can occur whenever + the default datatype of the constant does not match the datatype of + the column to which it is being compared. + + + + + + Partitioning can also be arranged using a UNION + ALL view: + + + CREATE VIEW measurement AS + SELECT * FROM measurement_yy04mm02 + UNION ALL SELECT * FROM measurement_yy04mm03 + ... + UNION ALL SELECT * FROM measurement_yy05mm11 + UNION ALL SELECT * FROM measurement_yy05mm12 + UNION ALL SELECT * FROM measurement_yy06mm01; + + + However, constraint exclusion is currently not supported for + partitioned tables defined in this manner. + + + + + + + + Constraint Exclusion in Queries + + + Partitioning can be used to improve query performance when used in + conjunction with constraint exclusion. As an example: + + + SET constraint_exclusion=true; + SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + + + Without constraint exclusion, the above query would scan each of + the partitions of the measurement table. With constraint + exclusion, the planner will examine each of the constraints and + try to prove that each of the partitions needs to be involved in + the query. If the planner is able to refute that for any + partition, it excludes the partition from the query plan. + + + + You can use the EXPLAIN command to show the difference + between a plan with constraint_exclusion on and a plan + with it off. + + + SET constraint_exclusion=false; + EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + + QUERY PLAN + ----------------------------------------------------------------------------------------------- + Aggregate (cost=158.66..158.68 rows=1 width=0) + -> Append (cost=0.00..151.88 rows=2715 width=0) + -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + ... + -> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + + + Now when we enable constraint exclusion, we get a significantly + reduced plan but the same result set: + + + SET constraint_exclusion=true; + EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + QUERY PLAN + ----------------------------------------------------------------------------------------------- + Aggregate (cost=63.47..63.48 rows=1 width=0) + -> Append (cost=0.00..60.75 rows=1086 width=0) + -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + + + Don't forget that you still need to run ANALYZE + on each partition individually. A command like this + + ANALYZE measurement; + + + only affects the master table. + + + + No indexes are required to use constraint exclusion. The + partitions should be defined with appropriate CHECK + constraints. These are then compared with the predicates of the + SELECT query to determine which partitions must be + scanned. + + + + The following caveats apply to this release: + + + + Constraint exclusion only works when the query directly matches + a constant. A constant bound to a parameterised query will not + work in the same way since the plan is fixed and would need to + vary with each execution. Also, stable constants such as + CURRENT_DATE may not be used, since these are + constant only for during the execution of a single query. Join + conditions will not allow constraint exclusion to work either. + + + + + + UPDATEs and DELETEs against the master table do not perform + constraint exclusion. + + + + + + All constraints on all partitions of the master table are considered for + constraint exclusion, so large numbers of partitions are likely to + increase query planning time considerably. + + + + + + + + *************** *** 1530,1536 **** ! Privileges --- 2140,2146 ---- ! Privileges *************** *** 1953,1959 **** schema. To allow that, the CREATE privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE privileges on ! the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. If you do --- 2563,2569 ---- schema. To allow that, the CREATE privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE privileges on ! the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. If you do --=-3E2RifVd8aZiK+zRBBgu-- From simon@2ndquadrant.com Fri Jun 5 04:52:19 2026 X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B435AD9644; Wed, 2 Nov 2005 15:57:36 -0400 (AST) 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 23040-05; Wed, 2 Nov 2005 19:57:34 +0000 (GMT) Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by svr1.postgresql.org (Postfix) with ESMTP id 014DCD9595; Wed, 2 Nov 2005 15:57:34 -0400 (AST) Received: from 192.168.0.3 (unknown [84.12.200.148]) by smtp.nildram.co.uk (Postfix) with ESMTP id 4B19524D99C; Wed, 2 Nov 2005 19:57:32 +0000 (GMT) Subject: Re: [PATCHES] Partitioning docs From: Simon Riggs To: Neil Conway Cc: pgsql-patches@postgresql.org, pgsql-docs@postgresql.org In-Reply-To: <1130887177.6884.38.camel@localhost.localdomain> References: <1130726816.8300.1377.camel@localhost.localdomain> <1130798509.8300.1536.camel@localhost.localdomain> <1130887177.6884.38.camel@localhost.localdomain> Content-Type: text/plain Organization: 2nd Quadrant Date: Wed, 02 Nov 2005 19:55:18 +0000 Message-Id: <1130961318.8300.1794.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.011 required=5 tests=[AWL=0.011] X-Spam-Score: 0.011 X-Spam-Level: X-Archive-Number: 200511/4 X-Sequence-Number: 3314 On Tue, 2005-11-01 at 18:19 -0500, Neil Conway wrote: > On Mon, 2005-31-10 at 22:41 +0000, Simon Riggs wrote: > > I believe this is now complete and ready for application. > > Comments: > > - INSERT, UPDATE, etc. should be marked with , unless > would be more appropriate > > - The names of GUC variables should be marked up with , unless > would be more appropriate > > - tags that link to the reference page of an SQL command should > be of the form: -- the > endterm attribute should not be omitted. > > - "PostgreSQL" should be marked-up with > > - In text like "You can use RULEs to ...", "rules" would be better. > > - The word following a colon should not be capitalized > > - "—" is an em dash, "--" and "---" are not > > - "indexes", not "indices" Thanks very much for a thorough review. > - Why "Constraint Exclusion" (or worse, "the Constraint Exclusion > feature") rather than simply "constraint exclusion"? OK > (I'm not even sure > it's a good idea to mention this term in end-user documentation.) We now have a parameter called constraint_exclusion, so the term already exists and so requires explanation. I would have had no objection to modifications of that term, but it has been in use now for 4 months, so changing it doesn't seem practical. > - I removed a few statements and paragraphs I thought were unnecessary > (e.g. Postgres was the first DBMS to have inheritance, some vague and > IMHO useless advice about query optimization differences with inherited > tables, etc.). Feel free to resubmit them if you disagree (although > perhaps not for 8.1.0). Trying to identify which bit of advice you refer to.... I put some comments in based upon feedback from the beta on specific queries that were not optimised the same as non-inherited tables. If thats what you're talking about, then I'd like to put that back. The manuals aren't written for you and me; why let others stumble when they could have it in black and white? > + All constraints on all partitions of the master table are considered > for > + Constraint Exclusion, so large numbers of partitions are likely to > + increase query parse time considerably. > > Wouldn't it primarily increase planning time, not parsing time? Yes. ....What generic term would you use for query compilation? query preparation? The distinction of parsing/planning/optimization etc is lost on most people. > + > + CE only works when the query directly matches a constant. A > + constant bound to a parameterised query will not work in the same way > + since the plan is fixed and would need to vary with each execution. > + Also, stable constants such as CURRENT_DATE may not be used, since > + these are constant only for during the execution of a single query. > + Joins conditions will not allow CE to work either. > + > > I'm not sure what the last sentence is intended to mean. OK, I'll work on a longer explanation of that. > Revised patch attached and applied. There are at least a few more things > that need cleaning up -- if no one beats me to it I'll do that shortly. Best Regards, Simon Riggs From neilc@samurai.com Fri Jun 5 04:52:20 2026 X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 218C3D9A25; Wed, 2 Nov 2005 19:57:42 -0400 (AST) 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 14840-03; Wed, 2 Nov 2005 23:57:37 +0000 (GMT) Received: from mailbox.samurai.com (mailbox.samurai.com [205.207.28.82]) by svr1.postgresql.org (Postfix) with ESMTP id 4F7F0D99BC; Wed, 2 Nov 2005 19:57:39 -0400 (AST) Received: from localhost (mailbox.samurai.com [205.207.28.82]) by mailbox.samurai.com (Postfix) with ESMTP id 1D7772395FC; Wed, 2 Nov 2005 18:57:42 -0500 (EST) Received: from mailbox.samurai.com ([205.207.28.82]) by localhost (mailbox.samurai.com [205.207.28.82]) (amavisd-new, port 10024) with LMTP id 16977-01-4; Wed, 2 Nov 2005 18:57:39 -0500 (EST) Received: from [192.168.1.104] (d226-86-55.home.cgocable.net [24.226.86.55]) by mailbox.samurai.com (Postfix) with ESMTP id B244C23946F; Wed, 2 Nov 2005 18:57:39 -0500 (EST) Subject: Re: [PATCHES] Partitioning docs From: Neil Conway To: Simon Riggs Cc: pgsql-patches@postgresql.org, pgsql-docs@postgresql.org In-Reply-To: <1130961318.8300.1794.camel@localhost.localdomain> References: <1130726816.8300.1377.camel@localhost.localdomain> <1130798509.8300.1536.camel@localhost.localdomain> <1130887177.6884.38.camel@localhost.localdomain> <1130961318.8300.1794.camel@localhost.localdomain> Content-Type: text/plain Date: Wed, 02 Nov 2005 18:57:39 -0500 Message-Id: <1130975859.6884.54.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at mailbox.samurai.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/5 X-Sequence-Number: 3315 On Wed, 2005-02-11 at 19:55 +0000, Simon Riggs wrote: > Trying to identify which bit of advice you refer to.... I put some > comments in based upon feedback from the beta on specific queries that > were not optimised the same as non-inherited tables. ISTM that query optimization *always* works differently for inherited versus non-inherited tables, so there are a wide variety of queries you could describe like that. The other problem is the documentation is sufficiently vague that it is of little use, IMHO. Simply saying "query X is optimized differently" without explaining what causes the difference, what the performance impact is likely to be, or how to workaround the problem isn't likely to be very helpful. -Neil