postgresql

PostgreSQL hint bits on streaming standby replicas


As I understand it, hint bits are set on first-access on heap pages after commits take place, based on the information in the commit log.

Since transactions and commit logs are replicated using WAL messages, but hint bits seemingly are not, does that mean that every standby server has to set them individually when a tuple is accessed?

I'm running a couple of clusters where standby nodes are not queried unless they are eventually promoted to primary, and I'm curious as to whether that would mean that they'd need to set the hint bits on all the data that has been modified since the last (auto)vacuum on a particular table when they are promoted?

I realise this is probably not a huge amount of data, given that the autovacuum runs fairly frequently.


Solution

  • You understand the situation correctly.

    If you don't want the first queries on a freshly promoted standby to suffer the performance hit from inspecting the commit log, you could opt to replicate hint bits as well by setting wal_log_hints = on. That will lead to a somewhat higher WAL volume.