Ctrl+K

Autopartition

Autopartition#

Autopartition allows you to split tables into several partitions. For more information, see Vertical scaling or scale up .

bdr.autopartition#

The bdr.autopartition function configures automatic RANGE partitioning of a table.

Synopsis#

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,
        managed_locally boolean DEFAULT false,
        enabled boolean DEFAULT on);

Parameters#

  • relation — Name or Oid of a table.

  • partition_increment — Interval or increment to next partition creation.

  • partition_initial_lowerbound — If the table has no partition, then the first partition with this lower bound and partition_increment apart upper bound is created.

  • partition_autocreate_expression — The expression used to detect if it’s time to create new partitions.

  • minimum_advance_partitions — The system attempts to always have at least minimum_advance_partitions partitions.

  • maximum_advance_partitions — Number of partitions to create in a single go after the number of advance partitions falls below minimum_advance_partitions .

  • data_retention_period — Interval until older partitions are dropped, if defined. This value must be greater than migrate_after_period .

  • managed_locally — If true, then the partitions are managed locally.

  • enabled — Allows activity to be disabled or paused and later resumed or reenabled.

Examples#

Daily partitions, keep data for one month:

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

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

Create five advance partitions when only two more partitions remain. Each partition can hold 1 billion orders.

bdr.autopartition(Orders, 1000000000,
        partition_initial_lowerbound := 0,
        minimum_advance_partitions := 2,
        maximum_advance_partitions := 5
     );

bdr.drop_autopartition#

Use bdr.drop_autopartition() to drop the autopartitioning rule for the given relation. All pending work items for the relation are deleted, and no new work items are created.

bdr.drop_autopartition(relation regclass);

Parameters#

  • relation — Name or Oid of a table.

bdr.autopartition_wait_for_partitions#

Partition creation is an asynchronous process. AutoPartition provides a set of functions to wait for the partition to be created, locally or on all nodes.

Use bdr.autopartition_wait_for_partitions() to wait for the creation of partitions on the local node. The function takes the partitioned table name and a partition key column value and waits until the partition that holds that value is created.

The function waits only for the partitions to be created locally. It doesn’t guarantee that the partitions also exists on the remote nodes.

To wait for the partition to be created on all PGD nodes, use the bdr.autopartition_wait_for_partitions_on_all_nodes() function. This function internally checks local as well as all remote nodes and waits until the partition is created everywhere.

Synopsis#

bdr.autopartition_wait_for_partitions(relation regclass, upperbound text);

Parameters#

  • relation — Name or Oid of a table.

  • upperbound — Partition key column value.

bdr.autopartition_wait_for_partitions_on_all_nodes#

Synopsis#

bdr.autopartition_wait_for_partitions_on_all_nodes(relation regclass, upperbound text);

Parameters#

  • relation — Name or Oid of a table.

  • upperbound — Partition key column value.

bdr.autopartition_find_partition#

Use the bdr.autopartition_find_partition() function to find the partition for the given partition key value. If partition to hold that value doesn’t exist, then the function returns NULL. Otherwise Oid of the partition is returned.

Synopsis#

bdr.autopartition_find_partition(relname regclass, searchkey text);

Parameters#

  • relname — Name of the partitioned table.

  • searchkey — Partition key value to search.

bdr.autopartition_enable#

Use bdr.autopartition_enable to enable AutoPartitioning on the given table. If AutoPartitioning is already enabled, then no action occurs. See :ref:``bdr.autopartition_disable` <bdr.autopartition_disable>` to disable AutoPartitioning on the given table.

Synopsis#

bdr.autopartition_enable(relname regclass);

Parameters#

  • relname — Name of the relation to enable AutoPartitioning.

bdr.autopartition_disable#

Use bdr.autopartition_disable to disable AutoPartitioning on the given table. If AutoPartitioning is already disabled, then no action occurs.

Synopsis#

bdr.autopartition_disable(relname regclass);

Parameters#

  • relname — Name of the relation to disable AutoPartitioning.

Internal functions#

bdr.autopartition_create_partition#

AutoPartition uses an internal function bdr.autopartition_create_partition to create a standalone AutoPartition on the parent table.

Synopsis#

bdr.autopartition_create_partition(relname regclass,
                               partname name,
                                 lowerb text,
                                 upperb text,
                                 nodes oid[]);

Parameters#

  • relname — Name or Oid of the parent table to attach to.

  • partname — Name of the new AutoPartition.

  • lowerb — Lower bound of the partition.

  • upperb — Upper bound of the partition.

  • nodes — List of nodes that the new partition resides on. This parameter is internal to PGD and reserved for future use.

Notes#

This is an internal function used by AutoPartition for partition management. We recommend that you don’t use the function directly.

bdr.autopartition_drop_partition#

AutoPartition uses an internal function bdr.autopartition_drop_partition to drop a partition that’s no longer required, as per the data-retention policy. If the partitioned table was successfully dropped, the function returns true .

Synopsis#

bdr.autopartition_drop_partition(relname regclass)

Parameters#

  • relname — The name of the partitioned table to drop.

Notes#

This function places a DDL lock on the parent table before using DROP TABLE on the chosen partition table. This function is an internal function used by AutoPartition for partition management. We recommend that you don’t use the function directly.

Contents