Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Page size: the granularity decision nobody revisits

Which two page sizes does a buffer pool have, and what does each one trade?

Two separate granularities live here, and they are often confused.

The pool's page size (4 / 8 / 16 / 32 KB)

This is mismatch in yet another form.

  • Too small: more frames, more hash lookups, more descriptors, and more I/Os per sequential scan.
  • Too large: a point lookup reads amplified bytes, and a row that does not divide evenly wastes the remainder.

The right size is set by the ratio of point-lookup traffic to scan traffic and by the efficient transfer size of the underlying device. That transfer size has moved by orders of magnitude from disk to NVMe, while default page sizes have barely moved. This mismatch is a live, real inefficiency, not a historical note.

The backing memory's page size

This is a different decision. A 100 GB pool backed by 4 KB virtual-memory pages needs 100 GB ÷ 4 KB = about 26 million page-table entries. A with on the order of a thousand-plus entries covers a few megabytes of that. So the pool misses in the TLB on essentially every access to a new page, and the hardware page walk is on the critical path of every buffer access. Backing the pool with 2 MB multiplies the TLB's reach by ~500× and turns this from a dominant cost into a non-issue.

The operational caveat

Transparent huge pages () are not the same as explicit ones. THP's background compaction can stall an allocation for tens of milliseconds while it defragments memory. That turns a steady workload into a spiky one. The standard advice across Oracle, MongoDB, Redis and PostgreSQL deployments is explicit huge pages with THP disabled. This is "variance over mean" again:

One-line compression: the pool's page size is a read-amplification trade. The pool's backing page size is a TLB-reach trade. Only one of them is usually tuned.