Ask HN: Can PostgreSQL standby read performance go down with writes on master?

17 pointsavivallssa1 comment

Someone told me that their usual read queries are performing faster when no writes to Master. Is this possible and what could be the reason behind it ?

View on HN

My understanding is that when a standby/secondary database receives a replication/write-ahead log (WAL) entry from the master, it still needs to process it, just as if it had been committed on its own system. That means ensuring indices are in memory, paging things in and out of memory if needed, etc. And if your reads are of a very different character than your writes, you might see circumstances where the writes cause things to move out of cache - things that would otherwise be in cache due to the read workload alone.

In general, standby/secondary reads aren't a performance panacea. If writes, and their cache contention, are consuming 50% of your CPU and IO on master, they'll still consume about as much on secondaries. So don't expect to be able to use 100% of the secondary's capacity. But it's a decent "growth hack" if you can tolerate stale reads and just want a way to scale horizontally without regards to costs.

Reply on news.ycombinator.com