Indexes (B-tree)senior🔒 Pro concept

B-tree page splits: why an ascending key packs tighter than a random one

Simple terms

Two tables, same rows, same index, and one index is visibly fatter on disk. The difference is where the pages split. When a page fills up it has to break in two, and Postgres picks the split point based on how the keys are arriving. Keys that keep climbing, like a timestamp or a serial id, split at the far right and leave the old page packed. Random keys split down the middle, so every page ends up about half empty. Same data. More pages.

You might be asked

Two tables, same 300000 rows, same single-column index. One is loaded on an ascending key, the other on a random key. The random one's index is noticeably bigger on disk. Walk me through why -- get into how B-tree page splits actually choose where to split.

TopicB-tree leaf page splits: rightmost (ascending) packing vs non-rightmost 50/50 splits, fillfactor, and why CREATE INDEX differs from incremental inserts
PostgreSQL17.10 (the rightmost-split heuristic and the 90/50 fillfactor logic are unchanged on 12-17; suffix truncation is PG12+)
Tools usedpgi17 lab (PostgreSQL 17.10), pgstattuple/pgstatindex (avg_leaf_density, leaf_pages), source @REL_17_10 src/backend/access/nbtree/nbtinsert.c + nbtsplitloc.c + src/include/access/nbtree.h
Last reviewed2026-06-19

Pro concept

Full answer and evidence depth sit behind Pro

You have the question and a plain-English lead. Pro unlocks the short answer, what the docs say, what the code does, the labeled lab evidence or reproduction protocol, how to use it under pressure, and the references.

ProFull answer + evidence depth

Unlock the full breakdown for B-tree page splits: why an ascending key packs tighter than a random one

You have the interview question and a plain-English lead. Pro opens the short answer, the manual walkthrough, the source decode, and a 12-line evidence section that states whether it is raw output, a captured run summary, or a protocol to run yourself, plus how to use it under pressure.
  • How you'd answer it, the full senior-level short answer
  • What the docs say, the manual's actual wording, with the citations
  • What the code does, the mechanism decoded from PostgreSQL's own source at a pinned tag
  • Proof from a real run, labeled as raw output, a captured run summary, or a run-it-yourself protocol
  • Using it under pressure, the situation, the call you'd make, what goes wrong, and what people get wrong
  • Version notes, what changed across PostgreSQL releases

Card required. Cancel before day 7 and you are not charged.

Compare plans

How this was verified

The open teaser is the question and a plain-English lead. The short answer, the manual walkthrough, the source decode, the lab evidence, and how to use it under pressure unlock with Pro. Evidence is labeled as raw output, a captured run summary, or a run-it-yourself protocol.

Connected

Where this concept connects

How this concept links across the library, the interview questions that test it, its plain-English glossary definition, and the guided pathways it belongs to. Open the full map to explore further.

Open in the interactive map →
← All interview concepts