/ Shayon Mukherjee / blog

Selective asynchronous commits in PostgreSQL - balancing durability and performance

March 16, 2025
~3 mins

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:

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

Intermediate Settings

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.

last modified March 16, 2025