Synchronous Replication in PostgreSQL: How It Works and When to Use

In PostgreSQL, synchronous replication ensures that data changes on the primary server are confirmed by one or more standby servers before the transaction is considered complete. This guarantees no data loss in case of a primary server failure by waiting for standby acknowledgment.

How It Works

Synchronous replication in PostgreSQL works like a handshake between the main database (primary) and one or more backup databases (standbys). When you save data on the primary, it sends the changes to the standby servers and waits for them to confirm they have received and saved the data.

Think of it like sending a letter and waiting for a reply to be sure the letter arrived safely before you move on. This way, if the primary server crashes, the standby has the exact same data, so no information is lost.

This process adds a small delay to transactions because the primary waits for confirmation, but it increases data safety and consistency across servers.

Example

This example shows how to set up synchronous replication in PostgreSQL by configuring the primary server to wait for a standby server's confirmation before completing transactions.

sql

ALTER SYSTEM SET synchronous_standby_names = 'standby1';

-- Reload configuration to apply changes
SELECT pg_reload_conf();

-- On the standby server, ensure it is connected and streaming
-- This setup requires proper replication slots and permissions configured beforehand.

Output

ALTER SYSTEM
 pg_reload_conf
----------
 t
(1 row)

When to Use

Synchronous replication is best when you need strong data safety and cannot afford to lose any committed transactions, such as in financial systems, order processing, or critical business applications.

It is useful when downtime or data loss would cause serious problems, and a slight delay in transaction speed is acceptable.

However, if your priority is speed and you can tolerate some data loss in a failure, asynchronous replication might be better.

Key Points

• Synchronous replication waits for standby confirmation before finishing transactions.
• It prevents data loss by ensuring standby servers have the latest data.
• This method adds some delay but increases reliability.
• Ideal for critical systems where data safety is a priority.
• Requires proper configuration of standby servers and replication slots.

Key Takeaways

Synchronous replication ensures no data loss by waiting for standby confirmation before completing transactions.

It adds a small delay but guarantees data consistency between primary and standby servers.

Use it for critical applications where data safety is more important than speed.

Requires configuration of synchronous standby names and replication setup.

Asynchronous replication is faster but may risk data loss on failure.