PostgreSQL synchronized commit performance

Question

I'm setting up a pair of PostgreSQL servers. Because cash is short I can only afford one high class server and some crapy backup box. If the expensive high end box should catch fire, operations can live with the slower backup system for a while.

I was wondering how much performance does the backup server need? It receives WAL and only has to apply it (I prosume that is the way syncrhonized commits work?). Is WAL always easy to apply, or would for example a delete with many checks on foreign key constraints, also have to do these constraint checks on the slow backup box?

Is the PostgreSQL WAL logical or image based? I'm not looking for absolute numbers, more a kind of answer like: "Applying WAL over TCP in sync commit mode on the hot backup will be cheap in any szenario, except for blahblah".

Answer 1

Don't do this with only one slave. If you do you are making your application more subject to failure, not less. Basically if you do this, and you lose your slave, your master will hang. You say your backup box is crappy, and I assume that means reliability-wise too, so this is more likely than losing your master. This is probably not what you want. Do not use synchronized commit option with replication and only one slave.

Synchronized commits in replication means that the master waits for the slave to acknowledge that the WAL was applied before noting that the commit happened and passing that back to the client. If you don't get the configured quorum, you wait for it, possibly for.... a..... long..... time......

Do it async. Use fsync set to on. You should be golden.