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:

.. code:: sql

   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:

.. code:: sql

   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:

.. code:: console

   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.

.. 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,
           enabled boolean DEFAULT on,
           analytics_offload_period);