Partitioning Support

5.3. Partitioning Support

Slony-I does not directly provide support for the PostgreSQL methodology of partitioning via inheritance, but it does not, by the same token, prevent the Gentle User from using that sort of replication scheme, and then replicating the underlying tables.

One of the tests in the regression tests called testinherit, tests that Slony-I behaves as expected to replicate data across partitions. This test creates a master sales_data table, from which various children inherit:

  • us_east

  • us_west

  • canada

The example is somewhat simplistic as it only provides rules to handle initial insertion into the respective partitions; it does not then support allowing tuples to migrate from partition to partition if they are altered via an UPDATE statement. On the other hand, unlike with many partitioning cases, this one permits the "parent" table to contain tuples.

Things worth observing include:

  • Each partition table must be added to replication individually.

  • Slony-I is not aware of the relationship between partitions; it simply regards them as a series of individual tables.

5.3.1. Support for Dynamic Partition Addition

One common "use case" of replication is to partition large data sets based on time period, whether weekly, monthly, quarterly, or annually, where there is therefore a need to periodically add a new partition.

The traditional approach taken to this in Slony-I would be the following:

In view of the fact that we can be certain that a thus-far-unused partition will be empty, we offer an alternative mechanism which evades the need to create extra replication sets and the need to submit multiple SLONIK SUBSCRIBE SET requests. The alternative is as follows; we use SLONIK EXECUTE SCRIPT, extending the DDL script thus:

  • Add the new partition(s) on each node

  • Run a Slony-I stored function to mark the new partition as being a replicated table

    On the origin node, if the table is found to have tuples in it, the DDL script will be aborted, as the precondition that it be empty has been violated.

    On subscriber nodes, we may safely TRUNCATE the new table.

There are several stored functions provided to support this; the Gentle User may use whichever seems preferable. The "base function" is add_empty_table_to_replication(); the others provide additional structure and validation of the arguments

  • add_empty_table_to_replication (set_id, tab_id, nspname, tabname, idxname, comment);

    This is the "base" function; you must specify the set ID, table ID, namespace name, table name, index name, and a comment, and this table will be added to replication.

    Note that the index name is optional; if NULL, the function will look up the primary key for the table, assuming one exists, and fail if it does not exist.

  • replicate_partition(tab_id, nspname, tabname, idxname, comment);

    If it is known that the table to be replicated inherits from a replicated parent table, then this function can draw set and origin information from that parent table.

Note: As has been observed previously, Slony-I is unaware that tables are partitioned. Therefore, this approach may also be used with confidence to add any table to replication that is known to be empty.