PostgreSQL logical replication with partitioned tables
This post explains how publish_via_partition_root changes the logical replication behavior of partitioned tables in PostgreSQL.
When a row is inserted into a partitioned table, it is stored in one of its leaf partitions. For logical replication, PostgreSQL must decide which table identity will be sent to the subscriber: the partitioned root table or the leaf partition.
This behavior is controlled by publish_via_partition_root in the publication.
The difference in a few words
| Setting | What is published | What happens with a new partition |
|---|---|---|
true |
The identity and schema of the partitioned root table are used. | The row is replicated through the root table. A subscription refresh is not needed. |
false |
The identity and schema of the leaf partition are used. | The subscription must be refreshed before the new leaf partition starts to replicate. |
The default value is false.
When it is set to true
With publish_via_partition_root = true, changes are published as changes of the root table.
On the subscriber, the row is inserted into the root table. If this table is also partitioned, the row is routed to a partition by the subscriber. Because the root table is already known by the subscription, a new leaf partition on the publisher does not require a subscription refresh.
This setting is useful when:
- partitions are created often and a refresh should not be required each time;
- the subscriber has a different partition layout;
- the target table on the subscriber is not partitioned.
If the subscriber table is partitioned, the required partition must still be created there before the row arrives.
When it is set to false
With publish_via_partition_root = false, changes are published using the leaf partition where they happened.
This means that a target table for this leaf partition must exist on the subscriber. It can be a partition or a normal table, but it must be a valid target for the published leaf table.
In the accompanying lab, FOR ALL TABLES is used. Therefore, a new partition is automatically included in the publication. It does not need to be added manually. However, the subscription does not know about the new leaf table until it is refreshed:
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;
By default, existing rows from the new table are also copied during the refresh because copy_data defaults to true.
This setting is useful when every leaf partition should be treated as a separate replicated table and matching target tables are maintained on the subscriber.
An important limitation
DDL is not replicated by PostgreSQL logical replication. New partitions and other schema changes must be created on the subscriber manually.
The TRUNCATE behavior also has an important difference:
| Setting | Truncate the root table | Truncate a leaf partition directly |
|---|---|---|
true |
Replicated | Not replicated |
false |
Replicated | Replicated after the leaf is known by the subscription |
Therefore, a TRUNCATE on the partitioned root table removes all rows from the subscriber with both settings. But a direct leaf partition TRUNCATE is skipped when publish_via_partition_root = true.
Try the lab
The complete example is available in the logical replication and partitions lab.
Two independent examples are provided. One uses publish_via_partition_root = true and the other uses false. The output shows when rows are replicated, when a subscription refresh is required, and how both kinds of TRUNCATE behave.
Conclusion
Use true when changes should be replicated through the root table. This is usually simpler when new partitions are added often or when the subscriber has a different table structure.
Use false when every leaf partition should keep its own identity on the subscriber. In this case, remember to refresh the subscription after a new partition is created.