Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 34
Submission, completion, concurrency, and durability boundaries
Reason about asynchronous database I/O from kernel submission queues through completion handling, backpressure, cancellation, and commit durability.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
An asynchronous interface separates submitting an operation from observing its completion. Its benefit comes from useful concurrency and reduced waiting or syscall overhead, not from making the storage device intrinsically faster.
02
io_uring uses shared submission and completion rings so PostgreSQL can issue reads and collect completions with fewer transitions. Capability, kernel version, and database release remain part of the result.
03
Prefetch advises or initiates future reads while the current work continues; true asynchronous execution also exposes completion. Both need predictable access far enough ahead to hide latency.
04
Unlimited outstanding I/O moves waiting into device queues and can inflate tail latency. A database needs bounded depth, fair scheduling, memory ownership, and cancellation when a query stops.
05
A completed write may have reached a kernel or device cache without crossing the durability boundary. Commit safety still depends on write ordering, flush semantics, WAL, and the configured acknowledgement policy.
06
Random misses, sequential scans, cached reads, and writeback have different opportunities for overlap. Report block size, concurrency, hit ratio, storage topology, and CPU saturation with any benchmark.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
Sweep outstanding operations while holding data, block size, and cache state constant. The useful depth is where throughput gains flatten before tail latency grows sharply.
for depth in 1 2 4 8 16 32:
record throughput, p50, p95, p99, CPU, queue latency
# Repeat cold and warm; do not merge the populations.A benchmark must retain the PostgreSQL release, kernel, selected I/O method, and fallback messages so an io_uring result is reproducible.
SHOW io_method;
SELECT version();
-- Retain server startup log and kernel version with results.Instrumentation should timestamp queue admission, submission, device completion, and consumer wake-up. One aggregate duration cannot reveal backpressure.
queue_delay = submitted_at - admitted_at
service_time = completed_at - submitted_at
resume_delay = consumed_at - completed_atCancel work with I/O in flight, then verify buffers and resources are released. For writes, separately crash-test the configured commit boundary.
1. Start a bounded read with observable I/O in flight.
2. Cancel the statement and verify cleanup.
3. For write tests, acknowledge a commit, terminate the instance,
4. Restart and verify committed and uncommitted invariants.08
Optional deep dives with the complete experiments and product-version context behind this guide.