The Alchemy of Shared Buffers: Balancing Concurrency and Performance

About

Talk dissects PostgreSQL's shared buffers: the three‑layer design (buffer pool → BufferDesc headers → buf_table hash), the hit/miss/I‑O lifecycle, and how pins, per‑page LWLocks, and atomic BM_* flags coordinate page‑granular concurrency. We will trace clock‑sweep and buffer rings that prevent big scans and VACUUM from polluting the cache, then follow WAL‑before‑data through bgwriter and the checkpointer to show how writes are made smoother. We will also discuss tuning and what's new in PG 17 (vectored reads), and PG 18 (async reads) and what can come next - we will dive into new design proposals discussed in 2025 - to allow dynamic resize of shared_buffers via segmented shared memory.

Key takeaways - Architecture & locks: three layers; pins + per‑page LWLocks + partitioned mapping yield high concurrency - Replacement & pollution control: clock‑sweep keeps hot pages; buffer rings fence off bulk scans and VACUUM - Durability & flushing: WAL‑before‑data with bgwriter/checkpointer turns random writes into managed I/O - Tuning: balance double buffering, size shared_buffers sensibly, smooth checkpoints, and verify with EXPLAIN (BUFFERS) and pg_stat_bgwriter - Roadmap: PG 18's async reads cut scan latency; dynamic shared_buffers resizing is being prototyped

0:00:00 Slide 1

0:02:01 Slide 2

0:02:24 Slide 3

0:02:47 Slide 4

0:03:33 Slide 5

0:04:28 Slide 6

0:05:40 Slide 7

0:07:28 Slide 8

0:08:36 Slide 9

0:09:57 Slide 10