Publication – The Data Publisher (for Logical Replication)

Publication is used in logical replication, where you choose which tables and actions to send to replicas.

On the primary:

CREATE PUBLICATION my_pub FOR TABLE users, orders;

On the replica:

CREATE SUBSCRIPTION my_sub
CONNECTION 'host=10.0.0.1 dbname=app user=replicator password=secret'
PUBLICATION my_pub;

So, Publication = “what to send”
and Subscription = “who receives it”

This allows selective replication — not the entire database, just specific tables or columns.

Analogy

Think of Publication like a newsletter system:

• The primary is the publisher
• The replica is the subscriber
• WAL is the content being delivered
• The replication slot makes sure no one misses an issue

Replication Lag – The Delay

Replication Lag means the replica is behind the primary, it hasn’t yet replayed all WAL changes that were written on the primary. You can measure lag using:

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_replay_lsn());

If the number is small, then replication is healthy. If it’s large, then the replica is delayed (network, disk, or CPU bottleneck).

Common causes of lag:

• Slow network between primary and replica
• Heavy write activity on primary
• Replica hardware too slow
• WAL files not being read fast enough

Analogy

Think of replication as a live video stream:

• The primary is the broadcaster
• The replica is the viewer
• If the internet is slow, the replica sees the video a few seconds late — that’s replication lag

The goal is to keep that delay as small as possible.

How They Work Together

flowchart LR
    A[Primary Database] --> B[WAL - Write Ahead Log]
    B --> C[Replication Slot - Keeps old WAL until read]
    C --> D[Publication - Defines data to send]
    D --> E[Replica Database]
    E --> F[LSN - Tracks position of WAL replay]
    F --> G[Replication Lag - Distance between Primary and Replica]

Flow Summary

1. The primary records every change in the WAL.
2. Each change has an LSN, a unique position marker.
3. The replication slot tells the primary how far the replica has read, so old WALs are not deleted too early.
4. The publication defines what data should be replicated (for logical replication).
5. The replica reads and replays WAL entries until it reaches the same LSN as the primary.
6. The replication lag is the gap between the two LSNs.

Relationship Between pg_replication_slots and pg_last_wal_replay_lsn()

These two are related, but they do not always match exactly.

Key Points

• pg_replication_slots stores metadata on the primary (not on the replica).
• It ensures the primary keeps WAL segments until the replica confirms it has read them.
• pg_last_wal_replay_lsn() lives on the replica and shows how far it has actually replayed changes.
• They are often close but not always identical.

Example

Replica has moved slightly ahead in applying changes,
but the slot on the primary will still retain older WAL until it’s safe to remove them.

Why They Differ

Analogy

Think of PostgreSQL replication like a newspaper delivery:

Sometimes the newsstand (replica) has already read further, but the publisher (primary) keeps older copies just in case.

Summary Table

Conclusion

PostgreSQL replication is powered by WAL, tracked with LSNs, managed safely with replication slots,
filtered with publications, and measured through replication lag. Together, they form a robust and reliable system that:

• Guarantees no data loss between primary and replica
• Allows both full (physical) and selective (logical) replication
• Supports monitoring and tuning through LSN and lag tracking

With this understanding, you can visualize how every part, from the WAL journal to the LSN page markers and replication slots, works together to keep PostgreSQL data safe, synchronized, and consistent across all nodes.