Christopher B. Browne's Home Page
cbbrowne@acm.org

4.5. 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:

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:

4.5.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(7) requests. The alternative is as follows; we use SLONIK EXECUTE SCRIPT(7), extending the DDL script thus:

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

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.

Google

If this was useful, let others know by an Affero rating

Contact me at cbbrowne@acm.org