> Did a minimum amount of tuning, mostly just turning off fsync for WAL writes
That is not something I would suggest to people on production systems, as that would give you a good chance of data loss when the system halts.
So, out of interest, were there any circumstances why turning off WAL fsync was considered a good choice in your situation?
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"
> 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.
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.
Re-read their post. It was a secondary system setup to just run these analytics which were loaded from the production system. No issues if the whole machine had to be rebuilt.
That is not something I would suggest to people on production systems, as that would give you a good chance of data loss when the system halts. So, out of interest, were there any circumstances why turning off WAL fsync was considered a good choice in your situation?