postgresql Table Partitioning Example

This example builds a partitioning structure equivalent to the declarative partitioning example above. Use the following steps:

Create the “root” table, from which all of the “child” tables will inherit. This table will contain no data. Do not define any check constraints on this table, unless you intend them to be applied equally to all child tables. There is no point in defining any indexes or unique constraints on it, either. For our example, the root table is the measurement table as originally defined:

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
Create several “child” tables that each inherit from the root table. Normally, these tables will not add any columns to the set inherited from the root. Just as with declarative partitioning, these tables are in every way normal PostgreSQL tables (or foreign tables).

CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
…
CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
Add non-overlapping table constraints to the child tables to define the allowed key values in each.

Typical examples would be:

CHECK ( x = 1 )
CHECK ( county IN ( ‘Oxfordshire’, ‘Buckinghamshire’, ‘Warwickshire’ ))
CHECK ( outletID >= 100 AND outletID < 200 )
Ensure that the constraints guarantee that there is no overlap between the key values permitted in different child tables. A common mistake is to set up range constraints like:

CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
This is wrong since it is not clear which child table the key value 200 belongs in. Instead, ranges should be defined in this style:

CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE ‘2006-02-01’ AND logdate < DATE ‘2006-03-01’ )
) INHERITS (measurement);

CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE ‘2006-03-01’ AND logdate < DATE ‘2006-04-01’ )
) INHERITS (measurement);

…
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE ‘2007-11-01’ AND logdate < DATE ‘2007-12-01’ )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE ‘2007-12-01’ AND logdate < DATE ‘2008-01-01’ )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE ‘2008-01-01’ AND logdate < DATE ‘2008-02-01’ )
) INHERITS (measurement);
For each child table, create an index on the key column(s), as well as any other indexes you might want.

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
We want our application to be able to say INSERT INTO measurement … and have the data be redirected into the appropriate child table. We can arrange that by attaching a suitable trigger function to the root table. If data will be added only to the latest child, we can use a very simple trigger function:

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
RETURN NULL;
END;

$LANGUAGE plpgsql; After creating the function, we create a trigger which calls the trigger function: CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger(); We must redefine the trigger function each month so that it always inserts into the current child table. The trigger definition does not need to be updated, however. We might want to insert data and have the server automatically locate the child table into which the row should be added. We could do this with a more complex trigger function, for example: CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS$

BEGIN
IF ( NEW.logdate >= DATE ‘2006-02-01’ AND
NEW.logdate < DATE ‘2006-03-01’ ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.);
ELSIF ( NEW.logdate >= DATE ‘2006-03-01’ AND
NEW.logdate < DATE ‘2006-04-01’ ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.);
…
ELSIF ( NEW.logdate >= DATE ‘2008-01-01’ AND
NEW.logdate < DATE ‘2008-02-01’ ) THEN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
ELSE
RAISE EXCEPTION ‘Date out of range. Fix the measurement_insert_trigger() function!’;
END IF;
RETURN NULL;
END;

$LANGUAGE plpgsql; The trigger definition is the same as before. Note that each IF test must exactly match the CHECK constraint for its child table. While this function is more complex than the single-month case, it doesn't need to be updated as often, since branches can be added in advance of being needed.$