F.39. pgpro_autopart — dynamic partition creation #

The pgpro_autopart extension enables dynamic creation of partitions, that is automatic partitioning when data is added or modified in a table. The fastest way to create partitions is to do it manually or on schedule using a scheduler. However, for some tasks the speed of adding data does not matter much. For such tasks, pgpro_autopart implements automatic partitioning using triggers on a view of a partitioned table.

F.39.1. Installation #

The pgpro_autopart extension is included in Postgres Pro. To enable pgpro_autopart, create the extension using the following query:

CREATE EXTENSION pgpro_autopart;

F.39.2. Usage #

The pgpro_autopart extension uses the ap_enable_automatic_partition_creation function for automatic partitioning.

Note

Note that the extension works only with newly created tables. It cannot be used with existing partitioned tables, since it cannot track partitions previously created by users.

First, this function adds the real_ prefix to the specified table name. Then, it creates a view with the original table name and adds the INSTEAD OF INSERT/UPDATE triggers to it.

Note

Note that the database administrator should grant privileges on the view created by the ap_enable_automatic_partition_creation function manually. It is expected that these privileges are the same as on the parent table.

These triggers work as follows:

  • INSTEAD OF INSERT: When an entry is inserted into the view, pgpro_autopart searches for a partition for this entry. If the partition is not found, it is created. Then this entry is inserted into the partitioned table.

  • INSTEAD OF UPDATE: When an entry is updated in the view, pgpro_autopart searches for a partition for this entry. If the partition is not found, it is created. Then this entry is updated in the partitioned table.

Currently, only tables with partitioning by range (BY RANGE) are supported, having one column of the date, timestamp, timestampz, smallint, int, or bigint type as a key.

When creating partitions for a partitioning key of date/timestamp types, valid values of the interval are year, quarter, month, and day. That is, a new partition is created for each new year, quarter, month or day.

Names for new partitions follow a strict format: a partitioned table name is followed by a suffix indicating the interval for which the partition is created. For example, for the following interval types table names will be as follows:

day

real_t_day_2024_05_13 for the real_t_day table

month

real_t_month_2024_05 for the real_t_month table

quarter

real_t_quarter_2024_2 for the real_t_quarter table

year

real_t_year_2025 for the real_t_year table

int

real_t_int_120_130 for the real_t_int table

bigint

"real_t_bigint_-60_-50" for the real_t_bigint table

The INSTEAD OF INSERT/UPDATE triggers check the presence of a partition for each inserted or updated entry, and it can slow down the performance.

Since the original partitioned table is renamed, you should use its name with the real_ prefix to work directly with the table.

Important

Remember that the maximum length for the table name in Postgres Pro is 63 bytes. When pgpro_autopart creates a partition, it automatically adds up to 29 bytes (prefix real_, two underscores, symbols of borders) to the table name for the int type and up to 51 bytes for the bigint type of the partition key. It is the user's responsibility to choose appropriate table names to avoid errors.

F.39.3. ap_tables_view View #

Tables with enabled automatic partitioning are shown in the ap_tables_view view. Here is an example of the data in the view.

SELECT * FROM ap_tables_view;
 apt_relname | apt_relschema |                                   apt_mode
-------------+---------------+-----------------------------------------------------------------------------
 t_month1    | user_schema   | automatic partition creation with using triggers on VIEW (C-implementation)
 t_bigint1   | user_schema   | automatic partition creation with using triggers on VIEW (C-implementation)
(2 rows)

F.39.4. Functions #

ap_enable_automatic_partition_creation(a_relname text[, a_relschema text], a_interval text) returns void #

This function is used for tables with a partitioning key of the date, timestamp, or timestampz type. It renames the partitioned table a_relname (of the a_relschema schema) by adding the real_ prefix. The function then creates a view with the same name as the original table and adds the INSTEAD OF INSERT/UPDATE triggers to it. These triggers create a new partition when needed. The limits of the partition are determined by the defined a_interval (year, quarter, month, day).

ap_enable_automatic_partition_creation(a_relname text[, a_relschema text], a_interval smallint, a_firstval smallint) returns void
ap_enable_automatic_partition_creation(a_relname text[, a_relschema text], a_interval int, a_firstval int) returns void
ap_enable_automatic_partition_creation(a_relname text[, a_relschema text], a_interval bigint, a_firstval bigint) returns void #

These functions are used for tables with a partitioning key of the smallint, int, or bigint type. They rename the partitioned table a_relname (of the a_relschema schema) by adding the real_ prefix. The function then creates a view with the same name as the original table and the INSTEAD OF INSERT/UPDATE triggers for it. These triggers create a new partition when needed. The limits of the partition are determined by the original a_firstval value, from which the intervals are counted, and the a_interval value defining the interval length.

ap_disable_automatic_partition_creation(a_relname text[, a_relschema text]) returns void #

The function removes the triggers created by the ap_enable_automatic_partition_creation function for the view of the defined partitioned table a_relname (of the a_relschema schema). It also deletes the view and renames the partitioned table, removing the real_ prefix from its name.

F.39.5. Example #

This example illustrates the usage of pgpro_autopart with the partitioning key of the bigint type.

Create the extension.

CREATE EXTENSION pgpro_autopart;

Create the table partitioned BY RANGE with one-column key of the bigint type.

CREATE TABLE t_bigint (b bigint, i int) PARTITION BY RANGE (b);

Use the extension to rename the t_bigint table into real_t_bigint, create the t_bigint view for this table, and then create the INSTEAD OF INSERT/UPDATE triggers. Note that the initial value for creating partitions is 100, and partitions are created at intervals of 10 in both directions.

SELECT ap_enable_automatic_partition_creation('t_bigint', 10, 100);
 ap_enable_automatic_partition_creation
----------------------------------------
(1 row)

Insert two entries into the table. The real_t_bigint_110_120 partition will be created automatically after the first entry.

INSERT INTO t_bigint VALUES (111, 1);
NOTICE:  New partition "public"."real_t_bigint_110_120" created
INSERT 0 1
INSERT INTO t_bigint VALUES (114, 2);
INSERT 0 1

Change the key field for one of the entries. This will automatically create the real_t_bigint_-60_-50 partition.

UPDATE t_bigint  SET b = -55 WHERE b = 114 RETURNING *;
NOTICE:  New partition "public"."real_t_bigint_-60_-50" created
  b  | i
-----+---
 -55 | 2
(1 row)
UPDATE 1

Check partitions of the real_t_bigint table. There should be two of them.

SELECT
  c.oid::pg_catalog.regclass AS "name",
  pg_catalog.pg_get_expr(c.relpartbound, c.oid) AS "condition"
FROM
  pg_catalog.pg_class c,
  pg_catalog.pg_inherits i
WHERE
  c.oid = i.inhrelid AND i.inhparent = 'real_t_bigint'::regclass;
          name           |             condition
-------------------------+------------------------------------
 real_t_bigint_110_120   | FOR VALUES FROM ('110') TO ('120')
 "real_t_bigint_-60_-50"   | FOR VALUES FROM ('-60') TO ('-50')
(2 rows)

Check the data in the real_t_bigint table and its partitions. The table should contain two entries:

SELECT * FROM real_t_bigint;
  b  | i
-----+---
 -55 | 2
 111 | 1
(2 rows)

There should be one entry in the real_t_bigint_110_120 partition:

SELECT * FROM real_t_bigint_110_120;
  b  | i
-----+---
 111 | 1
(1 row)

And also there should be one entry in the real_t_bigint_-60_-50 partition.

SELECT * FROM "real_t_bigint_-60_-50";
  b  | i
-----+---
 -55 | 2
(1 row)

Disable triggers, drop the view, and rename the real_t_bigint table into t_bigint.

SELECT ap_disable_automatic_partition_creation('t_bigint');
 ap_disable_automatic_partition_creation
-----------------------------------------

(1 row)

Drop the table and the extension.

DROP TABLE t_bigint;
DROP TABLE
DROP EXTENSION pgpro_autopart;
DROP EXTENSION