Selective asynchronous commits in postgresql - balancing durability and performance

src: Selective asynchronous commits in PostgreSQL - balancing durability and performance - 2025-03-16

🤖 Abstract

The article discusses the synchronous_commit setting in PostgreSQL, which controls the behavior of transaction commits. By default, PostgreSQL uses synchronous commit, ensuring durability by writing transaction changes to the Write-Ahead Log (WAL) and flushing them to permanent storage before acknowledging success. This can be a bottleneck, especially for small, frequent transactions.

The author suggests using asynchronous commit to improve performance by allowing PostgreSQL to acknowledge transaction success before WAL records are flushed. This setting can increase transaction throughput by 30% and reduce I/O and CPU usage significantly. However, it introduces a risk of data loss if the database crashes before the WAL records are written to disk, although data corruption is not a concern.

PostgreSQL allows flexible implementation of synchronous_commit, enabling it to be set per session, transaction, or for specific operations, allowing critical transactions to remain durable while boosting performance for less critical tasks. Additionally, PostgreSQL offers intermediate settings for synchronous_commit that balance performance and durability differently.

The article emphasizes that while turning off synchronous commit can significantly enhance performance, particularly in systems like Aurora PostgreSQL, it’s important to consider the potential for data loss and use the setting judiciously. The author encourages sharing experiences with using synchronous_commit in production environments.

I was recently looking into some workloads that generate a lot of I/O and CPU contention on some very high-write code paths and came across synchronous_commit (https://www.postgresql.org/docs/current/wal-async-commit.html). It can be very tempting to turn this off globally because the performance gains in terms of I/O, CPU, and TPS (transactions per second) are very hard to overlook. I noticed I/O completely gone, CPU down 20% (at peak), and a 30% increase in TPS. However, this comes with important trade-offs that are worthwhile keeping in mind.

What is Synchronous Commit?

By default, PostgreSQL uses synchronous commit, meaning when your application commits a transaction, PostgreSQL:

1. Writes the transaction’s changes to the Write-Ahead Log (WAL)
2. Flushes those WAL records to permanent storage
3. Only then acknowledges success to the client

This ensures durability - if the database crashes immediately after a commit, your transaction is still safe. However, this disk I/O operation is often the bottleneck for transaction throughput, especially for small, frequent transactions.

Enter Asynchronous Commit

Asynchronous commit changes this behavior. When enabled, PostgreSQL acknowledges transaction success immediately after the transaction is logically complete, before WAL records are flushed to disk.

-- Enable asynchronous commit
SET synchronous_commit = off;
 
-- Back to default (synchronous)
SET synchronous_commit = on;

The result? Significantly improved transaction throughput and reduced I/O pressure. In my testing, this simple change has increased transactions per second by 30%, especially on I/O-constrained systems.

The Trade-off: Understanding the Risk Window

The performance gain comes with a trade-off: a small “risk window” between when a transaction is reported as committed and when it’s actually written to disk. If the database server crashes during this window, the most recent transactions could be lost, and this is where it feels wrong to turn on this setting globally. The risk here is data loss, not corruption. PostgreSQL documentation explains this nicely in very plain terms: https://www.postgresql.org/docs/current/wal-async-commit.html

Selective Implementation

Even after testing other settings of synchronous_commit, I’ve found the beauty of this feature is that you don’t have to make a global all-or-nothing choice. You can toggle it:

• Per session
• Per transaction
• For specific operations

This allows for a nuanced approach where critical transactions remain fully durable while less critical operations get performance boosts.

In Ruby on Rails applications, it can be as simple as doing something like this:

def with_synchronous_commit_off(&block)
  ActiveRecord::Base.connection.exec_query("SET synchronous_commit = off")
  yield
ensure
  ActiveRecord::Base.connection.exec_query("SET synchronous_commit = on")
end
 
with_synchronous_commit_off do
  # Perform non-critical bulk operations here
  # e.g., analytics data, logs, or background processing
end

PostgreSQL offers more than just on/off for synchronous_commit. There are intermediate settings that provide different balances of performance and durability:

-- Options from strongest guarantee to highest performance:
SET synchronous_commit = 'remote_apply'; -- Strongest guarantee (for replicas)
SET synchronous_commit = 'remote_write'; -- Strong but faster
SET synchronous_commit = 'local';        -- Local durability only
SET synchronous_commit = 'off';          -- Maximum performance

However, at least on Aurora PostgreSQL, I’ve found the maximum benefit from setting it to OFF. I reckon, due to the way Aurora works and its requirement that 4 out of 6 nodes need to acknowledge a commit (https://aws.amazon.com/blogs/database/amazon-aurora-postgresql-parameters-part-2-replication-security-and-logging/), the rest of the settings may not be doing much or their benefits might get amortized.

Wrap up

I realize this might be a very well-known setting amongst seasoned PostgreSQL users. That said, I hope you found this post useful, and I’d love to hear about your experiences using synchronous_commit in production.