2 ms·

I probably meant to say "synchronous_commit", which is how data is written to disk from the WAL. If you want full data guarantees, with regular hard drives, you

by esaym 3y ago

I probably meant to say "synchronous_commit", which is how data is written to disk from the WAL. If you want full data guarantees, with regular hard drives, you'd be looking at less than 200 transactions per second. You set synchronous_commit to off, and suddenly you can do 10k transactions per second. You can tune when the WAL gets flushed to disk based on time and/or size. So you can set the amount of recent data loss you are comfortable with. From the docs: "setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty"

guenthert 3y ago
> If you want full data guarantees, with regular hard drives, you'd be looking at less than 200 transactions per second. That sounds like an anecdote from the time before SSDs. > "setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. " If your application is told that the transactions were committed, but your DB actually hasn't, you got a problem, methinks.
- literalAardvark 3y ago
  Synchronous replication exists to avoid more of the transactions committed problem. Thing is DB work is all about tradeoffs, so you'll never be free of them. As the parent explained, PostgreSQL actually lets you make a very good trade: you get full cached level performance but lose only a couple of transactions at most, instead of data corruption. If that's not ok for you, the leash can be tightened to lose nothing, but then your hardware had better keep up with your platform.
- esaym 3y ago
  > That sounds like an anecdote from the time before SSDs. Yes, I stated "regular hard drives" :)

by esaym 3y ago

guenthert 3y ago

> If you want full data guarantees, with regular hard drives, you'd be looking at less than 200 transactions per second. That sounds like an anecdote from the time before SSDs. > "setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. " If your application is told that the transactions were committed, but your DB actually hasn't, you got a problem, methinks.

literalAardvark 3y ago
Synchronous replication exists to avoid more of the transactions committed problem. Thing is DB work is all about tradeoffs, so you'll never be free of them. As the parent explained, PostgreSQL actually lets you make a very good trade: you get full cached level performance but lose only a couple of transactions at most, instead of data corruption. If that's not ok for you, the leash can be tightened to lose nothing, but then your hardware had better keep up with your platform.
esaym 3y ago
> That sounds like an anecdote from the time before SSDs. Yes, I stated "regular hard drives" :)