Multi-AZ PostgreSQL COMMIT wait events: WALSync, SyncRep & XactSync

When deploying your PostgreSQL application on AWS, you can replicate the database to multiple Availability Zones to failover in case of failures. This is commonly referred to as “Multi-AZ”, but this covers different options, and the documentation on how it works is not comprehensive. To achieve failover with a Recovery Point Objective (RPO) of zero, the replication must be synchronous to ensure that all changes are replicated when your commit is successful. When PostgreSQL waits, the reason is exposed with wait events, which are visible in pg_stat_activity, or Performance Insights in RDS. By examining the wait events for the COMMIT statement, you can determine the replication method being used by your deployment option. Here are some examples.

I have created three databases:

database-1 is RDS PostgreSQL Multi-AZ DB instance, using block storage replication to another AZ
database-2 is RDS PostgreSQL Multi-AZ DB instance, using PostgreSQL WAL replication to two other AZs
database-3 is RDS Aurora PostgreSQL Compatible, using its proprietary WAL replication to storage servers on three AZs

I described those options in a previous blog post:

On each of them, I’ve run PgBench for one hour, inserting rows from five sessions, with a commit every thousand rows:

I’ll explain what happens on my three databases

Multi-AZ DB instance: IO:WALSync when writing to replicated storage

The Multi-AZ DB instance was the first one available in Amazon RDS. It doesn’t use the database mechanism for replication but is implemented at a lower level on the block storage (EBS), where all database files are stored. For this reason, there is no specific wait event, but the synchronous replication impacts all writes. At COMMIT, the WAL must be synced to persistent storage to guarantee the Durability of ACID, and this is the IO:WALSync event. Because multiple sessions are writing to the monolithic WAL stream, the COMMIT statement also waits on LWLock:WALWrite to synchronize their writes.

Here is the database load during the test:

Here is the output from PgBench:

Replicating all writes is simple but not efficient, as it writes full blocks and waits for the acknowledgment. Performance is acceptable with only one replica but is then limited in terms of availability: if one AZ is down, the database is not protected anymore. This is one reason AWS introduced Aurora, to replicate to more replicas with less I/O transfer.

Multi-AZ DB cluster: IPC:SyncRep when sending WAL to standby instance

The Multi-AZ DB cluster deployment option was added more recently and uses PostgreSQL physical replication with WAL shipping to a standby instance, which applies it to its database and can also be used as a read replica. At commit, in addition to synching the WAL to the local file (IO:WALSync), it synches it with the replica (IPC:SyncRep).

Here is the database load during the test:

Here is the output from PgBench:

This approach offers the benefit of utilizing PostgreSQL native replication, which is widely recognized and efficient. However, it requires a standby database to receive and apply the Write-Ahead Log (WAL), resulting in increased costs. Nonetheless, it enables offloading some read operations, such as reporting, to the replicas, particularly those that can tolerate staleness. Having a replica ready to be activated also reduces the Recovery Time Objective (RTO).

Multi-AZ Aurora: IO:XactSync when sending WAL to storage servers

Aurora doesn’t write the WAL on local storage but sends it to the storage servers using its proprietary compute storage process. It is instrumented with a wait event that doesn’t exist in PostgreSQL: IO:XactSync

Here is the database load during the test:

Here is the output from PgBench:

The higher latency and lower throughput are due to the process of writing to storage with six replicas across three Availability zones. In this process, four out of the six replicas must acknowledge the Write-Ahead Log (WAL), which is later applied to blocks. The read replicas also need to receive the WAL asynchronously to invalidate their cache and read the newer version from the distributed storage. Contrary to popular belief, Aurora offers additional features such as higher availability and faster point-in-time recovery, but not necessarily better performance.

Summary

Protecting the database from failures impacts performance because synchronous replication is required to allow for no-data-loss recovery. While a single PostgreSQL cluster is always faster, it may lose data or experience high downtimes. Multi-AZ allows access to the database from multiple availability zones, but it is an active-passive configuration where only one instance at a time can open the database for read/write operations. To take it a step further and connect your PostgreSQL application to a database active on all Availability Zones, you can obtain YugabyteDB from the marketplace. When you compare the performance, it is important to compare with Multi-AZ deployments.