0

It seems clear that setting synchronous_commit OFF on sessions - on which the super rare drawback (allegedly-committed transactions being lost in case of a server crash) - is fine, increases the performance of those sessions, as they do not have to wait for the WAL to be flushed to disk.

However, what I fail to understand is how that affects all the sessions that commit in the same time window that have synchronous_commit ON.

It is my understanding that sessions that have synchronous_commit OFF produce just as much WAL as those that have it ON (or even more as they get more done in the same time, as they don't have to wait for WALWrite). So the sessions that have it ON will be just as slow as if all sessions had it ON?

Scenario 1: 10 sessions doing heavy DML that have it ON and 10 Sessions that have it OFF.

Scenario 2: 20 sessions doing heavy DML that have it ON.

What I think happens: In scenario 1, the 10 sessions with OFF produce even more WAL than in scenario 2 so the sessions with ON are even longer waiting on WALWrite than in scenario 1.

Am I understanding this right that each commit always waits for ALL current (xid <= own xid) WAL data to be written not only 'ITS OWN' WAL data?

Improve this question

1 Answer 1

Reset to default
1

or even more as they get more done in the same time, as they don't have to wait for WALWrite

You are assuming the sessions have an infinite amount of work to do. More generally a session has a particular task to accomplish, and once that is done it is done. It won't run around inventing new tasks just because it finished the original one early.

What I think happens: In scenario 1, the 10 sessions with OFF produce even more WAL than in scenario 2 so the sessions with ON are even longer waiting on WALWrite than in scenario 1.

Did you benchmark it and see? It will depend on things like your storage system, and how large each transaction is.

Am I understanding this right that each commit always waits for ALL current (xid <= own xid) WAL data to be written not only 'ITS OWN' WAL data?

Your understanding is correct but the implications are probably not. Generally the bottleneck is not in the number of bytes of WAL written, but in waiting for confirmation that all of that data has securely reached stable media. Writing and flushing a given number of bytes in 10 chunks is going to be less work (and less time) than writing and flushing that same number of bytes in 20 chunks, unless perhaps that number of bytes is very large.

If I am flushing synchronously but it happens that I'm not ready to start until right after someone else started their flush, then I can't start my flush until theirs finishes. While if they were async, then I can start my flush (which will include their data and my data) immediately upon become ready, and thus finish sooner.

Improve this answer
2
  • TYVM for your input. You say More generally a session has a particular task to accomplish, and once that is done it is done, which is correct, but if it has e.g. 10.000 tasks and can suddenly do more tasks per minute due to not waiting for WAL sync, it will create more WAL in the already critical overload time instead of this WAL creation being spread out a bit more due to WAL Waits is my line of thining. Commented Apr 9, 2024 at 6:41
  • 1
    @Peter If one set of workers are background bulk loading/ETL workers, running them async might consume too much bandwidth, while running them sync might consume too much IOPS. Both of which can be in short supply, so either way might slow down the "foreground" workers which have people waiting on the results. Ideally you would throttle these by inserting strategically place sleep calls, but PostgreSQL does nothing to make this easy for you to do. Maybe the first step would be just to not run 10 of them at the same time. Commented Apr 11, 2024 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.