# Pre-aggregates of a unit (metadata-only and low-cardinality aggregation)

Each stored unit of a column index carries sums, histograms (the *cube*) and per-list *rollups*, so count, sum, avg, min, max and low-cardinality `GROUP BY` are answered from metadata without reading rows. Results are exact; a unit that cannot answer is scanned. Size, build and timing runs are in perf/COL-PREAGG-RESULTS.md.

Versions: pre-aggregates 3.0 (column format minor 2; 3.0 writes minor 3); element rollups minor 4 (sections 2, 6, 7); on-demand rollup refresh by a worker 3.1 (section 7); `rollup_columns` minor 9 (sections 2, 6).

Files: `src/col/preagg.[ch]`, `src/col/preagg_opt.c`, `src/col/preagg_read/preagg_read.c` (formats, option, compute, verify; portable), `src/col/seg_write/seg_write.c`, `src/col/seg/seg.c`, `src/col/pagg.[ch]` (through the column source), `src/col/rollup.c` (on-demand refresh), `src/agg/colsrc.h` (`unit_exact`, `unit_agg`, `unit_hist`, `unit_rollup`, `preagg`), `src/agg/spec.c` (`sift_aggspec_preagg`), `src/agg/unit.c` (`preagg_*`). Tests: the table in section 6.

## 1. What other engines precompute, and what is taken

| System | Taken here |
|---|---|
| Parquet / ORC footer statistics ([Parquet statistics](https://github.com/apache/parquet-format/blob/master/README.md), [ORC ColumnStatistics](https://orc.apache.org/specification/ORCv1/)) | the zone maps (`SiftCZone`) hold `null_count`, `min`, `max`; exact 128-bit `sum`/`sum of squares` added (the zone's int64 sum overflows) |
| ClickHouse aggregating projections ([projections](https://clickhouse.com/docs/en/sql-reference/statements/alter/projection)) | the cube is a projection `GROUP BY dim` with `count`, `sum` per measure; the element rollup is a projection over all a list's dims with finalized `count`, `sum`, `min`, `max` as plain columns; no merge-time state, the unit is immutable |
| Apache Pinot star-tree ([star-tree index](https://docs.pinot.apache.org/basics/indexing/star-tree-index); Xin et al., *Star-Cubing*, VLDB 2003; Gray et al., *Data Cube*, ICDE 1996) | `cube(dim: m1, m2)` is a `dimensionsSplitOrder` of depth 1 with `SUM`/`COUNT` pairs (a query may use it only if all its aggregates and its one group column are stored); the element rollup keeps every dimension of the list in one level, no star nodes; `rollup_columns` does the same for the table's rows: distinct tuples of two to six scalar columns per unit, any subset answered by adding up tuples, capped per unit |
| Druid ingestion rollup ([rollup](https://druid.apache.org/docs/latest/ingestion/rollup/)) | the element rollup is the ingestion rollup per unit, beside the rows; a query that does not fit scans them |
| DuckDB, Snowflake zonemaps and micro-partition metadata ([micro-partitions](https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions)) | `sift_col_unit_exact` + zone cover test |
| PostgreSQL ([xaggr](https://www.postgresql.org/docs/current/xaggr.html), [index-only scans](https://www.postgresql.org/docs/current/indexes-index-only-scans.html)) | visibility rule of section 4; section 5 builds PostgreSQL's own transition states |

A unit is immutable, so correctness under DML is the visibility rule alone. Results are PostgreSQL-exact (no sketches).

**One rollup of all a list's dimensions per unit** (a star-tree answers a dimension subset only if the split order puts the query's dimensions first; a projection per subset copies the data; ingestion rollup discards the rows). A unit and list keep the distinct tuples of up to 6 low-cardinality dimensions with `count(*)` and, per measure, the non-NULL count, sum, minimum and maximum. Any GROUP BY over a subset and any filter on the dimensions is one pass adding up the tuples that agree on the group key. The price is that all dimensions are in every tuple, so the tuple count is the number of distinct combinations in the unit (bounded by a cap and the byte budget; the highest-cardinality dimension is dropped greedily when the product explodes). Example: 28 units of 100,000 documents (13,800 elements each) hold 36 tuples for `kind, flag` and 1,440 for `kind, sub, flag` (12, 40 and 3 values, every combination present). A query by a dropped dimension, an unstored measure or a high-cardinality key scans that unit.

**Not covered**: `count(DISTINCT x)` (except of a column the cube holds a histogram of: its groups are its distinct values, so answering units add them to the set the scan fills, `sift.agg_preagg_distinct`; one column, no `FILTER`, plain 32-bit or text); aggregates not additive over tuples (`percentile_*`, `mode`, `string_agg`, `array_agg`, `stddev` of floats); `min`/`max`/`sum` of an unstored measure or an expression; group keys with more distinct values than the tuple cap in a unit (that unit scans); numeric (decimal) measures of jsonb (section 8). Sketches (HLL, t-digest) would be approximate; answers here are exact or the unit scans.

## 2. What is precomputed (written when a unit is built, merged, promoted, rewritten by VACUUM's merge, and by every worker of a parallel build)

- **Placement**: after the zone arrays, inside the `ZONES` section of the segment (the segment header has no free section: BM25 shares it). Header `COLP` (version, ncols, nsb, offsets); no unit record is touched, so a metadata query never reads a chunk. A segment of minor 0, 1 or 2 is refused with the REINDEX hint.
- **Leaf sums** (every INT-class leaf, children of lists included): exact `sum` and `sum of squares` as int128 (`u64 lo`, `i64 hi` arrays over the units, 40 bytes per leaf and unit). `sumsq` leaves int128 only for int8 values of about 10^18: flag `SIFT_CAGG_SUMSQ_OVF`, the consumer then scans. Zone data are reused for `count`, `nnull`, `min`, `max`, float `sum` (row order of the unit).
- **Cube** (`sift_cpagg_cube`): per unit and dimension `GROUP BY dim -> count(*)`, and per measure the non-NULL count and the sum (INT measure: exact int128; float: double in row order, seeded with the first value as `float8pl` does, so a group of `-0` sums to `-0`). Group keys are in key domain, ascending, NULL group last: a text dictionary leaf by **segment code** (translate by value through the segment dictionary), an integer, `date`, `bool` or `numeric(p,s)` leaf by its stored value (a leaf holding `date_trunc('month', ts)` is the time dimension). Stored as varints (counts, key deltas, zigzag int128), framed by a directory so one dimension decodes alone.
- **Dimensions** are scalar INT/STR leaves (not the presence leaf `<column>#present` of a jsonb document, a flag of the column; no floats: `-0` and `NaN` group by value in PostgreSQL), top level, not inline text, no overflow value. A dimension with more than 256 groups in a unit has no histogram there (cap; `cube(...)`: 4096). **Measures**: `numeric`, `real`, `double`, `int2/4/8` leaves, at most 8 per dimension in the default (decimals and floats first, then integers); the dims a unit keeps are chosen by size, smallest histogram first, under the budget below, and a kept dim carries all its measures: +1.6% index bytes on a 10-column table with 4 dims (+0.0% to +1.6% from 25k to 200k rows).
- **Min and max per group** (column format minor 7): the boolean reloption `cube_minmax` (default on since 3.3; `false` writes what 3.2 wrote) makes every group of the cube of units written from then on hold, for each integer measure (`int2/4/8`, `numeric(p,s)` as its scaled integer), the smallest and largest value of its non-NULL rows (flag bit 1 of the dim body; after the measure's sums, the minima of groups that have a value, then the maxima, as zigzag keys). The decoder refuses a group whose minimum is above its maximum and any other flag bit; `sift.col_verify` recomputes them and refuses them in a segment older than minor 7. Float measures keep none. A `sift_table` writes them too (no setting: `sift.col_format_minor` below 7 writes none), except in the calendar cells of a date or timestamp column, whose bytes fit the calendar dims' share of the unit only without them (the dim says so in its flag byte; the verify recomputes each dim as it is stored).
- **Calendar cells of a date or timestamp** (column format minor 8, `sift_table`): a `sift_table` has no index option and no expression leaf, so each unit's cube also holds, for every top-level `date`, `timestamp` and `timestamptz` column, the groups of its calendar cells at the **finest grain that fits**: days, else months, else years (the *ladder*). A group's key is the cell's first day, as days since 2000-01-01 (a `date`); a `timestamptz` is cut in UTC days, whatever the session zone. The directory leaf is `grain * SIFT_PAGG_CAL` (128) plus the column (grain 1 day, 2 month, 3 year), so the grain is recorded per unit and the reader asks for a column's dim at whichever grain the unit has.
  - A grain fits when the unit has at most 1,024 cells of it (`SIFT_PAGG_CAL_GROUPS`: 2.8 years of days, 85 of months, 1,024 of years), each standing for 8 rows or more on average (`SIFT_PAGG_CAL_ROWS`), and the dims of all calendar columns together fit 10% of the unit's bytes (at least 8 kB, counted as built; the other dims have a budget of their own, 40%). A cell takes about 20 bytes with one measure and about 33 with four: 1,024 cells are 20 to 35 kB, the 5% of a unit of 400 to 700 kB. A unit with an infinity or a day before 4713 BC in the column, one that fits no grain, and one written before minor 8 has no dim for the column and scans.
  - The cube holds 16 dimensions (`SIFT_PAGG_MAX_DIMS`). When plain columns and date cells ask for more, the cells come first and the default dims give way (the dates' own histograms, then the last columns; a day cell answers what they do) and a NOTICE says which. A user-written `cube(...)` is kept as written (`col_cube_priority`). For a `sift_col` index an explicit `calendar_columns` list that does not fit is refused; the default or `'all'` keeps the columns that fit, in column order, and the build and `sift.col_refresh` NOTICE the ones left out, as for the other caps (a rollup no unit holds, a set summary or key bitmap not written).
  - **Beside `jsonb` paths** (3.4, same minor 8, written only where every kind of the rows is known): `calendar_columns = 'all'` or a list writes the date cells of an index with `paths` (3.3 refused it). Not the default there: on a 13-column index with 3 date columns and 353,029 rows they grew the index by 9.3% to 12.1%.
  - **Range tests from the cells** (3.4): a unit whose date has too many days for a histogram of its own answers a comparison, an AND or an OR of comparisons on it from the cells, each decided by its span (cell inside passes, outside does not, a cell a bound cuts leaves the unit to a scan), so `WHERE d >= a AND d < b OR d >= c AND d < e` is read from the cells of every unit no bound cuts (`agg_cal_paths`); a range with day bounds cuts the month cell of a unit that spans many months.

  **Keys answered from the cells.** The planner answers every key that is a function of the cell: for a key of the leaf `x`, the grains whose cells hold one value. Keys: `date_trunc(unit, x)` and `extract` or `date_part(field, x)` of a `date`, `timestamp` or `timestamptz`, the casts `::date`, `::timestamp`, nested and combined (`date_trunc('month', date_trunc('hour', x))::date`, `extract(year FROM date_trunc('month', x))::int`), and a comparison, `IN` or `IS NULL` of them as a test.
  - Depends only on the day (`day`, `week`, `dow`, `isodow`, `doy`, `isoyear`, `date_trunc('week' | 'day')`, `::date`, every function of a week start, whose year or month can differ from the day's): needs cells of days.
  - Depends only on the month (`month`, `quarter`): cells of months or days. Depends only on the year (`year`, `decade`, `century`, `millennium`): any cell.
  - A unit whose cells are coarser than the key scans (a week key over cells of months), as does a key that changes within a day (`hour`, `minute`, `second`, the timestamp itself): `sift.col_rollup_misses` says `rows_key_finer_than_day`. The key is evaluated once per cell, at its first day, so `GROUP BY extract(isoyear FROM ts)` costs a group per cell.
  - A `timestamptz` key reads the session zone (`date_trunc('month', tz)`), which UTC day cells cannot answer: `sift.col_rollup_misses` says `combination`. Over the UTC clock (`tz AT TIME ZONE 'UTC'`, `date_trunc('month', tz, 'UTC')`) the key is a function of the UTC day and is answered. Other miss reasons: `rows_key_not_a_column` (a key of an hour or less on a timestamp); `rows_key_time_zone` or `rows_key_finer_than_day` (a `date` key finer than a day); `combination` (a `date` column whose values, infinities included, are too far apart for a lookup table of its days). On a `date` column, keys that cast to `timestamptz` (`date_trunc('month', d)` is that) use cells of days only: a month cell would give a skipped day of a zone the value of its first day.
  - A range on the column (`ts >= a AND ts < b`, any zone for a `timestamptz`: the bounds are instants) is decided by each cell's span: inside passes, outside does not, the NULL cell never does, and a unit with a cell a bound cuts is scanned. With no key, a range also answers from cells the units whose zone it does not contain; an `extract(...) = k` or `IN` test of a `date` runs on the cells (`SAP_LUT`, its grains narrowed to those the test is constant over). Other tests, a `FILTER` of the aggregates, two keys and `min`/`max` of the column scan the unit.

  The decoder refuses a key that is not the first day of a cell of the unit's grain, a column that is not a date or timestamp, and keys out of order; `sift.col_verify` and the cube check recompute the cells from the chunks. A `sift_table` writes them only when `sift.col_format_minor` is 8 (the default) and the schema has no JSON path leaves. Since 3.3 a `sift_col` index keeps them for every date, timestamp and timestamptz column (reloption `calendar_columns`, default `'all'` or empty; `'ts, d'` names columns, `'off'` none), with the same ladder, budget and planner rules; an index with JSON paths, `preagg = 'off'` or a `cube(...)` given dim by dim has none by default and refuses the option when it names columns. A superunit does not merge the calendar cells.
- **Rollup of the rows** (column format minor 9): the reloption `rollup_columns = 'a, b'` or `'a, b, c: m1, m2'` (default `auto`, which picks a pair at `CREATE INDEX` when one fits; `'off'` writes none; names are columns of the index) makes every unit written from then on hold the **rollup of its rows**: the element rollup below with the unit's rows in place of a list's elements. A cell per distinct combination of the dims' values (two to six boolean, integer, date, `numeric(p,s)` or text top-level columns; NULL is a value of its own) with the cell's rows and, per measure, the non-NULL count, exact sum, minimum and maximum. Measures are the ones named after the colon, else the integer and `numeric(p,s)` columns that are not dims (decimals first, 8 at most); a float column is none, because a float sum depends on the order of additions. Same blob and encoding as a list's rollup, listed in the unit's element blob under the leaf number `ncols`, which no list has; the decoder also refuses cells that do not add up to the unit's rows, a cell with no rows, a measure with more values than its cell has rows, and a minimum above a maximum.
  - **Bounded**: a unit keeps its cells only when it has at most a tenth of its rows of them (at least 64, at most 4,096; the exact dims named, none dropped); otherwise it has no rollup and is scanned, so a high-cardinality column costs no bytes there (`Units Without The Rollup` of `EXPLAIN ANALYZE`).
- **Format-minor compatibility** (a 3.2 library reads everything written under a lower setting and refuses a higher-minor segment with the REINDEX hint; `sift.col_verify` refuses each feature in an older minor). Units written before a feature scan until `REINDEX`, a merge, a promotion or `sift.col_refresh` (section 7) writes it:

  | feature | minor | written when |
  |---|---|---|
  | `cube_minmax` | 7 | option on (`sift_table`: always), `sift.col_format_minor` >= 7 (default 12); for jsonb paths, every kind of the rows known (promotion and merge lower the setting otherwise); a setting of 6 or less ignores the option |
  | calendar cells | 8 | `sift_table`: setting 8 (default) and no JSON path leaves; `sift_col`: `calendar_columns` (below 7 the old bytes) |
  | `rollup_columns` | 9 | option on, setting >= 9 (default), jsonb-path rule as for minor 7; the on-demand refresh does not ask for it (it would turn a segment a 3.2 library reads into one it cannot), and `sift.col_refresh` counts a segment without it as stale only under a setting of 8 and where the rows' kinds are known |

- **Element rollups** (format minor 4; `COLP` directory version 2, written only by a minor 4 writer, `sift.col_format_minor`): for a list group (an array of objects `items[]{k, n, x}`, an array of scalars `tags[]`, a map `m.*`) each unit holds one **rollup** of the list whose rows are the elements: the distinct tuples of the values of up to 6 low-cardinality leaves of the list (the **dims**), each with its element count and, per **measure** (a numeric leaf of the same list, at most 8), the non-NULL count, sum, minimum and maximum. A tuple is a group of the `GROUP BY` of all its dims, so any `GROUP BY` over a subset of dims, any filter on dims, and `count`, `sum`, `avg`, `min`, `max` of a measure are answered by adding up the tuples that agree on the subset (an element that is not an object is a tuple with NULL members, as the unnest produces it).
  - Blob encoding: `u8 ndims, u8 nmeas, varint ntuples`, the leaf of every dim and measure, then column-wise: per dim a NULL bitmap and the keys as zigzag deltas (tuples sorted by dims, NULL after values; text by segment code, integers, dates and numerics by stored value, as in the cube), the counts, and per measure the non-NULL counts, sums (integer: exact int128 as two varints; float8: the sum in row order, seeded as `float8pl` does), minimums and maximums. A unit's element blob follows the column cubes: `u8 nlists`, then per list its leaf and byte count, then the blobs; the directory header gains `ecubedir_off` and `ecube_bytes` after the leaf-sum offsets.
  - **A unit's dims are in its blob**: units of one index may have been written with different dims (an `ALTER INDEX` between builds, a dim dropped for one unit), and the reader answers a unit only if the blob holds the dims and measures the query reads; otherwise that unit scans. Depth 1: a dim is a boolean, integer, date, numeric or text scalar leaf of a list that is itself a top-level column (a list inside a list is refused by the index).
  - **Default**: every list with at least one dim candidate, its dims (at most 6) and numeric leaves as measures (8 at most, decimals and floats first). Tuples are at most `min(4096, max(64, elements / 32))`; dims are dropped from the one with most distinct values in the unit until the tuples fit (`kind, sub, flag` of 12, 40 and 3 values with 13,800 elements per unit keep `kind, flag`: +1.7 kB a unit, 2% of the index; naming all three costs 43 kB a unit for 1,440 tuples of about 30 bytes, +49%). Each list has its own budget of 2% of the unit's encoded bytes, at least 2 kB, apart from the cube's.
  - **Explicit**: the option `rollup_leaves` (below) or the clause `rollup(items[].kind, items[].sub: items[].score)` of `preagg` names a list's dims (and measures); the unit holds exactly these or, when the tuples exceed the cap of 4096, no rollup for that list (it scans; `EXPLAIN` shows `Units Without The Rollup`; no dim is dropped behind a given name).
  - A unit has no rollup for a list whose count overflowed (a value that is not an array, a list cut by the shredder); an overflowed dim or measure is left out. Minor 3 segments have none, are read as before in the same index, and stay as written (the on-demand refresh leaves them alone: a rollup needs minor 4 and 3.0 would refuse the segment; the merge that rewrites them adds rollups). `sift.col_verify` recomputes every rollup from the chunks.
- **Options.** `preagg`: `'default'` (every dimension candidate; the kept dims of a unit, with all their measures and min/max, take at most 40% of the unit's encoded bytes as built, at least 8 kB: smallest histograms kept first, sizes within an eighth of each other in the order of the columns; on disk the index holds about a quarter of that for dims of few groups, more for many); `'off'` (leaf sums only, no rollups); `'cube(country: amount, quantity; status)'` (exactly these, no budget; attribute names or shredder paths); a `rollup(dims: measures)` clause beside a `cube(...)` clause (each clause replaces the default of its kind).
  - `rollup_leaves` (reloption of `sift_col`, **declarative**, 3.1; `rollup` until 4.1, still accepted): `WITH (paths = 'items[]{kind text, sub text, flag text, score float8}', rollup_leaves = 'items[].kind, items[].sub, items[].flag')` names only the dims, with the names of `paths`; their lists are inferred, the measures are the list's numeric leaves (8 at most), and segments written from now on hold exactly these dims (up to 4096 tuples a unit, no byte budget). `rollup_leaves = 'off'` disables the rollups and keeps the cube; `rollup_leaves = 'default'` or no option is the automatic rule.
  - Names are checked at `CREATE INDEX` and `ALTER INDEX ... SET (rollup_leaves = ...)`; errors: `invalid rollup_leaves option: "x" is not a leaf declared in paths`, `... is not a leaf of an array element or a map value`, `... is not a low-cardinality leaf: it must be a boolean, integer, date, numeric or text leaf` (a float, a JSON or an `astext` leaf cannot be a dim), `... is named twice`, `a leaf name is missing`, `the list of "x" has more than 6 rollup leaves`. Too many distinct values is not a DDL error: a unit whose tuples exceed 4096 holds no rollup for that list and scans (the first query that meets it asks for a refresh once per segment, section 7).
  - A change applies to segments written afterwards; older units are asked for on demand (section 7) or rewritten by a merge. The boolean reloption `rollup_on_demand` (default true) and the setting `sift.col_rollup_on_demand` turn the asking off per index and per session.

**Measured**:

| feature | figure | workload |
|---|---|---|
| cube, default | +1.1% to +1.2% of the index (2.4 kB a unit; `off` +0.07%); serial build CPU +3.9% (one hash per candidate dimension, three adds per stored measure; a high-cardinality dimension stops after 257 groups), inside the noise with workers | 300,000 to 3,000,000 rows, store benchmark index |
| cube, default, wide index | 16 columns, 8 dimensions of 50 values: `GROUP BY` a dimension 1.92 -> 0.32 ms (5 of 8 dimensions answered -> 8 of 8), +6.7% -> +8.9% of the index; 8 dimensions of 200 values: 5.4 -> 0.54 ms, +7.5% -> +30.4%; at most +41% (32 columns, 200 values); the store index is unchanged (the byte budget) | 1,000,000 rows |
| `cube_minmax` | `GROUP BY kind` with `min`/`max` of an int4 and an int8 5.5 -> 0.34 ms; of an int8 and a numeric(10,2) 30.5 ms normal plan, 2.6 ms scanned, 0.23 ms from the groups; +0.17% of the index (1.0% to 3.0% on a store-like table); insert, promotion, merge and VACUUM CPU inside the noise | 200,000 rows, 13 columns, 53 units |
| calendar cells | `GROUP BY date_trunc('month', d)` 2.4 -> 0.54 ms; UTC month of a `timestamptz` 2.6 -> 0.70 ms; +1.8% of the index for two columns (+1.0% for one) | 200,000 rows, 53 units |
| `rollup_columns` | `GROUP BY kind, sub` 104 ms normal plan, 2.11 ms scanned, 0.63 ms from the cells (2,688 cells, about 0.2 microseconds a cell); 2 to 8 times faster than scanning the units; index +4.6% for 96 cells a unit, +19% for 480; build CPU inside ±15% | 200,000 rows |
| element rollups | +2.0% of the index with the default rule (explicit `kind, sub, flag` +49%); build CPU +3.4% to +4.2%; `GROUP BY kind, flag` 18 to 30 times faster than its scan; 0.12 ms for 28 units and 1.3 to 2.9 ms for 276 units (5 to 10 microseconds a unit) | 100,000 and 1,000,000 documents |

The cube's histograms remain the better answer to one key (the element rollup is 1.3 to 5 times slower than a depth-1 cube for `GROUP BY kind`, 0.09 against 0.12 ms at 100,000 documents, but answers two keys, which the depth-1 cube could not) and are kept for it.

## 3. Interface (phase 1)

- Engine (`col/pagg.h`): `sift_col_unit_preagg_ok(unit)` (stored unit, `sift_col_unit_exact`), `sift_col_unit_agg(unit, leaf, &SiftCAgg)`, `sift_col_unit_hist(unit, dim, mcxt, &SiftCHistDim, &SiftColDict)` (false: the unit scans). A unit of the write buffer or a delta batch has no sidecar.
- Provider seam (`agg/colsrc.h`, additive, NULL = none): `preagg(rel, &SiftSrcPreagg)` (planner: the dimensions and measures the index writes), `unit_exact`, `unit_agg` (`SiftSrcUnitAgg`: counts, min/max, exact sums, sumsq, float sum, flags), `unit_hist` (`SiftSrcHist`: groups, `SiftSrcDict` of the segment, count, per measure `nn`, `sum`/`sumf`; memory valid until the next `unit_hist`). Implemented for `sift_col` (`agg/col.c`) and `sift_table` (`table/src.c`); both read the sidecar through `agg/pre.c`. A segment of an older layout answers by the schema it was written with: a column the layout lacks, or a measure the table dropped, scans the unit.
- SQL: `sift.col_unit_agg(index, leaf)` and `sift.col_unit_hist(index, dim, measure)` (one row per stored unit / group; `exact` is the visibility rule), `sift.col_index_info` columns `preagg_bytes`, `preagg_units`; `sift.col_verify` recomputes sums and cubes from the chunks (C4). `sift._test_col_pagg_fold` benchmarks the metadata path.
- **sift_table owner** (`tbl_preagg`): sealed segments carry the sidecar (same writer); the table provider has the four callbacks over the table's `SiftColUnit` (the same `sift_col_unit_*` calls). A table unit is exact when `sift_col_unit_preagg_ok` holds: `SiftColVisibility.sb_mask` (`tbl_sb_mask`) is all-ones only for a superblock with no tombstone and no write buffer row (UPDATE/DELETE invalidate only the superblocks they touch); delta pages are in-memory units and scan. `SiftSrcStats.exact_frac` estimates the qualifying share of superblocks.

## 4. When a unit's pre-aggregate may answer

1. **Visible and clean** (`unit_exact`): a stored unit with no dead row (SB not dirty in the segment's liveness bitmap: VACUUM marks it, a merge rewrites and recomputes it), and every heap page it has rows on is all-visible to the snapshot (VM, as index-only scans; never for a non-MVCC snapshot). A unit with dead rows whose pages are all-visible answers for counts and integer sums net of them (section 12). Rows inserted later are other units (write buffer, newer segment), scanned by the normal path and added to the result, so the aggregate describes exactly its own rows. Inserts, updates and deletes cost only the units they touch.
2. **Predicate covers the unit** (consumer): no predicate; or the predicate on the dimension itself (`=`, `IN`, range: select groups by key value, text by the dictionary); or a zone-covered predicate (`min..max` of the leaf inside the range with `nnull` accounted: the unit answers whole, a disjoint unit is pruned); or `IS NULL` / `IS NOT NULL` of a scalar leaf that is not a dimension, which the zone's `nnull` and `nvals` decide (`IS NOT NULL`: no NULL in the unit; `IS NULL`: only NULLs; no NaN stands in for one). A unit the predicate cuts through is scanned. A predicate on a leaf other than the dimension is never answered from a histogram.
3. **Leaf not lossy**: no `SIFT_COLZ_OVERFLOW` in the leaf and unit (`unit_agg` false), no inline-text zone for text min/max.

## 5. PostgreSQL-exact semantics (what the consumer builds)

| aggregate | from | notes |
|---|---|---|
| `count(*)`, `count(x)` | `nvals`, `nvals - nnull`; group `count`, `nn` | `int8` |
| `sum(int2/int4)` | int128 sum | `int8` when it fits (else the unit scans, PostgreSQL would raise); `sum(int8)` is `numeric` (its state is an int128 sum) |
| `sum/avg(numeric(p,s))` | scaled sum / 10^s | every value has dscale s, so the `NumericAggState` (N, sumX, maxScale s) is built from (count, sum); final function is PostgreSQL's |
| `avg(int)` | (count, sum) | build `int8[2]` / `Int128AggState`, call `int4_avg`, `int8_avg` |
| `stddev/variance(int, numeric)` | (count, sum, sumsq) | exact `Int128AggState`/`NumericAggState` with `sumX2`; sumsq overflow: scan |
| `sum/avg(float8)`, `avg(float4)` | unit sums in row order | same order caveat as PostgreSQL's parallel sum (float addition is not associative); refused: `sum(float4)` (float4 accumulator), float `stddev` (Youngs-Cramer `Sxx` is not stored) |
| `min/max` | zone key; per group of a cube with `cube_minmax`, the stored minimum or maximum of the integer or numeric measure | integer, date, time, numeric, float (`NaN` last, as PostgreSQL); `-0` vs `0` ties may print either; not text (codes and prefix keys are not order); per group not float |

An all-NULL measure group (`nn = 0`) yields NULL, the group still counts its rows; a NULL key is its own group. Text group keys are compared bytewise: deterministic collations only (as `SiftAgg`).

## 6. Consumption in SiftAgg (built)

- **Shapes** (`sift_aggspec_preagg`, `agg/spec_preagg.c`: the planner costs with it, the executor starts with it). A row-level scan of one `sift_col` index (no join, combo, set, arm or unnest) with no key, or one key that is a dictionary text, `int2/int4`, `int8`, `numeric(p,s)`, date or bool column the index writes a histogram of (`SiftSrcPreagg`; an `int8` or numeric key is the column itself, not an expression, and its group is the stored integer). Aggregates: `count(*)`, `count(m)`, `sum/avg(int2, int4, int8)`, `sum/avg(numeric)` of a measure `m` of that histogram, `count(key)`, `min/max` of an integer or numeric measure `m` per group when the cube holds them (`cube_minmax`), and, without a key, `min/max` of an integer, date or numeric column.
  - Conjuncts: on the key's column (without a key: on one column) any test the executor evaluates (`=`, `IN`, `IS NULL`, `LIKE`, comparisons, expressions of the column) runs on the groups; on any other column only an integer or date comparison the unit's zone lies inside (without NULLs), or `IS [NOT] NULL` of a leaf that is no dimension when the zone's NULL count holds the test for every row (the soft-delete shape, `WHERE deleted_at IS NULL`), keeps the unit answerable.
  - Scanned as before: float sums (addition order), `count(DISTINCT)`, `min/max` per group of an index or unit without them, a float `min/max` per group, two keys, an expression of a key, tests on other columns, text `min/max`.
- **Executor** (`agg/unit.c`, `preagg_unit`, called by `process_unit` first): `sift_colsrc_unit_exact`, the zone test of the other columns, then the unit's sums (no key) or histogram. A text group is translated to the key id once per dictionary (`dict_kmap`), an integer group through the range table (`key_cover`, shared with `key_prepare`); conjuncts on the key column run in `eval_pred` on a vector whose rows are the groups; each kept group adds its rows to `seen` and its `PaVal` (rows, non-null rows, exact int128 sum) to the group table's aggregate states (`preagg_add`: `count`, `sum`/`avg` of int, `sum`/`avg` of numeric in 128 bits, `min`/`max`). A unit that cannot answer (dirty page, write buffer, no histogram for the column in that unit, a measure the histogram lacks, an overflow value) is scanned into the same table, so a mixed table is exact and workers need no change.
- **Cost** (`agg_cost`, `preagg_share`): the share of exact units is the share of all-visible pages (`allvisible_frac`); they cost `AGG_UNIT_COST + groups * AGG_PREAGG_GROUP` (25 operators per group, about 80 ns), the others the scan. From 98% exact no parallel path is offered: starting workers costs 3.1 ms (1 M) to 6.5 ms (10 M), more than the answer.
- **Element level** (`sift_aggspec_preagg` with `group >= 0`, `preagg_rollup` in `agg/unit.c`): the unnest of an array leaf (`jsonb_to_recordset`, `jsonb_array_elements`, `jsonb_array_elements_text`) or a map (`jsonb_each`, `jsonb_each_text`: the key is the map rollup's dim, the value its measure) over that list's rollup.
  - Group keys: none, one or several of the unit's dims in any order, or a function of one dim (`upper(kind)`, `left(day, 7)`, a `CASE` on an integer dim: evaluated once per distinct value of the tuples). Conjuncts on dims: `=`, `<>`, `IN`, `IS [NOT] NULL`, `LIKE`, comparisons and an `OR` of them. Aggregates: `count(*)`, `count(x)` of a dim or measure, `sum`/`avg`/`min`/`max` of an integer or float8 measure of the same list, each with an optional `FILTER (WHERE ...)` on dims; on the rows only the zone comparisons above.
  - Every kept tuple adds its count and per-measure states to the group table (`preagg_fold`), through the key ids, the direct address table or the scan's `hash_group`, so a mixed table is exact. A unit that lacks a dim of the query, a measure it reads, or the rollup itself is scanned (other units still answer) and asks for a refresh (section 7).
  - Not answered: a conjunct on a leaf that is not a dim (`WHERE e.q > 3`), a key of the rows (`d.grp`), a key that is not a dim or a function of several, `count(DISTINCT)`, a `FILTER` on a measure, `percentile_*`, an aggregate of an expression, a float aggregate other than `sum/avg/min/max` of a float8 measure, `sum(float4)`. A float8 sum adds tuples where the scan adds rows, so last digits can differ (as for a parallel sum); the unit scans when a value of the float leaf is not finite or above 1e100 for `sum`/`avg` (min and max only refuse NaN), so `avg(float8)` keeps its overflow check.
  - `EXPLAIN` shows `Units From Pre-aggregates`, `Pre-aggregate Dims` and `Measures` (what the query reads), `Units Without The Rollup` (exact units scanned for lack of it) and `Rollup Refresh Queued` (segments asked for, section 7).
- **Rows level** (`sift_aggspec_preagg_why` tries `preagg_rollup` with group -1 when the histograms cannot answer a scan of the rows; `agg/preagg/` answers it): for an index with `rollup_columns`, a scan of the rows with two or more group keys, or a key and a conjunct on another column, or a conjunct on a column other than the histogram's whose zone a unit must lie inside (which only the units of a clustered column do). Group keys are dims, one or several, in any order; every conjunct tests dims only (`=`, `<>`, `IN`, `IS [NOT] NULL`, `LIKE`, comparisons, an `OR` of them) and runs on the cells, so a range of an unclustered column or an `IN` list with gaps is answered too. Aggregates: `count(*)`, `count(x)` of a dim or measure, `sum`/`avg` of a `smallint`, `integer`, `bigint` or `numeric(p,s)` measure, `min`/`max` of one, each with an optional `FILTER` on dims. A query the histograms answer (one key, tests of that key's column, no test at all) keeps them: they have fewer groups than the cells.
  - Not answered: a float aggregate, `count(DISTINCT)`, an expression of a key (`a % 3`) or of an aggregate's argument beyond `column * k + c`, a conjunct on a column that is not a dim, grouping sets, top-N, and a dim of `int8` or `numeric(p,s)` as key or test (the cells hold them, the executor tests 32-bit values and dictionary codes).
  - `EXPLAIN ANALYZE` shows `Pre-aggregate Rollup: rows`, `Pre-aggregate Dims` and `Measures`, `Units From Pre-aggregates`, and `Units Without The Rollup` for exact units that had none (written before the option, or over the cap); `EXPLAIN (ANALYZE, VERBOSE)` gives the reason of a refusal. The cost is the element level's (next bullet).
- **Rollup or scan, by cost** (`rollup_cheaper` in `agg/unit.c`, `sift_aggspec_unit_costs` in `agg/spec_preagg.c`, the planner's `preagg_unit_ops`): a tuple costs more than an element: the varint decode of the columns the query reads and the fold into the groups take 300 to 800 user instructions, where a scan spends 6 to 150 on an element (counts of one key 17, three keys 50, three float aggregates with min and max 150; 100,000 documents, 400,530 elements, 41,760 tuples in 29 units, `perf stat`). Rollup against scan, per unit of 13,800 elements and 1,440 tuples (10%), millions of instructions: `GROUP BY kind` with `count` 14.6 against 7.0; `GROUP BY` three keys 19 against 20; float `min`/`max` 32 against 54 to 78.
  - Per exact unit the executor takes the cheaper: the rollup's tuples (read from its header) times a tuple's cost for this query, against the unit's elements (the zone of one of its leaves) times an element's cost. Units scanned that way show as `Units Scanned By Cost` and ask for no refresh. `sift.agg_rollup_cost` scales the tuple (0: always the rollup; the rollup tests set it). The planner prices exact units with the same two costs and the tuples it expects (the product of the rollup dims' distinct values, at most the unit's elements and 4096).
  - Looking at a unit (its exactness, its rollup) costs buffer reads too: after three units in a row whose rollup costs at least a quarter more than their elements, the next sixteen are scanned without looking, and one is looked at again.
- **Switches and EXPLAIN**: `sift.agg_preagg` (on) turns it off for plan and executor; `EXPLAIN ANALYZE` shows `Units From Pre-aggregates` next to `Units` and counts `Rows Scanned` for scanned units only (the line appears only when the shape qualifies). `EXPLAIN (ANALYZE, VERBOSE)` of a node that cannot use them prints `Pre-aggregates: <reason>` (`sift_pawhy_name`, the first failed check in this order: switch off, none stored, arms, grouping sets of the rows (those over a list's elements are answered), joins, combos, post-aggregation, virtual leaves, no aggregate, search, top-N; then the rollup checks of keys, conjuncts and aggregates, or the histogram checks of the rows).
- **Tests** (each shape runs three ways: pre-aggregates, a scan of every unit, the heap; regress unless noted):

  | area | tests |
  |---|---|
  | histograms, cubes | `col_preagg` (every leaf and histogram against direct SQL after build, DML, VACUUM, promote, merge and a parallel REINDEX, on NULLs, `NaN`, `-0`, infinities, int8 near 10^19, negative integers, and invalid options); `agg_preagg` (default, explicit-cube and `preagg = 'off'` indexes; `pre` says all, some or none of the units answered); `pa_*` fixtures of `scripts/coldiff.sh` |
  | min/max, wide keys | `agg_preagg_minmax` (with and without `cube_minmax`, built under minor 6 or given the option after the build), `col_preagg_minmax`, the minor 7 step of the TAP `extension_lifecycle`; `agg_preagg_wide_key` (int8 and numeric group keys and tests) |
  | rows rollup | `agg_preagg_rows` (named and default measures, a dim over the cap, no option, the setting 7), `col_preagg_rows`, the minor 9 step of `extension_lifecycle` |
  | element rollups | `col_preagg_elem` (every histogram and rollup against `jsonb_array_elements` / `jsonb_each` after DML, VACUUM, promote, merge, parallel REINDEX, minor 3, 4 and mixed indexes), `agg_preagg_elem`, `agg_preagg_rollup` (default, explicit-`rollup`, minor 3 and mixed indexes, dims in any subset, filters, min/max, maps), `agg_preagg_elem_parallel` (a Gather over a partial `SiftAgg` on a mixed index), `pa_elem_*` and `pa_roll_*` fixtures, rows AP08 to AP12 |
  | refresh | `col_refresh`, `col_rollup_refresh`, the isolation spec `col_refresh`, the TAPs `col_refresh_crash` (a PANIC before the replacement, a cancel and three `kill -9` inside a refresh), `col_refresh_compat` (an index of the real 3.0.1 library, refreshed at minor 3 and read back by it) and `col_rollup_ondemand` |
  | superunits | `agg_preagg_super` (groups of 2, 4 and 256, no option, built under minor 7, DML, VACUUM, promotion, merge, an option set later, a parallel build), `col_preagg_super` (segment minors, byte for byte damage of the block, verify, merge, delete, VACUUM), the minor 8 step of `extension_lifecycle` |
  | exceptions | `agg_preagg_exceptions` (counts, integer, numeric and float measures, min/max, the guard, DML, VACUUM, merge, REINDEX, minor 14, groups of zero rows), the fuzz family `exceptions`, `col_index_crash`, `col_index_replica`, `compat_matrix` (a minor 19 clone is refused by the releases below) |

- **Not done**: a leader metadata pass that decides workers at run time (the planner decides: a table whose pages are not all-visible when planned gets the parallel path, and exact units are still answered by whoever scans them); a depth-2 cube of the top level; float `min/max` per group; float sums of the rows (the elements' are answered); rollups for a `sift_table` (the writer is shared, the planner callback is not); the planner costs an index of minor 3 segments as if their units were exact.

## 7. On-demand refresh of the rollups (`src/col/rollup.c`)

A rollup is written when a unit is built, so `ALTER INDEX ... SET (rollup_leaves = ...)`, a new dim, a measure the units were written without, or an index built before 3.1 leaves units that a rollup could answer. The index learns from its readers: a tier merge rewrites a segment only when its tier fills and `REINDEX` rewrites everything.

- **Request.** A `SiftAgg` that scans an exact unit only because its rollup is missing, was written with other dims, or lacks a measure the query reads (`Units Without The Rollup`) records the unit's segment, once per segment and generation, in a bounded shared-memory queue (64 segments; a named DSM segment, no `shared_preload_libraries` needed; a full queue drops the request and counts it). `Rollup Refresh Queued` shows how many units of the query asked. The SELECT writes no page and takes no extra lock, so it works in a read-only transaction; a parallel worker asks for the units it scans and the leader adds its counts; a hot standby never queues (`RecoveryInProgress()`). The result does not change: the unit was scanned.
- **Refresh.** A background worker (one per database, started at the first request, launched at most every 2 s, at most 8 databases at a time) rewrites a queued segment as a merge of that segment alone does (`sift_col_merge_segments` with `n = 1`, with the index's current options) under the maintenance lock, replacing the old segment with the atomic directory update of every merge. VACUUM, merges and promotion wait on the maintenance lock as they do for each other; readers and writers do not (a reader keeps the old segment through its directory snapshot). A request for a generation no longer in the directory is skipped and counted. A crash before the replacement leaves the old segment and the orphaned new one for the sweep. The granularity is the segment: a `CREATE INDEX` segment is the whole table of one build worker, and rewriting it also rewrites units that already had their rollup.
- **Not asked for**: dirty units (scanned for another reason, rewritten by the next merge); units of a minor 3 segment (a refresh would write minor 4, which a 3.0 reader refuses); any server with `sift.col_format_minor = 3`; a unit whose explicit rollup exceeded the cap (refreshed segments are remembered by index, generation and options; the memory is lost at a restart, costing one more rewrite per segment); an index with `rollup_on_demand = false`; a session with `sift.col_rollup_on_demand = off`.
- **Switches.** `sift.col_rollup_on_demand` (on, per session), the index option `rollup_on_demand` (true), `sift.col_rollup_worker` (on; off leaves requests queued for `sift.col_rollup_refresh(index)`, which serves them in the calling session and returns the segments rewritten: refresh on a schedule, or in a test).
- **Refresh of every segment on request: `sift.col_refresh(index [, max_segments])`** (returns `refreshed`, `remaining`). `ALTER INDEX ... SET (preagg, rollup, cube_minmax)` changes what units written afterwards hold; the call rewrites the segments written before, without waiting for queries (the on-demand refresh covers rollups only) or a `REINDEX`.
  - A segment is stale when a header-only census of its units' cube and element blobs (`sift_cpagg_census`, no chunk read) finds a dim, a measure set (exactly the named ones with `cube(...)`), a rollup list or rollup dims (exactly the named ones with `rollup(...)`), or min and max that the options and `sift.col_format_minor` do not ask; or when a section no unit holds is one every unit can: a `cube(...)` dim whose values lie in a range, or whose dictionary has the entries, of its group cap, a default dim of at most 32 groups, the dims of `rollup(...)`, the default rollups in a minor 3 segment. A unit over a cap or byte budget has none, as when written, and is no reason to rewrite.
  - Each stale segment is rewritten as the merge of it alone (the on-demand path: same rows, dead ones dropped, same answers) and replaces the old one in one WAL-logged directory change under the maintenance lock (the call holds `RowExclusiveLock` on the index, the lock of an insert). The new segment is written before the old one is released, so a cancel or crash leaves finished segments and the one in hand as they were; calling again does the rest.
  - `max_segments` bounds the rewrites of a call (NULL: all, 0: only count what is stale). A segment of a minor above `sift.col_format_minor` is left as written (a rewrite would drop what only that minor holds: at 3 the index stays what 3.0 reads, and a 3.0.1 library verifies and answers from the refreshed segments); a rewritten one is written at the setting. The old segments' space is reused after `VACUUM`, so the file holds up to one more copy of the index until then. A server remembers the segments it refreshed and does not rewrite them again for a unit that cannot hold a section. Not on a hot standby (it replays the rewritten segments).
- **What the planner counts.** The memoized segment statistics (read again when a segment, `preagg`, `rollup_leaves`, `cube_minmax` or `sift.col_format_minor` changes) also count the units that hold each dim's histogram, with min and max, and each list's rollup; `SiftAgg` is priced for pre-aggregate answers on that share of units (`preagg_share`), so a half-refreshed index costs between the two, and a dim no unit holds (a column with too many values) no longer counts as answered from the cube.
- **Observation.** `sift.col_rollup_state` (requested, refreshed, skipped, dropped, queued, refreshing: server counters since start) and `sift.col_rollup_requests` (index, segment generation, units that asked, time, state `queued` or `refreshing`); the objects are the last block of `sql/pg_sift--3.1.sql` and `sql/pg_sift--3.0--3.1.sql`.
- **Not done**: a miss counter that advises which dims to name in `rollup_leaves`; requests ask for what the option says.

**Measured**: adding min and max to every segment of a 200,000-row index takes 81 ms (23 ms at 25,000 rows; time grows with the rows rewritten) and the `GROUP BY` with `min`/`max` goes from 3.3 ms scanned to 0.24 ms from the groups; after `ALTER INDEX ... SET (rollup_leaves = 'items[].kind, items[].flag')` on 1,000,000 documents (276 units) the next query answers all units from the rollups 1.9 to 3.1 s after the `ALTER` (a transaction that began before the replacement keeps scanning: the new segment is newer than its snapshot).

## 8. Not done, risks

- Not supported: time buckets derived inside the writer (`date_trunc` of a timestamp leaf), because `timestamptz` buckets depend on the session `TimeZone` and month arithmetic is the host's. A leaf that holds the expression is the dimension; for time-clustered tables the zone cover test already answers units inside one month from the unit sum.
- The top-level cube has depth 1 (one dimension per histogram) and, unless the index has `cube_minmax`, no per-group `min/max`; lists have the full rollup (section 2). No float variance, no sketches. A dimension over the cap in a unit has no histogram there, silently: the consumer scans that unit (`unit_hist` false).
- Element rollups are not written by a minor 3 writer and are refused by a 3.0.x reader like every minor 4 segment.
- **Numeric measures of a jsonb path.** A rollup measure is a `smallint`, `integer`, `bigint`, `numeric(p,s)`, `real` or `double precision` leaf. A jsonb path has no numeric type (`paths` types are `bool, int2, int4, int8, float4, float8, text, astext`), so `sum(e.amount)` of `jsonb_to_recordset(...) e(amount numeric)` is scanned, and a jsonb amount is a measure only as `int*` or `float8`. Why: PostgreSQL's `numeric` sum has the display scale of the widest input (`1.5 + 2.5 = 4.0`, `1.50 + 2.50 = 4.00`) while jsonb keeps each value's scale as written (the shredder renders numbers with `numeric_normalize`); scaled integers of one declared scale `s` would print `4.00` for a group written with one decimal, and the pushdown must return exactly what the normal plan returns. Two designs, neither implemented (each adds a path type to the shredder, the planner and `col_verify`): (a) a declared `numeric(p,s)` path type whose slot holds the value times `10^s` only when the jsonb value has exactly `s` fractional digits (any other value is an overflow slot that scans; no format change beyond minor 4); (b) each value's own `dscale` (a nibble per slot) and the maximum per rollup tuple (a chunk flag, a rollup field and a verify rule: a minor 5).
- Churn: a unit with a dead row (liveness bit cleared by VACUUM) answers from its rows, or for counts and integer sums net of the dead rows when its pages are all-visible (section 12); a page that is not all-visible, a minimum, a maximum, a distinct count or a float sum are read from the rows; correctness never changes. The way back needs no `REINDEX`: `VACUUM` rewrites a lone segment more than `dead_rewrite_pct` percent dead (`sift_col` option, default 10; 100 never; the BM25 index keeps 50), `sift.col_promote` and the worker store the rows written since, and the tier merge rewrites the rest (`col_preagg_maint`). Below the threshold a segment keeps its dirty units on purpose: uniform deletes dirty every unit at 0.1% dead, and each rewrite costs the whole segment. PostgreSQL's `VACUUM` skips the index and leaves pages not all-visible when few tuples are dead (`INDEX_CLEANUP auto`, under 2% of the pages): units come back at the first `VACUUM` that vacuums the index, as for index-only scans. A `sift_table` has no pre-aggregates yet, so none of this applies to it.
- Dictionaries are per segment and parallel builds have one per participant: translation by value is per segment, not per unit.

## 10. Superunits: one summary for a group of units (column format minor 12, reloption `superunit`, default 16)

Answering from the pre-aggregates costs a read, a decode and a fold per exact unit (5 to 16 microseconds), so a whole-table aggregate is linear in the number of units (sections 6 and 9). A **superunit** is `superunit` (2 to 256) consecutive units of a segment with the merged sums and cube of the group stored once more, so the group is one read, decode and fold. The reloption `superunit` (default 16 since 3.3; 0: none, the old bytes) makes segments written from now on column format minor 12 (under `sift.col_format_minor = 12`, the default; 10 and below write what they wrote before).

Default 16: +1.0% of the index (200,000 rows, 53 units: whole-table `count`/`sum` 0.26 to 0.37 ms by the units, 0.05 to 0.08 ms by the groups); groups of 64 cost +0.8% but a range predicate cuts them more often into units, groups of 4 to 8 cost +1.3% to +1.5%. Insert, promotion, merge, UPDATE and VACUUM CPU stay inside the noise, and queries a summary cannot answer (a float sum, a filter on a column the cube lacks, `count(DISTINCT)`) stay within 10% of the plan without it. The one cost is a range query on a table of fewer than 16 units (2 units: 0.10 to 0.13-0.15 ms), the exactness test of a group that never forms.

- **What is stored.** After the element cubes and the small-set summaries, in the `COLP` directory of version 4 (`SuperHeader`, `src/col/seg_int.h`): the page mask of every unit (32 bytes: the superblock pages that hold rows, so a unit's exactness is decided without reading its record); for every INT leaf and group the merged `count`, `nnull`, `min`, `max`, exact `sum` and `sum of squares` (64 bytes); per group the merged cube: the dims every unit of the group holds with the same measures, added up by key (text by segment code), counts, non-NULL counts, exact sums and, with `cube_minmax`, the smallest and largest values. Left out: a float measure (its sum depends on the order of additions, which the units fix), a dim with more than 4,096 groups in the group, a leaf with an overflow value in a unit (no summary for that group) and list rollups. Written by `sift_cpagg_super_leaf` / `sift_cpagg_super_cube` from the stored units' bytes and recomputed by `sift.col_verify` with the same functions, so the bytes are checked byte for byte; the reader refuses an unknown flag, a record a group of its size cannot hold, sizes that do not add up, and a directory out of order.
- **When a group answers.** The scan hands over a group of a segment as one unit (`Superunits` in `EXPLAIN ANALYZE`) when every unit of the group passed the zone tests and is exact on its own (section 4, tested per unit in the order `unit_allvisible` then `unit_clean`: all-visible bits, directory, liveness) and the whole group lies inside the scan's range. The consumer applies the same rules as for a unit to the merged zone (a predicate on another column must cover the merged `min..max`) and the merged cube. When it cannot answer (a predicate cuts the group, a measure or dim the merge left out, a float sum), the group is split and its units are read one by one; after four splits and no answer in a scan it stops asking. The three ways (summaries, units, plain plan) return the same rows. `sift.agg_super` (on) turns the use off.
- **Cost, size.** The block is 0.5% to 1.5% of the index (+1.7% with groups of 16 at 200,000 rows of 1,024 rows a unit) and is built from the units' own bytes at the end of the segment (no second pass over the rows). Parallel scans take superblocks in ranges of `superunit * 4 * segments`, so a group fits in a range; a group cut by a range's end is read as units.
- **Scaling.** A scan of the pre-aggregates over N rows is O(units); with groups of F units it is O(units / F) reads, decodes and folds plus, per unit, the exactness test (the all-visible bits of its pages against the stored page mask, one liveness bit), under 0.1 microsecond a unit with groups of 64. Measured with groups of 64 from 25,000 rows (25 units) to 400,000 rows (391 units), PostgreSQL 18.6, 2026-10-22: `GROUP BY` a 20-value key with `count` and `sum` 0.34 -> 0.18 ms and 3.09 -> 0.22 ms; the superunit time stays flat while rows and units grow 16 times, the unit time grows with them (perf/COL-PREAGG-RESULTS.md). A predicate covering part of the table answers the groups inside it and the units at its edges: with groups of 16 a range over 87% of the rows takes 3.51 ms by the units and 0.96 ms by the groups at 400,000 rows; with groups of 64 only groups inside the range count (one a segment), so small groups suit range predicates and large ones whole-table aggregates. A query the summary cannot answer costs the exactness tests of the groups it tries, then is read as units.
- **Not done.** A second level (groups of groups: a tree whose range query combines O(log units) nodes), merged element rollups, merged float measures in an order-independent form, and groups for a `sift_table` (its provider has no sidecar callbacks).

## 9. Acceptance: the ten questions of the store benchmark (10 M rows = 511 units, 1 M = 52)

| q | question | from pre-aggregates |
|---|---|---|
| 01 | one order by id | no (btree) |
| 02 | orders per country | **yes**: histogram of `country`, 30 groups |
| 03 | revenue per month | **yes**: histogram of the `date_trunc('month')` leaf, `sum(amount)`, 36 groups |
| 04 | top 10 categories by revenue | **yes**: histogram of `category`, `sum(amount)`, 40 groups, sorted above |
| 05 | average of shipped, DE, 2025 | no: three predicates on three dimensions, no unit covered (rows are in id order, dates spread) |
| 06 | customers per month | no: `count(DISTINCT)` is exact in PostgreSQL; a per-unit set costs as much as the scan |
| 07 | orders over 100 in 30 days | no: row-level `amount` compare, no unit covered |
| 08 | search count | no (BM25 count) |
| 09 | search facets | no: the BM25 match set is a row subset; partial units cannot use a histogram |
| 10 | search, newest | no |

Questions 02, 03 and 04 are answered entirely from metadata at 10 M (all 511 units, Rows Scanned 0, results identical to the scan and the heap): 8.0 -> 2.1 ms, 35.0 -> 1.4 ms and 46.8 -> 3.6 ms, 6x, 46x and 13x faster than ClickHouse and 2.7x, 160x and 74x faster than Elasticsearch (medians). 05 and 07 stay scans; a depth-2 cube (`country, status, month`: up to 5 400 groups per 19.6 k rows, about 12% of the index) would answer 05 but is not worth it by default. 06 stays distinct-set work.

## 11. Exceptions: a filter `leaf IS NULL` answered from the pre-aggregates (column format minor 19)

A leaf that is NULL in nearly every row (a soft-delete stamp) keeps `WHERE stamp IS NULL` from the pre-aggregates. Without exceptions a unit holding one stamped row fails the zone test and is scanned (166 of 171 units of a 353,029-row table with 655 stamped rows).

- **What is stored.** Per unit, when exactly one top-level INT leaf with no overflowed value has at most 1 row in 100 not NULL (the fewest, the first of equals), the cube and element rollups of those rows, written with the dims and measures of the unit's own cube so groups match by key. The `COLP` directory is version 6: four more entries (directory and bytes of the exception cubes and exception element blobs, laid out like the cubes; an empty blob: the unit has none); the blob starts with the leaf. They are kept when their blobs take at most half of the unit's own, or one byte for each 32 rows of it (a unit cut by the grouping column holds few groups, and its cube is as small as its exceptions). A segment with any exception is column format minor 19. No option: the unit's own data decides; only the `sift_col` writer writes them (a `sift_table` writes none), and `sift.col_format_minor` 14 or less writes none (the default setting, 15, writes them).
- **What a reader does.** `leaf IS NULL` over a unit, as one conjunct among others the pre-aggregates already decide, is the unit's aggregate minus the exceptions' (`sift_col_unit_except`; `hist_subtract` and `rollup_subtract` merge-walk the keys of both); `leaf IS NOT NULL` is the exceptions alone. Counts, integer and numeric sums and averages and the standard deviations subtract exactly. `min` and `max` are not subtractable: the measure is dropped and the unit scanned. A group whose count falls to 0 is kept at 0 (a group of zero rows is not emitted). A float sum is guarded because the difference of two float sums cancels digits: `|result| * 64 < |total|` or not finite scans the unit for that measure (`Units With Exceptions Scanned` in `EXPLAIN ANALYZE`); otherwise it equals the scan within the tolerance float pre-aggregate sums already have.
- **Exactness.** Exceptions follow the unit's own rule: a dirty or not all-visible unit is scanned. A soft delete is an UPDATE and changes nothing stored; the merge or VACUUM rewrites the unit and the writer recomputes its exceptions from the rows (merge, defrag and REINDEX read rows, never sidecars). `sift.col_verify` recomputes the stored exception leaf from the chunks and compares both blobs byte for byte. Missing optional summaries are valid; an older writer may also have selected another qualifying leaf.
- **Upgrade.** A segment of minor 18 or less has no exceptions and reads as before (the unit is scanned for the filter); the merge, VACUUM's rewrite and REINDEX write minor 19. A library that reads up to 18 refuses a minor 19 segment with the REINDEX hint; `SET sift.col_format_minor = 14` and REINDEX writes the old bytes.
- **Measured** (353,029 rows, 171 units, 655 stamped rows, 166 units with exceptions, 3.95 rows a unit): index segment bytes +1.41% (6,381,568 to 6,471,680, no jsonb paths). `SELECT k, count(*), avg(m) WHERE stamp IS NULL GROUP BY k`: units from pre-aggregates 5 to 171 of 171 (166 net of exceptions, 0 scanned), 6.1 to 2.5 ms, rows scanned 342,920 to 0; the same on the index with jsonb paths. A date range together with the filter decides only the units the range covers (2 of 148): the edge units are scanned with or without exceptions.

## 12. Dirty units net of their dead rows (`sift.agg_dead_delta`, no format change)

A VACUUM that removes a row version clears its liveness bit in the unit, and the unit is then not exact (section 4): it was read by its rows however few of them were dead. After a month of churn that is most of
the units of a table (637 of 721 at 3,000,000 rows and 20% a month, with 7.6% of the rows dead), and the pre-aggregates sat unused although every page was all-visible.

- **What a reader does.** When every page the unit has rows on is all-visible (read before the liveness, as `unit_clean` does), the unit has no exceptions, and at most `sift.agg_dead_delta` percent (default 15) of its
  rows are dead, the unit answers from its stored cube and rollups minus its dead rows (`col/delta.c`). The dead rows are the cleared bits of the unit's liveness; the values of the leaves a reader needs are decoded at
  their positions only (`sift_cchunk_decode_sel`), and each dead row is taken off the group of the histogram (the calendar cell of its date for a calendar dim) or the tuple of the rollup its values name, found by binary
  search: the count, the count of non-NULL values and the exact sum of each integer or numeric measure go down. A group whose count falls to 0 stays at 0 and is not emitted, as for the exceptions (section 11).
  The unit's row count for the statement is its live rows.
- **What it gives, what it does not.** Counts, NULL counts, integer and numeric sums, averages and the filters the zone decides (`min`..`max` of the full unit bound the live rows; the NULL counts are net). A minimum, a maximum,
  `count(DISTINCT)`, the sketches and key lists, a float sum (the sum of a unit is added in its own row order, so a float cannot be taken out of it), a rollup of the elements of a list, a unit with exceptions, a superunit, a
  `sift_table` and the write buffer are read from the rows. Whether the aggregates of a statement can use it is decided once (`preagg_dead_ok`): a statement that cannot never reads the dead rows.
- **Cost.** No stored byte, no write, no format change, no option to build. A unit that cannot answer costs its liveness bits (decoded once, as the scan would) and the count of the dead ones; one that does costs the
  decode of the leaves it reads at the dead rows and a binary search for each.
- **Measured**:

  | rows, churn a month | dead rows | grouped sum off, ms (Minstr) | on, ms (Minstr) | units from pre-aggregates |
  |---|---|---|---|---|
  | 300,000, 20% | 5.1% | 5.26 (53.0) | 4.84 (40.3) | 8 to 89 of 108 |
  | 3,000,000, 20% | 7.6% | 21.6 (336) | 15.0 (160) | 84 to 721 of 721 |
  | 10,000,000, 5% | 7.3% | 69.2 (1,078) | 42.8 (439) | 17 to 1,052 of 1,052 |
  | 10,000,000, 20% | 11.8% | 104.1 (1,214) | 80.2 (821) | 99 to 2,130 of 2,412 |

  The clean state of the same rows answers in 0.05 to 0.13 ms. A statement the setting cannot help (a key lookup, a float sum, the newest rows) runs the same instructions
  within 1.3% (300,000 rows, key lookup: 25.98 to 26.31 million) or exactly (float sum: 49.78 million both ways); its time differences are the spread of the machine.
- **Default.** A dead row costs about 500 instructions here (10,000,000 rows: 771 million for 1.44 million dead rows and 2,415 units) and a row of the scan about 100, so a unit with more than about 17% of its
  rows dead is cheaper to scan. The default 15 stays under that: at 10,000,000 rows and 20% a month the 282 units over it are scanned, and the other 2,130 answer from the cube. A lone segment is rewritten by `VACUUM` above `dead_rewrite_pct` (10) before it gets there.
- **Exactness.** The answer of every statement is the same with the setting on, off (`sift.agg_dead_delta = 0`) and with the pushdown off; `col_dead_delta` and the `dead` family of `diff_fuzz` (NULLs, numeric, int8,
  calendar groups, rollups, ranges of ctid, deletes, updates, VACUUM, promotions and merges) check it. A float sum keeps the policy it had: nothing is reordered.

## 13. Dirty units with pre-aggregates net of their dead rows (VACUUM writes them; `SIFT_SEG_NET`, no format minor)

Section 12 takes the dead rows off a unit's stored aggregates one by one, and gives counts and integer sums only; a minimum, a maximum, a float sum, a rollup of the elements of a list and a unit with more than
`sift.agg_dead_delta` percent dead rows were read by their rows, and the churn of a few percent a round put a dead row in every unit within rounds (14 to 16 times the read of a rewritten segment after 8 rounds of 4%).
The segment is not rewritten for that any more: the VACUUM that cleared the bits writes, after the segment's stream, the cube, the rollups and the sums of each dirty unit **computed over its live rows**
(`col/seg/net.c`, `col/maint/net.c`; the layout is in docs/FORMATS.md section 5).

- **What a reader does.** Under the conditions of section 12 (every page of the unit all-visible, read before the liveness) a unit whose dead rows are exactly the ones its sidecar was made for (liveness bits are only
  cleared, so the same number is the same rows) answers from the sidecar as a clean unit does from its own: the same code, with the blobs of the sidecar and the live row count. Any aggregate the cube gives is
  exact, a minimum, a maximum and a float sum (added in the order of the live rows, as a rewrite would) included. A unit with other dead rows, or without a sidecar, goes on to section 12.
- **What it does not give.** Everything that is a list of the values of the unit rather than a summary of it: `count(DISTINCT)` of a key, the sketches (`approx_count_distinct`), the key lists and presence sets, sorted runs, point directories
  and superunits still describe the dead rows too; a query that needs them reads the unit by its rows, as before. A unit with exceptions, a leaf with overflow values, an element leaf (rollups of lists), an inline
  string leaf has no sidecar (the pass notes its dead rows and does not try it again) and keeps section 12.
- **What writes it.** VACUUM, before the merges it decides (`vacuum_net`): a segment with a unit whose dead rows the sidecar does not know gets a new one, one pass over the leaves of its dirty units, the cost of a
  verification of them; the segment keeps its generation, a new header page lists the extents of the stream and of the new sidecar, and the old header and sidecar pages are released with the directory switch (the
  relocation of a segment does the same). A crash after the pages and before the switch leaves unreferenced pages, which the orphan sweep returns.
- **What it changes for the rewrite.** The units a sidecar answers for count as clean in the rule of the automatic rewrite (`sift.col_auto_merge_dirty_pct`) and in the cost of `dead_rewrite_pct = 0`; a segment still is rewritten when half of it is dead, when its
  units are fragmented, when the write buffer is large, and by the merge tiers. A rewrite or a merge writes no sidecar (its output has no dead rows) and drops the one of its inputs with their pages.
- **Cost.** One sidecar is about the cube and rollups of the dirty units (a share of the segment's pre-aggregates section) and the sums of each leaf per unit (80 bytes); `sift.col_sections` shows it as `net`. It is written again whole
  by every VACUUM that clears more bits. `sift.col_verify` rebuilds each unit's blobs from its live rows and compares the bytes.
- **Measured** (8 rounds of 4% churn, `bench/churn/waste.sh` lane, promote and VACUUM each round). Maintenance WAL at 100,000 rows: 9.5 MB with sidecars, 5.7 MB leaving units dirty, 15.6 MB rewriting every round; the sidecar pass writes 3.9 MB where the rewrite writes 9.9 (-61%), of totals that include 2.3 MB of the heap's VACUUM (-39%); at 1,000,000 rows 53.8, 49.7 and 206.9 MB.
  Reads (instructions of one query, sidecar vs delta alone vs the same index rewritten): grouped sum 1.51 / 4.60 / 0.71 M at 100,000 rows, 16.0 / 46.2 / 10.0 M at 1,000,000; minimum and maximum 1.57 / 2.80 / 0.80 M and 16.1 / 32.1 / 10.4 M. The sidecar does not reach the rewritten index: the dirty index keeps several segments (3 against 1) with the blocks and instructions that go with them, which only a merge returns. Shapes the sidecar does not cover (distinct count, sketches, key lists, point directories) read what the delta alone reads.
  `sift.agg_dead_delta` stays for the units a sidecar cannot cover (overflow values, exceptions, element leaves, inline strings): it is the same dead-row subtraction, and the sidecar replaces it for the units it covers.