Recommended Free Tools
PostgreSQL logical replication can feed a reporting database with selected table changes, making it useful when reports need only part of a source database. It is not a self-maintaining duplicate: schema changes and sequence state do not replicate, subscriber-side conflicts can stop apply, and lagging replication slots can affect publisher WAL retention. Plan for those responsibilities before relying on the subscriber.
How logical replication works for reporting
A publisher defines publications and a subscriber creates subscriptions to receive changes for selected tables. Initial synchronization normally copies a snapshot; ongoing changes then follow. Within a subscription, the subscriber applies changes in publisher order, preserving transactional consistency for that subscription. PostgreSQL lists analytical consolidation among logical replication’s typical uses. See the PostgreSQL 18 logical replication overview.
As an Amazon Associate I earn from qualifying purchases.
The subscriber is a PostgreSQL database and can publish data onward, but that does not make it safe for applications to write freely to subscribed tables. Local changes can conflict with incoming changes, so a reporting subscriber is simplest when its subscribed tables are read-only to reporting clients.
What does not replicate automatically?
Schema and DDL
Logical replication does not copy schema changes or DDL commands. The subscriber’s schema need not match the publisher in every respect, but its tables must accept incoming rows. If a publisher change makes the arriving data incompatible, apply can fail until the subscriber schema is updated. PostgreSQL recommends applying additive subscriber changes first in many cases to avoid intermittent errors. Treat schema evolution as a coordinated two-sided deployment, not as a migration carried by the publication. The PostgreSQL 17 restrictions documentation states: “The database schema and DDL commands are not replicated.”
#1 Best Overall
Sequence state
Values already stored in serial or identity columns replicate as table data; the state of the sequence object does not. That is generally inconsequential while the reporting database remains read-only. If it may become writable or be promoted during a switchover or failover, reconcile sequence values with the publisher or set them above the relevant table values as part of the promotion procedure.
Views and other unsupported objects
Logical replication supports tables, including partitioned tables, but not views, materialized views, foreign tables, or large objects. Reporting views and summary tables therefore need to be built and maintained separately on the subscriber, and applications relying on large objects need another plan for them.
Rank #2
For partitioned tables, replication normally originates from publisher leaf partitions, so corresponding valid targets must exist on the subscriber. A publication can instead use the root table’s identity and schema with publish_via_partition_root. Review the partition layout and publication setting on both sides. TRUNCATE is supported, but truncating tables connected by foreign keys can fail at the subscriber if the truncation affects tables outside the subscription. Also check replica identity for tables that receive updates or deletes: REPLICA IDENTITY FULL has limitations for some types without a default B-tree or Hash operator class.
How subscriber writes and conflicts can stop apply
Logical apply behaves much like ordinary DML. Incoming changes can violate subscriber constraints, including unique constraints; subscription-owner permissions and applicable row-level security can also matter. A missing row for an update or delete may instead be skipped. When a conflict produces an error, replication stops until someone resolves it. Error details appear in subscriber logs, and conflict statistics are exposed through pg_stat_subscription_stats. Consult the PostgreSQL 18 conflict documentation.
Resolution may mean repairing subscriber data or permissions, or deliberately skipping a transaction. Skipping is not a way to discard only the conflicting row: the whole transaction is skipped, including its otherwise non-conflicting changes. That can leave the reporting copy inconsistent. If skipping is necessary, use the error context and LSN to make and record the decision, then reconcile the affected data.
Why replication-slot health matters to the publisher
A logical replication slot retains the WAL the subscriber still needs. PostgreSQL 18 documents max_slot_wal_keep_size as unlimited by default. A configured cap can bound retained WAL, but if the subscriber falls behind far enough, required WAL may be removed and replication may no longer continue from that slot. Monitor slot state and retained WAL as well as subscriber apply health, and have a recovery or reinitialization plan for a slot that has lost required WAL. See the PostgreSQL 18 replication configuration reference.
Worker capacity also affects initial synchronization and ongoing apply. Table synchronization workers and apply workers share the logical replication worker pool. Account for subscriptions, initial table copies, and publisher change volume when sizing; documented configuration defaults are not recommendations for a particular workload.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Logical subscribers are not physical hot standbys
Settings such as max_standby_streaming_delay and hot_standby_feedback concern query and recovery conflicts on physical standbys. They are not direct controls for logical subscribers. Workload-specific query isolation, resource sizing, and analytics-versus-apply tuning are not settled by the documented behavior alone; measure them with the intended workload on the PostgreSQL major version you deploy.
Operational checklist before relying on a reporting subscriber
- Limit publications to the tables reports need, and verify that every required target is a supported table.
- Plan schema deployments on both sides; where an additive change allows it, make the subscriber compatible before changing the publisher.
- Keep subscribed tables read-only to reporting clients unless you have a deliberate conflict and ownership strategy.
- Check replica identity for tables that receive updates or deletes, including unusual data types if considering
REPLICA IDENTITY FULL. - Review partition layouts and
publish_via_partition_rootbehavior on publisher and subscriber. - Add sequence reconciliation to any plan for a writable subscriber or promotion.
- Watch subscriber logs and
pg_stat_subscription_statsfor conflicts, and monitor publisher slots and WAL retention. - Define who can authorize a transaction skip and how skipped data will be reconciled.
- Validate initial synchronization, schema rollout, slot interruption, conflict recovery, and planned promotion against the deployed major version.
When logical replication fits—and what to compare
Logical replication is a reasonable candidate when reporting needs selected tables rather than a whole-cluster copy. Compare it with a physical standby or a separately refreshed reporting copy against these requirements:
- Whether reports need selected tables or the whole cluster.
- Acceptable freshness and replication lag.
- Whether the reporting database needs independent schema, views, or summary tables.
- How the team will handle coordinated schema changes and apply conflicts.
- The publisher’s WAL-retention exposure and the recovery burden if a slot falls behind.
- Whether promotion or failover is part of the design, including sequence reconciliation.
Check settings and restrictions in the documentation for the PostgreSQL major version actually deployed; the version references here are PostgreSQL 18 documentation for mechanism, conflicts, and configuration, and PostgreSQL 17 for the cited restrictions.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →
