PostgreSQL Logical Replication Performance - How to Not Let WAL Bloat Kill Your Database

WAL bloat will destroy your PostgreSQL server faster than you can say "disk full". Here's the shit you need to know.

Write-Ahead Logging is fundamental to PostgreSQL's ACID compliance, but logical replication turns it into a resource hog.

The WAL Retention Problem

Logical replication is a WAL retention nightmare. Physical replication consumes WAL in real-time, but logical replication? It hoards WAL like a data pack rat until every subscriber catches up.

One stuck slot = server death. I've watched our analytics server go from 50GB to 487GB in 6 hours because the data science team's Jupyter notebook went to sleep with an active subscription. The slot kept accumulating WAL while PostgreSQL helpfully logged "checkpoint starting: time" every 5 minutes like nothing was wrong.

max_slot_wal_keep_size: Your Insurance Policy

-- Set this or suffer
max_slot_wal_keep_size = 50GB

This kills replication slots when they get greedy. Yeah, it breaks replication, but it's better than taking down production. The alternative is explaining to your CEO why the customer-facing API returned 500 Internal Server Error for 3 hours because Jenkins had a stuck logical replication slot named debezium_analytics_slot that ate the root partition.

PostgreSQL 13 finally added this parameter after years of production disasters. Before this, your only option was manually dropping slots while watching disk space like a hawk.

Basic WAL Tuning That Actually Matters

Stop overthinking this. Here's what actually moves the needle:

-- Default wal_buffers sucks for logical replication
wal_buffers = 64MB

-- Compress WAL if you're paying for bandwidth
wal_compression = on

-- Don't run out of slots
max_replication_slots = 20
max_wal_senders = 20

The default wal_buffers is laughably small for logical replication - just 3% of shared_buffers when logical replication hits WAL like a freight train. I've seen logical decoding workers sitting idle waiting for WAL to flush while htop shows 3% CPU usage. One ALTER SYSTEM SET wal_buffers = '64MB' followed by a restart gave us 40% better replication throughput on our 32-core production box.

WAL compression saves bandwidth but costs CPU. If you're replicating across regions or paying AWS transfer costs, turn it on. If everything's in the same datacenter, skip it.

Monitor This Shit Before It Breaks

Copy this query and run it every 5 minutes:

-- The query that will save your ass
SELECT
  slot_name,
  database,
  active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 AS lag_mb
FROM pg_replication_slots
WHERE pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824; -- 1GB

Alert on this or explain the downtime to your boss:

• Any slot over 10GB (you have hours, maybe less)
• Any inactive slot over 1GB (someone's laptop died or network hiccupped)
• Disk space under 30% free (start panic planning)
• Any slot that's been inactive more than 4 hours (something's broken)

Set up postgres_exporter for Prometheus or just cron this query and email yourself. Use Grafana for visualization and alerting. Check out this comprehensive monitoring guide for more PostgreSQL monitoring strategies. I don't care if you use Grafana, Zabbix, or email alerts from a cron job. Just do it before you're in a 6am conference call explaining why logical replication filled /var/lib/postgresql and brought down the entire e-commerce platform during Black Friday.

Heartbeats for Dead Database Syndrome

Got databases that sit idle for hours? Your slots will get stuck even when nothing's wrong. PostgreSQL 14 added a hack for this:

-- Let your replication user send heartbeats
GRANT EXECUTE ON FUNCTION pg_logical_emit_message(boolean, text, text) TO replication_user;

-- Cron this every few minutes on quiet databases
SELECT pg_logical_emit_message(false, 'heartbeat', now()::varchar);

This tricks the slot into thinking something happened so it advances the LSN. It's a hack, but it works. Better than watching slots accumulate WAL because your analytics database only gets updated once a day.

Decoding Performance: The CPU Killer

Logical replication uses way more CPU than streaming because it has to decode WAL instead of just shipping bytes. Here's how to not let it kill your primary:

Don't use test_decoding in production. Use pgoutput - it's binary protocol instead of JSON text parsing. Much faster.

Fix disk spills that kill performance:

-- Default 64MB is tiny for real workloads
logical_decoding_work_mem = 256MB

Check if you're spilling to disk:

SELECT slot_name, spill_txns, total_txns,
       round(100.0 * spill_txns / total_txns, 1) as spill_pct
FROM pg_stat_replication_slots
WHERE total_txns > 0;

If spill_pct is over 10%, bump up the memory. Disk spills murder performance because logical decoding has to write temp files then read them back.

Don't Replicate Everything Like an Idiot

-- This kills performance
CREATE PUBLICATION my_pub FOR ALL TABLES;

-- This doesn't
CREATE PUBLICATION smart_pub FOR TABLE
  users, orders, inventory
  WHERE (status != 'deleted');

Row filtering and column lists (PostgreSQL 15+) make a huge difference. Instead of replicating 100 tables when you only need 10, replicate only what matters.

Real numbers from our 2TB production database: going from ALL TABLES (127 tables) to 10 specific tables cut WAL decoding CPU from 45% to 12% and network traffic from 2.1GB/hour to 380MB/hour. The difference is staggering - selective publications are mandatory for any serious deployment.

Emergency: When Shit Hits the Fan

When your disk is 90% full and replication slots are the problem, here's your nuclear option:

-- Find the bastard slots eating your disk
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag
FROM pg_replication_slots
WHERE pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 10737418240; -- 10GB

-- Nuclear option: kill the slot
SELECT pg_drop_replication_slot('the_slot_killing_your_server');

-- Verify the space was actually freed (may take a few minutes)
SELECT pg_size_pretty(sum(size)) as total_wal_size
FROM pg_ls_waldir();

-- Force WAL segment cleanup if needed
SELECT pg_switch_wal();
CHECKPOINT;

Yes, this breaks replication. Yes, you'll need to recreate everything from scratch. But it's better than explaining to your boss why the entire application is down because logical replication ate all the disk space.

Document this procedure and stick it in your team wiki with giant red letters. When PagerDuty wakes you up at 3:17am with "CRITICAL: Database disk 97% full", you want these commands copy-pasteable, not buried in documentation.

Publisher-side tuning keeps your server alive. Subscriber-side tuning determines if your replication actually keeps up or falls behind until slots get invalidated.

Here's the thing: you can have perfect WAL settings on the publisher, but if your subscriber can't keep up with the apply workload, you're still fucked. The apply workers become the bottleneck and everything backs up. Check the PostgreSQL subscription documentation for configuration details and performance considerations.

Parallel Apply Workers: Finally, PostgreSQL Gets Threading

PostgreSQL 14 added parallel apply workers. About damn time. Instead of one worker choking on your transaction stream, you get multiple workers that can process non-conflicting stuff simultaneously.

-- Turn on parallel workers (PostgreSQL 14+)
ALTER SUBSCRIPTION my_sub SET (parallel_apply = on);
max_logical_replication_workers = 16

Expect 40-60% improvement with mixed workloads. We tested this on 16-core systems processing 2,400 transactions/minute: 70% small transactions (1-5 rows), 30% medium updates (50-200 rows) across different table sets. Won't help much if you have giant transactions that touch the same tables constantly - those still bottleneck on lock contention.

Reality check: Parallel workers help with high transaction volume but create contention if you're updating the same rows constantly. I've seen diminishing returns past 8 workers for most workloads. Start with 4, monitor lag, adjust up.

The documentation makes it sound more complicated than it is. Turn it on, set reasonable limits, watch what happens.

Resource Tuning: Give Apply Workers Some RAM

work_mem = 64MB  -- Each apply worker gets this much
max_worker_processes = 16  -- Don't starve other stuff

Apply workers compete with your regular queries for resources. If your subscriber is also serving read traffic, plan accordingly.

The default work_mem of 4MB is a joke for logical replication. Apply workers processing large UPDATEs will spill to disk constantly. Bump it to 64MB minimum.

Network and Storage: The Usual Suspects

SSL compression actually helps here:

-- In your subscription connection string
'host=publisher port=5432 dbname=source_db user=repl_user
 sslmode=require sslcompression=1 keepalives_idle=600'

Storage matters for subscribers:

• Use SSDs. Apply workers do random writes to update existing rows
• Turn off synchronous_commit on subscribers (NOT publishers)
• Separate WAL and data if you have multiple drives

-- On subscriber only (never on publisher)
synchronous_commit = off

PostgreSQL 17 improved batch commits for logical replication with better transaction grouping. Also added synchronized_standby_slots for proper failover slot coordination. Fewer fsync operations when processing lots of small transactions. Nice improvement but not revolutionary.

Table Structure: Don't Make Apply Workers Suffer

Tables without primary keys are death. Apply workers scan entire tables for each UPDATE/DELETE. This scales like shit:

-- Find tables that will murder apply performance
SELECT schemaname, tablename
FROM pg_tables t
WHERE NOT EXISTS (
    SELECT 1 FROM pg_constraint c
    WHERE c.conrelid = (t.schemaname||'.'||t.tablename)::regclass
    AND c.contype = 'p'
);

Fix this or use REPLICA IDENTITY FULL as a last resort. FULL identity sends entire row images but at least apply workers don't have to scan.

Indexes matter. Apply workers need indexes to find rows efficiently. Monitor what they're actually using:

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_scan DESC;

If idx_scan is zero on subscriber indexes that exist on publisher, something's wrong with your table structure.

REPLICA IDENTITY FULL: The Bandwidth vs CPU Trade-off

-- This eats bandwidth but makes apply workers happy
ALTER TABLE problematic_table REPLICA IDENTITY FULL;

FULL identity sends complete row images. 2-4x more network traffic but apply workers don't have to hunt for rows. Use when network bandwidth isn't the bottleneck and you need consistent row data for analytics.

Monitor WAL impact:

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
FROM pg_replication_slots
WHERE slot_name = 'your_slot';

If this number keeps growing fast, REPLICA IDENTITY FULL might be killing you.

Monitor Apply Lag Before It Kills You

-- Check if apply workers are keeping up
SELECT subscription_name, pid, latest_end_time
FROM pg_stat_subscription;

Common apply worker failures:

1. Lock contention: Apply workers stuck behind long-running queries
2. Resource limits: Running out of connections or memory
3. Large transactions: Single huge transaction blocking everything

Check pg_stat_subscription when apply lag starts growing. Usually it's one of these three.

Performance Testing Reality

Test with realistic workloads, not toy data:

## Use pgbench to simulate real load
pgbench -i -s 100 test_db
pgbench -c 20 -j 4 -T 600 test_db

Monitor replication lag during testing:

SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag
FROM pg_replication_slots;

If lag keeps growing during steady load, you have a problem. Fix it before production.