PostgreSQL Performance & Query Optimization · Lektion

Publikationen, Subscriptions und Replica Identity

Konfigurieren Sie selektive Replikation und die erforderliche Replica Identity für korrektes Streaming von UPDATE und DELETE.

Lektion 1 von 413 Schritte

Publikationen, Subscriptions und Replica Identity ist eine kostenlose PostgreSQL Performance & Query Optimization-Lektion auf CoddyKit. Dies ist Lektion 1 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des PostgreSQL Performance & Query Optimization-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der PostgreSQL Performance & Query Optimization-Kurs umfasst insgesamt 4 Lektionen.

Teile dieser Lektion wurden noch nicht übersetzt und werden auf Englisch angezeigt.

Why Logical Replication for Performance

Physical (streaming) replication ships the entire WAL byte-for-byte to identical replicas. Logical replication instead decodes the WAL into row-level change events (INSERT/UPDATE/DELETE) and streams only the tables you choose.

Selective: replicate a hot subset of tables, not the whole cluster.
Cross-version & cross-schema: publisher and subscriber can differ in major version and have extra columns or indexes.
Performance use cases: offload reporting queries to a read replica, build a slimmer OLAP copy, or shard write traffic.

The two building blocks are a PUBLICATION on the source and a SUBSCRIPTION on the target.

Enabling Logical Decoding

Logical replication requires the WAL to carry enough information to reconstruct rows. Set wal_level = logical on the publisher (a server restart is required).

max_wal_senders must allow one slot per subscription plus headroom.
max_replication_slots bounds the number of logical slots.

Each subscription consumes one replication slot, which pins WAL on the publisher until the subscriber confirms it. An inactive subscriber can therefore cause unbounded WAL growth.

SHOW wal_level;

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;

-- Restart PostgreSQL, then verify
SHOW wal_level;

Creating a Publication

A publication is a named set of changes from one or more tables. You decide which tables and which operations are published.

FOR TABLE lists specific tables.
FOR ALL TABLES publishes every current and future table (superuser only).
publish controls which DML types stream: insert, update, delete, truncate.

Publishing only the operations you need reduces WAL decoding work and network traffic.

CREATE PUBLICATION orders_pub
  FOR TABLE orders, order_items
  WITH (publish = 'insert, update, delete');

-- A reporting feed that ignores deletes entirely
CREATE PUBLICATION analytics_pub
  FOR TABLE orders
  WITH (publish = 'insert, update');

Row and Column Filtering (PG 15+)

PostgreSQL 15 added row filters and column lists to publications, letting you replicate only the slice of data the subscriber needs. This shrinks the replicated dataset and the decoding cost.

A WHERE clause filters rows; it may only reference replicated columns.
A column list replicates a subset of columns and must include the replica identity columns.

This is ideal for building a lean reporting copy that excludes archived rows or sensitive columns.

-- Only stream active, recent orders, and only chosen columns
CREATE PUBLICATION active_orders_pub
  FOR TABLE orders (id, customer_id, total, status)
    WHERE (status = 'active' AND created_at > '2025-01-01');

Creating a Subscription

On the target server, a SUBSCRIPTION connects to the publisher, creates a replication slot, and starts applying changes. The target tables must already exist with compatible columns.

copy_data = true (default) snapshots existing rows first, then streams live changes.
Each subscription spawns an apply worker on the subscriber.

The connection string points back to the publisher with a role that has REPLICATION (or is superuser) and can read the published tables.

CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=pub.db port=5432 dbname=shop user=repl password=secret'
  PUBLICATION orders_pub
  WITH (copy_data = true, create_slot = true, enabled = true);

The Core Problem: Identifying Rows

An INSERT carries the full new row, so the subscriber can simply insert it. But an UPDATE or DELETE must tell the subscriber which existing row to change. That requires the WAL to contain an identifying "old" image of the row.

This identifying image is the replica identity. Without it, the publisher cannot encode the old key values, and the change either fails to apply or is silently dropped.

INSERT: needs no replica identity.
UPDATE / DELETE: requires a usable replica identity on the published table.

Replica Identity Modes

Every table has a REPLICA IDENTITY setting that controls what old-row data is written to the WAL for UPDATE and DELETE:

DEFAULT: logs the columns of the primary key. The usual, efficient choice.
USING INDEX: logs the columns of a chosen unique, non-partial, NOT NULL index.
FULL: logs the entire old row; the subscriber matches on all columns. Correct but expensive.
NOTHING: logs no old image; UPDATE/DELETE on the table then fail to replicate.

ALTER TABLE orders REPLICA IDENTITY DEFAULT;
ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_uniq_idx;
ALTER TABLE orders REPLICA IDENTITY FULL;
ALTER TABLE orders REPLICA IDENTITY NOTHING;

Tables Without a Primary Key

With the DEFAULT replica identity but no primary key, a table effectively behaves like NOTHING: UPDATE and DELETE cannot be replicated. PostgreSQL raises an error such as "cannot update table because it does not have a replica identity and publishes updates".

You have three fixes, in order of preference:

Add a primary key (best for performance).
Add a unique, NOT NULL index and set REPLICA IDENTITY USING INDEX.
Set REPLICA IDENTITY FULL as a last resort.

-- Preferred: give the table a stable key
ALTER TABLE events ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY;
ALTER TABLE events ADD PRIMARY KEY (id);

-- Or pin an existing unique index as the identity
CREATE UNIQUE INDEX events_key ON events (tenant_id, occurred_at);
ALTER TABLE events REPLICA IDENTITY USING INDEX events_key;

The Cost of REPLICA IDENTITY FULL

REPLICA IDENTITY FULL writes every column of the old row into the WAL on each UPDATE and DELETE. On wide or high-churn tables this inflates WAL volume and slows the publisher.

It also hurts the subscriber: to find the matching row it must compare all columns. Before PostgreSQL 16 this meant a sequential scan per change; PG 16+ can use a usable index on the subscriber, but full-row matching is still costlier than a key lookup.

Use FULL only when no unique key exists and you cannot add one.

Inspecting Replica Identity

The replica identity is stored in pg_class.relreplident: d = default (primary key), i = using index, f = full, n = nothing. Audit your published tables before going live so no UPDATE/DELETE silently fails to replicate.

Tables published with update or delete must not have relreplident = 'n' unless they also lack a usable key.
psql's \d+ tablename also prints the replica identity.

SELECT n.nspname AS schema,
       c.relname AS table,
       c.relreplident AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY c.relname;

Monitoring Lag and Slot Health

For a performance architecture you must watch replication health. The publisher exposes apply progress via pg_stat_replication, and slot retention via pg_replication_slots.

pg_replication_slots.active false plus growing restart_lsn distance means WAL is piling up because a subscriber is stuck.
On the subscriber, pg_stat_subscription.latest_end_lsn versus the publisher's current LSN shows apply lag.

Set max_slot_wal_keep_size so a dead subscriber cannot fill the publisher's disk.

SELECT slot_name,
       active,
       wal_status,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Quick Check: Replica Identity

A published table needs the right replica identity for UPDATE/DELETE streaming. Test your understanding.

Beantworte es in der interaktiven Lektion

Recap

You configured selective logical replication and the replica identity that makes UPDATE/DELETE streaming correct.

Publisher: wal_level = logical, then CREATE PUBLICATION with chosen tables, operations, and (PG 15+) row/column filters.
Subscriber: CREATE SUBSCRIPTION spawns an apply worker and a replication slot; copy_data seeds existing rows.
Replica identity: INSERT needs none; UPDATE/DELETE need DEFAULT (primary key), USING INDEX (unique NOT NULL index), or FULL. NOTHING and PK-less DEFAULT break UPDATE/DELETE.
Performance: prefer a key-based identity over FULL; monitor slots and lag, and cap retained WAL with max_slot_wal_keep_size.

Kostenlos starten

Lerne SQL mit einem KI-Tutor — kostenlos

Schreibe und führe echten Code in deinem Browser aus, bekomme sofortige Hilfe von einem 24/7 KI-Tutor und setze dein Lernen im Web oder in der App fort.

Kurse: 22
Lektionen: 88

Kostenlos lernen Den vollständigen Kurs ansehen

Häufig gestellte Fragen

Ist die Lektion „Publikationen, Subscriptions und Replica Identity“ kostenlos?

Ja — der vollständige Text von „Publikationen, Subscriptions und Replica Identity“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des PostgreSQL Performance & Query Optimization-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der PostgreSQL Performance & Query Optimization-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Publikationen, Subscriptions und Replica Identity“?

Konfigurieren Sie selektive Replikation und die erforderliche Replica Identity für korrektes Streaming von UPDATE und DELETE. Du übst PostgreSQL Performance & Query Optimization mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um PostgreSQL Performance & Query Optimization zu starten?

Keine Vorkenntnisse erforderlich. PostgreSQL Performance & Query Optimization auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 1 von 4.

Wie lange dauert die Lektion „Publikationen, Subscriptions und Replica Identity“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser PostgreSQL Performance & Query Optimization-Lektion Code schreiben und ausführen?

Ja. Jede PostgreSQL Performance & Query Optimization-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

Publikationen, Subscriptions und Replica Identity
Lese- und Analyse-Workloads auslagern
Major-Upgrades mit nahezu null Ausfallzeit
Replikationsverzögerung und Slot-Bloat überwachen

← Zurück zu PostgreSQL Performance & Query Optimization