The Row Group Keeps a Bounding Box

A columnar scan has two separate ways to say no.

The first is vertical:

the query does not need this column

That is the basic column-store bargain. Abadi, Boncz, Harizopoulos, Idreos, and Madden describe the physical layout difference directly: a column-store can read just the attributes needed by an analytic query, whereas a row-store block carries surrounding attributes too.¹

The second no is horizontal:

the query cannot match this row group

That is the zone-map bargain. Store a small summary for a block of adjacent rows. For an ordered column, the summary can be as plain as:

min_value
max_value

Then a predicate becomes a negative proof. For x BETWEEN lo AND hi, a row group can be skipped when:

group.max_x < lo OR group.min_x > hi

For a conjunction over several columns, it is enough for any filtered column to prove disjointness. If the time interval misses, skip. If the tenant interval misses, skip. If no interval misses, read the group and check the rows.

That last sentence is the fine print. A zone map proves absence. It does not prove presence.

The Index Is Lossy On Purpose

DuckDB’s performance guide says it automatically creates zonemaps, also called min-max indexes, and can skip a row group whose range does not contain the filter value.² The same guide points out the real lever: ordered data makes zonemaps more valuable; random data can make them almost useless.

PostgreSQL’s BRIN index has the same soul in row-store clothing. BRIN stands for Block Range Index. Its documentation describes block ranges as physically adjacent pages with summary information stored per range; min/max operator classes can store the minimum and maximum value in a range, and the index is lossy, so the executor rechecks tuples in ranges that survive the summary test.³

ClickHouse’s data-skipping index docs frame the same mechanism for MergeTree: skip indexes store metadata about blocks, such as min/max values, sets, or Bloom filters, so execution can avoid reading blocks guaranteed not to match. They also warn about the limiting case: if even one matching value exists in a block, the whole block must still be read.⁴

The useful mental model is:

not a row locator
not a B-tree
not a proof that a row exists

a cheap certificate that a block can be absent from the answer

Lab: Make The Boxes Narrow

The lab below builds a synthetic table with two filtered columns:

time    continuous coordinate from 0 to 1
tenant  continuous coordinate from 0 to 1

Rows are written into fixed-size row groups. Each group stores four numbers:

min(time), max(time), min(tenant), max(tenant)

The query predicate is a time band, a tenant band, or a two-column box. A group is scanned if its min/max box intersects the predicate box. A group is skipped only if the summary proves disjointness.

Rows

4096

Row group

128

Physical order

time sort

random ingest time sort tenant sort z-order sort Predicate

time + tenant box

time band tenant band time + tenant box Predicate width

12%

Outliers

2%

Seed

37

Scanned groups Skipped groups Matching rows / predicate False-positive scanned groups

The data and ordering are synthetic. The audit checks that every skipped row group truly contains no matching row, that bounding boxes contain their rows, and that scan accounting balances.

With the default two-column predicate, 4096 rows, and 128 rows per group, the same logical table gives these deterministic outcomes:

Physical order	Groups scanned	Rows skipped	Matching rows	False-positive groups
random ingest	32 / 32	0.0%	84	1
time sort	5 / 32	84.4%	84	0
tenant sort	5 / 32	84.4%	84	0
z-order sort	4 / 32	87.5%	84	2

The exported audit is reproducible from Node. It reports 17 named checks: four correctness invariants for each synthetic layout, plus one aligned row-group-size comparison.

const lab = require("./assets/js/zone-map-pruning-lab.js");
const EXPECTED_CASES = 4;
const EXPECTED_COMPARISONS = 1;
const EXPECTED_ROWS = 5;
const EXPECTED_CHECKS = 17;
const EXPECTED_CASE_CHECKS = [
  { name: "rows-in-unit-square", ok: true },
  { name: "zone-boxes-bound-rows", ok: true },
  { name: "no-skipped-matching-group", ok: true },
  { name: "scan-accounting", ok: true }
];
const EXPECTED_COMPARISON_CHECKS = [
  { name: "finer-aligned-groups-read-no-more", ok: true }
];
const EXPECTED_CHECK_NAMES = EXPECTED_CASE_CHECKS
  .map((check) => check.name)
  .sort();
const EXPECTED_CASE_SHAPE = [
  "box predicate random ingest:random:box:32:32:4096:0:84:1:4",
  "box predicate z-order:z:box:4:32:512:87.5:84:2:4",
  "tenant band with outliers:y:y:3:32:768:90.6:468:0:4",
  "time band sorted by time:x:x:4:32:256:87.5:165:0:4"
];
const EXPECTED_COMPARISON_SHAPE = [
  "smaller row groups read no more rows for aligned range:256:512:64:384:1"
];
const EXPECTED_TOTALS = {
  cases: EXPECTED_CASES,
  comparisons: EXPECTED_COMPARISONS,
  passed: EXPECTED_ROWS,
  total: EXPECTED_ROWS,
  passedChecks: EXPECTED_CHECKS,
  totalChecks: EXPECTED_CHECKS,
  monotone: true
};

function sameJson(actual, expected) {
  return JSON.stringify(actual) === JSON.stringify(expected);
}

const audit = lab.auditZoneMapLab();
const failed = audit.details.filter((row) => !row.passed || row.passedChecks !== row.totalChecks);
const auditShape = {
  totals: {
    cases: audit.cases.length,
    comparisons: audit.comparisons.length,
    passed: audit.passed,
    total: audit.total,
    passedChecks: audit.passedChecks,
    totalChecks: audit.totalChecks,
    monotone: audit.monotone
  },
  caseChecks: audit.cases.map((row) => [
    row.label,
    row.order,
    row.predicate,
    row.scannedGroups,
    row.groups,
    row.rowsRead,
    Number((row.skipRate * 100).toFixed(1)),
    row.matchRows,
    row.falsePositiveGroups,
    row.totalChecks
  ].join(":")).sort(),
  caseCheckRows: audit.cases.map((row) => ({
    label: row.label,
    checks: row.checks.map((check) => ({
      name: check.name,
      ok: check.ok
    }))
  })).sort((left, right) => left.label.localeCompare(right.label)),
  cases: audit.cases.length,
  checkNames: Array.from(new Set(audit.cases.flatMap(
    (row) => row.checks.map((check) => check.name)
  ))).sort(),
  comparisonChecks: audit.comparisons.map((row) => [
    row.label,
    row.coarseGroupSize,
    row.coarseRowsRead,
    row.fineGroupSize,
    row.fineRowsRead,
    row.totalChecks
  ].join(":")).sort(),
  comparisonCheckRows: audit.comparisons.map((row) => ({
    label: row.label,
    checks: row.checks.map((check) => ({
      name: check.name,
      ok: check.ok
    }))
  })).sort((left, right) => left.label.localeCompare(right.label))
};
const expectedCaseCheckRows = EXPECTED_CASE_SHAPE.map((row) => ({
  label: row.split(":")[0],
  checks: EXPECTED_CASE_CHECKS
})).sort((left, right) => left.label.localeCompare(right.label));
const expectedComparisonCheckRows = EXPECTED_COMPARISON_SHAPE.map((row) => ({
  label: row.split(":")[0],
  checks: EXPECTED_COMPARISON_CHECKS
})).sort((left, right) => left.label.localeCompare(right.label));
const shapeErrors = [
  sameJson(auditShape.totals, EXPECTED_TOTALS) ? null : "totals",
  sameJson(auditShape.caseChecks, EXPECTED_CASE_SHAPE) ? null : "caseChecks",
  sameJson(auditShape.caseCheckRows, expectedCaseCheckRows) ? null : "caseCheckRows",
  sameJson(auditShape.checkNames, EXPECTED_CHECK_NAMES) ? null : "checkNames",
  sameJson(auditShape.comparisonChecks, EXPECTED_COMPARISON_SHAPE)
    ? null
    : "comparisonChecks",
  sameJson(auditShape.comparisonCheckRows, expectedComparisonCheckRows)
    ? null
    : "comparisonCheckRows"
].filter(Boolean);
if (shapeErrors.length) {
  throw new Error(
    `audit shape drifted in ${shapeErrors.join(", ")}:\n` +
    JSON.stringify(auditShape, null, 2)
  );
}
const summary =
  `${audit.passed}/${audit.total} audit rows and ` +
  `${audit.passedChecks}/${audit.totalChecks} checks passed`;
if (failed.length || !audit.ok || audit.passed !== audit.total || audit.passedChecks !== audit.totalChecks) {
  throw new Error(JSON.stringify({
    summary,
    errors: audit.errors,
    failed,
    shapeErrors,
    auditShape
  }, null, 2));
}

console.table(audit.cases.map((row) => ({
  scenario: row.label,
  order: row.order,
  predicate: row.predicate,
  groups: String(row.scannedGroups) + "/" + row.groups,
  rowsRead: row.rowsRead,
  skipPct: Number((row.skipRate * 100).toFixed(1)),
  matches: row.matchRows,
  falsePositiveGroups: row.falsePositiveGroups,
  checks: `${row.passedChecks}/${row.totalChecks}`
})));
console.table(audit.comparisons);
console.log(summary);

Those checks are deliberately negative: skipped groups must contain no matching rows, min/max boxes must contain their rows, and the scan accounting must balance. The extra comparison checks that smaller aligned row groups do not read more rows than a coarser grouping on the same range query.

The random layout is the cautionary case. Each group contains a little bit of almost everything, so its min/max box is wide. The zone maps are correct and nearly useless.

The sorted layouts do not change the rows. They change the grouping. Once neighboring rows have similar values, the boxes shrink, and disjointness becomes easy to prove.

Why Outliers Are Expensive

One stray value can widen a group summary. If a group is otherwise tight in time but contains one old event, min(time) moves left. If it contains one tenant from the other side of the table, max(tenant) moves up. The summary is still true, but it is less selective.

This is why “mostly ordered” is an empirical statement, not a vibe. It depends on:

row-group size
insert order
sort key
late-arriving records
updates or rewrites
predicate shape
outlier distribution

Move Outliers up in the lab. The group boxes get fatter. Move Row group up. Each summary covers more rows, so it tends to cover more of the domain. Move Predicate width up. A wider query is harder to prove absent from a group.

None of those effects is a bug. They are the price of using a tiny summary instead of a row-level index.

Row Groups, Pages, And Granules

Different systems put the summary at different physical scales.

Parquet’s page-index documentation says earlier format versions stored statistics in column metadata and data page headers, which meant a reader had to touch page headers to decide whether pages could be skipped. The page index adds per-column structures: a ColumnIndex for locating pages based on column values and an OffsetIndex for navigating by row index.⁵

PostgreSQL BRIN summarizes page ranges. Its pages_per_range parameter sets the tradeoff: smaller ranges mean a larger index and more precise summaries; larger ranges mean less metadata and less precision.³

ClickHouse skip indexes summarize blocks at a configurable granularity. DuckDB’s docs talk in terms of row groups. The names differ, but the invariant is the same:

summary range too wide -> fewer blocks skipped
summary range tighter  -> more blocks skipped, more metadata or more ordering work

That is the whole machine.

The Multicolumn Trap

One sort order cannot perfectly serve every predicate.

Sorting by time makes time ranges narrow. If most queries are WHERE time BETWEEN ..., that is excellent. If queries often ask for one tenant across all time, a pure time sort may leave every tenant range broad.

Sorting by tenant flips the tradeoff. Z-order or other space-filling layouts try to preserve locality across several dimensions, but they are compromises. They can improve two-dimensional boxes while being less perfect for a single dominant column.

The lab’s z-order setting is not a production-quality clustering algorithm. It is a visual reminder that physical order is part of the index. The summary stores only min and max; the layout decides whether those numbers are useful.

What To Ask Before Trusting The Skip

A data-skipping index is worth inspecting with concrete workload questions:

Which predicates are common and selective?
Are those predicates correlated with physical order?
How many rows or pages does one summary cover?
How often do outliers widen the summary?
Are late records reclustered, compacted, or left in place?
Is the filter a range, equality, set membership, or text search?
How many false-positive blocks survive and still need row checks?

The last question is the one that keeps the mental model honest. The skipped groups are victories. The scanned groups are only candidates.

A row-group min/max box is a small negative proof. It does not find the answer. It prevents the engine from reading places where the answer cannot be.

The simulator source is in assets/js/zone-map-pruning-lab.js.

1. Daniel Abadi, Peter Boncz, Stavros Harizopoulos, Stratos Idreos, and Samuel Madden, “The Design and Implementation of Modern Column-Oriented Database Systems”, Foundations and Trends in Databases, 2012. ↩

2. DuckDB documentation, “Indexing”, especially the sections on zonemaps and ordering. ↩

3. PostgreSQL documentation, “BRIN Indexes: Introduction”. ↩ ↩²

4. ClickHouse documentation, “Use data skipping indices where appropriate”. ↩

5. Apache Parquet documentation, “Page Index”. ↩