PostgreSQL Partition Introduction

PostgreSQL supports the basic table partitioning feature. This article describes why table partitioning is required and how table partitioning is used in database design.

1. Overview

Partitioning means dividing a large logical table into physical chunks. Partitioning can provide several benefits:

• Some types of query performance can be greatly improved. In particular, the high-access rows in the table are in the case of a separate partition or a few partitions. Partitioning can reduce the index volume so that the index of the high utilization portion can be stored in memory. If the index is not all in memory, read and write on the index will result in more disk access.

• When querying or updating most of a partition's records, a continuous scan of that partition instead of using an index for discrete access to the entire table can make a huge performance gain.

• If records that require large loads or deletions are located on separate partitions, you can gain significant performance gains by directly reading or deleting that partition, becauseALTER table NO INHERITanddrop tableis much faster than manipulating large amounts of data. These commands can also avoidVACUUMoverloading caused by a large number ofdeletes.

• Infrequently used data can be moved to a less expensive slow storage medium.

This benefit is usually only valuable if the table is likely to become very large. How large a table will benefit from partitioning depends on the specific application, but there is a basic thumb rule that the size of the table exceeds the physical memory size of the database server.

Currently,PostgreSQL supports partitioning through table inheritance. Each partition must be created as a child table of a single parent table. The parent table itself is usually empty, and it exists only to represent the entire data set.

PostgreSQL can implement the following forms of partitioning:

Range Partitioning

A table is partitioned into a "range" of one or more key fields , which do not overlap within different partitions. For example, we can partition by time range or by the identifier range of a particular business object.

List partition

The table explicitly lists which key value implementations should appear in each partition.

2. Implementing partitioning

To set up a partitioned table, do the following steps:

2. Create a "primary table" from which all partitions inherit.

   There is no data in this table, do not define any check constraints on this table, unless you want the constraint to also apply to all partitions. Similarly, it does not make sense to define any index or UNIQUE constraint on it.

4. Create several "child tables", each inheriting from the primary table. Typically, these tables do not add any fields.

   We refer to the table as partitions, even though they are ordinary postgresql tables.

6. Add constraints to the partitioned table to define the allowable health values for each partition.

   The typical examples are:

CHECK (x = 1)
CHECK (county IN (‘Oxfordshire’, ‘Buckinghamshire’, ‘Warwickshire’))
CHECK (outletID> = 100 AND outletID <200)
Ensuring that these constraints ensure that there are no overlapping keys in different partitions. A common mistake is to set a range like this:

CHECK (outletID BETWEEN 100 AND 200)
CHECK (outletID BETWEEN 200 AND 300)
This is wrong because it doesn't make it clear that the 200 value belongs to that range.

Note that there is no difference in the syntax of ranges and list partitions; these terms are used for description only.

For each partition, create an index on the key field, and any other indexes you want to create. Key field indexing is not strictly required, but it is helpful in most cases. If you want the key values to be unique, then you should always create a unique or primary key constraint for each partition.

In addition, define a rule or trigger to redirect data into the main table to the appropriate partition.

Make sure the constraint_exclusion configuration parameter in postgresql.conf is turned on. Without this parameter, the query will not be optimized as needed.

For example, suppose we build a database for a huge ice cream company. The company measures the maximum temperature daily, as well as ice cream sales in each region. Conceptually, we need a table like this:

CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
);
We know that most queries only access data for the last week, last month, or last quarter, because the main purpose of this table is to prepare online reports for management. In order to reduce the old data that needs to be stored, we decided to keep useful data for the last three years. At the beginning of each month, we delete the oldest month's data.

In this case, we can use partitioning to help achieve all the different needs for the table. The following steps describe the above requirements. The partitions can be set up like this:

 

The main table is the measurement table, declared as above.

Then we create a partition for each month:

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);
Each partition is a complete table with its own content, except that they inherit definitions from the measurement table.

This solves one of our problems: deleting old data. Each month, all we need to do is execute a DROP TABLE on the oldest child table, and then create a new child table for the new month.

We must provide non-overlapping table constraints. Instead of just creating a partitioned table like above, our table-building script becomes:

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);
We may also need to have an index on the key field:

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 chose not to build more indexes first.

We want our application to say INSERT INTO measurement ... and the data is redirected to the corresponding partition table. We can arrange to attach a suitable trigger to the main table. If the data only enters the latest partition, we can use a very simple trigger:

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 will create a trigger that calls the trigger function:

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger ();
We must redefine the trigger every month so that it always points to the current partition. However, the trigger definition does not need to be updated.

We might want to insert data and want the server to automatically locate to which partition the data should be inserted. We can achieve this goal with the following complex triggers, such as:

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;
Every trigger is the same as before. Note that each IF test must match the CHECK constraint of its partition.

When this function is more complicated than the single month case, it does not need to be updated frequently, because branches can be added before they are needed.

Note: In practice, if most of the partitions are inserted, it may be best to check the latest partition first. For simplicity, we have shown in other parts of this example the testing of triggers in the same order.

We can see that a complex partitioning scheme may require considerable DDL. In the above example we need to create a new partition once a month, so it is wise to write a script to automatically generate the required DDL.

3. Management partition
The partition set is usually determined when the table is defined, but we often need to periodically delete the old partition and add new partitions. The most important benefit of partitioning is that it can adapt to this demand just right: operating the structure of the partition at an extremely fast speed, rather than the painful physical movement of large amounts of data.

The easiest way to delete old data is to delete partitions that are no longer needed:

DROP TABLE measurement_y2006m02;
This command can quickly delete partitions containing millions of records because it does not need to delete each record individually.

You can also delete a partition while retaining its ability to access it as a table:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
This will allow other operations to be performed on this data in the future (such as using COPY, pg_dump and other tools for backup). And this is also a good time to perform other data operations (data aggregation or running reports, etc.).

Similarly, we can create a new empty partition to handle the new data, just as we did with the original partition.

CREATE TABLE measurement_y2008m02 (
    CHECK (logdate> = DATE ‘2008-02-01’ AND logdate <DATE ‘2008-03-01’)
) INHERITS (measurement);
Sometimes it is more convenient to create a new table outside the partition structure and turn it into a partition after a period of time. Because this will allow the data in the table to be loaded, checked, transformed and other operations before it becomes partitioned.

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK (logdate> = DATE ‘2008-02-01’ AND logdate <DATE ‘2008-03-01’);
\ copy measurement_y2008m02 from ‘measurement_y2008m02’
--Other possible data preparation
ALTER TABLE measurement_y2008m02