The Hitchhiker's Guide to PostgreSQL Hacking: Don't Panic, Just Start Small

Hacking on PostgreSQL is challenging. It involves a massive codebase
and a rigorous—often bottlenecked—review process. For many new
aspiring contributors, the journey often begins with excitement
tempered by imposter syndrome and confusion. How do I start? What
should I work on? These are common questions new hackers ask. This
talk offers a practical guide to navigating that maze, using the
speaker’s own journey as a roadmap.

We will explore both the technical and psychological strategies
required to move from confusion to contribution: how to start small,
how to review patches, and how reviewing can naturally generate ideas
for what to work on next. Small, focused efforts compound over time.
In this talk, we follow one such journey—from removing a simple
polling loop, to building shared infrastructure and user-facing
features; from a small bug fix, to a deeper exploration of
PostgreSQL’s evolving I/O architecture.

As a concrete case study, we examine how replacing simple
pg_usleep-based polling loops with a structured WAIT FOR LSN
infrastructure—built on latches and shared-memory pairing
heaps—resulted in a 39% reduction in CPU cycles and a 44% improvement
in IPC. We then show how streamifying the same code path can further
boost performance by more than 2× when combined with the new AIO
framework.

Attendees will learn:
• How to Start Small: Practical ways to build familiarity and
confidence through patch reviews, bug fixes, and feature testing.
• How to Avoid Polling with IPC Mechanisms: Understanding and using
core primitives such as latches and condition variables to replace
inefficient sleep-and-poll patterns.
• The WAIT FOR LSN Infrastructure and Command: How to wait for WAL to
be written, flushed, or replayed efficiently, and how to achieve
effective read-after-write consistency in asynchronous replication
settings.
• The “Streamification” Recipe: What Read Streams is, when to use it,
and how to identify code paths that can hide I/O latency and enable
asynchronous I/O.

Hopefully, this talk could demonstrate that knowledge expands
fractally and learning compounds over time. By getting close enough to
the gaps, a persistent hacker can develop understanding—and make
meaningful contributions to PostgreSQL.

Transcript

1. Xuneng Zhou The Hitchhiker's Guide to PostgresSQL hacking Don't Panic, Just Start Small
2. Journey starts sometimes with: • Excitement • Curiosity • Possibility • Self-doubt • Uncertainty • Confusion but also with:
3. Who am I Am I eligible for contributing? • A freelance Postgres hacker • Non-CS major • Not graduating from top-notch school • No company support for hacking • Not a native English speaker • Not an experienced developer • …. Imposter syndrome
4. Hacking on Postgres is *hard* Why motivation matters? • Codebase is massive and complex • The review process is rigorous & bottlenecked • The technology itself is inherently di ffi cult
5. Codebase is massive • Near 1.5 million lines of low-level C code. • Various complex & intertwined modules • Near impossible to read them through and complex
6. Rigorous & bottlenecked review The project needs more reviewer The review for major feature could easily span hundreds of pages
7. The technology is di ff i cult • Writing reasonably good patches is *hard*, even so for seasoned committers • Expertise requires more than 10000 hours intensive training
8. Motivation This is why motivation matters a lot • For fun—hacking Postgres could be fun • For community—one change to help all • For pro fi t—Postgres is *the* database for the future
9. On tour
10. What to do? The very fi rst question & confusion • No low-hanging fruit left - all easy tasks are done in Postgres (?) • It is very mature - all necessary things are done in Postgres (?) • No feature roadmap - when/where/how to begin?
11. Start small to build con fi dence and familiarity • Start from reviewing patches • Great way to generate ideas for what to work on • Build your connections with community • This is what the project needs a lot • Start from fi xing bugs • Small bug fi x is self-contained, easy to follow • Start from testing features • Get to know the basic functionality and edge cases
12. Knowledge expands fractal Small can go big with compounding • “Knowledge expands fractally, and from a distance its edges look smooth, but once you learn enough to get close to one, they turn out to be full of gaps.”
 
 — Paul Graham
13. Start from reviewing patches
14. Reviewing patches is underrated learning while contributing • Patch review is a fi rst-class contribution • Accelerates learning, unblocks CommitFests, and builds long-term trust • One of the fastest paths into PostgreSQL development • Review can help generate ideas of what to work on • How to get started • Pick a patch in CommitFest and sign up (no permission needed) • Send timely feedback on pgsql-hackers; partial reviews are welcome • Practical review work fl ow • Build & test (--enable-cassert, make check) • Check behavior, usability, and error messages • Sanity-check performance and concurrency • Review code style, architecture, tests, and docs
15. A tale of heavy polling In function read_local_log_page_guts
16. How to observe it • Create a long-running blocking condition • On the primary, it starts a background session: • BEGIN; ... pg_sleep($RUNTIME); COMMIT; • This holds resources long enough to keep downstream operations blocked. 2) Trigger an operation that gets stuck (the “polling target”) • Trigger an operation that gets stuck (the “polling target”) • On the standby, it runs: • SELECT pg_create_logical_replication_slot(‘test_slot’,'test_decoding'); • This is launched in the background, expected to block. • Measure the metrics • sudo perf record --call-graph dwarf -p $WAITING_PID -- sleep $RUNTIME • sudo perf stat -e cycles,instructions,cache-misses,context-switches -p $WAITING_PID -- sleep $RUNTIME
17. Dissecting the polling Why polling is ine ff i cient? • cycles: 2,930,606,156 • instructions: 1,003,179,713 (0.34 IPC) • cache-misses: 144,808,110 • context-switches: 77,278 The backend spent billions of cycles and tens of thousands of context switches just to wait.
18. Replace the polling with IPC mechanisms • The need: 
 notify me when what I want is ready • What does Postgres o ff er to do so? • Latch • Condition variable • Based on WaitEventSet
19. The two options Layer Mechanism Purpose Key Operations Latch Boolean fl ag "Poke" a process awake Set / Reset / Wait ConditionVariable Wait queue + spinlock Ordered wakeups Sleep / Signal / Broadcast WaitEventSet OS abstraction Portable multiplexing epoll / kqueue / poll / Win32 • Latch (Core Wakeup Primitive) • Boolean fl ag with SetLatch/ResetLatch/WaitLatch • Used to sleep until work arrives; supports local and shared latches • Condition Variable • Spinlock‑protected wait list of PGPROCs • Signal/Broadcast wakes waiters by setting their process latches
20. Condition variable walsender use it to wake up while WAL is replayed/ fl ushed • Broadcasting on every update triggers unnecessary wake-ups and does not scale when many backends wait on di ff erent LSN targets.
21. How to eliminate the false wake-ups of condition variable? • Accurate signaling requires mapping waiters to target LSNs and waking only those satis fi ed • This implies a request queue indexed by LSN → targeted wake-ups, increasing complexity but eliminating broadcast cost • This requires a per-backend latch, so only the processes whose target LSN has been reached are woken
22. The WAIT FOR LSN Infra based on latches • The new interface of WAIT FOR LSN • WaitLSNWakeup for wake-up • WaitForLSN for sleep • Support wait type • standby_replay • standby_write • standby_ fl ush • primary_ fl ush
23. The hardware metrics after polling elimination • Replace pg_usleep(1000L) with • WaitForLSN(WAIT_LSN_TYPE_PRIMARY_FLUSH, loc, -1); • WaitForLSN(WAIT_LSN_TYPE_STANDBY_REPLAY, loc, -1);
24. Lessons learned • Polling is ine ffi cient and error-prone, we should avoid it • Always try to use condition variable or latch for e ff i cient waiting • latch for event-loop style waits and mixed wake sources • condition variable for shared-state predicates and coordinated wakeups • If you need to wait certain LSNs to be replayed/ fl ushed/written, try to use WAIT FOR LSN infra in src/backend/access/transam/xlogwait.c
25. You may wonder why do we bother so much for so small • Wait For LSN is also an external user-facing need! • How to achieve Read-after-write consistency in asynchronous settings e ff ectively • We can expose the functionality of Wait For LSN infra as *command* to user
26. Read-after-write consistency in asynchronous settings •
27. The polling solution before • Polling pg_last_wal_replay_lsn • Obtain the target LSN from pg_current_wal_insert_lsn() on the primary • Poll until pg_last_wal_replay_lsn() on the standby reaches the target • Which could be ine ffi cient
28. WAIT FOR LSN Command example
29. WAIT FOR LSN '<lsn>' [WITH (MODE '<mode>', ...)] • 'standby_replay' (default): Wait for WAL to be replayed to the speci fi ed LSN • 'standby_write': Wait for WAL to be written (received) to the speci fi ed LSN • 'standby_ fl ush': Wait for WAL to be fl ushed to disk at the speci fi ed LSN • 'primary_ fl ush': Wait for WAL to be fl ushed to disk on the primary server
30. Start from fi xing bugs
31. A tale of read stream • Encountered a rare bug in read_stream.c’s split IO handling • Great explanation of the bug by Thomas Munro • Too complex to explain fully here
32. A tale of streaming read the talks • Inspired to watch talks on read streaming and AIO: • AIO in PG 18 and beyond • What went wrong with AIO • Streaming I/O and vectored I/O • IO in PostgreSQL: Past, Present, Future • Streaming I/O New abstractions for e ffi cient fi le I/O
33. More opportunities to streamify? • One slide of the talks hit me
34. Read streaming Look‑Ahead with Adaptive Prefetch • Traditional “stop‑and‑wait” • Postgres needs Page A • reads Page A (may block • It processes Page A • It discovers it needs Page B (repeat) • Read Stream model • A caller provides a callback that supplies the next block number in a stream • The stream forms and starts I/Os (via StartReadBu ff ers) for upcoming blocks, combining adjacent ones • read_stream_next_bu ff er() returns the next bu ff er, waiting only if its I/O is still in fl ight • While processing one bu ff er, the next reads are already underway • Why it exists • Hiding I/O latency • Better bandwidth utilization • Centralized prefetch logic • Works with non‑sequential but predictable streams
35. The interface of read streaming
36. The recipe for streami fi cation Characteristics for Read Stream Suitability • Good Candidates (High Viability) • Sequential block access, predictable, can prefetch ahead • Deterministic block ordering, callback can calculate next block without reading current • Batch-oriented processing, CPU work overlaps I/O for next batch • Long-running scans, Stream setup cost amortized • Poor Candidates (Low Viability) • Pointer chasing, next block unknown until page is read • Data-dependent navigation, control fl ow depends on page contents
37. Decision Checklist • Can you predict future blocks? • Yes → ✅ Strong candidate • No (pointer chasing) → ❌ Avoid • Access pattern • Sequential range → block_range_read_stream_cb • Computable mapping → Custom callback • Data-dependent → ❌ Not suitable • Locking behavior • No / short-lived locks → ✅ OK • Lock coupling across iterations → ❌ Problematic • CPU work between reads? • Yes → ✅ I/O overlaps CPU (good payo ff ) • No → ⚠ Limited bene fi t Streamify pgstatindex
38. Pgstatindex benchmark
39. Streamify more code paths Code path Status Streamify Bloom scan paths Under review Streamify GIN vacuum cleanup Under review Streamify pgstatindex functions Under review Streamify Bloom VACUUM paths Under review Streamify heap bloat estimation scan Under review Streamify log_newpage_range WAL logging Under review Streamify hash index VACUUM bucket pages Under review
40. Lessons learned, so far • Start small • Be con fi dent & persistent • Be open to feedback • “Knowledge expands fractally, and from a distance its edges look smooth, but once you learn enough to get close to one, they turn out to be full of gaps.” — Paul Graham
41. Who am I I am a proud Postgres contributor. • I help make Postgres fl exible • I help make Postgres fast • I help make Postgres robust
42. The end or the beginning?