logical replication issue: increasing wal files and replication lag

Question

I have 2 PostgreSQL 16 instances in different location and I am trying to apply logical replication between couple of tables even there are thousands of tables in publisher site. I mean there are about 5000 tables in publisher site but I am using logical replication for only 60 tables

All the replicated tables are in sync as number of record count currently. I have checked with select count(*) from table

My problem is when I run following query I could see increasing huge replication lag and number of files in WAL directory increasing (number of wal files are 9952) If I drop subscription and replication slot, postgresql cleaning WAL directory

# SELECT

slot_name,
    confirmed_flush_lsn,
    pg_current_wal_lsn(),
    (pg_current_wal_lsn() - confirmed_flush_lsn) AS lag
FROM pg_replication_slots;
                 slot_name                 | confirmed_flush_lsn | pg_current_wal_lsn |     lag      
-------------------------------------------+---------------------+--------------------+--------------
 log_rep_slot                              | 6C5/2C77D4E0        | 6E7/F6A54B40       | 149420865120
 pg_316283_sync_248141_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248213_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248175_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248194_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248206_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248232_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248159_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248147_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248129_7306809026670557899 |                     | 6E7/F6A54B40       |             
 pg_316283_sync_248165_7306809026670557899 |                     | 6E7/F6A54B40       |             
(11 rows)

So why WAL files and replication lag are increasing even if all my replicated tables are in sync

Answer 1

My guess: you have long running transactions on the publisher, and you didn't define the subscription with streaming = parallel.

But wait a minute: you still have some workers running that copy original table contents. Replication won't start until they are done. Perhaps you should start replication with a few tables and then use ALTER PUBLICATION and ALTER SUBSCRIPTION ... REFRESH PUBLICATION to add more tables little by little.

Once the table sync workers are done, replication should start to catch up (check restart_lsn in pg_replication_slots). If logical decoding is too slow for your rate of WAL creation, increase logical_decoding_work_mem on the publisher. If that is not sufficient, you are out of luck, and logical replication won't work for you. If it is the subscriber that is too slow applying the changes, you can split the workload to several publications and subscriptions, or you can try to increase max_parallel_apply_workers_per_subscription on the subscriber.