Database
13 min read read

PostgreSQL Performance: AIO Internals, B-Tree Skip Scans, and UUIDv7 Indexing

Amit Narwal
Freelance Full Stack & AI Developer
PostgreSQL Performance: AIO Internals, B-Tree Skip Scans, and UUIDv7 Indexing

Modern Database Engineering

High performance begins with data layout and hardware I/O efficiency, not premature query rewriting.

1. Asynchronous I/O Subsystem Architecture

Historically, PostgreSQL relied on traditional synchronous POSIX read and write calls. A background worker executing a sequential scan or fetching heap pages would block waiting for the storage controller.

Modern PostgreSQL builds integrate asynchronous I/O (AIO) backed by Linux io_uring. By submitting batch read requests concurrently without blocking worker threads, analytical queries on NVMe storage achieve significantly higher throughput without modifying application SQL.

2. UUIDv7: Eliminating B-Tree Index Fragmentation

Random UUIDv4 identifiers cause severe B-tree page splits. Because random values insert uniformly across the entire index tree, leaf pages quickly exceed capacity, splitting into half-empty pages and ballooning disk usage.

UUIDv7 addresses this by placing a millisecond-precision UNIX timestamp in the most significant bits:

  • Monotonic Ordering: New rows append to the right-most leaf pages of the index, matching the sequential insertion characteristics of integer serials.
  • Cache Locality: Recent write working sets remain clustered in Postgres shared_buffers rather than churning random memory pages.
  • Zero Collision Penalty: Maintains distributed UUID generation benefits across client applications without centralized sequence locks.

3. Practical Memory & Work Tuning

Avoid applying generic formulas blindly. Tailor settings to your workload:

  • shared_buffers: Allocate ~25% of total system RAM on dedicated database servers. Going higher (e.g. 50%+) often causes double-buffering competition with the Linux page cache.
  • work_mem: Set conservatively based on max_connections. Complex queries with multiple joins and sorts can consume multiple work_mem allocations per session.
  • random_page_cost: Set to 1.1 on modern NVMe SSDs. The default 4.0 assumes spinning platters and unnecessarily penalizes index scans.