Autopartition ============= Autopartition allows you to split tables into several partitions. For more information, see :ref:`Vertical scaling or scale up ` . bdr.autopartition ^^^^^^^^^^^^^^^^^ The ``bdr.autopartition`` function configures automatic RANGE partitioning of a table. Synopsis ^^^^^^^^ .. code:: sql 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: .. code:: sql 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. .. code:: sql 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. .. code:: sql bdr.drop_autopartition(relation regclass); .. _parameters-1: 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-1: Synopsis ^^^^^^^^ .. code:: sql bdr.autopartition_wait_for_partitions(relation regclass, upperbound text); .. _parameters-2: Parameters ^^^^^^^^^^ - ``relation`` — Name or Oid of a table. - ``upperbound`` — Partition key column value. bdr.autopartition_wait_for_partitions_on_all_nodes ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. _synopsis-2: Synopsis ^^^^^^^^ .. code:: sql bdr.autopartition_wait_for_partitions_on_all_nodes(relation regclass, upperbound text); .. _parameters-3: 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-3: Synopsis ^^^^^^^^ .. code:: sql bdr.autopartition_find_partition(relname regclass, searchkey text); .. _parameters-4: 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-4: Synopsis ^^^^^^^^ .. code:: sql bdr.autopartition_enable(relname regclass); .. _parameters-5: 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-5: Synopsis ^^^^^^^^ .. code:: sql bdr.autopartition_disable(relname regclass); .. _parameters-6: 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-6: Synopsis ^^^^^^^^ .. code:: sql bdr.autopartition_create_partition(relname regclass, partname name, lowerb text, upperb text, nodes oid[]); .. _parameters-7: 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-7: Synopsis ^^^^^^^^ .. code:: sql bdr.autopartition_drop_partition(relname regclass) .. _parameters-8: Parameters ^^^^^^^^^^ - ``relname`` — The name of the partitioned table to drop. .. _notes-1: 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.