Is PostgreSQL highly consistent or eventually consistent?

Are SQL databases always highly consistent? Let’s explore this in today’s article.

PostgreSQL supports two types of replications: synchronous and asynchronous replication. Let’s briefly explore both:

Asynchronous Replication

• In asynchronous replication, the primary server (master) sends updates to replica servers (standby) without waiting for confirmation that the changes have been applied.
• The primary server continues processing transactions without being delayed by the replication process.
• Asynchronous replication is commonly used when there is tolerance for some delay in data consistency between the primary and replica servers.

Configuration

• Asynchronous replication is the default setting in PostgreSQL. You can set the synchronous_commit configuration parameter to control the level of synchronization. When set to `off`, it indicates asynchronous replication.

synchronous_commit = off

Synchronous Replication

• In synchronous replication, the primary server waits for confirmation from one or more replica servers that the changes have been successfully written before acknowledging a transaction commit to the application.
• This ensures a higher level of data consistency between the primary and replica servers.
• However, it may introduce some latency in transaction processing, as the primary server needs to wait for confirmation from the replicas.

Configuration

• To enable synchronous replication, you can set the synchronous_commit parameter to a value other than `off`. For example:
  synchronous_commit = on
• You can also set the synchronous_standby_names parameter to specify which replicas should confirm transactions synchronously.
  synchronous_standby_names = ‘standby1,standby2’
• This indicates that transactions should be considered committed only when both standby1 and standby2 have confirmed the changes.

Choosing between asynchronous and synchronous replication depends on the specific requirements of your application in terms of data consistency and performance. Asynchronous replication is often chosen when low-latency writes and high throughput are priorities, while synchronous replication is preferred when maintaining strong data consistency is critical.