PostgreSQL流复制之上启用逻辑复制的可行性、实践及性能考量

Great question—this is a super common scenario when bridging operational PostgreSQL databases with data warehouses for reporting or analytics. Let's break down all your concerns clearly:

Absolutely—PostgreSQL fully supports running both physical streaming replication and logical replication simultaneously. They operate at distinct layers:

• Streaming replication replicates entire WAL (Write-Ahead Log) blocks at the physical level, creating an exact bit-for-bit copy of your primary database.
• Logical replication parses WAL into human-readable logical change events (like INSERT, UPDATE, DELETE statements) and syncs only those events via a publish-subscribe model.

You can even chain them: primary → physical replica → data warehouse (via logical replication) to offload work from your primary server entirely.

Feasibility #

Yes, it’s technically feasible to run both on the same primary server or database. Logical replication uses separate replication slots and background processes that don’t interfere with the physical streaming replication pipeline.

Best Practice? #

It depends entirely on your workload:

• If your primary has low to moderate load: Running logical replication directly from the primary is acceptable, especially if you don’t want to manage an extra replica server.
• If your primary is under heavy application load: This is not a best practice. The CPU and I/O overhead of parsing WAL into logical events will compete with your user traffic, potentially slowing down your application. Instead, use a physical replica as the source for logical replication—this is the recommended approach for most production environments.

There are a few key performance tradeoffs to plan for:

1. Increased WAL volume: Enabling logical replication requires setting wal_level = logical (up from the default replica for streaming replication). This makes WAL files larger because they now include detailed logical change metadata, which increases disk I/O on the primary and WAL transfer bandwidth to replicas.
2. CPU overhead: Parsing WAL into logical events is CPU-intensive. If you run this on the primary, it will take cycles away from your application. On a replica, it adds to the CPU load already used for replaying physical WAL.
3. WAL retention risks: If your logical replication subscriber (data warehouse) falls behind, the replication slot will retain WAL files to avoid data loss. This can lead to rapid disk bloat if not monitored closely—same risk applies to physical replication slots, but logical replication can exacerbate it if the subscriber is slow to consume events.

• Set wal_level = logical first: This is a non-negotiable prerequisite for logical replication, and it requires a PostgreSQL restart. Plan a maintenance window for this change—note that physical streaming replication still works perfectly with wal_level = logical.
• Use a physical replica as your logical replication source: Offload the WAL parsing work to a replica to protect your primary’s performance. Ensure the replica has hot_standby_feedback = on to prevent the primary from vacuuming data the replica still needs, and increase max_replication_slots to accommodate both physical and logical slots.
• Monitor replication slots religiously: Check the pg_replication_slots view regularly. Stalled slots (from disconnected subscribers) will hoard WAL files and eat up disk space. Delete any unused slots immediately.
• Tune WAL-related parameters:
  • Increase wal_keep_size to ensure replicas (physical or logical) don’t run out of WAL to replay.
  • Bump max_wal_senders to accommodate the extra connections needed for logical replication (each logical subscription uses one sender process).
• Filter your replication sets: Don’t replicate unnecessary data. Logical replication lets you publish specific tables, columns, even rows (via WHERE clauses). Only sync the data your data warehouse actually needs to reduce bandwidth and processing overhead.
• Test in staging first: Simulate your production workload, streaming replication traffic, and logical replication load in a staging environment to gauge resource usage. This will help you avoid unexpected performance hits in production.
• Know logical replication limitations: Logical replication doesn’t automatically sync DDL changes (you’ll need triggers or custom scripts if you need this), has limited support for some exotic data types, and can struggle with extremely large transactions. Make sure these limitations align with your data warehouse sync requirements.

内容的提问来源于stack exchange，提问作者Kris