Question

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

Posted on October 23, 2023
Database
Asked by Volodymyr Nabok

I have created a PostgreSQL database cluster, that consists of one primary+standby database and three read-only databases (each read-only database for a specific region).

During development I’ve faced with the next issue. When I write a row into database and then immediately (but after a promise is resolved of course) read the table, then I’ve got dataset without those new row (in most cases, but sometimes the dataset contains all expected rows).

But if I put some delay (600ms, for example), then the dataset contains contains all expected rows for all cases.

My question is: Does such behavior relate to some latency that is appeared because of copying data from primary node to all read-only nodes? If so, then how many time is needed for such copying? Or maybe, if the time is floating and depends on many factors, then how is it possible to measure this latency for my case?

Thanks for the help!

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Bobby Iliev

Site Moderator

• February 24, 2024

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.

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.
Transaction Size: Large transactions can take longer to replicate compared to smaller ones.

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

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;
```
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