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.

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.

These triggers work as follows:

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:

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.

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)

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