Welcome to the 2nd blog post of PG19 Hacktober!
In two of our previous posts, we explored one of the most exciting additions in PostgreSQL 18 — Asynchronous I/O (AIO). In Part 1, we looked at the architecture behind PostgreSQL 18 AIO, including the Worker Pool, io_uring, and Sync methods. In Part 2, we went deeper into AIO configuration, monitoring, and performance, including io_method, io_workers, pg_stat_io and benchmark comparisons between the different I/O methods. Together, these posts gave us a practical understanding of how PostgreSQL 18 handles asynchronous I/O and how it can impact real-world workloads. Now, PostgreSQL 19 Beta 4 builds on that foundation instead of introducing another completely new I/O subsystem, PostgreSQL 19 makes the existing asynchronous I/O infrastructure smarter. Two improvements are particularly interesting:
- Improved async I/O read-ahead scheduling: how PostgreSQL decides what to read ahead of time, and what that means for scan performance.
- Table scans update the visibility map: how regular table scans now help keep the visibility map current, and why that matters for index-only scans and vacuum.
All tests in this post were run on a PostgreSQL 19 beta4 instance on port 5419:
1. Smarter Read-Ahead Scheduling in PostgreSQL 19
In PostgreSQL 18, the read-stream infrastructure introduced a single distance variable that controlled how far ahead PostgreSQL would issue asynchronous I/O requests. This distance would:
- Ramp up (double) each time PostgreSQL had to wait for an I/O to complete.
- Decay (decrement by 1) each time a buffer was already in cache (a cache hit).
This worked well for sequential scans (seqscans), where you access every block exactly once. But it had a hidden flaw for index scans: index scans commonly access the same table blocks multiple times (e.g., many index entries pointing to the same heap page), so the decay mechanism was far too aggressive. it would shrink the read-ahead window faster than it should, starving the I/O pipeline.
PostgreSQL 19 solves this by splitting the single distance into two independent distances:
| Concept | What It Controls |
|---|---|
readahead_distance | How far ahead to issue I/O requests (asynchronous prefetch depth) |
combine_distance | How many consecutive blocks to merge into a single larger I/O call |
This separation means that even when effective_io_concurrency=1 (no extra parallel I/O workers), PostgreSQL 19 can still issue large combined I/O reads — e.g., reading 11 × 8 KB blocks in a single pread() call. This is the io_combine_limit parameter in action.
Additionally, two new behaviors make the read-ahead smarter:
- Non-blocking early exit: When a query has a
LIMITclause or an anti-join short-circuits an index scan, PostgreSQL 19 callspgaio_wref_abandon()to drop in-flight prefetch requests without blocking. In PostgreSQL 18, partially consumed scans could still trigger unnecessary I/O. - Fast-path optimization: When all buffers are already in cache (
ios_in_progress == 0,distance == 1,pinned_buffers == 1), the read-stream switches to a zero-overhead fast path, skipping all async bookkeeping.
Why index scans benefit the most:
Index scans running inside nested loop anti-joins (e.g., NOT EXISTS) are often never fully consumed — they start, find a match, then stop. Aggressive, uncontrolled prefetching in this scenario wastes I/O bandwidth. PostgreSQL 19’s smarter distance management and non-blocking abandon fix this precisely.
2. Table Scans Now Update the Visibility Map
The visibility map (VM) is a per-table bitmap that PostgreSQL maintains alongside every heap file. Each bit tells PostgreSQL: “all tuples on this page are visible to all transactions” (the all-visible bit) and “all tuples on page are frozen” (the all-frozen bit).
The visibility map powers two critical optimizations:
- Index-only scans (IOS): If a page’s all-visible bit is set, an IOS can return tuple data directly from the index without touching the heap.
Heap Fetches: 0inEXPLAINmeans the VM did its job perfectly. - VACUUM skip: Pages marked all-visible can be skipped by VACUUM, dramatically reducing vacuum overhead on large tables.
The problem in PostgreSQL 18: Only VACUUM itself could set or maintain these bits. Regular table scans (seqscans, index scans) never updated the VM, even when they knew a page’s tuples were all-visible.
The fix in PostgreSQL 19: Table scans now opportunistically set the all-visible bit on pages they visit when they determine all tuples are visible. This means the VM stays fresh between VACUUM runs — reducing heap fetches in subsequent IOS and giving VACUUM less work to do.
Test -1:Index Scan with Heap Prefetching
We created a test table with 2,000,000 rows and a B-tree index and ran the vacuum:
postgres=# CREATE TABLE prefetch_test (
id INT,
cat INT,
val INT,
payload TEXT
);
CREATE TABLE
postgres=#
postgres=# INSERT INTO prefetch_test (id, cat, val, payload)
SELECT
g,
(random() * 100)::INT,
(random() * 50000)::INT,
repeat(md5(g::TEXT), 4)
FROM generate_series(1, 2000000) g;
INSERT 0 2000000
postgres=#
postgres=# CREATE INDEX idx_prefetch_val ON prefetch_test(val);
CREATE INDEX idx_prefetch_cat_val ON prefetch_test(cat, val);
VACUUM ANALYZE prefetch_test;
CREATE INDEX
CREATE INDEX
VACUUMLeaf page items triggering async read-ahead queues for heap blocks ahead of tuple execution.
Why it matters: In PG18, this was serialized (1 I/O wait per tuple). In PG19, queue depth scales up to effective_io_concurrency.
postgres=# DISCARD ALL;
SELECT pg_stat_reset_shared('io');
DISCARD ALL
pg_stat_reset_shared
----------------------
(1 row)
postgres=# SET max_parallel_workers_per_gather = 0;
SET enable_bitmapscan = off;
SET enable_seqscan = off;
SET enable_indexscan = on;
SET effective_io_concurrency = 32;
SET
SET
SET
SET
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS, TIMING ON)
SELECT count(*), sum(length(payload))
FROM prefetch_test
WHERE val BETWEEN 2000 AND 2150;
QUERY PLAN
------------------------------------------------------------------------------------------------------
------------------------------------------------
Aggregate (cost=22015.87..22015.88 rows=1 width=16) (actual time=40.328..40.331 rows=1.00 loops=1)
Buffers: shared hit=2316 read=3684
-> Index Scan using idx_prefetch_val on prefetch_test (cost=0.43..21972.13 rows=5831 width=132) (
actual time=0.135..36.752 rows=5996.00 loops=1)
Index Cond: ((val >= 2000) AND (val <= 2150))
Index Searches: 1
Buffers: shared hit=2316 read=3684
Planning:
Buffers: shared hit=40 read=3
Planning Time: 2.328 ms
Execution Time: 40.414 ms
(10 rows)
postgres=# SELECT
backend_type,
context,
reads,
read_bytes,
round(read_bytes::numeric / NULLIF(reads, 0), 2) AS bytes_per_read
FROM pg_stat_io
WHERE backend_type = 'client backend' AND reads > 0;
backend_type | context | reads | read_bytes | bytes_per_read
----------------+---------+-------+------------+----------------
client backend | normal | 3721 | 30482432 | 8192.00
(1 row)
What this shows:
The index scan read 3,684 heap buffers (those not in cache), and pg_stat_io recorded 3,721 reads at exactly 8,192 bytes/read, one standard 8 KB page per I/O. This is the normal I/O context. Total execution time: 40.4 ms for ~6,000 (5,996) rows. PostgreSQL 19’s read-stream is correctly issuing individual async prefetch requests for random heap pages that the index points to, keeping the I/O pipeline busy.
Test 2 – Visibility Map: Index-Only Scan Before and After VACUUM
Goal: Show how the visibility map affects Heap Fetches in an index-only scan, and how PostgreSQL 19 updates the VM during scans.
Step 1 — Dirty the table (make pages non-visible):
postgres=# UPDATE prefetch_test SET val = val + 0 WHERE id % 5 = 0;
DISCARD ALL;
SET enable_bitmapscan = off;
SET enable_seqscan = off;
SET enable_indexscan = off;
SET enable_indexonlyscan = on;
SET effective_io_concurrency = 32;
UPDATE 400000
DISCARD ALL
SET
SET
SET
SET
SETStep 2 — Run index-only scan on dirty table:
postgres=# EXPLAIN (ANALYZE, BUFFERS, TIMING ON)
SELECT cat, val
FROM prefetch_test
WHERE cat = 50 AND val BETWEEN 100 AND 5000;
QUERY PLAN
------------------------------------------------------------------------------------------------------
-------------------------------------------------
Index Only Scan using idx_prefetch_cat_val on prefetch_test (cost=0.43..1608.73 rows=2325 width=8) (
actual time=0.155..102.207 rows=1991.00 loops=1)
Disabled: true
Index Cond: ((cat = 50) AND (val >= 100) AND (val <= 5000))
Heap Fetches: 2376
Index Searches: 1
Buffers: shared hit=2812 read=2221 dirtied=2230 written=497
Planning:
Buffers: shared hit=4 read=2 dirtied=1
Planning Time: 0.403 ms
Execution Time: 102.592 ms
(10 rows)
With Heap Fetches: 2,376, PostgreSQL had to visit heap pages because the visibility map bits were cleared. 102.6 ms.
Step 3 — Run VACUUM to rebuild the visibility map:
postgres=# VACUUM prefetch_test;
VACUUMStep 4 — Re-run index-only scan:
postgres=# EXPLAIN (ANALYZE, BUFFERS, TIMING ON)
SELECT cat, val
FROM prefetch_test
WHERE cat = 50 AND val BETWEEN 100 AND 5000;
QUERY PLAN
------------------------------------------------------------------------------------------------------
---------------------------------------------
Index Only Scan using idx_prefetch_cat_val on prefetch_test (cost=0.43..82.91 rows=1888 width=8) (ac
tual time=0.026..0.418 rows=1991.00 loops=1)
Disabled: true
Index Cond: ((cat = 50) AND (val >= 100) AND (val <= 5000))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=962
Planning:
Buffers: shared hit=29
Planning Time: 0.340 ms
Execution Time: 0.512 ms
(10 rows)Heap Fetches: 0. The VM confirms all pages are all-visible. Zero heap I/O needed. Execution time drops from 102.6 ms → 0.512 ms, a 200× speedup.
In PostgreSQL 19, table scans that visit pages with all-visible tuples now opportunistically refresh those VM bits. This means the gap between VACUUM runs becomes less harmful — subsequent index-only scans can benefit from an up-to-date VM without waiting for the next full VACUUM cycle.
Test 3 — Early Termination with LIMIT (Non-Blocking Reset)
Goal: Demonstrate that PostgreSQL 19 abandons in-flight prefetch I/Os non-blockingly when a scan terminates early due to LIMIT.
postgres=# DISCARD ALL;
SET enable_bitmapscan = off;
SET enable_seqscan = off;
SET enable_indexscan = on;
SET effective_io_concurrency = 64;
DISCARD ALL
SET
SET
SET
SETpostgres=# EXPLAIN (ANALYZE, BUFFERS, TIMING ON)
SELECT id, val, payload
FROM prefetch_test
WHERE val >= 1000
ORDER BY val
LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------
-------------------------------------------------
Limit (cost=0.43..1.72 rows=10 width=140) (actual time=0.049..0.155 rows=10.00 loops=1)
Buffers: shared hit=3 read=10
-> Index Scan using idx_prefetch_val on prefetch_test (cost=0.43..252461.92 rows=1959637 width=14
0) (actual time=0.046..0.151 rows=10.00 loops=1)
Index Cond: (val >= 1000)
Index Searches: 1
Buffers: shared hit=3 read=10
Planning:
Buffers: shared hit=8 read=3
Planning Time: 2.316 ms
Execution Time: 0.180 ms
(10 rows)What this shows:
The query touched only 13 buffers total (3 hits + 10 reads) and completed in 0.180 ms, despite the index scan having a potential range of ~1.96 million rows. In PostgreSQL 18, even a LIMIT 10 query could trigger unnecessary prefetch I/Os beyond the first few rows because the in-flight I/O requests couldn’t be abandoned cheaply. PostgreSQL 19’s pgaio_wref_abandon() mechanism drops those requests the moment the LIMIT is satisfied — no wasted bandwidth, no blocking.
This is especially valuable for nested loop anti-joins, correlated subqueries, and LIMIT-over-index-scan patterns that are very common in OLTP workloads.
Test 4 — Combined Multi-Block I/O (io_combine_limit)
Show that PostgreSQL 19’s combine_distance enables large combined reads even at effective_io_concurrency=1.
postgres=# DISCARD ALL;
SELECT pg_stat_reset_shared('io');
DISCARD ALL
pg_stat_reset_shared
----------------------
(1 row)
postgres=# SET max_parallel_workers_per_gather = 0;
SET effective_io_concurrency = 1;
SET io_combine_limit = 16;
SET
SET
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS, TIMING ON)
SELECT count(*)
FROM prefetch_test
WHERE payload LIKE '%zzz%';
QUERY PLAN
------------------------------------------------------------------------------------------------------
--------------------
Aggregate (cost=76064.50..76064.51 rows=1 width=8) (actual time=597.517..597.519 rows=1.00 loops=1)
Buffers: shared hit=4367 read=46697
-> Seq Scan on prefetch_test (cost=0.00..76064.00 rows=200 width=0) (actual time=597.503..597.504
rows=0.00 loops=1)
Filter: (payload ~~ '%zzz%'::text)
Rows Removed by Filter: 2000000
Buffers: shared hit=4367 read=46697
Planning Time: 1.441 ms
Execution Time: 597.558 ms
(8 rows)
postgres=# SELECT
backend_type,
context,
reads,
read_bytes,
round(read_bytes::numeric / NULLIF(reads, 0), 2) AS bytes_per_read
FROM pg_stat_io
WHERE backend_type = 'client backend' AND reads > 0;
backend_type | context | reads | read_bytes | bytes_per_read
----------------+----------+-------+------------+----------------
client backend | bulkread | 4125 | 382541824 | 92737.41
client backend | normal | 5 | 40960 | 8192.00
(2 rows)
What this shows:
With io_combine_limit=16 and effective_io_concurrency=1, PostgreSQL 19 issued 4,125 read calls but transferred ~383 MB, averaging 92,737 bytes per read call. That is approximately 11.3 × 8192 bytes, meaning PostgreSQL combined ~11 contiguous 8 KB blocks into each single I/O request.
92,737 ÷ 8,192 ≈ 11.32 blocks per combined I/OThis is the combine_distance at work — completely independent of effective_io_concurrency. In PostgreSQL 18, combining I/Os at effective_io_concurrency=1 was not possible to this degree. The split between readahead_distance and combine_distance in PostgreSQL 19 is what enables this behavior.
Here is the demo:
Conclusion
PostgreSQL 19 doesn’t introduce a brand-new I/O subsystem — it refines the asynchronous I/O foundation laid in PostgreSQL 18, making it smarter, more efficient, and better suited for the mixed workloads that real applications generate.
The two improvements we explored today — smarter read-ahead scheduling and visibility map updates during table scans — might seem like incremental changes, but the benchmark numbers tell a compelling story:
A dirty index-only scan that took102.6 msdrops to0.512 msafter VACUUM, thanks to the VM updates. In PostgreSQL 19, scans themselves help keep the VM fresh, reducing the dependency on the VACUUM cycle.- A
LIMIT 10index scan completes in 0.180 ms, touching just 13 buffers out of millions — because PostgreSQL 19 can abandon in-flight prefetch requests the moment the limit is satisfied. - A sequential scan with
io_combine_limit=16achieves 92,737 bytes per read call, even ateffective_io_concurrency=1— something that wasn’t achievable in the same way in PostgreSQL 18.
For DBAs and developers, the practical takeaway is clear:
- Run VACUUM regularly (or let autovacuum do its job) — and in PostgreSQL 19, your scans themselves will help maintain visibility map freshness.
- Use
io_combine_limitfor large sequential scan workloads to maximize I/O throughput per system call. - Trust the planner with index scans — PostgreSQL 19 is now much smarter about not over-prefetching when scans are partial or short-circuited.
PostgreSQL 19 is still in beta, but these I/O refinements alone make it a significant step forward for I/O-bound workloads. We’ll continue exploring more PG19 features throughout Hacktober — stay tuned!
Thank you for reading the blog and see you in the Day3 of PG19 Hacktober!!