| F.40. pgpro_autopart — dynamic partition creation | ||||
|---|---|---|---|---|
| Prev | Up | Appendix F. Additional Supplied Modules and Extensions Shipped inpostgrespro-std-17-contrib | Home | Next |
F.40. pgpro_autopart — dynamic partition creation#
Thepgpro_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.40.1. Installation#
Thepgpro_autopart extension is included inPostgres Pro. To enablepgpro_autopart, create the extension using the following query:
CREATE EXTENSION pgpro_autopart;
F.40.2. Usage#
Thepgpro_autopart extension uses theap_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 thereal_ prefix to the specified table name. Then, it creates a view with the original table name and adds theINSTEAD OF INSERT/UPDATE triggers to it.
Note
Note that the database administrator should grant privileges on the view created by theap_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.
Note
TheINSERT orUPDATE statement must contain a value for a partitioning key.
When using a sequence as the key value, it is required to explicitly set the value usingnextval() because it can not be omitted. Otherwise, theINSERT orUPDATE operation will fail.
Currently, only tables with partitioning by range (BY RANGE) are supported, having one column of thedate,timestamp,timestampz,smallint,int, orbigint type as a key.
When creating partitions for a partitioning key ofdate/timestamp types, valid values of the interval areyear,quarter,month, andday. 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:
dayreal_t_day_2024_05_13for thereal_t_daytablemonthreal_t_month_2024_05for thereal_t_monthtablequarterreal_t_quarter_2024_2for thereal_t_quartertableyearreal_t_year_2025for thereal_t_yeartableintreal_t_int_120_130for thereal_t_inttablebigint"real_t_bigint_-60_-50"for thereal_t_biginttable
TheINSTEAD OF INSERT/UPDATE triggers check the presence of a partition foreach inserted or updated entry, and it can slow down the performance.
Since the original partitioned table is renamed, you should use its name with thereal_ prefix to work directly with the table.
Important
Remember that the maximum length for the table name inPostgres Pro is 63 bytes. Whenpgpro_autopart creates a partition, it automatically adds up to 29 bytes (prefixreal_, two underscores, symbols of borders) to the table name for theint type and up to 51 bytes for thebigint type of the partition key. It is the user's responsibility to choose appropriate table names to avoid errors.
F.40.3. ap_tables_view View#
Tables with enabled automatic partitioning are shown in theap_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.40.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, ortimestampztype. It renames the partitioned tablea_relname(of thea_relschemaschema) by adding thereal_prefix. The function then creates a view with the same name as the original table and adds theINSTEAD OF INSERT/UPDATEtriggers 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 voidap_enable_automatic_partition_creation(a_relname text[, a_relschema text], a_interval int, a_firstval int) returns voidap_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, orbiginttype. They rename the partitioned tablea_relname(of thea_relschemaschema) by adding thereal_prefix. The function then creates a view with the same name as the original table and theINSTEAD OF INSERT/UPDATEtriggers for it. These triggers create a new partition when needed. The limits of the partition are determined by the originala_firstvalvalue, from which the intervals are counted, and thea_intervalvalue 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_creationfunction for the view of the defined partitioned tablea_relname(of thea_relschemaschema). It also deletes the view and renames the partitioned table, removing thereal_prefix from its name.
F.40.5. Example#
This example illustrates the usage ofpgpro_autopart with the partitioning key of thebigint type.
Create the extension.
CREATE EXTENSION pgpro_autopart;
Create the table partitionedBY RANGE with one-column key of thebigint type.
CREATE TABLE t_bigint (b bigint, i int) PARTITION BY RANGE (b);
Use the extension to rename thet_bigint table intoreal_t_bigint, create thet_bigint view for this table, and then create theINSTEAD 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. Thereal_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" createdINSERT 0 1INSERT INTO t_bigint VALUES (114, 2);INSERT 0 1
Change the key field for one of the entries. This will automatically create thereal_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 thereal_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 iWHERE 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 thereal_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 thereal_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 thereal_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 thereal_t_bigint table intot_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 TABLEDROP EXTENSION pgpro_autopart;DROP EXTENSION