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.
The pgpro_autopart extension is included in Postgres Pro. To enable pgpro_autopart, create the extension using the following query:
CREATE EXTENSION pgpro_autopart;
The pgpro_autopart extension uses the
ap_enable_automatic_partition_creation
function for automatic partitioning.
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 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.
The INSERT or UPDATE 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 using nextval()
because it can not be omitted.
Otherwise, the INSERT or UPDATE
operation will fail.
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.
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.
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)
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.
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