AutoPartition

AutoPartition allows tables to grow easily to large sizes by automaticpartitioning management. This utilizes the additional features of BDRsuch as low-conflict locking of creating and dropping partitions.

New partitions can be created regularly and then dropped when thedata retention period expires.

BDR management is primarily accomplished via SQL-callable functions.All functions in BDR are exposed in the bdr schema. Unless you put it intoyour search_path, you will need to schema-qualify the name of each function.

Auto Creation of Partitions

bdr.autopartition() is used to create or alter the definition of automaticrange partitioning for a table. If no definition exists, it will be created,otherwise later executions will alter the definition.

bdr.autopartition() does not lock the actual table, it only changes the definition of when and how new partition maintenance actions will take place.

bdr.autopartition() leverages the EDB Postgres Extended features to allow apartition to be attached or detached/dropped without locking the rest of thetable, the feature to set a new tablespace while allowing SELECT queries.

An ERROR is raised if the table is not RANGE partitioned or a multi-columnpartition key is used.

A new partition is added for every partition_increment range of values, withlower and upper bound partition_increment apart. For tables with a partitionkey of type timestamp or date, the partition_increment must be a validconstant of type interval. For example, specifying 1 Day will cause a newpartition to be added each day, with partition bounds that are 1 day apart.

If the partition column is connected to a timeshard or ksuuid sequence,the partition_increment must be specified as type interval. Otherwise,if the partition key is integer or numeric, then the partition_incrementmust be a valid constant of the same datatype. For example, specifying’1000000’ will cause new partitions to be added every 1 million values.

If the table has no existing partition, then the specifiedpartition_initial_lowerbound is used as the lower bound for the firstpartition. If partition_initial_lowerbound is not specified, then the systemtries to derive its value from the partition column type and the specifiedpartition_increment. For example, if partition_increment is specified as 1Day, then partition_initial_lowerbound will be automatically set to CURRENTDATE. If partition_increment is specified as 1 Hour, thenpartition_initial_lowerbound will be set to the current hour of the currentdate. The bounds for the subsequent partitions will be set using thepartition_increment value.

The system always tries to have a certain minimum number of advance partitions.In order to decide whether to create new partitions or not, it uses thespecified partition_autocreate_expression. This can be a SQL evaluableexpression, which is evaluated every time a check is performed. For example,for a partitioned table on column type date, ifpartition_autocreate_expression is specified as DATE_TRUNC('day',CURRENT_DATE), partition_increment is specified as 1 Day andminimum_advance_partitions is specified as 2, then new partitions will becreated until the upper bound of the last partition is less thanDATE_TRUNC('day', CURRENT_DATE) + '2 Days'::interval.

The expression is evaluated each time the system checks for new partitions.

For a partitioned table on column type integer, thepartition_autocreate_expression may be specified as SELECT max(partcol) FROMschema.partitioned_table. The system then regularly checks if the maximum value ofthe partitioned column is within the distance of minimum_advance_partitions *partition_incrementof the last partition’s upper bound. It is expected thatthe user creates an index on the partcol so that the query runs efficiently.If the partition_autocreate_expression is not specified for a partition tableon column type integer, smallint or bigint, then the system willautomatically set it to max(partcol).

If the data_retention_period is set, partitions will be automaticallydropped after this period. Partitions will be dropped at the same time as newpartitions are added, to minimize locking. If not set, partitions mustbe dropped manually.

The data_retention_period parameter is only supported for timestamp (andrelated) based partitions. The period is calculated by considering the upperbound of the partition and the partition is either migrated to the secondarytablespace or dropped if either of the given period expires, relative to theupper bound.

By default, AutoPartition manages partitions globally. In other words, when apartition is created on one node, the same partition is also created on allother nodes in the cluster. So all partitions are consistent and guaranteed tobe available. For this, AutoPartition makes use of Raft. This behaviour can bechanged by passing managed_locally as true. In that case, all partitionsare managed locally on each node. This is useful for the case when thepartitioned table is not a replicated table and hence it may not be necessaryor even desirable to have all partitions on all nodes. For example, thebuilt-in bdr.conflict_history table is not a replicated table, and ismanaged by AutoPartition locally. Each node creates partitions for this tablelocally and drops them once they are old enough.

Tables once marked as managed_locally cannot be later changed to be managedglobally and vice versa.

Activities are performed only when the entry is marked enabled = on.

The user is not expected to manually create or drop partitions for tablesmanaged by AutoPartition. Doing so can make the AutoPartition metadatainconsistent and could cause it to fail.

Configure AutoPartition

The bdr.autopartition function configures automatic partinioning 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 will be created.

  • `partition_autocreate_expression` - is used to detect if it is time to create new partitions.

  • `minimum_advance_partitions` - the system will attempt to always have at

    • least minimum_advance_partitions partitions.

  • `maximum_advance_partitions` - number of partitions to be created in a single

    • go once the number of advance partitions falls below minimum_advance_partitions.

  • `data_retention_period` - interval until older partitions are dropped, if

    • defined. This must be greater than migrate_after_period.

  • `managed_locally` - if true then the partitions will be managed locally.

  • `enabled` - allows activity to be disabled/paused and later resumed/re-enabled.

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 5 advance partitions when there are only 2 more partitions remaining (each partition can hold 1 billion orders):

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

Create One AutoPartition

Use bdr.autopartition_create_partition() to create a standalone AutoPartitionon 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` - The lower bound of the partition

  • `upperb` - The upper bound of the partition

  • `nodes` - List of nodes that the new partition resides on

Stopping Auto-Creation of Partitions

Use bdr.drop_autopartition() to drop the auto-partitioning rule for thegiven relation. All pending work items for the relation are deleted and no newwork items are created.

bdr.drop_autopartition(relation regclass);

Parameters

  • `relation` - name or Oid of a table

Drop one AutoPartition

Use bdr.autopartition_drop_partition once a BDR AutoPartition table has beenmade, as this function can specify single partitions to drop. If the partitionedtable has successfully been dropped, the function will return true.

Synopsis

bdr.autopartition_drop_partition(relname regclass)

Parameters

  • `relname` - The name of the partitioned table to be dropped

Notes

This will place a DDL lock on the parent table, before using DROP TABLE on thechosen partition table.

Wait for Partition Creation

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

The function only waits for the partitions to be created locally. It does not guaranteethat the partitions also exists on the remote nodes.

In order to wait for the partition to be created on all BDR nodes, use thebdr.autopartition_wait_for_partitions_on_all_nodes() function. This functioninternally checks local as well as all remote nodes and waits until thepartition is created everywhere.

Synopsis

bdr.autopartition_wait_for_partitions(relation regclass, text bound);

Parameters

  • `relation` - name or Oid of a table

  • `bound` - partition key column value.

Synopsis

bdr.autopartition_wait_for_partitions_on_all_nodes(relation regclass, text bound);

Parameters

  • `relation` - name or Oid of a table.

  • `bound` - partition key column value.

Find Partition

Use the bdr.autopartition_find_partition() function to find the partition for thegiven partition key value. If partition to hold that value does not exist, thenthe 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.

Enable/Disable AutoPartitioning

Use bdr.autopartition_enable() to enable AutoPartitioning on the given table.If AutoPartitioning is already enabled, then it will be a no-op. Similarly, usebdr.autopartition_disable() to disable AutoPartitioning on the given table.

Synopsis

bdr.autopartition_enable(relname regclass);

Parameters

  • `relname` - name of the relation to enable AutoPartitioning.

Synopsis

bdr.autopartition_disable(relname regclass);

Parameters

  • `relname` - name of the relation to disable AutoPartitioning.

Synopsis

bdr.autopartition_get_last_completed_workitem();

Return the id of the last workitem successfully completed on all nodes in thecluster.

Check AutoPartition Workers

From using the bdr.autopartition_work_queue_check_status function, you cansee the status of the background workers that are doing their job to maintainAutoPartitions.

The workers can be seen through these views:autopartition_work_queue_local_status``autopartition_work_queue_global_status

Synopsis

bdr.autopartition_work_queue_check_status(workid bigint
   *                          local boolean DEFAULT false);

Parameters

  • `workid` - The key of the AutoPartition worker

  • `local` - Check the local status only

Notes

AutoPartition workers are ALWAYS running in the background, even before thebdr.autopartition function is called for the first time. If an invalid worker IDis used, the function will return ‘unknown’. ‘In-progress’ is the typical status.