I have a Postgres 13.10 database running on AWS RDS, with a primary instance and a replica. The replication is managed by AWS - it's not logical replication I manage myself.
The replica is configured with hot_standby_feedback=1. Queries on the replica always run with a statement timeout of 60s to ensure they're not canceled if they conflict with WAL replication.
The database has a widgets table that is ~1Tb on disk, including a number of bloated indexes. The table has a relatively high update rate as part of a transactional system. We're aware the size is larger than recommended and are working on addressing that. The table predates declarative partitioning and it is a parent table with a single child in a table inheritance hierarchy.
When I run the following query on the primary and the replica, I find that the replica does significantly more Heap Fetches than the primary. The primary is slower in these tests because some blocks are read from disk, but that's not concerning us.
At the time of this particular test, the replica required ~2.5x the number of heap fetches. As a result, responding to the query touches ~800k buffers on the primary but ~2.1M buffers on the replica.
I think this suggests the visibility map on the replica isn't helping as much as on the primary?
Can anyone explain why the Heap fetches
are so different on the two instances? Are changes to the visibility map replicated to the replica as the table is updated?
Additional observations that are interesting:
- running
REINDEX ( VERBOSE ) INDEX CONCURRENTLY index_widgets_foo
on the primary results in theheap fetches
numbers on the replica dropping to about the same as on the primary, Great! Except then as the table accumulatesUPDATES
on the primary theHeap fetches
numbers start to slowly diverge again - Running a vacuum on the table (on the primary) also seems to bring the
Heap fetches
numbers close together (for a while).
explain (analyze, buffers) SELECT queue, COUNT(*)
FROM ONLY widgets
WHERE account_id = 123
AND thing_id IS NULL
AND type = 'foo'
AND destroyed_at IS NULL
GROUP BY queue;
primary
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=40780.06..41327.15 rows=54709 width=29) (actual time=63238.961..63239.240 rows=62 loops=1)
Group Key: queue
Batches: 1 Memory Usage: 1561kB
Buffers: shared hit=600937 read=218122 dirtied=7703
I/O Timings: read=61192.139
-> Index Only Scan using index_widgets_for_metrics_with_legacy_queue on widgets (cost=0.56..39897.92 rows=117618 width=21) (actual time=0.019..63235.662 rows=16916 loops=1)
Index Cond: (account_id = 123)
Heap Fetches: 1067274
Buffers: shared hit=600937 read=218122 dirtied=7703
I/O Timings: read=61192.139
Planning:
Buffers: shared hit=3
Planning Time: 0.530 ms
Execution Time: 63240.040 ms
Replica
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=40780.06..41327.15 rows=54709 width=29) (actual time=2065.951..2066.156 rows=59 loops=1)
Group Key: queue
Batches: 1 Memory Usage: 1561kB
Buffers: shared hit=2116371
-> Index Only Scan using index_widgets_for_metrics_with_legacy_queue on widgets (cost=0.56..39897.92 rows=117618 width=21) (actual time=0.649..2062.977 rows=16642 loops=1)
Index Cond: (account_id = 123)
Heap Fetches: 2541914
Buffers: shared hit=2116371
Planning:
Buffers: shared hit=3
Planning Time: 0.475 ms
Execution Time: 2066.303 ms