Speed of copying data from PostgreSQL primary node to read-only nodes

Hi there,

The behavior you’re experiencing is indeed related to replication latency within your PostgreSQL database cluster. When you write data to the primary node, it takes some time for these changes to be replicated to the read-only nodes. The exact amount of time can vary due to several factors, including network latency, the size of the data being replicated, the load on the primary node, and the configuration of your replication setup.

1. Write Load: High write loads on the primary node can lead to replication lag as the system works through the queue of changes to be replicated.

2. Transaction Size: Large transactions can take longer to replicate compared to smaller ones.

To measure replication latency, you can use the following approaches:

1. pg_stat_replication View: This view provides information on the replication status of standby servers. The delay column can give you an idea of the replication lag.

   You can query it like so (run this on your primary node):

   SELECT * FROM pg_stat_replication;
   

2. Log Sequence Number (LSN) Differences: You can compare the write-ahead log (WAL) positions between the primary and standby nodes to measure lag. On the primary, get the current LSN with:

   SELECT pg_current_wal_lsn();
   

   Then, on a standby node, see how far it is behind with:

   SELECT pg_last_wal_replay_lsn();
   

   The difference between these values indicates replication lag.

If you require up-to-date reads immediately after a write, consider reading from the primary node for those transactions or implementing application-side logic to check for the presence of the newly written data before proceeding.

Generally speaking, replication latency is normal in distributed database systems but can be managed and mitigated through careful configuration and design.

Best,

Bobby