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, ORC ColumnStatistics) 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) 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; 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) 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) sift_col_unit_exact + zone cover test
PostgreSQL (xaggr, index-only scans) 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 1018: 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 / 10s 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 1019, 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.