[HN Gopher] Selective async commits in PostgreSQL - balancing du...
___________________________________________________________________
Selective async commits in PostgreSQL - balancing durability and
performance
Author : shayonj
Score : 97 points
Date : 2025-03-16 17:24 UTC (3 days ago)
(HTM) web link (www.shayon.dev)
(TXT) w3m dump (www.shayon.dev)
| feverzsj wrote:
| It just breaks the transaction guarantee. In most cases, you
| don't want it. In other cases, like logging, pipeline mode may be
| better suited.
| ddorian43 wrote:
| No? Can you explain?
|
| It breaks the durabality for that transaction until a new
| transaction with synchronous commit 'on' completes or time
| passes for auto-fsync.
| feverzsj wrote:
| On client side, there is no explicit way to know which
| transaction is broken when there are multiple transactions
| undergoing. If your use case relies on transactional
| assumption, it may fail at some point.
| ddorian43 wrote:
| On client side you know because you're setting the async-
| commit on a case basis.
|
| All transactions work the same. When/If the server fails,
| some transactions (that you allowed yourself) may be not
| durable (or not replicated). But all things are still
| transactional.
| kelseydh wrote:
| We use PostgreSQL locally but moved our production database over
| to AWS Aurora.
|
| If I use `SET synchronous_commit = off;` in my Rails code will
| Aurora also mimic this behaviour?
| shayonj wrote:
| Yes, i touched a bit on it towards the end. If you turn it off,
| Aurora won't wait for 4 out of 6 nodes to acknowledge the
| commit (https://aws.amazon.com/blogs/database/amazon-aurora-
| postgres...)
| cryptonector wrote:
| I suspect many apps would benefit from splitting the commit
| completion notification into two pieces:
|
| - commit logically committed (meaning it won't fail to complete
| for any reason other than power or hardware failure, and in
| particular no trigger or other such logic can keep it from
| completing)
|
| - commit completed durably (i.e., all associated writes have
| reached stable, persistent storage, and future reads will see
| them even if there is a power failure in between)
|
| A UI could indicate that a transaction is logically complete and
| have a second way to indicate that the transaction is durably
| committed.
| ddorian43 wrote:
| Probably higher cost to send 2 notifications to the user
| compared to always fsync on all writes in a SSD.
| shayonj wrote:
| +1 - I have found its better to instead use it on paths that
| you know are idempotent and recoverable. Liking expiring data
| on a cron and so on.
| cryptonector wrote:
| The network is faster then the storage.
| rubiquity wrote:
| No it isn't and hasn't been for quite some time and it will
| probably remain that way for the rest of our lives.
| koolba wrote:
| IIRC, that doesn't work in practice because once you have a
| synchronous commit requested, all existing async commits that
| preceded it must be guaranteed to be fsynced for that sync
| commit to be fsynced. Which kind of makes sense if you think
| about the later work potentially reading the result of the
| earlier one.
|
| The high throughput gains if async commit shine when the
| totality of the workload is async.
| rzwitserloot wrote:
| I think you mean: If anybody anywhere decided to invoke a
| wait() until the state "my COMMIT; has been durably
| committed", then that means all previous commits must also be
| in durably committed state before we can continue.
|
| Sure. But this doesn't affect other code that is merely
| wait()ing for 'logically committed'. Not in the past, and
| usefully, not in the future either. The 'logical system' can
| be far ahead of the 'durable' system, it doesn't have to
| wait.
|
| Where it would go wrong is if code A needs to wait around for
| code B to do a thing (such as provide a value), and B's code
| for some reason contains a wait() on durable. Possibly
| because B was written before the split in commit behaviour
| was around, and for backwards compatibility reasons, for them
| COMMIT; means: "wait for durable commit".
|
| Given that `SET synchronous_commit = off;` works per
| transaction, I'm kinda inspired here. I can think of a few
| places where we commit for various logical reasons but I
| don't need the fsync guarantee.
| x0x0 wrote:
| Right, but there are common use cases (audit, log, analytics
| tables) where you can tolerate a small risk of loss and get
| some hefty speedups.
| paulddraper wrote:
| But....
|
| Only the sync committer needs to wait for those. (Nothing
| changes.)
|
| The async committers can go on their merry way.
| ltbarcly3 wrote:
| Just run work in parallel if possible. You are fighting fsync
| latency in large part, which can be done in parallel.
| ndriscoll wrote:
| Parallel doesn't really help that much IME, but batching your
| work into larger transactions to amortize locks and fsyncs
| does.
| shayonj wrote:
| +1
| anarazel wrote:
| Concurrent commits can group commit with one WAL flush (i.e.
| one fsync/fdatasync).
| drdaeman wrote:
| I'm not sure working in parallel is always a good decision.
|
| An anecdote: just the other day I've had to implement batching
| instead of concurrent parallel processing because PostgreSQL
| really hated me having thousands of concurrent transactions on
| the same table.
|
| My particular workflow was essentially this - I get a batch
| (full state dump) with some products, and I need to update my
| `products` table to keep track of them (soft-deleting what had
| disappeared, inserting new, updating existing):
| BEGIN; -- Quickly load the batch into a temporary table
| CREATE TEMPORARY TABLE products_tmp (LIKE products INCLUDING
| ALL) ON COMMIT DROP; COPY products_tmp FROM STDIN;
| -- Soft-delete products missing from the current batch
| UPDATE products SET is_active = FALSE WHERE is_active AND
| store_id = ANY($1) AND id NOT IN (SELECT id FROM products_tmp
| WHERE store_id = ANY($1)); -- Upsert products from the
| current batch (add new, update existing) INSERT INTO
| products (...) SELECT ... FROM products_tmp ON CONFLICT (id) DO
| UPDATE SET ...; COMMIT;
|
| With just a few thousands of concurrent writers things started
| to look quite ugly, with constant serialization failures (I
| started at SERIALIZABLE, then downgraded to REPEATABLE READ,
| was reluctant to use READ COMMITTED) and deadlocks preventing
| me from performing some DDL (schema migrations) on the products
| table.
|
| So I've started to batch those batches elsewhere and dump them
| at periodic intervals - and things started to look a lot
| better. Maybe that was a naive/bruteish approach and I
| should've done some parameter tweaking and/or fancy table
| partitioning or something else (idk) for congestion control
| instead, but at least it worked.
| anonymousDan wrote:
| Presumably if you disable this on a per-transaction basis you are
| still guaranteed that transactions are replicated in FIFO order?
| Otherwise you could end up in weird situations where a synced txn
| reads from a lost txn and your state is effectively corrupted.
| anarazel wrote:
| Correct. Async commit transactions add their changes to the WAL
| the same way normal transactions do. The only relevant change
| is that the WAL is not synchronously flushed to disk before
| COMMIT completes.
| pseudopersonal wrote:
| Thanks for sharing this! Like many others, we're at a scale where
| we use postgres for almost everything to reduce operational
| overhead, and there are some ETL,log and audit tables that would
| love a 30% speed bump for such a small risk window.
| shayonj wrote:
| exactly! those are the places I have found it to be super
| useful. Glad you found it useful
| jasonthorsness wrote:
| I didn't know this could be per-session or per-transaction. That
| seems a way better way to take advantage of this for very
| carefully thought-through use-cases without setting up an
| invisible footgun in a global setting someone else doesn't
| realize is changed from the default.
| uhoh-itsmaciek wrote:
| 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
|
| I'm not a Rails expert: does this reliably give you the same
| connection from the pool? If not, you could be setting
| `synchronous_commit = off` on one "physical" connection, and
| clearing it on another. This is setting it at the Postgres
| session level, so it will persist for the lifetime of the
| connection. If it's not managed reliably, it could lead to some
| very confusing bugs (only triggered in a very small window when
| Postgres shuts down unexpectedly while the transaction is still
| unsynced to durable storage, making it extra fun to debug).
|
| In general, though, this is a very handy feature.
| cyberax wrote:
| Yes, the connection is "sticky" for the duration of the
| request. The middleware also automatically starts a transaction
| for you when you check out the connection.
| shayonj wrote:
| There is something to be said about using SET LOCAL SESSION
| maybe. Esp. when using pgbouncer in between rails app and
| PostgreSQL and using session var
| drdaeman wrote:
| In your code, in the `ensure` part you want `SET
| synchronous_commit TO DEFAULT` instead of the explicit `on`
| (https://www.postgresql.org/docs/current/sql-set.html)
|
| Then, I suspect you may probably rather want to use `SET LOCAL`
| rather than just `SET`, so it applies to a single transaction
| only. So maybe something more like def
| asynchronous_transaction(&block)
| ActiveRecord::Base.connection.transaction do
| ActiveRecord::Base.connection.execute("SET LOCAL
| synchronous_commit = off") yield end
| end
|
| Please note that I don't really know Rails (in particular, I've
| no idea whenever `Base.connection` is guaranteed to be exactly
| the same throughout this function's lifecycle or if e.g.
| there's a connection pool underneath), so I could be
| introducing some subtle bugs here.
___________________________________________________________________
(page generated 2025-03-19 23:01 UTC)