James Ainslie

Replicating selected tables and routing inserts into partitions have both required substantial machinery around PostgreSQL. Version 10 brings native interfaces for those jobs into the server. Its October 5 release announcement introduces logical replication through publications and subscriptions, together with declarative range and list partitioning. The common improvement is that PostgreSQL can represent more of the intended arrangement directly.

That does not make the two features interchangeable ways of distributing data. Logical replication transfers selected table changes between databases. Partitioning divides a table into separately stored parts within a database structure. Each has different consistency rules and maintenance requirements, and the version 10 implementation places explicit limits on their combination.

I default to PostgreSQL for relational work because its behavior is inspectable and its schema operations are generally predictable. These additions interest me for the same reason. Database-managed routing and replication configuration can replace application machinery, provided the configuration's meaning and remaining responsibilities are understood.

Logical replication describes changes to rows

The logical replication guide at the 10.0 tag distinguishes logical replication from physical replication. Physical replication refers to exact block addresses and copies bytes. Logical replication identifies data objects and their changes through a replica identity, ordinarily a primary key.

A publication defines a set of table changes in one database. A subscription identifies the connection to a publisher and the publications the subscriber wants. Publications can select combinations of inserts, updates, and deletes, with all three included by default. A table can participate in multiple publications, and a publication can have several subscribers.

The publisher's WAL sender starts logical decoding and loads the built-in pgoutput plugin. That plugin converts WAL changes into the logical replication protocol and filters them according to the publication. The subscriber's apply worker maps the incoming data to local tables and applies the changes in transactional order.

This retains WAL as the source of changes while changing the representation sent downstream. The subscriber does not need a byte-for-byte copy of the publisher's entire cluster. It needs compatible local tables for the selected data. That makes selected-table distribution and consolidation possible without requiring the subscriber to be a physical standby.

Initialization includes a copy and a synchronization phase

Replication normally begins by copying a snapshot of existing table data. Table-synchronization workers use temporary replication slots for that initial work. They then stream changes that occurred during the copy until the table catches up with the main apply process. Control subsequently passes back to that process for ongoing replication.

This sequence explains why initial copying is part of the replication mechanism. A table copy takes time, and writes can continue while it runs. The synchronization phase accounts for those overlapping changes before normal application of the stream takes over. A completed snapshot copy alone would not establish that the subscriber had reached the publisher's current state.

Within a single subscription, PostgreSQL applies changes in the publisher's commit order and guarantees transactional consistency for the publications in that subscription. The scope of that guarantee matters. Defining unrelated subscriptions does not create one shared ordering guarantee across all of them.

Each subscription receives changes through a replication slot. The guide explains that an abandoned slot can continue reserving WAL, eventually filling disk. Subscription and slot lifecycle are therefore related operational state. When a publisher is unreachable during subscription removal, disassociating the slot may be necessary, followed by manual cleanup if the publisher still exists.

Table compatibility is defined by names and types

Published tables must already exist on the subscriber because schema definitions are not replicated. PostgreSQL matches tables by fully qualified name and columns by name. Column order may differ, while column types must match. Additional subscriber columns receive their default values.

For existing tables named users and departments, the guide's basic publication form names both tables explicitly. It defines which tables contribute changes to this publication:

CREATE PUBLICATION mypub FOR TABLE users, departments;

On a separately prepared subscriber, the corresponding statement supplies the publisher connection and publication name. The subscription becomes the downstream configuration for receiving those changes:

CREATE SUBSCRIPTION mysub
CONNECTION 'dbname=foo host=bar user=repuser'
PUBLICATION mypub;

These statements assume the server configuration, authentication, privileges, and matching tables have already been prepared. The publisher needs wal_level = logical, sufficient replication slots, and WAL sender capacity. The subscriber needs capacity for apply workers and initial table synchronization. Creating the SQL objects is one part of that setup.

Updates and deletes require a replica identity so PostgreSQL can identify the destination row. A primary key supplies the usual identity. An eligible unique index can be selected instead, or REPLICA IDENTITY FULL can use the whole row as a less efficient fallback. Inserts do not require the same identity because they do not have to locate a pre-existing destination row.

A logical subscriber is not automatically a complete failover target

Sequence state is not replicated. Values already stored in sequence-backed table columns arrive as table data, but the subscriber's sequence generator does not thereby advance to match the publisher. A subscriber intended to accept writes after a switchover needs those generators brought to suitable values separately.

Version 10 also does not replicate TRUNCATE, large objects, or DDL. Schema changes need coordination outside the replication stream. An additive schema change can often be applied to the subscriber first, so incoming rows fit when the publisher begins using it. If incoming changes do not fit the subscriber's schema, replication reports an error until the mismatch is addressed.

Local writes can create conflicts as well. The subscriber remains an ordinary database, and conflicting incoming data can stop replication when it violates a constraint. The guide requires manual resolution. Applications treating the subscribed tables as read-only avoid this source of conflict for a single subscription.

I see selective replication as useful precisely because it gives the receiving database a different role. An analytical subscriber or a database sharing selected tables has different requirements from a physical standby. Those uses benefit from the flexibility without requiring the unsupported assumption that every part of the source database has been duplicated.

Partitioning moves row routing into the server

The table-definition guide at the release tag describes native range and list partitioning. A partitioned table declares a partition key, and each partition declares its bounds. PostgreSQL routes inserted rows according to that key and rejects overlapping partition bounds.

A time-based table can declare the date column as its partition key. A separate partition definition supplies the interval of dates assigned to that table:

CREATE TABLE reading (
    observed_on date NOT NULL,
    value integer
) PARTITION BY RANGE (observed_on);

CREATE TABLE reading_october PARTITION OF reading
FOR VALUES FROM ('2017-10-01') TO ('2017-11-01');

This describes the routing relationship directly in the schema. Earlier inheritance-based partitioning required users to maintain routing triggers or rules. Version 10 still uses inheritance internally for the partition hierarchy, but supplies a dedicated interface with stricter rules. For example, a partition cannot add columns absent from its parent.

An insert with no matching partition fails. PostgreSQL does not invent the next date partition automatically, so maintaining future partitions remains an administrative responsibility. Existing data can be prepared in a standalone table and attached later, while detaching a partition preserves it as an independent table.

That lifecycle is useful for retention. Detaching old data can permit archiving or aggregation before removal. Dropping an old partition removes its stored rows without individually deleting each row. These operations still have locking requirements, including an ACCESS EXCLUSIVE lock on the parent for the documented partition maintenance operations.

The first partitioning interface has substantial limits

Indexes must be created separately on individual partitions. Version 10 cannot define a primary key, unique constraint, or exclusion constraint spanning the entire hierarchy. It consequently does not support foreign keys to or from a partitioned table, or ON CONFLICT against that partitioned table.

An update that would move a row into another partition fails because the new row no longer satisfies its original partition's constraint. Row triggers must be defined on individual partitions. These restrictions affect application design, particularly when a partition key can change during the lifetime of a row.

Logical replication also requires ordinary base tables at both ends. Partition roots are not publication or subscription targets in this release. A hierarchy can be replicated through corresponding individual tables, but the guide does not support using that to replicate into a differently partitioned arrangement. The two headline capabilities therefore have a documented boundary where they meet.

PostgreSQL 10 also adds SCRAM-SHA-256 authentication, broader query parallelism, and quorum commit for synchronous replication. Its version numbering now treats 10 as the major version, with 10.1 designated as a minor update. For replication and partitioning, the immediate architectural change is concrete server representation of selected change streams and row-routing rules, alongside explicit tasks that remain with the database operator.