Autopartitioning

Autopartitioning#

Autopartitioning in PGD allows you to split tables into several partitions, other tables, creating and dropping those partitions are needed. Autopartitioning is useful for managing large tables that grow over time as it allows you to separate the data into manageable chunks. For example, you can archive older data into its own partition, and then archive or drop the partition when the data is no longer needed.

Autopartitioning and replication#

PGD autopartitioning is managed, by default, locally through the bdr.autopartition function. This function allows you to create or alter the definition of automatic range partitioning for a table. If no definition exists, it creates one; otherwise, it alters the existing definition.

Range partitioning#

PGD autopartitioning supports range partitioning using the RANGE keyword. Range partitioning allows you to partition a table based on the ranges of values in a column. For example, you can partition a table by date, where each partition contains data for a specific date range. This is useful for managing large tables that grow over time, as it allows you to separate the data into manageable chunks.

For example, you can create a table that is partitioned by date:

CREATE TABLE measurement (
    logdate date not null,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (logdate);

Then, you can use the bdr.autopartition function to create daily partitions and keep data for one month:

select bdr.autopartition(measurement, 1 day, data_retention_period := 30 days);

This function creates a partition for each day and retains the data for 30 days. After 30 days, the partitions are automatically dropped. If you look at the database tables you’ll see the partitions created for the measurement table:

pgddb=# \dt
__OUTPUT__
                               List of relations
 Schema |                  Name                  |       Type        |  Owner
- -------+----------------------------------------+-------------------+----------
 public | measurement                            | partitioned table | postgres
 public | measurement_part_1231354915_2103027132 | table             | postgres
 public | measurement_part_1520219330_1231354915 | table             | postgres
 public | measurement_part_1670975046_3921991865 | table             | postgres
 public | measurement_part_2103027132_2095358725 | table             | postgres
 public | measurement_part_2877346473_1670975046 | table             | postgres
 public | measurement_part_3921991865_1520219330 | table             | postgres
(7 rows)

Why are there so many partitions? Because, by default, the autopartition creates five advance partitions, for future use and will automatically do that whenever it uses all but two of the partitions. This means that it will always have at least two partitions available for new data. You can change this behavior by setting the minimum_advance_partitions and maximum_advance_partitions parameters in the bdr.autopartition function.

bdr.autopartition(relation regclass,
        partition_increment text,
        partition_initial_lowerbound text DEFAULT NULL,
        partition_autocreate_expression text DEFAULT NULL,
        minimum_advance_partitions integer DEFAULT 2,
        maximum_advance_partitions integer DEFAULT 5,
        data_retention_period interval DEFAULT NULL,
        enabled boolean DEFAULT on,
        analytics_offload_period);
Contents