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 thereal_t_day
tablemonth
real_t_month_2024_05
for thereal_t_month
tablequarter
real_t_quarter_2024_2
for thereal_t_quarter
tableyear
real_t_year_2025
for thereal_t_year
tableint
real_t_int_120_130
for thereal_t_int
tablebigint
"real_t_bigint_-60_-50"
for thereal_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
, ortimestampz
type. It renames the partitioned tablea_relname
(of thea_relschema
schema) by adding thereal_
prefix. The function then creates a view with the same name as the original table and adds theINSTEAD OF INSERT/UPDATE
triggers to it. These triggers create a new partition when needed. The limits of the partition are determined by the defineda_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
, orbigint
type. They rename the partitioned tablea_relname
(of thea_relschema
schema) by adding thereal_
prefix. The function then creates a view with the same name as the original table and theINSTEAD OF INSERT/UPDATE
triggers for it. These triggers create a new partition when needed. The limits of the partition are determined by the originala_firstval
value, from which the intervals are counted, and thea_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 tablea_relname
(of thea_relschema
schema). It also deletes the view and renames the partitioned table, removing thereal_
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