# pg_sift usage Reference for everything beyond the [README](../README.md) quick start. The examples use the `docs` table created there. Which of the options to touch, and which to leave alone: [TUNING.md](TUNING.md). ## Install Requires PostgreSQL 17, 18 or 19, server headers with `pg_config` on `PATH`, and a C compiler. Tested on x86-64 (whole suite), Linux arm64 (PostgreSQL 18; fast tier of a pull request in CI) and macOS arm64 (Homebrew build and test plus a few regression tests, in CI). The column engine's chunk codec links `liblz4` and `libzstd` when their headers are installed (`liblz4-dev`, `libzstd-dev`); `make SIFT_NO_LZ4=1` or `SIFT_NO_ZSTD=1` build without one; the packages depend on both. ```sh make USE_PGXS=1 && sudo make USE_PGXS=1 install # PG_CONFIG=/path/to/pg_config picks another installation ``` - Arch Linux: AUR `pg_sift` (recipe in [packaging/](../packaging/)). - Docker, Docker Compose, CloudNativePG: [CONTAINERS.md](CONTAINERS.md) (the image recipes are in [packaging/docker/](../packaging/docker/)); a five-minute tour: [QUICKSTART.md](QUICKSTART.md). Supported versions: [SUPPORT.md](SUPPORT.md). - Debian 12 and 13, Ubuntu 22.04 and 24.04 with PGDG `postgresql-N` (N is 17, 18 or 19, the major of your server): download the `.deb` from the [latest release](https://github.com/brauliobo/pg_sift/releases/latest) and `sudo apt install ./postgresql-N-pg-sift_4.3.2-1_amd64.deb` (`_arm64.deb` on arm64). - RHEL, Rocky, Alma 9 or 10 with PGDG `postgresqlN-server`: `sudo dnf install https://github.com/brauliobo/pg_sift/releases/download/v4.3.2/pg_sift_N-4.3.2-1.el9.x86_64.rpm` (`aarch64` in the name on arm64). - Amazon Linux 2023 with the distribution's `postgresqlN-server` (N is 17 or 18; it has no 19): the same URL with `pg_sift_N-4.3.2-1.amzn2023.x86_64.rpm` (`aarch64` on arm64). The `el9` package installs there but the server does not find the extension. - arm64 (aarch64) packages run on any aarch64 Linux (AWS Graviton, Ampere, Azure and Google Cloud Arm, Raspberry Pi 4 or 5); a managed service that does not load extensions of its own (Amazon RDS and Aurora) cannot run pg_sift: use a self-managed PostgreSQL. Then, as a superuser: `CREATE EXTENSION pg_sift;` (after installing a newer version: `ALTER EXTENSION pg_sift UPDATE`). `shared_preload_libraries` is not needed for the `sift` index, the `sift_col` index or the `sift_table` access method; only publishing a `sift_table` under `wal_level = logical` needs it ([Replication](#replication)). A backend loads the library when a statement first opens one of its objects. For a heap table with a `sift_col` index that is in `planner()`, after it skipped the planner hook that decides CTE and lateral-join plans. The executor start therefore re-plans that first statement through the hook if it is a plain `SELECT`, an `EXPLAIN` query (it prints the plan the statement gets; `EXPLAIN ANALYZE` runs it) or a `CREATE TABLE AS` query; extended-protocol and `EXECUTE` parameters are planned with their own types and values (a custom plan) and the plan cache is reset. Any other first statement (a cursor, a query inside a function) keeps its plan, the ordinary one for aggregates that need the hook. **Preload the library for any workload of aggregates over `sift_col` indexes**: `ALTER DATABASE mydb SET session_preload_libraries = 'pg_sift'` (new sessions, no restart) or `shared_preload_libraries` (a restart). Without it the first statement of every backend is planned twice, and a pool that gives each request a backend of its own pays that on every request: a two-arm statement of 18 KB (five CTEs, 306,500 rows) took 44.3 million instructions and 29 to 34 million cycles to plan in a new backend with PostgreSQL's plan, 90.9 million and 54 to 68 million with pg_sift not preloaded (the second planning is 30 million of them, the first statement of the whole cluster 162 million), and 60.3 million and 39 to 50 million with it preloaded (what a backend that has planned the statement before pays: 48.1 against 30.8 million). The library cannot say it was loaded late: the check would have to be remembered by the cluster, which a library that is not preloaded has no shared memory to do, and a line in the log of every backend would be the noise of the pool it is for. The install grants `USAGE` on schema `sift` to `PUBLIC` (since 3.3): any role can call `sift.score(ctid)` and the read-only diagnostics; each function still checks the table or index it reads and the extension's tables stay unreadable. `REVOKE USAGE ON SCHEMA sift FROM PUBLIC` takes it back (then `GRANT USAGE ON SCHEMA sift TO` the roles that need it; `@@>` and the access methods need none). `ALTER EXTENSION pg_sift UPDATE` grants it again. ### Install with Homebrew ```sh brew tap brauliobo/pg_sift https://github.com/brauliobo/pg_sift.git brew install brauliobo/pg_sift/pg_sift # the latest release; --HEAD builds main brew test brauliobo/pg_sift/pg_sift ``` The formula builds the latest release (`--HEAD`: `main`), for macOS arm64 and Homebrew PostgreSQL 17 and 18 (no PostgreSQL 19 formula yet), with LZ4 and zstd, and links extension files through Homebrew's versioned directories. CI builds it and runs `brew test` on PostgreSQL 17 and 18 (temporary clusters, BM25 queries, an extension upgrade, a `sift_col` aggregate). For aggregate pushdown in a new session run `LOAD 'pg_sift';` first. Remove with `brew uninstall pg_sift && brew untap brauliobo/pg_sift`; PostgreSQL installations and databases are managed separately. ## Queries `AND`, `OR`, `NOT` are keywords in upper case only; juxtaposition is `AND`; precedence is `NOT` > `AND` > `OR`; a `NOT` needs a positive term in its AND group. A wildcard, regex, fuzzy or prefix leaf expands to dictionary terms and matches every word it matches, however many (`h*`, `*ing`, `/.*tion/` or `~2` on a million names). Above `sift.max_expansions` (512) terms it scores as one term whose documents are those with such a word (for a prefix, the term `prefix_index` would hold), not as the best of its terms. A regex cannot have back-references. ```sql SELECT id FROM docs WHERE body @@> 'battery NOT squeaky'; -- exclusion: 2 SELECT id FROM docs WHERE body @@> '{case clip squeaky}'; -- any of a set of words or patterns SELECT id FROM docs WHERE body @@> '"friendly support"'; -- phrase: 2, 5 SELECT id FROM docs WHERE body @@> 'batt* OR sq?eaky OR /sq.*y/ OR frendly~1'; -- prefix, wildcard, regex, fuzzy SELECT id FROM docs WHERE body @@> '(case OR clip) AND friendly^3'; -- grouping and boost SELECT id FROM docs WHERE body @@> ANY (ARRAY['case', 'clip']); -- OR of several queries, same as 'case OR clip' (scored, counted) ``` ## Ranking, filters and joins `ORDER BY sift.score(ctid) DESC LIMIT k` runs a ranked scan that skips blocks that cannot reach the top k; ties break by ctid. Without a `LIMIT` it ranks every match in one search. A prepared `LIMIT $1 [OFFSET $2]` gets the same scan as a constant, also in a generic plan. It works on materialized views, in `INSERT ... SELECT`, and in a `HAVING` that reads the score of an aggregate. `sift.score()` is computed by the scan: use it in the select list or `ORDER BY` of the query level that scans the table. - In `WHERE` it may only be compared (`>`, `>=`, `<`, `<=`, either side) with a constant, a parameter or a stable expression of them (`BETWEEN` and `NOT (score > c)` qualify after rewriting). The scan tests it (`EXPLAIN` shows `Score Filter`; several cutoffs must all hold). A cutoff from below keeps the `LIMIT` hint; one from above reads every match. - Any other use in `WHERE` (`OR`, `=`, `<>`, an expression of the score, `sift.max_score()`) is an error: filter on the score from a subquery. - `ORDER BY name, sift.score(ctid) DESC LIMIT k` (columns of the scanned table first, then the score) scores only the rows tied with the k-th best `name` and gives the answer of ranking all matches and sorting after; `EXPLAIN` shows `Order: name, score DESC` and `Key Passes`. - `UPDATE`, `DELETE`, `MERGE` and `FOR UPDATE` re-check rows, which a scan cannot: a subquery, view or `WITH` query of such a statement that reads the score is planned apart (as with `OFFSET 0` or `MATERIALIZED`) and the statement uses its rows by key (`UPDATE t SET s = q.s FROM (SELECT id, sift.score(ctid) AS s FROM docs WHERE body @@> 'case') q WHERE t.id = q.id`). A score read straight from a table the statement joins is an error (planned, with a hint). ```sql SELECT id, sift.score(ctid) FROM docs WHERE body @@> 'friendly OR case' AND sift.score(ctid) > 0.5 ORDER BY sift.score(ctid) DESC LIMIT 100; -- a minimum score SELECT id, s FROM (SELECT id, sift.score(ctid) AS s FROM docs WHERE body @@> 'friendly OR case' ORDER BY s DESC LIMIT 100) top WHERE s > 0.5; -- filter on the score of the top 100 SELECT d.id, c.name FROM docs d JOIN (VALUES (1, 'audio')) c(id, name) ON c.id = d.category WHERE d.body @@> 'friendly' ORDER BY sift.score(d.ctid) DESC LIMIT 10; -- joins work too ``` ### Row-level security A role under a row-level security policy gets no ranked scan by default: the match is not leakproof and the index statistics (document count, document frequencies, field lengths, the vocabulary a wildcard expands over) include the rows the policy hides. `sift.rls_scores = on` (superuser setting: `ALTER ROLE app SET sift.rls_scores = on`, or `GRANT SET ON PARAMETER` to a role that may change it) lets the ranked scan serve such a role: ```sql ALTER TABLE docs ENABLE ROW LEVEL SECURITY; CREATE POLICY own ON docs USING (tenant = current_user); SET sift.rls_scores = on; -- as a superuser, or per role with ALTER ROLE ... SET SET ROLE app; SELECT id, sift.score(ctid) FROM docs WHERE body @@> 'friendly' ORDER BY sift.score(ctid) DESC LIMIT 10; ``` The policy's quals run on every row the ranker offers before any qual of the query (so a query expression that raises for a row never sees a hidden one); the ranker is asked for more rows until `k` pass, and no hidden row is returned. Policies that call functions or read other tables work. - `EXPLAIN` does not estimate matches from the index (also on a partition or inheritance child) and `EXPLAIN ANALYZE` does not print its counters (`Candidates`, `Superblocks`, `Heap Checks`, ...), as they count hidden rows; the estimates are the same whatever the hidden rows hold. - `BUFFERS`, `pg_statio_*` and the time still show the pages read, which grow with the hidden rows offered before `k` visible ones passed (a sequential scan shows them in `Rows Removed by Filter`). - Without a ranking (`WHERE body @@> ...` with no score), `count(*)`, `sift.facets` and the column index behave as before: a sequential scan, no pushdown. `sift.max_score()` stays an error (it covers hidden rows). - A table owner and a role with `BYPASSRLS` are not under the policy and score as always; `FORCE ROW LEVEL SECURITY` puts the owner under it. **The caveat.** A visible row's score comes from the statistics of the whole index, as the owner sees it, so it depends on hidden rows (a term they repeat weighs less; adding one changes scores). No hidden row is returned or counted, but scores leak corpus information (a row of known length and term counts fixes each term's weight, hence how many documents hold it): document count, average length, how common a term is, whether a wildcard expands past `sift.max_expansions`. A role that can insert documents can probe any word; time and pages read (`BUFFERS`, `pg_statio_*`) are side channels as for any query. Enable it for roles whose tenants may see that much (a shared corpus with per-user visibility); where tenants' documents must be unobservable by each other, index each tenant separately (a partition per tenant, a sift index on each) or leave it off. The setting is read at planning: a prepared statement planned with it on keeps its plan for the session. ### Filter pushdown With `sift.filter_pushdown` (`on` by default) the planner builds a bitmap of the other quals from their indexes (btree, BRIN, GIN) and gives it to the ranked scan, which scores only matches that pass; results equal the post-filter plan. A 0.1% filter on 218,058 articles went from 23.6 ms to 0.22 ms (clustered) and 15.0 ms to 0.86 ms (scattered). `force` pushes down whenever a bitmap path exists; `on` costs both plans. - The post-filter plan is priced from the filter column's statistics: the ranker verifies matches in heap order until `k` pass, so a filter at the end of a correlated column (recent ids) costs the whole heap before it. Both plans are charged for the blocks the ranker scores; an id list (`= ANY(array)`) is priced by the btree leaf pages it touches, so a long list of scattered ids is pushed down. - A generic plan (prepared statement, `plan_cache_mode`, a preparing driver) is planned without its query, so it keeps the bitmap as a child the scan builds only when the bound query matches enough documents (`Filter Mask From Matches` in `EXPLAIN VERBOSE`; a filter keeping a tenth of the rows or more on the benchmark corpus never gets one). Rare queries check the filter after the ranker, common ones use the mask (prepared 2.3% filter, common words: 2.72M to 1.51M cycles; rare: 772k to 658k). - A value stored in one stretch of the heap (`country_code = 'US'` on rows loaded by country) is placed from the btree at planning time (a key's entries are in heap order) and pushed down when the stretch is late: the filtered one-letter prefix of the places went from 8.74M to 1.26M user cycles. A stretch at the start of the heap keeps the filter after the ranker. ```sql CREATE INDEX docs_published ON docs (published); SET sift.filter_pushdown = force; EXPLAIN (analyze, costs off, timing off, summary off, buffers off) SELECT id, sift.score(ctid) FROM docs WHERE body @@> 'friendly' AND published >= '2026-02-01' ORDER BY sift.score(ctid) DESC LIMIT 10; -- Bitmap Index Scan child; Filter Superblocks / Pages Exact / Pages Lossy RESET sift.filter_pushdown; ``` ### INCLUDE columns: tests and small joins inside the ranked scan `filter_pushdown` finds the rows that pass a qual through another index. A column that a search filters on all the time can instead live in the sift index, as `INCLUDE`; the ranker then drops a document that fails the test before it scores it, and the scan stops after `LIMIT` passing documents rather than after the many matches of a common term: ```sql CREATE INDEX docs_fts ON docs USING sift (body) INCLUDE (category_id, created_at); SELECT id, sift.score(ctid) FROM docs WHERE body @@> 'common OR terms' AND created_at >= '2026-09-26' ORDER BY sift.score(ctid) DESC LIMIT 10; -- Include Tests: 1 SELECT d.id FROM docs d JOIN categories c ON c.id = d.category_id WHERE d.body @@> 'common OR terms' AND c.region = 3 ORDER BY sift.score(d.ctid) DESC LIMIT 10; -- Include Tests: 1 (the keys of the rows of region 3) ``` Nothing is stored unless `INCLUDE` names the column. The index keeps the value of each document in the segment (up to 8 columns; `boolean`, `smallint`, `integer`, `bigint`, `date`, `timestamp` and `timestamptz`; another type keeps nothing and its tests stay on the heap), in the width the range of the values of a superblock needs: the lab corpus of 1,000,000 articles (a date of 300 days, a category of 1,000, a counter of 1,000,000 values) grows by 1.2, 1.3 and 1.3 bytes a document for the three columns (0.54% and 0.60% of a 212.6 MB index each), 2.4 bytes for the date and the category, 3.7 for the three. The records of a column are together in the segment, so a statement reads the pages of the columns it tests, and `EXPLAIN` says `Include Tests: n`. `INCLUDE` needs a heap table (the values are read from its tuples): `CREATE INDEX` on a `sift_table` refuses it, the table or not. **What is tested before scoring:** `col = c`, `<>`, `<`, `<=`, `>`, `>=` (either side; a cross-type integer comparison too), `col IN (c, ...)` and `= ANY` of a constant array of up to 1,024 values, `col IS NULL`, `IS NOT NULL`, and a boolean column or its `NOT`; a comparison is false of NULL. And the join of the scan with a table of at most 65,536 estimated rows that has quals of its own, by an equality between its column and an INCLUDE column, when the query has no outer join: the table is read at the start of the scan and the keys of its rows that pass become one more `IN` test (the join stays, and checks every row as before). **Superblocks outside the range:** the record of a column of a superblock begins with the smallest and the largest value it holds, and a superblock where no value passes the tests is not read at all (no term, no block). On a table whose column follows the heap order (a creation date of rows appended over time) a search of the last 10% of the dates reads the superblocks of those rows only: over 1,000,000 documents a statement runs 17.6 M instructions and 1.67 ms without the check, 4.7 M and 0.68 ms with it (100,000 documents: 3.2 M to 1.7 M, 300,000: 6.8 M to 2.5 M). A table whose values are spread over every superblock reads them all as before for 0.2% more instructions (0.6% over 100,000 documents; the ranker stops asking after 32 superblocks of which none was dropped). A superblock that holds a document whose heap tuple was gone when its segment was written is read whatever its range, and a segment written by an earlier version has no range: a merge or a `REINDEX` writes it. **What stays as it is, and why:** every qual stays in the plan and is checked on the heap row, so the answer is the post-filter plan's (a document whose heap tuple was gone when its segment was written, and a document of the write buffer, pass every test and are checked there). `OR`, an expression of the column, `NOT IN`, a function, a column that is not in `INCLUDE`, a join with an outer join or a table over 65,536 rows are checked after the ranker as without the columns. An `UPDATE` of an INCLUDE column is not a HOT update (the column is an indexed column of the table, and the new row version gets its own entry in the index). A segment is written with the values the heap held at that time; a merge and a VACUUM that rewrite a segment read them from the heap again, a `REINDEX` of an index of an older version writes them. ## Analyzers, phrases, several columns Text is split into words, case folded, accent folded (`résumé` is `resume`) and optionally stemmed: `stemmer = 'en'` is the built-in Snowball English (no stop words), and any PostgreSQL text search configuration gives the stems and the stop words of its language ([Other languages](#other-languages)). The analyzer is fixed per index: build the replacement with `CREATE INDEX CONCURRENTLY` and swap. With `positions = on` (default) the index stores word positions and decides phrases itself; `positions = off` gives a much smaller index that rechecks phrases in the heap. An index has one key, a `text` column or an immutable expression: concatenate columns (`coalesce` nullable ones; `NULL` keys are not indexed). ```sql SELECT token, pos FROM sift.tokenize('Running Cafés, RÉSUMÉ', stemmer => 'en'); -- run, cafe, resum CREATE INDEX CONCURRENTLY docs_body_en ON docs USING sift (body) WITH (stemmer = 'en'); DROP INDEX docs_body; ALTER INDEX docs_body_en RENAME TO docs_body; SELECT id FROM docs WHERE body @@> 'run'; -- 4: runners, running, runs CREATE INDEX docs_title_body ON docs USING sift ((coalesce(title, '') || ' ' || coalesce(body, ''))); -- query with the same expression ``` ### Other languages `stemmer` also takes the name of a PostgreSQL text search configuration: the 29 Snowball languages of the server (`german`, `french`, `spanish`, `russian`, ...: `SELECT cfgname FROM pg_ts_config`) or one you made, with synonym, thesaurus, ispell or unaccent dictionaries chained in front of the stemmer. The words are the ones `to_tsvector` indexes: our tokenizer finds the words, the dictionaries the configuration maps to word tokens decide the terms (a word in one pass, as `to_tsvector` stems it; a stop word is dropped; the first lexeme when a dictionary answers several), and queries use the same configuration. A stop word keeps its position, so `"hund und katze"` is two apart as `phraseto_tsquery` has it (the canonical text of `sift.parse_query` shows the terms without the gap). Positions, phrases, prefixes, `INCLUDE` columns, merges, parallel builds and `positions` healing work as for any index. ```sql CREATE INDEX docs_body_de ON docs USING sift (body) WITH (stemmer = 'german'); SELECT id FROM docs WHERE body @@> 'Häuser'; -- Haus, Häuser SELECT id FROM docs WHERE body @@> '"Hund und Katze"'; -- the phrase with the stop word between CREATE TEXT SEARCH CONFIGURATION public.my_de (COPY = pg_catalog.german); -- your own: name it with its schema CREATE INDEX docs_body_my ON docs USING sift (body) WITH (stemmer = 'public.my_de'); ``` - A name without a schema is a `pg_catalog` configuration (`CREATE INDEX` runs with a restricted `search_path`): write other configurations as `schema.name`. `english` is the built-in `en`; PostgreSQL's English configuration, with its stop words, is `pg_catalog.english`. - Accent folding is off for such an index (the configuration's own dictionaries decide what is folded, as for `to_tsvector`); `accent_folding = true` is honored, and then the dictionaries see the folded word: on 400 words of generic corpora it changes the matches of 0 of 400 queries in German, 106 in French (-6.5% matches), 89 in Spanish (-8.2%) and 2 in Russian, for an index 3.3%, 3.3%, 0.6% and -0.2% larger. - The index keeps the configuration name in its metapage; `sift.verify` names it when the option no longer matches. Like an expression index on `to_tsvector`, the index is not rebuilt when the configuration or its dictionary files change (`ALTER TEXT SEARCH CONFIGURATION`, a new stop list): `REINDEX` after that. - Memory: a build or merge process keeps a memo of the words its dictionaries answered, about 24 bytes a distinct word (37,600 German words: 0.9 MB; 476,000 English words would need 8.4 MB, a little over the cap: +2.8% build instructions against an unbounded memo), at most 8 MB of entries and 4 MB of table; a parallel build holds one per participant (8 MB x (workers + 1) at most) and starts a dictionary once per process (1.1 ms). A backend that inserts or queries keeps the memo between statements, for its 4 most recent configurations, at most 2 MB of entries and about 1 MB of table each (about 12 MB in all); a change to a configuration, a dictionary, a parser or a schema name drops it. - Cost and size, per language: [stemmer](OPTIONS.md#siftstemmer). Russian queries read more with the stemmer (or 2.27 to 2.85 M, and 0.97 to 1.47 M, phrase 0.91 to 1.32 M instructions): stemming merges word forms into longer posting lists, which is more recall. - An index with a configuration as its `stemmer` is on-disk format 1.5 (a 3.7 library refuses it with that format; `REINDEX` with `stemmer = 'none'` there, or install 4.0). For autocomplete and weighted fields, build the key from sections with `sift.sections(a, b, ...)` (joined with `chr(29)`; a NULL section is empty), name the leading ones with `fields` and set `prefix_index = n` (up to 15) so prefixes of up to n letters are one term lookup. It trades index size for speed and is optional: without it a broad prefix such as `h*` is read from the postings of all its words, once per backend and then from a cache (`sift.prefix_cache_size`): 10-15 ms for a one-letter prefix of a million names, under a millisecond with the option, which costs index size (a million names: 17 MB, 34 MB at 3, 73 MB at 15): ```sql CREATE TABLE places (id int PRIMARY KEY, name text, admin1 text, country text); CREATE INDEX places_sift ON places USING sift (sift.sections(name, admin1 || ' ' || country)) WITH (fields = 'name', prefix_index = 3); SELECT id FROM places WHERE sift.sections(name, admin1 || ' ' || country) @@> '(gr* ho*) AND (name:{gr* ho*}^3 OR (gr* ho*))' ORDER BY sift.score(ctid) DESC LIMIT 20; -- every word a prefix, name matches boosted x3 ``` - `name:word`, `name:(a b)` and `name:{a b}` need the field in the index; a name it lacks is an error (`42601`, listing the fields), so a query on one key means the same on every plan. Without `fields` (or with no sift index) a colon is text: `name:word` is the word `name:word`, `name:(a b)` the words `name:a` and `name:b`. - With several sift indexes on one key, `@@>` means the default analyzer when one of them has it, else that of the lowest OID. An index that answers differently (one with `fields` next to a plain one) does not serve it; `sift.match_idx(key, query, index)` names the analyzer. Partitions bind `@@>` to their own indexes. ### Field weights `sift.field_query(query, fields [, index])` searches each word, prefix, wildcard and fuzzy term in every listed field, with the field's weight times the term's boost. A row scores the sum of its fields' scores (per-field BM25, each with its own `idf` and `tf` and the length of the whole key); matching rows are those of the unweighted query on those fields. A weight is above 0 and up to 10000, and no weight is 1. With `index` the field names are checked against its `fields`. Phrases, regular expressions and the terms of a `NOT` are not scoped to a field; there is no BM25F, no analyzer per field and no highlight per field ([FIELDS-FACETS.md](FIELDS-FACETS.md)). ```sql CREATE INDEX items_sift ON items USING sift (sift.sections(title, body, tags)) WITH (fields = 'title,body,tags'); SELECT id FROM items WHERE sift.sections(title, body, tags) @@> sift.field_query('wireless headphones', 'title^3, body, tags^0.5', 'items_sift') ORDER BY sift.score(ctid) DESC LIMIT 10; ``` ## Partitioned tables Declarative partitioning (also multi-level) and inheritance parents work. `CREATE INDEX ... USING sift` on the parent builds one sift index per leaf partition, also for partitions attached later. Matching and `count(*)` span partitions; ranking works through the parent: each partition runs its own ranked scan and a MergeAppend returns the global top-k. ```sql CREATE TABLE logs (id int, region text, msg text) PARTITION BY LIST (region); CREATE TABLE logs_eu PARTITION OF logs FOR VALUES IN ('eu'); CREATE TABLE logs_us PARTITION OF logs FOR VALUES IN ('us'); INSERT INTO logs SELECT i, CASE WHEN i % 2 = 0 THEN 'eu' ELSE 'us' END, 'disk full on node ' || i % 10 FROM generate_series(1, 1000) i; CREATE INDEX logs_msg ON logs USING sift (msg); SELECT region, count(*) FROM logs WHERE msg @@> 'disk "node 7"' GROUP BY region; SELECT id, region, sift.score(ctid) FROM logs WHERE msg @@> 'disk "node 7"' ORDER BY sift.score(ctid) DESC LIMIT 3; ``` ## Facets `sift.facet_counts(table, key, query, facets [, filter [, hits]])` returns the counts of several facets of one search in one call: terms with a top N and the count of the other values, range, histogram and date buckets, on columns of any type (also arrays), with multi-select (a facet ignores its own selection). Every count is exact, the `count(*)` of the same snapshot. Design, options and limits: [FIELDS-FACETS.md](FIELDS-FACETS.md). ```sql SELECT * FROM sift.facet_counts('items', 'title', 'wireless', '[{"expr":"brand","size":10,"missing":true}, {"expr":"price","edges":[100,300]}, {"expr":"added","unit":"month","fill":true}, {"expr":"tags","array":true}]', 'active', hits => true); -- facet, kind, value, lo, hi, count SELECT * FROM sift.facet_counts('items', 'title', 'wireless', '[{"expr":"brand","selected":["acme"]}, "category"]'); -- category counts only brand acme; brand counts all brands ``` `kind` is `value` (terms), `bucket` (range, histogram, date), `other` (`lo` values past `size`, `count` their sum), `missing` (NULL) or `hits` (rows left after every selection). `sift.facet_sql(...)` returns the statement `facet_counts` runs, for `EXPLAIN`; it runs as the calling role, so permissions and row-level security apply. With a `sift_col` index on the columns each facet is a `Custom Scan (SiftAgg)` with the search as the row selection (a million rows, four facets: 3-13 ms against 140-310 ms for the ordinary plan on broad queries); an array facet or a filter on a column outside the index runs as the ordinary plan, with the same counts. ### Keyword facets `analyzer = keyword` indexes each whole value as one term, so `sift.facets(index, facet_index, query, lim, nulls)` counts a query's matches per value of another column from the bitmaps, equal to the same snapshot's `GROUP BY`. It pays for broad queries on low-cardinality columns (1.9-4.5x on 26 values); selective queries or thousands of values favor `GROUP BY`. A value over 255 bytes is indexed as its first bytes plus a hash of all of it, so `@@>` still finds exactly the rows with that value (`sift.facets` shows that start and the hash). ```sql CREATE INDEX docs_category ON docs USING sift ((category::text)) WITH (analyzer = keyword); -- text keys: cast integers SELECT * FROM sift.facets('docs_body', 'docs_category', 'friendly OR case', 10); -- value, count: 1 | 3 ``` ## Parallel scan `sift.parallel_scan` (`off` by default) lets the planner add a Gather over a parallel SiftScan or SiftCount; a ranked one needs a `LIMIT` and a single table, and rows, scores and ties equal the serial plan. It is off because a Gather costs milliseconds before any work: on 218,058 articles only an unordered scan with a heavy residual qual gained (1.8x). Try it on much larger tables or with expensive quals. ```sql SET sift.parallel_scan = on; EXPLAIN (costs off) SELECT count(*) FROM docs WHERE body @@> 'friendly'; -- Gather over a parallel SiftCount when it pays RESET sift.parallel_scan; ``` ## Maintenance and operations Writes go to small write buffers through generic WAL; a background worker (`sift.async_maintenance`) turns them into immutable segments and merges; VACUUM promotes, merges and clears dead postings. Disk, memory and time of a large index: [SIZING.md](SIZING.md). - `sift.workers` lists every readable `sift` and `sift_col` index and every readable `sift_table` (`index` holds the table, `kind` is `sift_table`; its background keeps no note of a failure, so the columns that note one are `0` and NULL) with its directory entries, write buffers awaiting promotion, whether maintenance runs and, after a failed background step, `failures` in a row, `failed_at`, `next_retry` (the delay doubles from `sift.maintenance_naptime` up to an hour), the SQLSTATE and `last_error`; the next success clears them. For a `sift_col` index `due` is the answer of `sift.col_maintenance_due`; a failing `sift_col` worker records its error the same way and new-worker requests wait out the same delay. - The write buffer of a `sift_col` index is read by every backend that scans the index: its first statement decodes the rows of the buffer (5,900 instructions a row, 115 M for 19,619 rows; the batches are kept for the backend's next statements). The server adds up the rows backends decoded for the open buffer, and once they cost half of its promotion (18,600 instructions a row, once, and about 36 bytes of WAL a row) the backend that crossed it asks the worker, which seals and promotes the buffer: two backends that read all of a buffer of 19,619 rows are enough, and a buffer of fewer than a quarter of a unit of rows (18,624) is left for `write_buffer_limit`, a VACUUM or `sift.col_promote`. It needs `sift.col_auto_merge` and `sift.async_maintenance`, as the other requests of the reads do, and runs for a table that opted out of autovacuum too (it asks for no VACUUM). A buffer that is only written has no reads and is promoted at its limit as before. The first statement of a new backend over one entity of a 600,000-row index with 19,619 rows in the buffer takes 131.9 M instructions (14.8 ms) with the buffer and 17.3 M (3.9 ms) once promoted; the second statement is the same either way (3.7 M and 3.8 M, one more segment to list). - With `sift.col_wbuf_ref` on, the write buffer of a `sift_col` index of a heap table that tracks changes holds 16 bytes a row (the address of the heap tuple and the transaction that inserted it) and the values are in the shared arena of the server, filled by the backend that inserted the row, so that the pages written for a batch of rows are a third to a half and its WAL a fifth (500,000-row index, batches of 2,000 rows: 100 to 32 B a row of index pages, 76 to 15.5 B of index WAL; the free end of the index that VACUUM tries to cut is 5.0 MB against 0.6 MB, so a VACUUM with a reader holding the index does not wait 5 s for the lock of the cut). The arena is made of 1 MB segments of dynamic shared memory that are made when the buffers need them and stay until the server stops, 128 MB at most for the server (`dynamic_shared_memory_type` and the size of `/dev/shm` have to allow it; a shortage is the ordinary out-of-shared-memory error of the statement), and holds at most 256 buffers at once: a buffer takes the pages it needs (88 bytes a row of a table of 13 columns, 64 kB for 500 rows, 4.7 MB for the 47,000 rows of a full buffer) and is full at `write_buffer_limit` bytes of values, so a buffer of references seals at about 47,000 rows of that table where one of values seals at 35,000. 50 and 200 indexes that each buffer batches of 500 rows keep every row in the arena (no fallback, no forced seal; 4 and 16 MB). When the arena has no room (all 256 slots, or the 128 MB), the buffer written longest ago is asked back: the statement that found no room seals it and promotes it (as `sift.col_promote` does, in the foreground whether or not the worker is on), and the rows that found no room until then are written with their values, as without the setting, so the answers are the same (300 indexes that each buffer batches of 1,000 rows, past the 256 slots: 644 of 900,000 rows written with their values, 644 buffers promoted ahead of their limit); `sift.col_arena_status()` counts the rows written that way (`put_fallbacks`), the buffers asked back (`seals_forced`) and the rows built again from the heap (`rows_rebuilt`). A reader takes the values from the arena: warm a read costs the same (within 1%), the first statement of a backend 4% to 5% more at 47,000 rows. A server that starts again after a stop or a crash, a standby (replay writes the records, nobody fills its arena) and a promoted standby have no entries: the first reader builds them from the heap, one reader at a time, and the readers after it find them (11,900 instructions a row, once for the buffer: 12,000 buffered rows, first read of the server 65 M to 209 M instructions, 8 to 24 ms). A buffer that holds a reference is column format minor 44 in its directory entry, so a release that does not know the records refuses the index. - The open write buffer of a BM25 `sift` index is walked by every search (a buffer of format 0 has a 256-bit bloom of term hashes for each page, which a page of 900 or more terms fills and so passes; a buffer opened by 4.2 has a bloom of 8,192 bits for each full page, and a page it rules out is not read): about 250 cycles a document for each search, against 80 once the document is in a segment, and its promotion costs 55,000 cycles a document, once (295 M cycles and 95 ms for 5,000 documents). The server adds up the documents the searches walked for the open buffer, and once they cost half of its promotion (110 searches that walk all of a buffer) the backend that crossed it asks the worker, which seals and promotes the buffer; a buffer of fewer than 2,000 documents is left for `write_buffer_limit` (4 MB), a VACUUM or `sift.promote`, and a buffer that is only written has no walks and never promotes before its limit. The request goes through the usual one per `sift.maintenance_naptime` for a backend and the backoff of the index, and it needs `sift.async_maintenance`. A 5,000-document buffer in a 220,000-document index: a count of a term of 5% of the documents 3.3 M cycles with the buffer, 1.2 M promoted. - `sift.col_index_status` is one row per readable `sift_col` index: size of the index, its segments and the free and reusable blocks, segments against the 128 the directory holds and the largest one, rows, dead rows (`dead_pct`), segments VACUUM marked dirty, rows in the write buffer and buffers sealed, whether maintenance runs or fails, and `sift.col_maintenance_status` (last VACUUM, rows changed since, why a rewrite is `due`, what it is `blocked_by`). - The file of a BM25 index is larger right after a fast ingest than the segments it holds, and the merge worker lags a loader that is quicker than it: 300,000 documents in 60 batches of 5,000 transactions leave 48 MB of file for 30.0 MB of segments (3 segments and a write buffer), against 29 MB after a `REINDEX`; the merges out of place leave the blocks of the segments they replaced free inside the file, and VACUUM cuts them off the end (30 MB, 1.03 times the `REINDEX`). The worker starts at most once per `sift.maintenance_naptime` (2 s) and a merge starts when `merge_fanin` (4) segments of one size are there, so a stream faster than that keeps a few more small segments until the next tick, and a statement reads each of them. - Ranking scores use document frequencies that may include up to 5% deleted documents until the next merge or `REINDEX`; documents tied near the cut may reorder. VACUUM counts the dead postings of each term of a segment again only when the documents cleared since the last count are 5% of its documents or more, and below that it scales the counted dfs to the live documents. Measured on 300,000 documents with a normal and a skewed term distribution, the scores differ from those of a fresh `REINDEX` of the same rows by 0.1 to 0.2% at 1 to 3% dead, 0.3 to 0.5% at 10% and 0.4 to 0.9% at 20%, and the top 20 overlap by 99.9 to 100% up to 3% dead (98.6 to 100% at 10%); a vocabulary with every term equally frequent, whose scores lie close together, lost 4% of the top 20 at 1% dead with a score error of 0.3%, documents tied near the cut changing places. Counts (`count(*)`, `count(DISTINCT ...)`) stay exact; only the single-term `count(*)` shortcut that reads the dfs is off until the table is counted again (+0.5 M instructions at 3% dead). A merge and a `REINDEX` always give exact scores, and so does a segment with 5% or more cleared. After one round of 1% of the rows inserted, updated and deleted (2% dead, the count skipped), VACUUM takes 88 M instructions, 37 ms and 10.4 MB of WAL at 100,000 documents and 637 M, 179 ms and 34.2 MB at 300,000 (every process counted), where a GIN index of a tsvector on the same rows takes 1,293 M, 173 ms and 39.9 MB, and 2,850 M, 389 ms and 99.5 MB. Averaged over 12 such rounds (WAL with `full_page_writes = off`) it takes 2,888 M instructions, 356 CPU ms and 12.8 MB at 300,000 documents (3,576 M before the 5% rule; GIN 3,005 M, 409 ms and 43.1 MB) and 10,875 M, 1,246 ms and 44.4 MB at 1,000,000 (13,059 M before; GIN 9,655 M, 1,380 ms and 109.1 MB): over the 12 rounds GIN takes fewer instructions at 1,000,000 documents, because the large segment is rewritten once in them, and sift takes less WAL and time. A round that skips the count takes 608 M instructions against 1,638 M at 300,000. - The first statement of a session that loads pg_sift (a session without `shared_preload_libraries` or `session_preload_libraries` naming it) is planned before the library is loaded, so its plan lacks the planner step of `ORDER BY sift.score(ctid) DESC, other_column LIMIT k` (`EXPLAIN` shows no `Includes Ties` on the SiftScan): the rows are the same, and a k-th score shared by many rows costs time. Any statement after it, `SELECT sift.version()` for one, and every session of a server that preloads pg_sift, gets the step. The step of the finished plan that moves the match of a BM25 index with a stemmer from the filter to the recheck of a bitmap heap scan needs no hook and is applied to that first plan too. - `sift.segments(index)` and `sift.verify(index, true)` inspect and check an index. `sift.index_info(index)` shows size, high-water mark, free and reusable blocks, directory entries and whether a maintenance run holds the lock; `sift.version()` is the loaded library's version (compare `pg_extension.extversion`). Which build is loaded (every release of a line reports the same `sift.version()`, `3.4` for 3.4.0 to 3.5.1): on PostgreSQL 18, `SELECT version FROM pg_get_loaded_modules() WHERE module_name = 'pg_sift'` is the release and the commit, `3.5.1+2a7e47598` (`3.5.1-110-g8734357ff+8734357ff` between releases, `-dirty` for a build with local changes). `git describe` of a checkout, or the `BUILD_ID` that `git archive` fills in for the packages, gives it; `make SIFT_BUILD_ID=x` sets it, and a tarball with neither says `unknown`. `sift.promote`, `merge`, `verify`, `segments`, `segment_postings` and `index_info` need the table owner or a role with `MAINTAIN` on it, like `VACUUM`. - `REINDEX [CONCURRENTLY]` works as usual; `CREATE INDEX` is parallel on large tables. Changing `k1`, `b` or an analyzer option needs it. `pg_stat_progress_create_index` shows a build's phase (`scanning table`, `spilling runs`, `merging runs`, `writing segments`) and the documents done. pg_dump restores rebuild the index ([Replication](#replication)). - A build writes postings to temporary files (`temp_tablespaces`) before the segments: about 2.3 times the index size at the default `maintenance_work_mem`, about 4.3 times at `1MB` (a second merge pass), on 100,000 articles (64 MB index). Memory stays within the budget at any table size. - `EXPLAIN` shows `Custom Scan (SiftScan)`, or `SiftCount` for the `count(*)` pushdown. - A `sift_col` unit answers from its pre-aggregates only over pages the visibility map calls all-visible. A plain VACUUM sets every page it can: on 400,000 rows with 3% updated in ten transactions and `synchronous_commit = on`, none stays unset with no old snapshot; with a transaction holding a snapshot older than the updates, 9,814 of 10,052 pages stay unset until it ends, then one more VACUUM sets them (0 left). PostgreSQL lets the oldest snapshot decide what VACUUM may call all-visible, so when grouped aggregates still read rows after a VACUUM, check the oldest `xact_start` or `backend_xmin` in `pg_stat_activity` and replication slots. With `synchronous_commit = off` unflushed commits do the same (see `sift.col_auto_merge`). - Deleted or updated rows not yet vacuumed cost a heap check: a scan reads the heap page of every row on a page that is not all-visible (`Rows Vetted By Heap` in EXPLAIN ANALYZE). With 3,000,000 rows, 6% deleted from 16% of heap pages and no VACUUM (SiftAgg cells of a grouped-aggregate corpus, warm cache), a wide scan vetted 181,251 to 295,931 rows over 11,615 to 25,029 heap pages and ran within noise of the same scan after VACUUM (263.6 against 272.6 ms; cells differ -18% to +21% either way). The cost is page reads (I/O on a cold cache) and vanishes after a VACUUM. - With `sift.col_auto_merge` on, the first scans that vet rows ask the worker to vacuum the table unless it was vacuumed lately: 5 `sift.maintenance_naptime`s, and on PostgreSQL 18 ten times its average VACUUM (an hour-long VACUUM is not repeated by the worker within ten hours). Here the VACUUM before the deletes took 2.4 s; the worker's ran 24 s after the deletes, on the fourth scan, and the next scan vetted 0 rows. - A VACUUM that cannot remove the dead rows (long transaction, replication slot, standby with `hot_standby_feedback`) leaves them vetted whatever the trigger does. - A standby never asks for the VACUUM (it cannot run one): its scans vet the rows of the pages the primary has not yet made all-visible, and `sift.col_maintenance_status` on the primary says when the worker vacuums next. To keep a replica's vetting small, lower the primary's `autovacuum_vacuum_insert_scale_factor` (and `autovacuum_vacuum_scale_factor`) for the table, or run a VACUUM from a job. - Rows only appended, never deleted, cost the same heap check until a VACUUM on a table **without a unique key** (a bulk-loaded events table): the pages the load wrote are not all-visible, and the index cannot tell that nothing was deleted from them. A table with a one-column primary key (`track_changes = 'auto'`, the default) or an index with `track_changes = 'ctid'` answers those pages from its log of deleted rows without reading the heap, within 10% of a vacuumed table (8.8 M cycles for the grouped scan below, 86 blocks). 300,000 rows loaded in one statement, `sift_col` with `key_cut`, warm, one backend, `sift.enable_agg` on, minimum of 8 interleaved rounds (cycles decide): `SELECT dim_a_id, count(*), sum(amount) .. GROUP BY dim_a_id` takes 70.2 M instructions, 32.2 M cycles, 6.8 ms and 2,028 blocks before a VACUUM and 18.4 M, 8.1 M, 1.4 ms and 72 blocks after it (one entity: 8.3 M instructions, 6.1 M cycles, 813 blocks against 1.7 M, 2.0 M, 18; one month of dates, grouped: 21.4 M cycles against 3.7 M); on a cold cache the grouped scan reads 2,012 pages against 58. `VACUUM` the table after a bulk load, and for a table that keeps taking appends lower `autovacuum_vacuum_insert_threshold` and `autovacuum_vacuum_insert_scale_factor` on it (`ALTER TABLE t SET (autovacuum_vacuum_insert_scale_factor = 0.01)`), as for a standby above; with `sift.col_auto_merge` on, the first scans that vet rows ask the worker for it, so the case that stays is `autovacuum_enabled = false`, `sift.col_auto_merge` off, or a standby. - Updated tables: how soon the units answer from their pre-aggregates again depends on who vacuums. 300,000 rows, 60 keys of 5,000 rows, the heap in arrival order, a column no index holds updated: ten rounds four seconds apart, each updating 1% of the rows and inserting 0.5% (30,000 and 15,000 in all; 84% of the updates were HOT in the default run), warm, one backend, `sift.enable_agg` on. One update in a thousand rows puts one on every heap page, so the first rows changed take the visibility map from the whole table (6,000 updated rows: every unit read from its rows): | state of the table | `count(*), avg(score)` of one key | `GROUP BY` the 60 keys | VACUUMs of the table in the 40 s | |---|---|---|---| | fresh, VACUUMed | 0.33 M instructions, 28 blocks | 1.09 M, 31 | | | after the ten rounds, nothing vacuums (`autovacuum_enabled = off`, or `sift.col_auto_merge` off with autovacuum at its defaults: 0.2 and 0.05 both stayed under their threshold, the statistics held 6,700 dead tuples of 30,675 updated rows) | 7.4 M, 2,792 | 54.9 M, 3,212 | 0 | | `autovacuum_vacuum_scale_factor` and `autovacuum_vacuum_insert_scale_factor` 0.01 on the table (`sift.col_auto_merge` off) | 1.98 M, 338 | 6.9 M, 743 | 4 (+8.4 G instructions with the b-tree twin's: about 1 G for a VACUUM of both tables and the merge after it) | | defaults, `sift.col_auto_merge` on (the worker asks for the VACUUM) | 3.1 M, 283 at 50 s; 4.6 M, 2,817 at 98 s, with a batch just written; 2.2 M, 274 settled | 13.1 M at 50 s; 4.8 M settled | 10 in 164 s, one per ten to fifteen seconds while the rows change (+2.9 G instructions, 290 M each with the merge) | The b-tree on the same rows reads the heap for `avg(score)` in every state (11.5 to 12.0 M instructions and 2,400 to 2,600 blocks for one key, 535 to 585 M for the grouped statement), so the table gives no comparison of a state; its index-only scans depend on the same map. The recommendation is the default: leave `sift.col_auto_merge` on and the table's `autovacuum_enabled` true. The worker asks as soon as a thousandth of the rows has changed (50 at least, 100,000 at most), where autovacuum waits for a fifth; set `autovacuum_vacuum_scale_factor` and `autovacuum_vacuum_insert_scale_factor` to 0.01 on the table when `sift.col_auto_merge` is off for the server. `sift.col_index_status` says why a table is not being vacuumed (`blocked_by`: `sift.col_auto_merge is off`, a VACUUM too lately, a snapshot holding back the rows), and `Rows Vetted By Heap` in `EXPLAIN ANALYZE` what it costs. - Updated `sift_table` tables: a VACUUM of the table writes the pre-aggregates of the units that kept a dead row net of those rows (the same sidecar a `sift_col` merge writes), so the units answer from them again until more of their rows die; the rows deleted after it are read from the unit and the tombstones, as before. `EXPLAIN ANALYZE` shows the units answered this way as `Units Net Of Dead Rows`. Held by `tbl_net_sidecar`. 300,000 rows, 60 keys, 4.5% deleted at random, one backend, warm, instructions of `SELECT key, count(*), sum(n) .. GROUP BY key` (`SELECT count(*), avg(n) .. WHERE key = 7` in brackets): 1.5 M (1.2) fresh, 38.8 M (8.4) with the deleted rows and no VACUUM since, 2.3 M (1.9) after the VACUUM (the heap table: 519 M (267), 605 M (364), 501 M (260)); a VACUUM of that table costs 276 M instructions and 2.0 MB of WAL. The background pass runs without a setting: it is asked for when the reads of the table since the last pass have cost as much as the pass (about 38 reads of a table dirty throughout), so a table that is read settles by itself and one nobody reads is left to autovacuum; the setting `sift.table_auto_defrag_dead_pct` of 3.3 had no effect since 4.0 and is removed in 4.2 (`settings_removed`). Cases `table_read_churned` and `table_read_settled` (`bench/bench cases`) time the two states. - Free space in a `sift_col` file depends on the rewrite budget (1M rows, 0.87% changed per cycle, 21% in 24 cycles): - `sift.col_auto_merge_wal_pct = 0` (no budget, a rewrite at every VACUUM): the file reaches 2.2 times the live bytes (54 to 55% of blocks free) in 5 cycles and stays, on 3.5.1 and 3.4 alike. - Default 5%: a fresh index stays at 1.2 to 1.3 times (cycles 60 to 240), bursting to 1.5 to 1.75 when a segment is rewritten whole. - An index from an old version with a few very large segments does not recover alone: rewriting one costs its size in WAL, the budget refuses it against 5% of the changes' WAL, and VACUUM's space step copies only end-of-file segments that fit `sift.vacuum_rewrite_limit` in a round (up to 64 rounds, the lock let go between them), and a segment larger than it is moved down a run at a time. - To recover space in a maintenance window, either `SET sift.vacuum_rewrite_limit = 4096; VACUUM t;` (MB to move; `sift.col_segments` lists segments, end-of-file ones first; exclusive lock, WAL equal to the bytes moved: 20.2 to 13.4 MB of file for 7.7 MB of WAL in 26 ms, 9.3 MB live), or `REINDEX INDEX CONCURRENTLY` (20.2 to 10.4 MB of file, 10.4 MB of WAL, 0.9 s per million rows with the heap cached; the disk holds both files until the swap). - The small segments (overlays) that promotions leave in a `key_cut` index over the keys of new rows (the segments under half a partition, or under half the largest one when the index is not cut into partitions) are merged by their own rule (`sift.col_maintenance_due` says `overlay units`), apart from the budget above: the budget counts the WAL the server wrote since the oldest large segment was written, and an overlay merge waits for no WAL of other writes (a server that writes nothing else is when it costs least). It is paced by the size of a pass instead: at most an eighth of `max_wal_size` of overlay bytes, and `sift.col_auto_merge_wal_pct` percent of it, so that it forces no checkpoint of its own; the next pass takes the rest. A step takes the smallest overlays that come to twice the largest of them, so a row is rewritten once for each doubling and not at every promotion; when more than four overlays are alive (the most a read of a key opens a unit in) and the index has been read, the smallest are taken whatever their sizes, as far as a pass allows. An overlay is written lean: without point directories, whose arrays span the rows of the whole index (its order arrays stay too once the overlays together hold over a tenth of the rows of the index or over 65,536 rows, a sixteenth of that with a text array; until then they keep none, so that the arrays of the larger segments cover at least 0.9 of the values, as the order planner asks; a walk of the arrays reads the rows of such a segment, decoded and sorted in memory for each scan, as those of the write buffer, up to 65,536 rows of a number or a time, and the plan is priced with them), and it gets no net pre-aggregates of its dead rows (the merge drops them); the merge into a larger segment writes the full set again. The merge is due when the units and segments a request for a typical key of the overlays would no longer open (a unit is worth 46,000 instructions to open and 20 for each row it holds that the request scans: the overlays of a promotion are as wide as the units of the index, so that two overlays of 30,000 rows fall due after about 400 requests and not 4,400), times the requests the index has had (`idx_scan`, which aggregate scans count too), come to half of the rewrite of the overlays' rows; dead rows after a VACUUM are not a reason of their own (the units that hold them answer from their net pre-aggregates as clean ones do), so `col_maintenance_due` is NULL at 10% dead rows until the overlays the churn left pay; an index nobody reads, or with fewer than two overlays, is not merged. The worker, VACUUM and `sift.col_maintain` run it; the reads of a backend ask for the worker, so autovacuum of the table need not run. A standby does none of it: the request, the tick and the worker return at once in recovery, and the standby reads the merged index after replay (checked on a streaming standby: 1,200 reads there asked for nothing and logged nothing, 1,800 reads on the primary merged 13 segments into 1, the standby showed 1 and the same rows as the primary and as the normal plan). | Setting | Applies to | Default | Class | Values | Context | Description | Cost | |---|---|---|---|---|---|---|---| | `sift.max_expansions` | BM25 | 512 | safety | int 1..1000000 | superuser | Maximum terms a wildcard, regex or fuzzy leaf may expand to, and a prefix may be scored over. A prefix with more terms matches all of them and scores as the one term prefix_index would index. | above it a prefix matches all its terms but scores as one term [USAGE](USAGE.md#queries) | | `sift.prefix_cache_size` | BM25 | 8192 kB | default | int 0..1048576 kB | superuser | Memory a backend keeps the postings of broad prefixes in (kB), 0 to keep none. | up to this much memory per backend; 0 keeps none [USAGE](USAGE.md#queries) | | `sift.enable_scan` | BM25 | on | diagnostic | bool | session | Enables the sift custom scan (ranked and unordered). | none; off gives the plan PostgreSQL has without them [USAGE](USAGE.md#filter-pushdown) | | `sift.enable_count` | BM25 | on | diagnostic | bool | session | Enables count(*) pushdown into the sift index. | none; off gives the plan PostgreSQL has without them [USAGE](USAGE.md#filter-pushdown) | | `sift.filter_pushdown` | BM25 | on | default | enum: off, on, force | session | Ranked scans take the tuples a bitmap of the other quals keeps: off, on (by cost) or force. | none; off gives the plan PostgreSQL has without them [USAGE](USAGE.md#filter-pushdown) | | `sift.prefetch` | BM25 | on | diagnostic | bool | session | Reads ahead the heap pages an unordered scan is about to visit. | none; off gives the plan PostgreSQL has without them [USAGE](USAGE.md#filter-pushdown) | | `sift.parallel_scan` | BM25 | off | opt-in | bool | session | Lets the planner use parallel sift scans and counts (they pay off on large, heap-bound scans only). | off because a Gather costs milliseconds: only an unordered scan with a heavy residual qual gained (1.8x on 218,058 articles) [USAGE](USAGE.md#parallel-scan) | | `sift.rls_scores` | BM25 | off | opt-in | bool | superuser | Lets a ranked scan serve a role under a row-level security policy. The scores count the rows the policy hides: document frequencies and field lengths come from the whole index. Off, such a role gets no ranked scan and sift.score() raises an error. | scores use the statistics of the whole index, hidden rows included [USAGE](USAGE.md#row-level-security) | | `sift.standby_conflicts` | all | on | safety | bool | superuser | Logs the reuse of released blocks as a recovery conflict, so a standby without hot_standby_feedback waits for or cancels the queries that may still read them. Off, such a query fails with "segment recycled during scan, retry" when the blocks it reads are reused. | replay can wait up to max_standby_streaming_delay [SIZING](SIZING.md#settings-that-matter) | | `sift.log_min_duration` | all | 1000 ms | diagnostic | int -1..max ms | superuser | Logs the promotions, merges and maintenance worker runs of an index that take at least this long (ms), -1 to log none. | none; a log line [SIZING](SIZING.md#settings-that-matter) | | `sift.simd` | all | avx512 | diagnostic | enum: scalar, avx2, avx512, auto | session | Caps the SIMD level used by sift kernels. auto uses the best level the CPU supports; avx2 and avx512 are the levels of x86-64, neon the level of AArch64, and a level of the other architecture caps nothing. | none; a lower level only slows the kernels (auto is the best level of the CPU, shown as avx512 on x86-64 and as neon on AArch64, whose levels are scalar and neon; the level names of the other architecture cap nothing, so one configuration file serves a mixed fleet) [USAGE](USAGE.md#maintenance-and-operations) | | `sift.async_maintenance` | all | on | default | bool | superuser | Promotes and merges segments in a dynamic background worker. | a merge of a 10 MB index took 0.27 s at cost delay 0 and 5.3 s at 20 ms (at most 6.4 MB/s of I/O) [USAGE](USAGE.md#maintenance-and-operations) | | `sift.maintenance_naptime` | all | 2000 ms | default | int 200..60000 ms | reload | Time between background maintenance workers of an index; a worker stays this long after it starts. | a merge of a 10 MB index took 0.27 s at cost delay 0 and 5.3 s at 20 ms (at most 6.4 MB/s of I/O) [USAGE](USAGE.md#maintenance-and-operations) | | `sift.maintenance_cost_delay` | all | 0 | safety | int 0..100 ms | superuser | Milliseconds the maintenance worker sleeps after every 16 pages (128 kB) its merges read or write, 0 to run them at full speed. | a merge of a 10 MB index took 0.27 s at cost delay 0 and 5.3 s at 20 ms (at most 6.4 MB/s of I/O) [USAGE](USAGE.md#maintenance-and-operations) | | `sift.maintenance_wal_rate` | all | 0 | safety | int 0..1048576 kB | superuser | Kilobytes of WAL per second the merges of the maintenance worker may write, 0 for no limit. A merge waits before the page that would pass the rate, so that its WAL leaves room for the writes of the queries and inserts and for the standbys to replay it; it adds to sift.maintenance_cost_delay. | a merge of a 10 MB index took 0.27 s at cost delay 0 and 5.3 s at 20 ms (at most 6.4 MB/s of I/O) [USAGE](USAGE.md#maintenance-and-operations) | | `sift.vacuum_rewrite_limit` | sift_col, sift_table | 128 MB | safety | int 0..1048576 MB | session | Megabytes of segments a column VACUUM may rewrite to merge (past the first merge step), with a separate equal allowance to copy the end of the file into free blocks below it, which holds the table or index under an exclusive lock; 0 keeps the one merge step and moves nothing. | the VACUUM that moves the end of the file holds the table or index under an exclusive lock [USAGE](USAGE.md#maintenance-and-operations) | | `sift.enable_col_scan` | sift_col | on | default | bool | session | Lets the planner use a sift_col index with the sift.col_*_ops operator classes as a bitmap index scan. On by default: the planner chooses it by cost against the b-trees and the sequential scan; off keeps the aggregate scans of SiftAgg only, see docs/COL-SCAN.md. | on: the planner prices the bitmap scan and the Index Scan against the b-trees and the sequential scan; with the b-trees present the plans are the b-tree's [COL-SCAN](COL-SCAN.md#how-it-works) | | `sift.vis_page_cache` | sift_col | on | diagnostic | bool | session | Keeps, for the rest of a scan, the visibility of the rows of a heap page the scan reads twice. | about 48 bytes for each heap page the scan visits (at most 1,048,576 pages), freed with the scan; off checks the page again each time [COLUMN-STORAGE](COLUMN-STORAGE.md#cutting-the-units-by-a-key) | | `sift.col_rollup_on_demand` | sift_col | on | default | bool | session | A scan that reads units for lack of their rollups asks the background worker to rebuild them. Nothing is written by the query; the next one answers from the rollups. Off: the units stay as they are until a merge or REINDEX. | background rewrites of segments; off leaves the units as they are until a merge or REINDEX [COL-PREAGG 7](COL-PREAGG.md#7-on-demand-refresh-of-the-rollups-srccolrollupc) | | `sift.col_rollup_worker` | sift_col | on | default | bool | session | A request for a rollup refresh starts the background worker that makes it. Off: the requests wait in the queue until sift.col_rollup_refresh(index) is called. | background rewrites of segments; off leaves the units as they are until a merge or REINDEX [COL-PREAGG 7](COL-PREAGG.md#7-on-demand-refresh-of-the-rollups-srccolrollupc) | | `sift.cluster_advice` | sift_col | on | diagnostic | bool | session | sift.cluster_advice() recommends a physical order for the tables of the sift_col indexes. Off: it answers 'not recommended' for every index, and CREATE INDEX does not suggest key_cut. The index option cluster_advice switches one index off. | none; maintenance only [USAGE](USAGE.md#is-the-index-clustered-and-what-did-the-scans-do) | | `sift.col_format_minor` | sift_col, sift_table | 15 | default | int 3..15 | superuser | Column format minor of the segments written (15 by default; an option with a newer layout, such as key_cut or point_tid_columns, stamps its own higher minor): 6 keeps them readable by pg_sift 3.2, 4 by 3.1, 3 by 3.0. Segments already written keep their minor; a 3.2 library refuses the ones above 6. A segment of a schema with wide leaves is minor 5 at least, and the minor 6 of the complete kinds is written only for a schema with jsonb paths, from rows whose kinds are known. The minor 7 is written only for an index with the option cube_minmax, the minor 8 only for a sift_table with a date or timestamp column, the minor 9 only for an index with the option rollup_dims, the minor 10 only for one with the option key_columns, the minor 12 only for one with the option superunit, the minor 13 only for one with the option order_columns, the minor 14 only for one with a dictionary of 1,024 strings or more (the option dict_lookup), the minor 15 only for one whose key_columns keep a key list or a bitmap. Those above 15 are written by the options that need them, and a library older than the release that added the layout refuses the segment. | the options that write a minor above the setting are ignored; a 3.2 library refuses segments of minor 7 and above [FORMATS 4](FORMATS.md#4-column-segments-siftsegentryformat-of-access-methods-sift_col-and-sift_table) | | `sift.col_auto_merge` | sift_col | on | default | bool | superuser | Keeps the sift_col indexes answerable after churn: a background worker vacuums the heap table, and after a VACUUM that left an index scattered it seals the write buffer and merges all its segments into one. Rows on a page the visibility map does not call all-visible (any page with an UPDATE or DELETE since the last VACUUM) are checked one by one in the heap, and a unit holding a dead row answers from its rows; the VACUUM sets the map, and the merge after it makes the units answer from their pre-aggregates again. A backend that inserts a row version, or reads many rows in the heap, looks at the statistics of the table once per sift.maintenance_naptime and starts the worker when the rows changed since the last VACUUM are a thousandth of the table (50 rows at least, 100,000 at most). It vacuums as autovacuum does (its reloptions, cost delay, skipped when the table is locked), at most once per five sift.maintenance_naptime (ten times the average VACUUM of the table on PostgreSQL 18), and not a table with autovacuum_enabled = false. The merge rule is sift.col_auto_merge_dirty_pct, _fragmentation and _buffer_pct, and it stays within sift.col_auto_merge_wal_pct of the WAL the server writes; the rewrite sleeps as sift.maintenance_cost_delay says and is skipped when the maintenance lock is held. An index over sift.vacuum_rewrite_limit is rewritten by partitions of that size (sift.col_auto_merge_partition_kb sets another), one step each, so that the lock is let go between them. Needs sift.async_maintenance; sift.col_maintain(index) applies the same rule from a job. A DELETE alone calls nothing and waits for a scan or an insert; a scan of a partitioned table does not ask, an insert into a partition does. Set it for the server or the database: the worker reads no session setting. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_auto_merge_dirty_pct` | sift_col | 30 | default | int 1..100 | superuser | Percent of the units of a sift_col index holding a dead row above which the automatic rewrite runs. Such a unit answers from its rows, not from its pre-aggregates, however few rows are dead. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_auto_merge_fragmentation` | sift_col | 2 | default | real 1.1..1000 | superuser | Units listed per superblock they cover from which the automatic rewrite of a sift_col index runs. Each promotion of the write buffer adds a segment over the superblocks the churn touched. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_auto_merge_buffer_pct` | sift_col | 1 | default | real 0.01..100 | superuser | Percent of its rows the write buffer of a sift_col index holds above which the automatic rewrite runs. The rows of the buffer are read as one in-memory unit per superblock they touch, on every query, until a promotion. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_auto_merge_wal_pct` | sift_col | 5 | safety | real 0..100 | superuser | Percent of the WAL the server writes that the automatic rewrite of a sift_col index may add. The rewrite writes the whole index, so it waits until the server has written 100 / this percent times the bytes of the index since the index was last written (its build or its last rewrite): 5 bounds the rewrites at a twentieth of the WAL, and the churn that makes one worth it is the churn that wrote that WAL. The segments of a build that overlap each other (a parallel build leaves one set per worker) are not churn: their rewrite does not wait. 0 does not wait. sift.col_maintain(index) ignores it. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_auto_merge_pass_mb` | sift_col | 1024 MB | safety | int 0..2097151 MB | superuser | Megabytes of overlapping runs one pass of the automatic rewrite of a sift_col index rewrites. A build leaves runs of rows sorted by the key_cut key, each as large as a partition and overlapping its neighbours in the key; the rewrite cuts them by key, a window of the runs that overlap at a time, and stops when a pass has written this much. The next VACUUM, worker pass or sift.col_maintain(index) goes on, so the WAL and the I/O of the rewrite are spread over passes (the index stays valid and equal between them). The blocks a pass frees are reusable only when the snapshots that may read them end, so the automatic rewrite waits for them once they reach this much (or half the index, when that is less): the file stands at the live bytes plus one pass meanwhile. 0: one pass does all of it. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_build_key_ranges` | sift_col | on | default | bool | session | A serial sift_col build with key_cut of a large table writes runs that do not overlap in the key. The rows go to a temporary file for each stretch of the key (cut at quantiles of a sample of the table) while the table is scanned, and the stretches are written as runs in the order of the key, where the runs of the rows in table order each hold every key and wait for a rewrite. temp_file_limit and the free room of the temporary tablespaces bound it (a build that does not fit them writes the runs in table order). off: runs in table order. | the rows go to temporary files while the table is scanned (about the size of the index, no WAL), and a sample of the table is read before the scan: the build takes about a quarter longer than runs in the order of the table, and half the time and half the WAL of those runs and the rewrite that cuts them [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_unit_floor` | sift_col | 2048 | default | int 0..32768 | superuser | Rows per unit below which the rows of a sift_col promotion or overlay merge are cut by TID range, not one unit per superblock; 0 keeps a unit per superblock. Churn puts a few dozen rows in each of the superblocks it touches, and a unit costs a scan about as much as a thousand rows however few it holds, so a promotion that reaches 16 superblocks or more with under half this many rows each is written as units of this many rows in TID order, and a merge that stays that sparse does the same. A merge that reaches this many rows per superblock cuts by superblock again. Needs a table of 16 superblocks (4,096 pages) or more. | a segment cut by TID range is minor 17 (a 3.3 library refuses it, REINDEX with the setting at 14 or less writes the superblock cut); a scan that reads the newest rows of a large table reads the 2,048-row unit that holds them, not a unit of a few rows (ORDER BY ts DESC LIMIT 20: +0.3 ms at 3,000,000 rows and 20% a month, +0.1 ms at 10,000,000 and 20%, +0.4 ms at 10,000,000 and 5%) [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_wbuf_ref` | sift_col | on (new in 4.3) | default | bool | superuser | A row of a heap table with a sift_col index that tracks changes goes to the write buffer as a 16 byte reference to its heap tuple, not as its values. The backend that inserts the row keeps its values in shared memory (1 MB segments made on demand, 128 MB for the server at most, 88 bytes a row of a 13 column table), and the reader of the buffer takes them from there. A server that starts again, a standby and a promoted standby have none: the first reader builds them from the heap, once for the buffer. A row that finds no room is written with its values (sift.col_arena_status shows how many). Records written while it was on stay references after it is turned off; a buffer that holds one is column format minor 44. An index without track_changes, a speculative insertion and a table that is not a heap keep the values. | the values of the buffered rows take shared memory, 88 bytes a row for a table of 13 columns, in 1 MB segments made on demand up to 128 MB for the server (a buffer of 500 rows holds 64 kB); a server that starts again, a standby and a promoted standby build them from the heap, once for each buffer, by the first reader (about 11,900 instructions a row) [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_auto_merge_max_active` | sift_col | 0 | safety | int 0..10000 | superuser | Client backends of the database running a statement above which the automatic rewrite of a sift_col index waits for a quieter moment. The worker looks again after sift.maintenance_naptime, then twice as late each time, 8 times in all; the next VACUUM asks again. 0: never waits. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_auto_merge_partition_kb` | sift_col | 0 | opt-in | int 0..max kB | superuser | Size of the segments the automatic rewrite of a sift_col index writes, which then rewrites only the ones the churn touched; 0 rewrites the whole index into one. A step rewrites the partition with most dirty units (over sift.col_auto_merge_dirty_pct of its own), or the overlays the promotions left over the partitions, and holds the maintenance lock and needs the free space of one partition, not of the index. A segment under half of this size is an overlay; the merge tiers leave the partitions alone. An index under half of it is rewritten whole. 0: partitions of sift.vacuum_rewrite_limit for an index over it, or of a third of maintenance_work_mem where that is less, which is what the writer of a segment holds in memory. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.col_auto_merge_max_workers` | sift_col | 0 | safety | int 0..64 | superuser | Rewrite workers of sift_col indexes that may run at once in a database, 0 for no limit. A worker above the limit looks again after sift.maintenance_naptime, then twice as late each time, 8 times in all; the next VACUUM asks again. | background WAL and I/O: the table is vacuumed every five naptimes while it keeps changing, and the rewrites take at most sift.col_auto_merge_wal_pct (5) of the WAL the server writes: +0.9% to +1.9% total WAL at 1M rows with full-page images and a checkpoint per round, +0.0% without full-page images (it waits for the WAL), against +12% to +28% when it rewrites at every VACUUM; the file is up to twice its live size after a rewrite until later VACUUMs; no rewrite while a snapshot holds back the blocks the last one freed [USAGE](USAGE.md#maintenance-and-operations) | | `sift.enable_agg` | sift_col, sift_table | on | default | bool | session | Enables aggregate pushdown into sift_col column indexes (Custom Scan (SiftAgg)). | none; off gives the plan PostgreSQL has without sift [USAGE](USAGE.md#column-indexes-aggregates-and-sift_table) | | `sift.agg_left_lateral` | sift_col, sift_table | on | diagnostic | bool | session | Pushes down LEFT JOIN LATERAL over a jsonb_to_recordset or jsonb_array_elements list of the scanned row: a row none of whose elements passes is one tuple with NULL element columns. | none; off keeps the normal plan of such a query (the way to compare it and to isolate a wrong result) [AGG](AGG.md) | | `sift.agg_probe_direct` | sift_col, sift_table | on | diagnostic | bool | session | Probe the btree of a correlated lookup child directly, a batch of keys at a time, instead of running the child for each key. | none; a child the probe does not take (a parameter in its filter, a sub-plan, a non-btree index, an inequality key, a serializable transaction) keeps its plan for every key [AGG](AGG.md) | | `sift.agg_float_step` | sift_col, sift_table | on | diagnostic | bool | session | Evaluate a key or an aggregate argument of a float column that reads it through floor, ceil, trunc, round or an integer cast once for each bucket of the values, not for each distinct value. | none; a value with no bucket number (NULL, NaN, an infinity, a quotient or product that overflows or underflows, a result outside int32, a negative zero) keeps the id of its value, and a statement that reads the column another way as well keeps the ids of its values [AGG](AGG.md) | | `sift.agg_preagg` | sift_col, sift_table | on | diagnostic | bool | session | Answers the units of an aggregate scan whose rows are all visible from the pre-aggregates the sift_col index stored with them. | none on the index; off scans every unit [COL-PREAGG 4](COL-PREAGG.md#4-when-a-units-pre-aggregate-may-answer) | | `sift.agg_preagg_distinct` | sift_col, sift_table | on | diagnostic | bool | session | Answers count(DISTINCT col) of a column the cube stores a histogram of from the groups of the histograms of the units whose rows are all visible, with sift.agg_preagg. | none on the index; off scans every unit [COL-PREAGG 4](COL-PREAGG.md#4-when-a-units-pre-aggregate-may-answer) | | `sift.agg_dead_delta` | sift_col | 15 | default | int 0..100 | session | Most percent of the rows of a unit that may be dead for the unit to be answered from its pre-aggregates net of them (counts and integer sums), with sift.agg_preagg; 0 scans such a unit. A VACUUM that removes a row version clears its liveness bit in the unit, and the unit then has no exact pre-aggregates: it was read row by row. When every page it has rows on is all-visible, the values of its dead rows are read at their positions and taken off the groups of its pre-aggregates, one row at a time. Minimum, maximum, count(DISTINCT), sketches, float sums and leaves of elements still scan the unit. | no stored byte and no write; a unit with more dead rows than the limit, or a page that is not all-visible, is scanned as before; a dead row costs about 500 instructions against about 100 for a scanned row, so a unit over about 17% dead is cheaper to scan; the attempt is dropped for a statement whose first 8 units read for their dead rows were scanned anyway (a statement the setting cannot help: key lookup +1.3% instructions at 300,000 rows, +0.8% at 3,000,000, float sum +0%) [COL-PREAGG 12](COL-PREAGG.md#12-dirty-units-net-of-their-dead-rows-siftagg_dead_delta-no-format-change) | | `sift.agg_super` | sift_col, sift_table | on | diagnostic | bool | session | Answers a group of exact units from the summary the sift_col index stored for the group (reloption superunit) instead of from each unit. | none on the index; off scans every unit [COL-PREAGG 10](COL-PREAGG.md#10-superunits-one-summary-for-a-group-of-units-column-format-minor-12-reloption-superunit-default-16) | | `sift.enable_topn` | sift_col, sift_table | on | default | bool | session | Enables ORDER BY .. LIMIT from the column units (Custom Scan (SiftAgg) with Top-N), with sift.enable_agg. | none; off gives the normal plan, topn_walk and topn_zone_keys narrow the walk, topn_runs needs order_columns [COL-SEARCH](COL-SEARCH.md#facets-and-top-n) | | `sift.topn_walk` | sift_col, sift_table | on | diagnostic | bool | session | Lets a top-N scan visit the units of a sort column that does not follow the table by the best value of their zones, and stop at the first that cannot beat the n-th. | none; off gives the normal plan, topn_walk and topn_zone_keys narrow the walk, topn_runs needs order_columns [COL-SEARCH](COL-SEARCH.md#facets-and-top-n) | | `sift.topn_zone_keys` | sift_col, sift_table | on | diagnostic | bool | session | Lets the walk of a top-N bound the units by the zones of the sort keys after the first too: a first key that ties between units is decided by the next. | none; off gives the normal plan, topn_walk and topn_zone_keys narrow the walk, topn_runs needs order_columns [COL-SEARCH](COL-SEARCH.md#facets-and-top-n) | | `sift.topn_runs` | sift_col, sift_table | on | diagnostic | bool | session | Lets a top-N scan read a unit through the sorted run of its first sort column (segments written with the deprecated option order_columns) instead of testing every row of the unit. | none; off gives the normal plan, topn_walk and topn_zone_keys narrow the walk, topn_runs needs order_columns [COLUMN-STORAGE](COLUMN-STORAGE.md#order-and-top-n) | | `sift.agg_lookup_pushdown` | sift_col, sift_table | on | diagnostic | bool | session | Loads the rows a lookup join can match only: an index scan or scan keys on the table's own `column = value` conditions, and the other own conditions tested while loading. | memory: up to 256 MB a backend, and shared memory of 5% of shared_buffers by default; the memory limits stop a larger table from being loaded [SET-FILTERS 4](SET-FILTERS.md#4-the-lookup-table-loads-only-the-rows-it-can-match-siftagg_lookup_pushdown) | | `sift.agg_lookup_columns` | sift_col, sift_table | on | diagnostic | bool | session | Loads the rows of a lookup table from the leaves of a sift_col index of it that holds the columns the join reads, not from the heap. | none: the same memory as the load from the heap [SET-FILTERS 4](SET-FILTERS.md#4-the-lookup-table-loads-only-the-rows-it-can-match-siftagg_lookup_pushdown) | | `sift.agg_probe_rewrite` | sift_col, sift_table | on | diagnostic | bool | session | Plans a join to a large table, or an EXISTS, NOT EXISTS or IN over one, with a btree on its key also as a lateral probe of the btree for each key the scan meets, and keeps that plan where it runs in the scan and costs less. | planning only: one more plan made for a statement that joins a large table by a btree column [SET-FILTERS 4](SET-FILTERS.md#4-the-lookup-table-loads-only-the-rows-it-can-match-siftagg_lookup_pushdown) | | `sift.agg_case_filter` | sift_col, sift_table | on | diagnostic | bool | session | Plans agg(CASE WHEN p THEN x [ELSE NULL \| 0] END) of sum, avg, min, max and count as agg(x) FILTER (WHERE p), which the scan computes on the kernels of x under a test of p. | planning only: a walk of the statement for aggregates of that shape; the statement is planned once more as written when the rewritten one does not run in the scan [AGG](AGG.md#aggregates-over-case) | | `sift.agg_lookup_cache_kb` | sift_col, sift_table | 262144 kB | safety | int 0..max kB | session | Memory per backend that keeps the rows of the joined tables a scan loaded for the next query, while the table does not change (0: off). | up to 256 MB a backend while the table is unchanged and fully vacuumed; a table that needs over half of it is loaded per query [AGG](AGG.md#join-key-filters-and-shared-lookups) | | `sift.agg_lookup_shared_kb` | sift_col, sift_table | -1 | safety | int -1..max kB | superuser | Shared memory, for all backends together, that keeps the rows of the joined tables a scan loaded for the queries of other backends, while the table does not change (0: off, -1: the smaller of sift.agg_lookup_cache_kb and a twentieth of shared_buffers). Dynamic shared memory, taken as entries are kept and returned as they go, so it needs no restart; with posix shared memory it comes from /dev/shm, which it must stay below. Backends that run the same query at once wait for the one that loads the rows (30 s at most); a segment the system has no room for gives a notice and the backend loads its own tables. | dynamic shared memory taken as entries are kept (by default the smaller of the per-backend cache and 5% of shared_buffers); with posix shared memory keep it below what /dev/shm holds [AGG](AGG.md#join-key-filters-and-shared-lookups) | | `sift.agg_dimmap_kb` | sift_col | 65536 kB (new in 4.3) | default | int 0..max kB | superuser | Shared memory, for all backends together, that keeps the dimension maps (the class of each key of a joined table, read from its sift_col index) for the queries of every backend, while the table does not change (0: off). Dynamic shared memory, taken as maps are kept and returned as they go, so it needs no restart; with posix shared memory it comes from /dev/shm, which it must stay below. A map is 2 bytes a key of the table and a row for each class of its columns; one that does not fit is not kept and the query reads the classes itself. | dynamic shared memory taken as maps are kept (2 bytes a key of the table and a row for each class); 64 MB by default; with posix shared memory keep it below what /dev/shm holds [AGG](AGG.md#join-key-filters-and-shared-lookups) | | `sift.verdict_cache_kb` | sift_col, sift_table | 256 kB | default | int 0..1024 kB | superuser | Shared memory, for all backends together, that keeps the choice between a shared CTE and its inline plan, and between the flattened form of a statement and the one as written, so that a new backend plans the form that won only (0: off). The table is allocated, at the size of this setting, by the first backend that plans such a statement after the server starts; a later change turns the use of it on and off (0), not its size. An entry is 56 bytes and a statement keeps up to three; the largest table is about 1 MB. | one table for the server, whatever the number of backends: 56 bytes an entry and up to three entries a statement, 256 kB by default (1 MB at most); where it cannot be allocated the backend logs once and plans in full; the size is taken when the first backend allocates it (a restart to change it); a plan in full at every eighth read of an entry, every 16th, 32nd and 64th once the plans in full have confirmed it [USAGE](USAGE.md#the-choice-for-a-shared-cte-is-kept-per-backend) | | `sift.agg_verdict_cache` | sift_col, sift_table | on | diagnostic | bool | session | Keeps the choice between a shared CTE and its inline plan, and between the flattened form of a statement and the one as written, for the statements of a backend and the others (off: every statement is planned in full, so a plan does not depend on the statements planned before it). | every such statement is planned in full: the shared and the inline plan, and the larger sizes of sift.agg_scale_horizon for a close call; on three scans over the 30,000 rows of the plan-gate tables 4.61 million user instructions and 0.7 ms of planning off against 4.66 million and 0.7 ms on (no measurable difference at that size); a 27 KB dashboard statement (21 CTEs, 16 lateral sub-selects) over 3,000,000 rows takes 128 ms to plan in full against 11 ms from the verdict [AGG](AGG.md#plans-that-depend-on-what-ran-before) | | `sift.agg_lookup_max_kb` | sift_col, sift_table | 262144 kB | safety | int 0..max kB | session | Memory of the rows a sub-select, VALUES list, function or spine joined to Custom Scan (SiftAgg) may return, as the planner estimates them: the rows go into a lookup table in memory when the scan starts; a larger one keeps the normal join. | a larger sub-select, VALUES list or function keeps the normal join [AGG](AGG.md#joins-to-plans) | | `sift.agg_lookup_max_rows` | sift_col, sift_table | -1 | deprecated | int -3..max | session | The older form of sift.agg_lookup_max_kb: rows a sub-select, VALUES list or function joined to Custom Scan (SiftAgg) may return, whatever their width (-1: the memory limit decides). | the older form of agg_lookup_max_kb; -1 lets the memory decide [AGG](AGG.md#joins-to-plans) | | `sift.agg_share_lookups` | sift_col, sift_table | on | diagnostic | bool | session | Lets the Custom Scan (SiftAgg) nodes of one statement that join the same table share the rows they load (UNION ALL arms). | memory: up to 256 MB a backend, and shared memory of 5% of shared_buffers by default; the memory limits stop a larger table from being loaded [AGG](AGG.md#join-key-filters-and-shared-lookups) | | `sift.agg_semi_lut` | sift_col, sift_table | on | diagnostic | bool | session | Answers a semi or anti join (IN, EXISTS, NOT EXISTS) on an integer leaf from a byte table over the keys of the joined table. | memory of that table, 4096 kB by default per scan and per worker; keys that do not fit keep the generic join [SET-FILTERS 3](SET-FILTERS.md#3-the-set-as-a-table-in-select--exists-not-exists-siftagg_semi_lut) | | `sift.agg_connect_found` | sift_col | on (new in 4.3) | diagnostic | bool | session | Answers a semi, anti or inner join to the parent of a connected sift_col index that reads no column of the parent from the flag the index keeps that the parent row was found, less the keys that changed in the parent since. | nothing stored by this option; the flag is written with every connected index (176 bytes of leaf at 1,000,000 rows); a probe of the parent btree for each key the journal names [COL-CONNECT](COL-CONNECT.md#found) | | `sift.agg_child_first` | sift_col | on (new in 4.3) | diagnostic | bool | session | Plans the count of the rows of a table that have (or have no) row in another by a unique key, and the aggregates of a table left-joined to a table by its key, from the other table first: its rows counted by their keys or grouped by them, and keeps that plan where it costs less. | nothing stored; one more plan made for each statement of the shape, kept as a verdict for the next: +0.22 M instructions warm (about 0.06 ms, 977 k against 760 k for a left join of generated days), +1.2 M in a new backend (docs/COL-CONNECT.md) [COL-CONNECT](COL-CONNECT.md#parent-to-child) | | `sift.agg_semi_lut_size` | sift_col, sift_table | 4096 kB | safety | int 0..1048576 kB | session | Memory of the byte table (or hash set) of the keys of a semi or anti join, per scan and per worker; keys that do not fit keep the generic join. | memory of that table, 4096 kB by default per scan and per worker; keys that do not fit keep the generic join [SET-FILTERS 3](SET-FILTERS.md#3-the-set-as-a-table-in-select--exists-not-exists-siftagg_semi_lut) | | `sift.agg_param_set_size` | sift_col, sift_table | 1000 | default | int 0..100000000 | session | Elements assumed for an array parameter of a set filter (col = ANY($1)) in a generic plan, to cost the index and bitmap scans against Custom Scan (SiftAgg); 0 keeps the planner's own guess. | none; planning only (0 keeps the planner's guess of 10) [SET-FILTERS 2](SET-FILTERS.md#2-the-set-as-one-array-parameter-col--any1int4) | | `sift.agg_par_min_rows` | sift_col, sift_table | 80000 | default | int 0..max | session | Rows a scan must keep (in the units the zones and the keys of its lookups leave) to start the workers its plan asks for, when the planner priced them for the rows the scan reads rather than for the join rows PostgreSQL expects; 0 starts them for any scan. | none; one process for a scan of fewer rows (0 starts the workers whatever it keeps) [AGG](AGG.md) | | `sift.agg_key_skew` | sift_col, sift_table | on | diagnostic | bool | session | Prices the probes of the normal plan for the keys of a lookup join by the rows a key owns by the column's most common values, not by the mean rows of a key. | none; planning only [SET-FILTERS 4](SET-FILTERS.md#4-the-lookup-table-loads-only-the-rows-it-can-match-siftagg_lookup_pushdown) | | `sift.agg_rollup_cost` | sift_col, sift_table | 1 | default | real 0..1000 | session | Scale of the cost of a tuple of a unit's rollup against the scan of an element: a unit is answered from its rollup only when that costs less (0: always). | none; planning only (0 always, large never) [COL-PREAGG 6](COL-PREAGG.md#6-consumption-in-siftagg-built) | | `sift.agg_dense` | sift_col, sift_table | on | diagnostic | bool | session | Aggregates the chunks of counts, sums and averages from the bitmap of their rows, without a list of tuples. | none on the index; off gives the generic path with the same rows [COL-PLAN](COL-PLAN.md#executor) | | `sift.agg_two_level` | sift_col, sift_table | on | diagnostic | bool | session | Aggregates an aggregate over per-row aggregates (UNION ALL arms grouped by a row id) in one scan of the index: the cells of a row are kept in the scan. | none on the index; off gives the generic path with the same rows [COL-PLAN](COL-PLAN.md#executor) | | `sift.agg_dist_stream` | sift_col, sift_table | on | diagnostic | bool | session | Visits the superblocks of a count(DISTINCT) of an integer column in the order of the zone minimum of the column when that keeps its sets small: the values below the minimum of the superblocks to come are dropped. | none on the index; off gives the generic path with the same rows [AGG](AGG.md#counting-distinct-values-over-the-rows-of-a-scan) | | `sift.agg_fuse` | sift_col, sift_table | on | diagnostic | bool | session | Runs the UNION ALL arms that read the same rows in one pass over the index, as the grouping sets of one scan. | none on the index; off gives the generic path with the same rows [COL-PLAN](COL-PLAN.md#one-pass-for-the-arms) | | `sift.agg_join_filter` | sift_col, sift_table | on | diagnostic | bool | session | Tests the integer key leaf of an inner or semi lookup join against the keys of the loaded table before tuples are made (zone pruning, source filters, row bitmaps). | none on the index; off gives the generic path with the same rows [AGG](AGG.md#join-key-filters-and-shared-lookups) | | `sift.agg_rows` | sift_col, sift_table | on | diagnostic | bool | session | Groups the rows of a plan that reads Custom Scan (SiftAgg) nodes with exact count(DISTINCT) in Custom Scan (SiftAggRows), without a sort of the rows. | none on the index; off gives the generic path with the same rows [AGG](AGG.md#counting-distinct-values-over-the-rows-of-a-scan) | | `sift.agg_combo_cache` | sift_col, sift_table | 1048576 | safety | int 16..16777216 | session | Distinct value combinations of an expression PostgreSQL evaluates for the aggregate scan kept before the cache starts again. | memory grows with the combinations kept before the cache starts again [COL-PLAN](COL-PLAN.md#expressions-postgresql-evaluates) | | `sift.explain_refusals` | sift_col, sift_table | off | diagnostic | bool | session | Reports as a notice why a query kept the normal plan instead of Custom Scan (SiftAgg): the first reason each candidate path gave up for. | none when off; the plans are the same on or off [USAGE](USAGE.md#why-a-query-kept-the-normal-plan) | | `sift.count_refusals` | sift_col, sift_table | off | diagnostic | bool | session | Counts in sift.stats() why queries kept the normal plan (refused., limit.), without the notice of sift.explain_refusals. | a few microseconds a query that reaches a candidate path [USAGE](USAGE.md#why-a-query-kept-the-normal-plan) | | `sift.gpu` | sift_col, sift_table | off | opt-in | enum: off, auto, on | superuser | Runs the integer aggregates of a SiftAgg scan on a Vulkan device (docs/GPU.md): off, auto (above sift.gpu_min_rows) or on. A build without Vulkan support (make VULKAN=1) runs on the CPU whatever this says. | needs a build with VULKAN=1 and a device; without them the CPU runs whatever this says [GPU](GPU.md#settings) | | `sift.gpu_min_rows` | sift_col, sift_table | 5000000 | opt-in | int 0..max | session | Rows of the index above which sift.gpu = auto uses the device for a grouped aggregate; an ungrouped one runs there only with sift.gpu = on. | needs a build with VULKAN=1 and a device; without them the CPU runs whatever this says [GPU](GPU.md#settings) | | `sift.gpu_device` | sift_col, sift_table | '' | opt-in | device index or part of its name; empty: the best | superuser | The Vulkan device sift.gpu uses: its index, or a part of its name. Empty: the best one. | needs a build with VULKAN=1 and a device; without them the CPU runs whatever this says [GPU](GPU.md#settings) | | `sift.gpu_cache_mb` | sift_col, sift_table | 256 MB | safety | int 16..1048576 MB | superuser | Device memory, in MB, for the column chunks that stay on the Vulkan device between queries (per backend). | device memory per backend for chunks that stay on the device [GPU](GPU.md#settings) | | `sift.table_write_buffer_limit` | sift_table | 4096 kB | default | int 2048..8192 kB | session | Size of a sift_table write buffer (row format) before it is closed, to be promoted into columns. Rounded down to whole superblocks of 2 MB; a row has to fit one segment. | rounded down to 2 MB; a row must fit one unless table_wide_rows is on [COL-TAM 2.3](COL-TAM.md#23-write-buffer-row-format-the-write-buffer) | | `sift.table_wide_rows` | sift_table | on | default | bool | session | Accepts a row of a sift_table that does not fit one write buffer (up to PostgreSQL's 1 GB), in pages beyond the buffer's table (format 11). Off: such a row is refused. On (the default): the write buffer that takes it is marked format 11, which a build that does not know it cannot write to; rows that fit keep the layout of earlier versions. | none for rows that fit; the buffer that takes a wide row is directory format 11, which a build that does not know it cannot write to [COL-TAM 2.3](COL-TAM.md#23-write-buffer-row-format-the-write-buffer) | | `sift.table_hot_update` | sift_table | on | default | bool | session | An UPDATE of a sift_table that keeps the columns its indexes read gets no entry in them: the entries of the old version lead to the new one (a HOT chain on the tombstone log). Off: every update inserts the new version into every index. Chains written while it was on are still followed. | none for tables whose updates change an indexed column; a BM25 count right after the updates, before VACUUM, reads the chains (+13% instructions, 308 M against 273 M); a table with HOT updates cannot go back to a release before this one (it reads them as plain updates and loses the rows an index reaches through the chain) [COL-TAM 2.4](COL-TAM.md#24-tombstone-segment) | | `sift.table_inline_promote` | sift_table | on | default | bool | session | Promotes the closed write buffers of a sift_table into columns while rows are inserted (VACUUM always does). | off leaves the promotion to VACUUM and autovacuum [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_dict_lookup` | sift_table | on | default | bool | session | Stores the order of the strings too in the dictionary of 1,024 strings or more of the column segments written from now on, so that a test for a string reads the dictionary in place (column format minor 14); the order is 4 bytes a string, kept while the orders of a segment take at most a quarter of its chunks. | the order is 4 bytes a string, +22.9% of a sift_table with a uuid and a text column of 300,000 distinct strings (the cap is 25% of the segment; a column of short repeated strings keeps the compact form); load and VACUUM +3.0% instructions [COLUMN-STORAGE](COLUMN-STORAGE.md#sift_table) | | `sift.table_key_table` | sift_table | on | default | bool | session | The column segments of a sift_table written from now on keep a key table of each foreign key or *_id column (column format minor 22), so that count, sum, min and max WHERE key = x read one record of a segment instead of its units. On by default, where the table fits 3% of the segment. Off: segments are written without them, column format minor 15 or 18, which 3.4 reads. | a table of at most 3% of the segment (+0.27% of the segments' bytes at 900,000 rows, +2.7% at 100,000); +0.4% instructions on a load and promotion, +1.5% on a rewrite [COLUMN-STORAGE](COLUMN-STORAGE.md#sift_table) | | `sift.table_async_promote` | sift_table | off | opt-in | bool | session | A backend that closes a write buffer of a sift_table asks a background worker to promote it, where it promotes it itself. Needs sift.async_maintenance and sift.table_inline_promote. From half of the directory on the writer promotes and merges itself. | off by default: 4 clients of single-row INSERT, tps and p99 equal within noise [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_bulk_groups` | sift_table | on | default | bool | session | COPY, CREATE TABLE AS and REFRESH MATERIALIZED VIEW write the rows of a sift_table straight into column groups, with no write buffer to promote. On by default. Only a table without a unique or exclusion index, a trigger or a foreign key, and not published; other tables keep the write buffers. | a table with a unique or exclusion index, a trigger or foreign key, or a published table keeps the write buffers [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_backpressure` | sift_table | 0 | opt-in | int 0..99 | session | Percent of the directory of a sift_table from which a writer that needs a new write buffer first waits for the maintenance lock and merges. 0 (off): a writer waits only when the directory is nearly full. Above 50, the percent of the entries. | the wait; 0 waits only when the directory is nearly full [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.tomb_log_limit` | sift_table | 8388608 kB (new in 4.2) | safety | int 16..max kB | session | Memory a backend keeps of the tombstone log of a sift_table (64 bytes an entry) before a read or a statement fails. The log holds an entry for each row deleted or updated since the last VACUUM, those of the running statement included; VACUUM is not bound by it, and the maintenance worker is asked for when the log reaches a quarter of it. | a read or a statement that would pass it fails with 54000 naming the setting [SIZING](SIZING.md#settings-that-matter) | | `sift.table_compression` | sift_table | '' | default | auto, none, lz4[:N], zstd[:N], encodings; per column 'auto; col=zstd:9' | session | Compression of the column segments a sift_table writes: auto, none, lz4, zstd or encodings; per column: 'auto; col=zstd:9'. | the sift_col trade-off: zstd:3 -4.7% of the table, scans no slower in a small run (100,000 rows) [COL-TAM 8](COL-TAM.md#8-compression-options-defaults) | | `sift.table_dead_rewrite_pct` | sift_table | 30 | default | int 1..100 | session | Percent of dead rows above which VACUUM rewrites a column segment of a sift_table alone to drop them. 100 never rewrites. | 100 never rewrites [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_auto_defrag` | sift_table | on | default | bool | session | A backend that closes a write buffer of a sift_table, or deletes, updates or reads it, asks a background worker to rewrite its mostly dead column segments and give back its free space when it is worth it, without VACUUM. Needs sift.async_maintenance. With sift.table_auto_defrag_indexes the worker runs a whole VACUUM when the reads of the table since the last pass have cost as much as the pass; without it the worker clears no index entry and VACUUM still has to run for those. | background WAL and I/O, bounded by the budget, the cost delay, the WAL rate and the active-backend check below; the reads of a table with dead rows are read from its rows until the pass, which is asked for when they have cost as much as it [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_auto_defrag_indexes` | sift_table | on | default | bool | session | The background pass of a sift_table whose dead rows reach sift.table_dead_rewrite_pct is a whole VACUUM, index cleanup and tombstone rewrite included. Off: it only clears the dead rows of the column segments and returns space, and the tombstone log and the index entries wait for a VACUUM. | background WAL and I/O, bounded by the budget, the cost delay, the WAL rate and the active-backend check below; the reads of a table with dead rows are read from its rows until the pass, which is asked for when they have cost as much as it [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_auto_defrag_free_pct` | sift_table | 25 | default | int 1..100 | session | Percent of the file that is free blocks from which the background pass of a sift_table gathers and returns them. | background WAL and I/O, bounded by the budget, the cost delay, the WAL rate and the active-backend check below; the reads of a table with dead rows are read from its rows until the pass, which is asked for when they have cost as much as it [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_auto_defrag_delay` | sift_table | 10 ms | safety | int 0..100 ms | session | Cost delay of the background pass of a sift_table, in milliseconds: it sleeps this long (at most four times) each time its page accesses add up to vacuum_cost_limit, and after each segment it rewrites. 0 never waits; at most 100, the most vacuum_cost_delay allows. | background WAL and I/O, bounded by the budget, the cost delay, the WAL rate and the active-backend check below; the reads of a table with dead rows are read from its rows until the pass, which is asked for when they have cost as much as it [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_auto_defrag_max_active` | sift_table | 0 | safety | int 0..10000 | session | Client backends of the database running a statement above which the background pass of a sift_table waits for a quieter moment. The worker looks again after sift.maintenance_naptime, then twice as late each time, 8 times in all, and between the merges of a pass; a later request asks again. 0: never waits. | background WAL and I/O, bounded by the budget, the cost delay, the WAL rate and the active-backend check below; the reads of a table with dead rows are read from its rows until the pass, which is asked for when they have cost as much as it [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_auto_defrag_wal_rate` | sift_table | 0 | safety | int 0..max kB | session | Kilobytes of WAL per second the background pass of a sift_table may write on average. After a step that put it ahead of that rate the worker sleeps until the rate is met. A rewritten segment makes every backend that reads it load its dictionaries again, so the rate also bounds how often that happens. 0: no limit. | background WAL and I/O, bounded by the budget, the cost delay, the WAL rate and the active-backend check below; the reads of a table with dead rows are read from its rows until the pass, which is asked for when they have cost as much as it [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_auto_defrag_coalesce` | sift_table | on | default | bool | session | The background pass of a sift_table merges the small column groups next to the ones it rewrites into one group. | background WAL and I/O, bounded by the budget, the cost delay, the WAL rate and the active-backend check below; the reads of a table with dead rows are read from its rows until the pass, which is asked for when they have cost as much as it [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.table_auto_defrag_budget` | sift_table | 128 MB | safety | int 0..1048576 MB | session | Megabytes of column segments one background pass of a sift_table rewrites by merging at most. The worker passes again while the budget stopped the last one. 0 is no limit. | background WAL and I/O, bounded by the budget, the cost delay, the WAL rate and the active-backend check below; the reads of a table with dead rows are read from its rows until the pass, which is asked for when they have cost as much as it [COL-TAM 7](COL-TAM.md#7-maintenance) | | `sift.plan_scale` | sift_col, sift_table | 1 | diagnostic | real 1..1000000 | session | Plans as if the tables with a sift_col index had this many times the pages and rows (1: as they are). The tables without one, and the statistics of the columns, stay as they are. For the planner's choice at a larger size: compare the plans of EXPLAIN and sift.explain_why at 10 and 100 without the data. PostgreSQL up to 18. | none at 1; a diagnostic [USAGE](USAGE.md#column-indexes-aggregates-and-sift_table) | | `sift.agg_force` | sift_col, sift_table | off | diagnostic | bool | session | A diagnostic: takes the aggregate scan wherever one is possible, whatever the plans cost (an arm that is refused for its shape stays refused). The scan is priced at a millionth of its cost (the paths with workers and without keep their order) and the inline plan of a shared CTE is kept when it has a scan, to time the plan the cost model refused. | none; off; a diagnostic: the scan is priced as free and an inline shared CTE with a scan is kept [USAGE](USAGE.md#column-indexes-aggregates-and-sift_table) | | `sift.agg_scale_horizon` | sift_col, sift_table | 100 | default | real 1..1000000 | session | Size, as a multiple of the table, up to which an inline shared CTE that is a close call must stay the better plan (1: only the size it is). Used where the scans of the inline plan cost less than the work of the shared CTE but not by the margin: the plans are made again at the square root of the horizon and at the horizon times the rows (sift.plan_scale), and the inline one is kept if it wins at both. | none at 1; two more plans of the statement where the scans cost less than the shared work but not by the margin [USAGE](USAGE.md#column-indexes-aggregates-and-sift_table) | | `sift.col_leaf_cache_kb` | sift_col | 16384 kB | default | int 0..262144 kB | superuser | Shared memory, for all backends together, that keeps what the zones of the units of a sift_col index say to the planner (a record for each leaf and a sample of the unit zones), so that a new backend does not read them all (0: off). The table is allocated, at the size of this setting, by the first backend that plans a statement over a sift_col index after the server starts; a later change turns the use of it on and off (0), not its size. A record is about 33 kB for each integer leaf and 0.4 kB for the others; a record that does not fit is not kept, and the oldest goes when the table is full. | one table for the server, whatever the number of backends: the statistics of an index are about 33 kB for each integer leaf and 0.4 kB for the others, 16 MB by default (256 MB at most); where it cannot be allocated the backend logs once and folds them itself; the size is taken when the first backend allocates it (a restart to change it); the record of an index is read again from the zones when its segments or options change [USAGE](USAGE.md#is-the-index-clustered-and-what-did-the-scans-do) | | `sift.col_dict_shared_cache_kb` | sift_col | 65536 kB | default | int 0..4194304 kB | superuser | Shared memory, for all backends together, that keeps the parsed dictionaries of sift_col segments (128 kB or more each), so that a new backend or a pooled connection reads a dictionary another backend parsed in place instead of parsing it (0: off, each backend parses and keeps its own, up to the dict_cache_kb of the index). The ring of records is allocated, at the size of this setting, by the first backend that keeps a dictionary after the server starts, and its size is recorded for the other backends: a later change turns the use of it on and off (0), never its size, which changes at the next start of the server. The memory is in addition to shared_buffers, is taken whole when the ring is made, and with dynamic_shared_memory_type = posix comes from /dev/shm (at most a quarter of it is used, with a message in the log) and counts in the shared memory of the container. A record is about 21 bytes for each distinct value, and 16 to 32 more with its hash table; a record over half of the ring is not kept. | one ring of records for the server, whatever the number of backends, taken whole from dynamic shared memory when the first backend keeps a dictionary (65,536 kB by default, 4,194,304 kB at most, so that the setting cannot ask for a size a server is not likely to have): this memory is in addition to shared_buffers, is not reclaimable like page cache (with dynamic_shared_memory_type = posix it counts against /dev/shm and the shared memory of a container, and is swap-backed), and gets no huge pages; with posix at most a quarter of /dev/shm is used and the log says so; where the ring cannot be had the backend logs once and parses its own; a dictionary under 128 kB or over half of the ring is not kept; the context is superuser, as the other shared caches: the value of the backend that creates the ring wins (a superuser who sets a size in a session before the ring exists decides the size for the server), so to set the size for the server set it in postgresql.conf and restart (a later change of the setting only turns the sharing on and off, 0 for off, and never resizes or frees the ring). The first backend after a server start parses each dictionary it meets and writes it to the ring: 26 to 167 ms once per dictionary (100,000 to 1,000,000 distinct strings), the others find it (1.4 to 2.8 ms for their first statement, a b-tree 2.0 to 2.7); no setting pre-warms it, the first statement does. A ring smaller than twice the record of a dictionary, or 0, makes each backend parse its own once and keep it in its 64 MB cache (an equality over 500,000 to 700,000 hashes: 23 to 47 ms, then 0.1 ms). [COLUMN-STORAGE](COLUMN-STORAGE.md#dictionaries-and-text) | | `sift.eval_methods` | sift_col | '' | diagnostic | empty, or the name penalty_maintenance | superuser | Comma-separated developer evaluation methods that are on. | The method can add maintenance work: a VACUUM and a rewrite that the reads priced [OPTIONS](OPTIONS.md) | | `sift.col_auto_connect` | sift_col | on (new in 4.3) | default | bool | session | CREATE INDEX and REINDEX of a sift_col index connect it to the sift_col indexes of the tables its validated foreign keys reference. On: an index without the option connect that holds the column of a validated single column foreign key to an int2, int4 or int8 key of a heap table that has a sift_col index tracking that key is connected to it (the choice is written into the option as 'auto: ..', and a NOTICE names it). Off: only an index with connect = 'auto' or an explicit connect option is connected. The first child connected to a parent index holds the writers of the parent table out from the end of its scan to the end of the statement. | the parent index keeps a journal (one 32-byte record for each entry that an UPDATE, DELETE or key change ends); the first child of a parent holds the writers of the parent table out from the end of its scan to the end of the statement; the segments of the child are column format minor 44, which 4.2 and older refuse; one boolean leaf for each connection says whether the parent row was found [COL-CONNECT](COL-CONNECT.md#automatic-connections) | | `sift.col_connect_refresh_pct` | sift_col | 10 (new in 4.3) | default | int 1..100 | superuser | Percent of the rows of a parent table whose keys changed since the stamp of a segment of a connected sift_col index from which the automatic rewrite fills the segment again. A read patches the rows of the changed keys from the join, so a few changed keys cost less than the rewrite of the segment (the found flags it holds are as of its stamp). A segment that was never filled (the first child of a parent, whose journal starts after its scan) or whose parent index was rebuilt is rewritten whatever the share, within the rules of sift.col_auto_merge (the WAL budget included). | a rewrite of the segments of the connected index (the fill reads the keys of the parent table once); the rewrite is within the WAL budget of sift.col_auto_merge [COL-CONNECT](COL-CONNECT.md#refresh) | The `sift.table_*` settings that act on one table (`write_buffer_limit, wide_rows, inline_promote, async_promote, backpressure, hot_update, bulk_groups, dict_lookup, key_table, compression, dead_rewrite_pct, auto_defrag, auto_defrag_free_pct, auto_defrag_budget, auto_defrag_coalesce, auto_defrag_indexes`) can also be set **for one sift_table**: `SELECT sift.table_set_option('events', 'write_buffer_limit', '8MB')` (the owner of the table; the value is checked as `SET` checks it), `sift.table_get_option('events', 'write_buffer_limit')` for the value in force, `sift.table_reset_option('events', 'write_buffer_limit')` to go back to the setting, and `SELECT * FROM sift.table_options('events')` for every option with its value, its source (`table`, `setting`, `default`) and its default. The option of a table beats the setting whatever its source, including a `SET` of the session: reset the option to test a setting on that table. The options are dumped, restored and upgraded with the database ([COL-TAM 8.1](COL-TAM.md#81-options-of-one-table)). Index options go in `WITH (...)` at `CREATE INDEX`; `write_buffer_limit`, `vacuum_segments`, `merge_fanin`, `dead_rewrite_pct` and `vacuum_truncate` can be changed afterwards (`ALTER INDEX ... SET (...)`), the rest need `REINDEX`. | Index option | Index | Default | Class | Values | Description | Cost | |---|---|---|---|---|---|---| | `k1` | `sift` | 1.2 | default | real 0..1000 | BM25 term frequency saturation | REINDEX to change; no size effect [SIZING](SIZING.md#features-and-their-costs) | | `b` | `sift` | 0.75 | default | real 0..1 | BM25 document length normalization | REINDEX to change; no size effect [SIZING](SIZING.md#features-and-their-costs) | | `analyzer` | `sift` | text | opt-in | enum: text, keyword | Analyzer: text, or keyword to index each whole value as one term (facets, REINDEX to apply) | REINDEX to change [FIELDS-FACETS](FIELDS-FACETS.md#facets-siftfacet_counts) | | `stemmer` | `sift` | '' | opt-in | none or en | Stemmer: en (Snowball English) or none | REINDEX to change [SIZING](SIZING.md#features-and-their-costs) | | `case_folding` | `sift` | true | default | bool | Fold case | REINDEX to change [SIZING](SIZING.md#features-and-their-costs) | | `accent_folding` | `sift` | true | default | bool | Strip accents | REINDEX to change [SIZING](SIZING.md#features-and-their-costs) | | `positions` | `sift` | true | default | bool | Store word positions, so phrase queries are decided by the index instead of rechecked in the heap (REINDEX to apply) | index about 2.5x the size of positions=false (130.9 against 52.5 MiB on 218,058 articles); REINDEX to change [SIZING](SIZING.md#disk) | | `prefix_index` | `sift` | 0 | opt-in | int 0..15 | Index the first 1..n characters of every word, so prefix queries of up to n characters need no expansion (0 = off, REINDEX to apply) | a larger index (more terms, growing with n); REINDEX to change [USAGE](USAGE.md#queries) | | `fields` | `sift` | '' | opt-in | names of the leading sections of the text, as name:word | Names of the first sections of the indexed text (split at chr(29)) that queries can search apart, as name:word | REINDEX to change [FIELDS-FACETS](FIELDS-FACETS.md#fields-siftsections-and-siftfield_query) | | `write_buffer_limit` | `sift` | 4096 | default | int 64..1048576 | Write buffer size in kB before it is sealed for promotion | 1024 against 4096 kB (100,000 documents): promotion 37 to 13 ms but 3.7x as often, 16-client INSERT 107k to 87k rows/s, index 72 to 98 MB [USAGE](USAGE.md#maintenance-and-operations) | | `vacuum_segments` | `sift` | 2 | default | int 1..128 | Directory entries (segments, the write buffer included) VACUUM merges the index down to; more keep VACUUM short and queries slower | 2 against 16 (60 segments of 50,000 documents): VACUUM 0.19 s against 0.10 s, count(*) 0.08 against 0.35 ms [USAGE](USAGE.md#maintenance-and-operations) | | `merge_fanin` | `sift` | 4 | default | int 2..32 | Segments of a size tier that trigger a merge; a lower value merges more often and keeps fewer segments, a higher one merges less | lower merges more often (more write work), higher keeps up to merge_fanin - 1 segments a tier [USAGE](USAGE.md#maintenance-and-operations) | | `build_segments` | `sift` | 0 | diagnostic | int 0..64 | Segments written by CREATE INDEX, 0 for one per build participant | a serial build [USAGE](USAGE.md#maintenance-and-operations) | | `dead_rewrite_pct` | `sift` | 50 | default | int 1..100 | Percent of dead documents above which maintenance rewrites a lone segment to drop them (100 never) | 5 against 50 (400,000 documents, 10 update rounds): VACUUM 37.8 s against 20.2 s, index 133 against 92 MB; 100 never rewrites [USAGE](USAGE.md#maintenance-and-operations) | | `vacuum_truncate` | `sift` | true | default | bool | VACUUM gives the free blocks at the end of the index file back to the operating system (takes a brief exclusive lock when it can) | a brief exclusive lock when it is free; none while a physical standby streams (a cut would stall its replay for as long as its longest query on the index runs), the free end then stays in the file for the next rewrite [USAGE](USAGE.md#maintenance-and-operations) | | `write_buffer_limit` | `sift_col` | 4096 | default | int 64..1048576 | Write buffer size in kB before it is sealed for promotion | the trade-off measured for the sift index (1024 against 4096 kB: 3.7x as many promotions); ALTER INDEX applies from the next seal [USAGE](USAGE.md#maintenance-and-operations) | | `dead_rewrite_pct` | `sift_col` | 0 | default | int 0..100 | Percent of dead rows above which maintenance rewrites a lone segment to drop them (0 when the scans of its dirty units cost more than the rewrite, 100 never) | a rewrite writes the whole segment and re-encodes every live row (4,200 instructions a row at 7 bytes, 35,400 at 75); 100 never rewrites [USAGE](USAGE.md#maintenance-and-operations) | | `vacuum_truncate` | `sift_col` | true | default | bool | VACUUM gives the free blocks at the end of the index file back to the operating system | a brief exclusive lock when it is free; none while a physical standby streams (a cut would stall its replay for as long as its longest query on the index runs), the free end then stays in the file for the next rewrite [USAGE](USAGE.md#maintenance-and-operations) | | `paths` | `sift_col` | '' | default | attname: a.b type, a.b[]{f type}, *:type; ...; empty or auto: read from a sample of the documents at CREATE INDEX | jsonb paths shredded into columns, by index attribute name | the declared leaves are stored once more; the chosen paths are written to the option as 'auto: spec' and a REINDEX keeps them; a table with no key that qualifies still refuses a jsonb column [COL-SHRED](COL-SHRED.md#declaring-paths-reloption-paths) | | `compression` | `sift_col` | '' | default | auto, none, lz4[:N], zstd[:N], encodings; per column 'auto; col=zstd:9' | chunk compression: auto, none, lz4, zstd, encodings; per column: 'auto; col=zstd:9' | zstd:3 builds 17% slower and a grouped scan reads 1.4x slower than auto (100,000 rows); REINDEX to apply [COL-TAM 8](COL-TAM.md#8-compression-options-defaults) | | `preagg` | `sift_col` | '' | default | default, auto (new in 3.4), off, or cube(dim: measure, ...; ...) | pre-aggregates of every unit: default, off, or cube(dim: measure, ...; dim2: ...) | the default takes at most 40% of the encoded bytes of a unit as built (every measure of a kept dimension and its min and max; at least 8 kB; about a quarter of that on disk for dimensions of few values) and gives the cube's 16 dimensions to the columns in their order; auto ranks the plain columns by data at CREATE INDEX instead (the ones whose histograms fit 256 groups in a unit first) and writes the list as 'auto: a, b'; off keeps leaf sums only; applies to segments written afterwards [COL-PREAGG 2](COL-PREAGG.md#2-what-is-precomputed-written-when-a-unit-is-built-merged-promoted-rewritten-by-vacuums-merge-and-by-every-worker-of-a-parallel-build) | | `rollup_leaves` | `sift_col` | '' | default | leaf, leaf, ..., auto or off | the leaves of array elements and map values that the rollup of every unit groups by: 'leaf, leaf, ...', 'auto' (default) or off | explicit names hold up to 4096 tuples a unit with no byte budget; the default rule has a budget of 2% of the unit per list [COL-PREAGG 2](COL-PREAGG.md#2-what-is-precomputed-written-when-a-unit-is-built-merged-promoted-rewritten-by-vacuums-merge-and-by-every-worker-of-a-parallel-build) | | `rollup_on_demand` | `sift_col` | true | default | bool | A scan that reads units for lack of their rollups asks the background worker to rebuild them (default on) | a background rewrite of the segment; the query itself writes nothing [COL-PREAGG 7](COL-PREAGG.md#7-on-demand-refresh-of-the-rollups-srccolrollupc) | | `cluster_advice` | `sift_col` | true | diagnostic | bool | sift.cluster_advice() may recommend a physical order or a key_cut for the table of this index, and CREATE INDEX may suggest key_cut (default on; maintenance only, not part of the format) | none; maintenance only, not part of the format [USAGE](USAGE.md#is-the-index-clustered-and-what-did-the-scans-do) | | `dict_lookup` | `sift_col` | auto | default | enum: auto, on, true, off, false | A dictionary of 1,024 strings or more of the segments written from now on is stored in blocks that are read in place, without loading it whole, where that adds at most 3% to the segment (ids, hashes, uuids and long strings; short strings that repeat words stay in the compact form); the option adds the order of the strings, so that an equality, an IN list or a prefix test reads it in place too: auto (default) for the columns of point_columns, key_columns and return_columns, on for every column, off for none (the segments are column format minor 14; the order is 4 bytes a string) | format minor 14; the order is 4 bytes a string: +10% to +70% of an index of one high-distinct text or uuid column, and since 3.6 the hash of the strings 5 bytes more (minor 25), for every dictionary in blocks of 1,024 strings or more too (a plain index: uuid +1.0 MB of 3.4 MB on 200,000 rows), none with off [COLUMN-STORAGE](COLUMN-STORAGE.md#dictionaries-and-text) | | `cube_minmax` | `sift_col` | true | default | bool | The cube of every unit written from now on also holds the minimum and maximum of each integer measure per group; the segments are column format minor 7 (default on) | format minor 7; +0.17% of the index alone, about +1.5% with superunit 16; insert, promotion and merge CPU inside the noise [COL-PREAGG 2](COL-PREAGG.md#2-what-is-precomputed-written-when-a-unit-is-built-merged-promoted-rewritten-by-vacuums-merge-and-by-every-worker-of-a-parallel-build) | | `array_lengths` | `sift_col` | auto | default | enum: auto, on, true, off, false | The array columns of the index keep their cardinality and array_length(x, 1) as two integer columns of their own, so that cardinality(x), array_length(x, 1) and x IS [NOT] NULL are read without the arrays (about 1 byte a row for each array column): on for every array column, off for none, auto (default) is on for the index that CREATE INDEX or REINDEX builds and off for an index built before the option; after a change the table takes no write until the REINDEX | about 1 byte a row for each array column and lane (+10% of the index on six array columns, 1,000,000 rows); a build, merge or promotion writes two more columns for each array column; after a change the table takes no write until the REINDEX | | `rollup_columns` | `sift_col` | '' | default | col, col or col, col: measure, ...; auto (the default), off | the scalar columns that the rollup of the rows of every unit written from now on groups by: 'col, col' or 'col, col: measure, ...', 'auto' (default) or 'off'; the segments are column format minor 8 | format minor 9; +5.3% of the index, build +25% (two columns); auto picks the pair at CREATE INDEX (+11% of the index and 3.5 to 0.66 ms on the pair's GROUP BY at 300,000 rows) [COL-PREAGG 2](COL-PREAGG.md#2-what-is-precomputed-written-when-a-unit-is-built-merged-promoted-rewritten-by-vacuums-merge-and-by-every-worker-of-a-parallel-build) | | `calendar_columns` | `sift_col` | '' | default | col, col, all, auto or off; empty and auto: every date, timestamp and timestamptz column | the date, timestamp and timestamptz columns whose calendar cells (days, else months, else years) every unit written from now on keeps, so that a GROUP BY of date_trunc, extract or a cast of them, and a range, are answered from the cells; 'col, col', 'all' or 'off' (default all, unless the index has JSON paths; the cube holds at most 16 dimensions with the default dims, a list that does not fit is refused and 'all' keeps the columns that fit; the segments are column format minor 8) | format minor 8; +1.0% of the index for one column, +1.8% for two (superunits no longer merge the cells); with JSON paths only when named (3.4: `all` or a list; +9.3% to +12.1% of the main index of an application); the cube holds at most 16 dimensions and the calendar cells come first: the default dims give way (the dates' own histograms, then the last columns; a NOTICE names them), a cube(...) of the user does not, so a list that does not fit is refused, `all` keeps the columns that fit and the build names the ones it left out [COL-PREAGG 2](COL-PREAGG.md#2-what-is-precomputed-written-when-a-unit-is-built-merged-promoted-rewritten-by-vacuums-merge-and-by-every-worker-of-a-parallel-build) | | `key_columns` | `sift_col` | '' | default | auto (the default), off, or a list of columns: int2, int4 and date for a key list or bitmap, also integer, text and uuid for a filter | the columns whose keys every unit written from now on keeps, as a key list or a bitmap where that takes a few percent of the unit (a count(DISTINCT column) over whole units reads no row, an equality or IN test skips the units that lack the key) and, for a column named here, as a bloom filter where it does not; 'col, col', 'off' or 'auto' (the int2, int4, int8 and date foreign key columns and the columns named *_id that are not unique, or unique when the index is key cut or the heap is not in their order, and that the cube does not hold; chosen by CREATE INDEX and REINDEX when the option is not set); the segments are column format minor 10, 15 for a key list or bitmap, 23 for a list of runs (default auto) | format minor 15; auto keeps a key list or bitmap of the int2, int4 and date foreign keys and *_id columns that fit 3% of a unit: +1.1% to +1.4% of the index on the copies; a named filter column of random keys adds no bytes and no gain on them (build within noise at 100,000 rows) [COLUMN-STORAGE](COLUMN-STORAGE.md#counts-and-keys) | | `key_table` | `sift_col` | true | default | bool | The segments written from now on keep, for each key_columns leaf, a table of its distinct keys with the rows, sums, minima and maxima of each, where that adds at most 3% to the segment, so that count, sum, min and max WHERE key = x read one record of a segment, not its units; the segments are column format minor 22, which 3.4 and older libraries cannot read (default on) | format minor 22; a 3.2 library cannot read it; a table of at most 3% of the segment (+0.46% to +2.2% of the segments' bytes at 300 keys); +2.6% instructions on CREATE INDEX, +4.3% on a merge, +0.9% on insert and promotion [COLUMN-STORAGE](COLUMN-STORAGE.md#counts-and-keys) | | `count_columns` | `sift_col` | '' | default | empty (default: automatic), off, or a list of text columns (a named one also gets its dictionary read in place); text columns (varchar and text with a deterministic collation; a dictionary of 1,024 strings or more) | the text columns whose dictionary of 1,024 strings or more every segment written from now on stores with the rows of each string in byte order (the columns whose dictionary is read in place do so unless this is off, the ones named here also get that order), so that count(*) of a prefix (LIKE 'abc%'), a range or an equality of the column over segments with no dead row reads two positions of the dictionary and no row; 0.1 to 1 byte for each distinct string, the segments are column format minor 24, which only 3.5 and later read (default empty: automatic; off: none) | format minor 24; a 3.2 library cannot read it; 0.13 bytes for each distinct string of unique strings (+131 KB, +0.17% of the segments of the 8-column test table at 1,000,000 unique strings), about 0.4 bytes for strings of one to three rows; +0.44% instructions on CREATE INDEX [COLUMN-STORAGE](COLUMN-STORAGE.md#counts-and-keys) | | `point_tid_columns` | `sift_col` | '' | opt-in | integer, text or uuid columns (the leaves of point_columns) | the plain integer, text and uuid columns whose point directory lists the heap addresses of the rows, not their numbers in the segment (an equality or a short IN list of the access method then returns the rows' addresses from the directory without reading a unit, and the heap checks them; a unit with a dead row is read instead); the segments are column format minor 33 or more (37 from 3.7), which only 3.7 and later read, and the lists take 1 to 5 bytes per row (default none) | format minor 37; a 3.2 library cannot read it; the size of point_columns within 3% (a key of many rows takes 1 to 3 bytes a row, not 4); the build 65% longer, a promotion 5% longer, inserts unchanged; not for an index-only scan, which reads the units the addresses name [COLUMN-STORAGE](COLUMN-STORAGE.md#point-lookups) | | `key_cut` | `sift_col` | '' | default | auto (the default), off, or up to 4 integer, date, timestamp, numeric or float columns of the index, most significant first | the integer, date, timestamp, numeric and float columns, most significant first, whose range cuts the units that promotion, merge and REINDEX write (a key-cut unit lists its TIDs; the segments are column format minor 17); 'col, col', 'off' or 'auto' (CREATE INDEX and REINDEX sample the heap and cut by the first key-shaped column whose equality reads many units and that no ordered leaf competes with; a unit is a superblock otherwise; default auto) | format minor 17; a 3.2 library cannot read it; CREATE INDEX and REINDEX read a sample of the heap to decide (at most 400,000 rows, or 16 superblocks when they hold more) (0.07 to 0.25 s on a 1,500,000-row table); chosen, the build sorts the rows by the key and writes each once in its unit: build 2.30 to 3.25 s serial and 1.29 to 1.83 s parallel (+41%), a promotion of 50,000 rows 0.047 to 0.057 s (+21%), insert unchanged (0.20 s); the index 13% smaller serial and 5% smaller parallel on the measured table; the units list their TIDs (0.65 to 1.5 bytes a row, by how scattered the key is), so an index of independent columns grows 30% at 2 leaves, 9% at 6, 5.3% at 11, and the build then only advises; segments are minor 17, which 3.2 and older refuse [COLUMN-STORAGE](COLUMN-STORAGE.md#cutting-the-units-by-a-key) | | `order_array_columns` | `sift_col` | '' | default | auto (the default), off, or a list of integer, date, timestamp, numeric(p <= 18) or text columns (auto chooses text and numeric columns too) | the integer, date and timestamp columns whose segments written from now on also keep every row's key and heap address sorted, so that a range of the column of up to 16,384 rows and ORDER BY column LIMIT n above 32 read the entries between the bounds, whatever the number of units; 4 to 7 bytes a row for each column; the segments are column format minor 21; 'col, col', 'off' or 'auto' (CREATE INDEX and REINDEX choose up to two columns of 100,000 rows or more whose values are scattered over the heap, that is the default) | format minor 38; a 3.2 library cannot read it; auto: 4.4 bytes a row for a 4-byte key and 5.6 for an 8-byte one, plus the sorted run it extends (2.2 and 3.5): a 7-column index of 800,000 rows 24.3 to 29.4 MB (+21%), +7% of the 93 bytes a row of an index with point_tid_columns; the build +22% instructions (5.99G to 7.32G); segments are minor 38, which 3.6 and older refuse (minor 40 where the array holds the ends of a segment only, which 3.7 and older refuse); a text column 7.4 bytes a row (+14% of a 9-column index), CREATE INDEX +81% under the C collation and +390% under pt_BR (the sort of the strings), segments are minor 42, which 3.7 and older refuse [COLUMN-STORAGE](COLUMN-STORAGE.md#order-and-top-n) | | `order_array_group` | `sift_col` | '' | default | auto (the default; 'auto: col' keeps col a candidate for the group), off, or one integer, date or timestamp column, not in order_array_columns | a column (integer, date or timestamp) that the arrays of order_array_columns are grouped by: they keep the rows sorted by this column and then the key, so that WHERE group = x ORDER BY column LIMIT n reads n entries of the group x whatever the number of units; a row with no value of it is not in them, and ORDER BY column LIMIT n without the condition reads the units; 3 to 7 bytes a row for each column (default none) | format minor 38; a 3.2 library cannot read it; auto: 6.0 B a row for each date, timestamp or integer array (the plain order stays; the entity gives back its own array of 2.4 B): 500,000 rows, random timestamps, a 9-column index 25.2 to 28.5 MB (+13.2%); 300,000 rows, a 7-column index with a date of 730 values and an entity of 3,000: 3.06 to 4.27 MB (+39.7%), the integer and date arrays of a 7-column index of 24 B a row +37%. With a column named: 6.0 B a row for each array column over the runs (the group is a third part of each entry), a row with no group is not in the arrays [COLUMN-STORAGE](COLUMN-STORAGE.md#order-and-top-n) | | `key_order` | `sift_col` | '' | opt-in | one integer, date, timestamp, numeric or float column of the index, or 'group, key' (two) | the integer, date, timestamp, numeric and float column, or 'group, key', whose order the rows are kept in: for one column it stands for key_cut and order_array_columns on it, for 'group, key' for order_array_columns on the key and order_array_group on the group (key_cut is left to the build), each while the option itself is not set; for a table that is stored in the order of the key (default none) | one column: the index of a sorted table -21% (0.58 to 0.46 MB, 100,000 rows; the point directory it no longer takes was +64%), of a table in arrival order -14% (1.60 to 1.37 MB); 12 rounds of churn (1% inserts, 1% updates, 1% deletes): 18 to 41% fewer instructions than the default and the same WAL, but 10 to 24% more WAL and an index 18 to 57% larger than key_cut alone. 'group, key' (300,000 rows, 1,000 tenants): index -21% (10.5 to 8.4 MB), build -52% (7,144M to 3,437M instructions), VACUUM and the insert into a hot tenant equal; count(*) of one tenant 0.32M to 1.58M (0.13 to 0.21 ms); ORDER BY tenant, ts OFFSET 10,000 4.7M to 95M; an IN list of 60 tenants +12%, of 10 entities over 2,000 (300,000 rows) 3.5M to 12.6M; the index +47% in arrival order (4.25 to 6.25 MB) and -15% on a table sorted by the entity. On a partitioned table it is neutral to worse (hash64: latest 20 of the top tenant 1.52M to 4.17M, OFFSET 1000 4.1M to 15.5M, index +24%): it replaces nothing there [USAGE](USAGE.md#column-indexes-aggregates-and-sift_table) | | `order_layout` | `sift_col` | '' | default | auto (the default), whole, compact, or a list that names the columns of each layout: 'compact: ts, created; whole: id' (a column not named is auto); it adds no array, order_array_columns says which columns have one | how the order arrays of order_array_columns are laid out: 'whole' keeps the key of every row, 'compact' keeps the heap address of every row and its key rounded to a bucket (the exact key is read from the table row where a scan needs it); 'auto' (default) keeps each chunk of 512 entries compact where that is at least a fifth smaller and whole elsewhere; a list gives a layout to columns: 'compact: ts, created; whole: id'. It never adds an array: order_array_columns says which columns have one | format minor 41; a 3.2 library cannot read it; compact: 3.6 bytes a row for a microsecond timestamp (1,000,000 rows) against 6.5 for whole; keys that tie much (a date of 40 values over 20,000 rows) cost more compact than whole, which auto leaves whole; compact rounds every key and has no such guard, so a column that ties in runs (half unique timestamps and half 50 days) reads up to 4.3 times the blocks of whole for a bound on a run, which auto avoids by keeping a chunk with a run of 8 equal keys or more whole; the first and last 8,192 entries of a segment and its NULL rows stay whole, and so does a segment of fewer than 32,768 values; segments with a compact chunk are column format minor 41, which 3.7 and older cannot read [COLUMN-STORAGE](COLUMN-STORAGE.md#order-and-top-n) | | `track_changes` | `sift_col` | 'auto' | default | auto (the default): the primary key of one column when the index holds it and the table is one a statement trigger hears, else off with a NOTICE; or one column of the index that a unique index of the table covers by itself, not null; or off; or ctid: the address of each version a statement ends, with no key column (a table without a primary key, an index that does not hold it), by AFTER ROW triggers, for a heap table | the column that identifies a row (off, auto = the primary key when the index holds it, else off with a NOTICE, or a column with a unique index of its own, NOT NULL): the index records the entries of the row versions that stopped being live (an UPDATE, a key change, a DELETE) in a tombstone log, so that a read of rows changed and not yet vacuumed can leave the heap out. It installs two internal statement triggers on the table (AFTER UPDATE and AFTER DELETE), dropped with the index, and writes a baseline of the dead versions in a pass over the heap (column format minor 43, which only 4.0 and later read); it adds the column to point_tid_columns | format minor 43; a 3.2 library cannot read it; UPDATE of 5% of 100,000 rows covered by the index 144.6 to 207.7 M instructions (+43.6%, WAL +1.7%); 100 rows 3.51 to 4.77 M (+35.9%); one row 0.36 to 0.41 M (+14%); key change of 1% 66.4 to 86.9 M (+30.8%); DELETE of 2% 53.6 to 72.3 M (+34.9%); INSERT +0.6%; CREATE INDEX and REINDEX +187 M (672 to 858 M: the baseline pass over the heap and the log); the tombstones of a statement are held in the backend and appended by the page at the commit; each further index of a table adds its own probe, trigger and log to a statement (UPDATE of 5% of the rows with 1, 2 and 3 indexes 207.7, 222.2 and 233.4 M, key change of 1% 86.9, 99.8 and 111.9 M; plain INSERT and COPY do not look); the first read of the log of a backend takes about 5 M instructions and 1.8 ms for 60,000 entries; one 32-byte log entry for each ended version until VACUUM [USAGE](USAGE.md#column-indexes-aggregates-and-sift_table) | | `hll_columns` | `sift_col` | '' | opt-in | int2, int4, int8, date, timestamp, timestamptz, text, varchar or uuid columns | the int2, int4, int8, date, timestamp, timestamptz, text, varchar and uuid columns whose distinct values every unit written from now on keeps as a HyperLogLog sketch (sift.approx_count_distinct reads one sketch a unit, not the rows; count(DISTINCT) stays exact); the segments are column format minor 18 (default none) | format minor 20; a 3.2 library cannot read it; a sketch of 8 B + 3 B a register to 12,296 B a unit and column (169 B for 50 values, 8,209 B for 2,700, 12,307 B above 4,096): the index 23% larger for four columns of a million rows, the build 70% longer; segments are minor 20, which 3.3 and older refuse [COLUMN-STORAGE](COLUMN-STORAGE.md#counts-and-keys) | | `return_columns` | `sift_col` | '' | opt-in | text, varchar or uuid columns | the text, varchar and uuid columns an index-only scan returns from the index (their strings are read from the dictionary of the segment; default none: a query that needs one reads the heap) | no extra storage: the strings are the dictionary the index keeps [COL-SCAN](COL-SCAN.md#how-it-works) | | `superunit` | `sift_col` | 16 | default | int 0..256 | Units per superunit summary: the sums and the cube of every group of this many consecutive units of a segment are stored once more, merged, and answer a query that no predicate cuts through from one read; the segments written from now on are column format minor 12 (0: none, else 2 to 256; default 16) | format minor 12; +1.0% of the index (groups of 64: +0.8%, of 4 to 8: +1.3% to +1.5%); a range query on a 2-unit table 0.10 to 0.13-0.15 ms [COL-PREAGG 10](COL-PREAGG.md#10-superunits-one-summary-for-a-group-of-units-column-format-minor-12-reloption-superunit-default-16) | | `connect` | `sift_col` | '' (new in 4.3) | opt-in | fk -> [schema.]parent(key); empty or off: none | connects this index to the sift_col index of the table it joins: 'fk -> [schema.]parent(key)' names the column of this index that holds the parent key, the parent table (in the schema of this index's table when not qualified) and its key column; the parent columns that queries read come from the parent's own index. The parent index (the one that tracks the key) keeps a journal of the keys its updates and deletes end, and this index depends on it, so DROP INDEX of the parent index needs CASCADE (column format minor 44, which only 4.3 and later read); the key is an int2, int4 or int8 column (default none) | format minor 44; a 3.2 library cannot read it; one 32-byte journal record for each entry of the parent that an UPDATE, DELETE or key change ends, only while a child index is connected; a parent without a child writes none [COL-CONNECT](COL-CONNECT.md) | `ALTER INDEX ... SET` of the options below takes effect at the next `VACUUM`, seal, merge or worker step, with no `REINDEX`; an index built before an option exists keeps working with the default. **`vacuum_segments`** (1 to 128; out of range is an error): an index that already has N segments or fewer is left as it is. A segment more than `merge_fanin` times the size of the others that would merge with it stays, since rewriting it costs more than the count gains (a 0.1 MB segment next to one of 11 MB would rewrite the 11 MB at every `VACUUM`); an index may thus keep more than N segments, and `sift.merge(index, true)` merges them all. A segment over `dead_rewrite_pct` percent dead is rewritten at any N. A lower N means more merging per `VACUUM` and faster queries (60 segments of 50,000 documents): | `vacuum_segments` | `VACUUM` | `count(*)` | ranked top-10 | |---|---|---|---| | 2 (default) | 0.19 s | 0.08 ms | 0.18 ms | | 4 | 0.15 s | 0.14 ms | 0.21 ms | | 8 | 0.12 s | 0.22 ms | 0.28 ms | | 16 | 0.10 s | 0.35 ms | 0.33 ms | **`merge_fanin`** (2 to 32, default 4, 8 before 4.0; out of range is an error): the segments of one size tier (a factor of `merge_fanin` in size, starting at `write_buffer_limit`) that make the worker, inserters that need room and `sift.merge()` merge; one merge takes at most twice that. More than `greatest(32, 2 * merge_fanin)` directory entries merge the smallest segments whatever their tier, and inserters merge themselves from 112 of the 128 entries, so no value in range can fill a directory. Merging 39 segments of similar size leaves 1 segment at 2 to 8, 8 at 16, 6 at 17 and 1 at 31 and 32 (`test/sql/merge_fanin.sql`). **`sift.maintenance_cost_delay`**: after every 16 pages (128 kB) its merge reads or writes the worker sleeps that many milliseconds (a 10 MB index: 0.27 s at 0, 5.3 s at 20, at most 6.4 MB/s). The worker reads its database's settings, so set it with `ALTER DATABASE ... SET` or in `postgresql.conf`. It never delays `VACUUM`, `sift.merge()`, merges inserters do for room, or the promotions writers wait for. **`sift_col` churn.** `VACUUM` rewrites a segment alone only above `dead_rewrite_pct` percent dead rows (default 0: by cost, see USAGE), but a `DELETE` or `UPDATE` spread over the table puts a dead row in every unit long before that, and a unit with a dead row answers from its rows, not its pre-aggregates. `sift.col_auto_merge` (above) answers it: the `VACUUM` that sees the rule satisfied starts a worker that does what `sift.col_promote` plus `sift.col_merge(index, true)` do, without blocking readers or writers. - A `key_cut` index in partitions takes the rows of new promotions as overlays, which hold rows of every key. One that grew to half a partition meets every partition, as the runs of a build do, but only overlapping segments that make up an eighth of the index or more are a build's (the bytes whose removal leaves none overlapping): the rewrite of those is not held by `sift.col_auto_merge_wal_pct`, that of a grown overlay is, like any churn. Before, each promotion round that grew an overlay to that size rewrote the whole index (24 rounds of 2,000 inserts and 10% updates into a 1,000,000-row index: 4 rewrites, 21.7 KB of index pages written per inserted row; now 0 and 4.3 KB, a btree on a random column 4 to 5 KB). A rewrite leaves the file twice the index, the new copy above the holes of the old one; VACUUM of a table with `sift.col_auto_merge` on does not copy it down into the holes (a second write of the index, 133 MB for 133 MB of file in the same run) and only cuts a free end: the next rewrite takes the lowest free blocks and the end it leaves is cut with no copy, so the file is between one and two times the index, a rewrite writes the index once, and a VACUUM after it 56 KB instead of 133 MB. `sift.col_auto_merge = off` keeps the copy of VACUUM. - `sift.col_maintain(index)` applies the same rule in a session (for example a `pg_cron` job after the nightly `VACUUM`: `SELECT sift.col_maintain('idx')`; false when nothing is due or the maintenance lock is held, never in a read-only transaction or on a standby). `sift.col_maintenance_due(index)` tells why it would (`dirty units`, `fragmented units`, `write buffer rows`). The merge of 12 MB of segments or more of an index with `key_cut` (the rewrite, `sift.col_merge`) is made by the backend and 2 of the `max_parallel_maintenance_workers` workers, a slice of the key range each, and leaves 3 segments disjoint in the key (about 3% more bytes), where one process leaves one; a smaller index is merged by one process; `max_parallel_maintenance_workers = 0` merges in one process. - The rewrite writes the new segment before freeing the old ones, so the file is about twice its live size until later `VACUUM`s reuse and give back the free end. That and the unthrottled default of `sift.maintenance_cost_delay` (0) are why the setting is off by default: turn it on for a database with scattered churn, with a delay of a few milliseconds. - With `sift.col_auto_merge_partition_kb` the rewrite is by range: the index is kept as segments of that size, a step rewrites the one the churn left most dirty (or merges small segments over them), and the maintenance lock is held, and free space needed, for one step. Where churn is local (a retention window, a hot tail) untouched partitions are not written. Without it, an index over a third of `maintenance_work_mem` (at least 4 MB) is rewritten in partitions of that size as well (never more than 48 of them, so a large index gets larger ones): the writer of a segment keeps the key and the heap address of every row of a global order until its last unit is in, about 2.5 bytes of memory for each byte of segment, so the memory of a rewrite follows `maintenance_work_mem` and not the size of the index. `sift.maintenance_wal_rate` and `sift.col_auto_merge_max_workers` bound what the workers take from the WAL and from other sessions. **`dead_rewrite_pct`**: `VACUUM` clears dead postings either way; the rewrite of a lone segment (one no merge picks) also drops the dead documents' terms. A `sift_col` index has the option with default 0 (0: when the scans of its dirty units cost more than the rewrite of the live rows, or half of it is dead): a unit with a dead row answers from its pre-aggregates net of the dead rows ([docs/COL-PREAGG.md](COL-PREAGG.md)), so lone segments are rewritten sooner when that is cheap. The BM25 default 50 against 5 (400,000 documents of 40 words, 10 rounds of updating or deleting 6% of the rows, each followed by `VACUUM`, mean of 5 runs); 5 rewrites the segment every round and costs more everywhere except the queries after the deletes: | load | `dead_rewrite_pct` | `VACUUM` total | WAL | index file | `count(*)` | ranked top-10 | |---|---|---|---|---|---|---| | update | 50 (default) | 20.2 s | 1,340 MB | 92 MB | 1.06 ms | 3.9 ms | | update | 5 | 37.8 s | 1,428 MB | 133 MB | 1.40 ms | 5.0 ms | | delete | 50 (default) | 2.3 s | 86 MB | 67 MB | 3.5 ms | 4.1 ms | | delete | 5 | 15.8 s | 455 MB | 89 MB | 0.15 ms | 1.5 ms | **`write_buffer_limit`** (kB) is also where the merge tiers start. 1024 against 4096 (100,000 documents of 40 words, mean of 5 runs) promotes and merges faster per step and leaves queries on the grown index faster, but does it 3.7 times as often and the writers pay, so the default stays 4096: | `write_buffer_limit` | 16 clients, `INSERT` | `INSERT .. SELECT` | `COPY` | promotion / merge | index, segments | `count(*)` / top-10 OR / top-10 AND | |---|---|---|---|---|---|---| | 4096 (default) | 107k rows/s, p99 0.69 ms | 113k rows/s | 261k rows/s | 37 ms / 1.4 s | 72 MB, 17 | 11.5 / 12.5 / 6.1 ms | | 1024 | 87k rows/s, p99 0.81 ms | 94k rows/s | 137k rows/s | 13 ms / 0.43 s | 98 MB, 78 | 10.6 / 4.1 / 2.6 ms | **`vacuum_truncate`.** `VACUUM` returns the free blocks at the end of the index file to the operating system, as for a heap (`sift.index_info(index)`: `relation_blocks` falls to `high_water`). - It tries when free space reaches 1000 blocks or a 16th of the index, asks for `AccessExclusiveLock` without queueing (every 50 ms for up to 5 seconds, like the truncation of a heap; a shorter `lock_timeout` ends the wait, a cancel ends it at once), and releases it once the file is cut. If a query, insert or maintenance step still holds the index after that it does nothing and the next `VACUUM` retries (`VACUUM VERBOSE` shows the new size). A reader that holds the table for a moment no longer keeps the free blocks in the file: on an index file of 24.3 MB that a merge had left with 55% free, a `VACUUM` that met a reader holding the table for 2 seconds waited 1.5 s and cut it to 10.0 MB (before, it returned after 23 ms and cut nothing, until a `VACUUM` found no reader). The background worker and `sift.table_maintain()` ask once: the worker again between its steps, the call never waits for a reader and leaves the tail to its next pass. A cut that does not happen says why once per `VACUUM`, with the free blocks that stay in the file: `VACUUM (VERBOSE)` at `INFO`, an autovacuum that logs (`log_autovacuum_min_duration` of 0 or more) at `LOG`, for example `index "u_idx": not truncating, a lock is held on it (waited 5000 ms): 1809 free blocks stay` (the other reasons: a standby that streams, a snapshot that may still read the blocks; [Messages](MESSAGES.md)). - **When the lock stays refused for the whole wait, the cut is online.** A reader that overlaps the next one all the time, or a writer, keeps `AccessExclusiveLock` out for good; the `VACUUM` (and the step of the worker that freed the blocks) then does what it does with a standby that streams, without one: it seals and promotes an open write buffer that lies above the end the file can have, moves the segments above that end into the free runs below (a quarter of the file at most in one pass, no lock beyond the maintenance lock of the index) and cuts the free end under an `ExclusiveLock`, which readers do not conflict with, once no snapshot can read the blocks that left. Writers wait for the cut of the free end only, and the message says what happened: `index "t_idx": a lock is held on it (waited 5000 ms): 2628 blocks moved, 0 given back online` (the blocks a move released are cut by the next `VACUUM` after a transaction, as with a standby). Measured (PG18, tmpfs, an index file of 42.3 MB for 10.8 MB live that merges left, two readers whose statements overlap all the time and a writer inserting a row a statement): the file was 10.9 MB after a few `VACUUM`s with the readers and the writer running (1.01 times the live bytes); with the code before, the same `VACUUM`s left it at 28.2 MB (2.6 times), the `VACUUM` waiting its 5 s each time. A writer's longest statement was 18 ms (median 9 ms, psql start included, as in a run with no `VACUUM`). A kill while the pages are copied or when the move is published leaves an index that verifies and counts as its table, and a standby that replays it verifies the index (`col_cut_online_fallback`). - A hot standby replays `AccessExclusiveLock` first and waits for every query that holds the index, up to `max_standby_streaming_delay`, then cancels it; `hot_standby_feedback` does not prevent it. So while a physical standby streams from the server `VACUUM` does not take that lock. It seals and promotes an open write buffer that lies above the end the file can have (appenders cache its pages, so it cannot move), moves the segments that lie above it into the free runs below, a quarter of the file at most in one pass and with no lock beyond the maintenance lock of the index, and it cuts the free end under an `ExclusiveLock`, which a standby does not replay, once no snapshot can read the blocks that left (`hot_standby_feedback` of the standby and its long queries hold them back, and the file does not grow meanwhile). A table that is written reaches the live bytes in a few `VACUUM`s with transactions between them (an index whose merges had left it at 2.1 times the live bytes: 1.00 after three, measured with a standby streaming); `sift.col_space(index)` shows the blocks it still has to move and what owns the last block, `sift.col_extents(index)` where every run lies. - A `VACUUM` with parallel workers (several large indexes at once) cannot take the lock, which needs a transaction id, and leaves it to the next one: `VACUUM (PARALLEL 0)` and autovacuum do truncate. - Under the exclusive lock, released space can be reused at once and tail segments relocated into interior free runs before truncation, in the same `VACUUM`. See COL §2.7 for the write allowance and conditions that defer relocation. Free pages left inside the file stay reusable. A move is made only if a free block is left after the copy is reserved, for the free-list page that publishes the blocks it frees (the file never grows for a move): a segment of N blocks above one free extent of exactly N blocks, with no other free block in the file, is not moved whole, and one above a free extent of N + 1 blocks is. This step stays for a primary without a standby: the blocks a `VACUUM` releases are reusable only by transactions that start after it, so the online path returns them at the next `VACUUM` after another transaction, not in the same call (the same `VACUUM` is its own horizon). With the online path always on, `col_space`, `col_space_shift`, `store_space`, `vacuum_truncate` and `col_holes_fuzz` fail on that, and `fz_col` reports 1,144 of 1,443 blocks free after a `VACUUM`. - `ALTER INDEX ... SET (vacuum_truncate = false)` turns truncation off for an index (no `REINDEX`), like `vacuum_truncate` of a heap table. - **Size the disk for a merge or a rewrite: plan the free space of the volume at the size of the index.** A merge or a rewrite writes its output before it frees its inputs, so the file reaches about twice the live index until the step is done, and the next `VACUUM` gives the free end back. Measured, one backend, an index of wide rows with `key_cut`: a merge of 19 partitions peaks at 142.9 MB for 70.8 MB live (2.02 times, 71 MB of WAL); a merge of 63 partitions at 1,527 MB for 822 MB live (1.86 times, 829 MB of WAL); the rewrite after a cycle of inserts, updates and deletes at 85.7 MB for 41.3 MB live (2.07 times, 349 MB of WAL). On an index of 110 GB the transient is another 110 GB. - **Each step of the background worker of a `sift_col` index (a promotion, a merge, a pass of a rewrite) runs in a transaction of its own**, so the blocks one step freed are reusable by the next one. The worker used to keep one transaction and one snapshot for all its steps: the xid that released the blocks and the snapshot held the horizon at that xid, so every step took new blocks at the end of the file and the next `VACUUM` cut them back. Measured (PG18, tmpfs, `sift_col` with `key_cut`, 600,000 rows then 40 rounds of 2% inserts, an update and a delete, default maintenance): the largest file 16,242 blocks before and 10,314 after, the mean file over the live bytes 1.28 before and 1.15 after, the rounds above 1.25 times 5 before and 3 after; with the cut off, the file after 40 rounds 14,608 blocks (live 7,822) before and 11,015 (live 7,408) after. 48 promoted segments merged by the worker: the file 7.1 MB before the worker and 11.4 MB after it before the change, 8.4 MB after it now, with the same 3.4 MB of WAL and the same time (127 ms). A rewrite of the whole index still needs room for its output beside its input (the largest file of the run above is that rewrite); `sift.col_auto_merge_partition_kb` makes it by partitions. Free space under 16 blocks (118 blocks at the moment the worker had to extend the file in the run above) is not used by a stream. - **A `VACUUM` leaves the moves of a loose file to a rewrite only when the automatic rewrite will start.** An index that is due for a rewrite (`sift.col_maintenance_due(index)`) has its segments written into the free blocks by the rewrite, so the `VACUUM` only cuts the free end. It used to count a rewrite as coming when the rule said it was due, also when the automatic one waited for its WAL budget (`sift.col_auto_merge_wal_pct`) or for the snapshots that hold the blocks of the last rewrite: under a reader that overlaps another all the time the file then stayed at twice the live bytes for as long as the wait, and every `VACUUM` in it did nothing. Measured (PG18, tmpfs, `sift_col` with `key_cut`, 600,000 rows, 14 rounds of inserts, an update and a delete, then 14 `VACUUM`s of a trickle of inserts, two readers whose statements overlap all the time, autovacuum on): the file over the live bytes in the trickle was 2.01 to 2.03 times before (10,513 blocks for 5,180 to 5,226 live, through all 14 `VACUUM`s, 56 s) and 1.19 times on average after (1.02 to 1.45: 6,197 blocks for 5,428 live, and one rewrite at 7,911); a second run, after a rewrite had doubled the file, was back to 1.04 after 44 s (10,431 blocks for 5,053 live, then 5,277: the `VACUUM`s of that run came every 10 to 15 s, the first moved 1,245 blocks and the next one cut them, see below); in the rounds the largest file was 2.08 times the live bytes before and 1.32 after, the mean 1.41 before and 1.18 after. A file of 27.0 MB for 11.4 MB live whose rewrite is due and waits for its WAL budget stays at 27.0 MB before and is 11.6 MB after one `VACUUM` (`col_vacuum_defer_gate`). - Why it is not smaller: the segments a `key_cut` build leaves each span the whole key range, and so does the segment a promotion of the write buffer adds, so none of their units is free before the last one is read; a rewrite after writes is a step over every partition for that reason, whichever the size of the partitions (`sift.col_auto_merge_partition_kb`, `sift.vacuum_rewrite_limit` cut the output, not the peak). A rewrite alone of the partitions that hold a dead row and no promotion peaked at 1.16 times in a test, but wrote 84% more WAL and left the file larger after each `VACUUM`, so it is not done. A build without `key_cut` leaves no overlapping segments to merge. - While a physical standby streams the doubled size of a merge or a rewrite is moved and cut as above, not left until the next rewrite. **Merging is automatic.** Sealed segments merge `merge_fanin` (4) at a time per size tier; the directory (128 entries) is kept under `greatest(32, 2 * merge_fanin)` by merging the `merge_fanin` smallest; inserts wait for the worker at 112 entries and seal the lane themselves at 4 waiting segments. VACUUM merges down to `vacuum_segments`, except a segment more than `merge_fanin` times larger than the ones before it (`sift.merge(index, true)` merges it anyway). `write_buffer_limit`, `merge_fanin` and `dead_rewrite_pct` tune these thresholds; the rest are constants. **Cost of one library (3.0).** BM25 index and column engine share one library, about twice the size of 2.0's, so each new backend links more symbols. Against 2.0.x (`bench/steps/cpu_gate.sh`, user instructions per operation) three BM25 cells are accepted above 1.02: the first query of a session (`session`, about 1.04 to 1.05), a `VACUUM` with nothing to free (`vacuum_nofree`, about 1.04, on 3.9M instructions) and a ranked query on a one-segment index (`idle1_rank`, 1.02 to 1.03); every other cell (queries, inserts, `COPY`, `CREATE INDEX`, `sift.merge`, `sift.promote`, `VACUUM` with work) is within 1.02. Connection pooling amortizes the session cost; a deployment without `sift_col` or `sift_table` pays it for nothing. **A new backend for every statement.** A pool that gives each request a process of its own loads the library (`dlopen`, `_PG_init`) in that process unless it is preloaded. With `shared_preload_libraries = 'pg_sift'` (a restart) the postmaster loads it once and every backend inherits it; `session_preload_libraries` loads it at each connection start, which moves the cost to the connect and does not save it. `bench/steps/fresh_backend_statements.sh` runs 20 statements (a correlated lookup of a mapping table and a group-by over a `sift_col` index), each in a new backend, against the same statements in one backend and against PostgreSQL's plan (user instructions, cycles, planning blocks; the median of 38 statements, the minimum of the cycles): loaded on first use 14.5 M instructions, 12.2 M cycles (minimum) and 469 planning blocks; preloaded 12.8 M, 9.6 to 10.7 M and 462; (a statement that plans in 43 M instructions, as the two-arm one above, pays the second planning in full: see [Install](#install) above) a backend that has run the statement before 7.9 M and 4. The 4.9 M that a preloaded new backend still spends above that is PostgreSQL's own cold catalog and relation caches, which the native plan pays as well (438 planning blocks against 462 here, 80 M instructions to execute the native plan against 12.8 M). The leaf statistics of the index and the dictionaries of its segments are not rebuilt by a new backend: the shared records serve it (`leaf_stats_built = 0`, `leaf_stats_shared = 1`, `dict_shared_hits = 1` in `sift.stats()` of its first statement). ## Column indexes, aggregates and sift_table Three pieces share the column engine; none needs `shared_preload_libraries`, and `CREATE EXTENSION pg_sift` (3.0) installs all of them. **`sift_col` index** over a heap table: `CREATE INDEX ON orders USING sift_col (tenant_id, status, total, (placed_at::date))` keeps those columns once more, column by column, with zone maps. Filters and aggregates read it instead of the heap rows; the heap stays authoritative (MVCC is the table's). On a `sift_table` it is refused: that table is already in column format and `SiftAgg` reads it as it is. - Types: integers, `bool`, dates and timestamps, `float4`/`float8`, `numeric(p, s)` of any precision, unconstrained `numeric`, `money`, `interval`, `time`, `timetz`, enums, `uuid`, `bytea`, `inet`, `cidr`, `macaddr`, `macaddr8`, `bit`, `varbit`, arrays, ranges and multiranges, `char(n)`, `name`, and `text` of any length (up to 1 GB; a unit with strings over 255 bytes stores them inline). - Reloptions: `write_buffer_limit` (kB, 4096, like the `sift` index) and `paths` for `jsonb` attributes (`WITH (paths = 'metrics.* float4, items[]{sku text, qty int4}')`, [docs/COL-SHRED.md](COL-SHRED.md)) and `compression` (`auto`, `none`, `lz4[:N]`, `zstd[:N]`; per column `'auto; col=zstd:9'`; needs `REINDEX`) and `preagg` (`default`, `off` or `cube(dim: measure, ...)`, the pre-aggregates written with every unit; a change applies to segments written afterwards, [docs/COL-PREAGG.md](COL-PREAGG.md)) and `dead_rewrite_pct` (default 10) and `cluster_advice` (default on: `sift.cluster_advice()` may recommend an order for the table). - `sift.index_advice(index)` profiles a `sift_col` index's columns and what its options could still use (advice only). Per column: kind (`dimension`: at most 256 distinct values in 90% of 32 evenly spaced units; `key`, `unique`, `date`, `measure`, `text`, `list`), what the index holds of it (cube histogram in so many units, key list or bitmap, calendar cells, a rollup) and the advice when a rule holds (add the dimension to `preagg = 'cube(...)'`, `key_columns = 'auto'`). Per pair of dimensions: the share of units whose combinations fit a rollup of the rows, and `rollup_columns = 'a, b: '` when that is 90% of them for no more than 3% of the index. Same rows after a `REINDEX`; options stay explicit. - Maintenance is as for `sift` indexes (background worker, VACUUM); `sift.merge`, `sift.promote` and `sift.verify` take a `sift_col` index too (`col_merge`, `col_promote` and `col_verify` are the same calls). `sift.col_index_info`, `col_segments`, `col_leaves`, `col_verify`, `col_promote`, `col_merge`, `col_maintain` and `col_maintenance_due` inspect and drive one; `col_merge(index, true)` seals the open write buffer and merges every segment into one, or from 12 MB of index in a `key_cut` index into 3 segments disjoint in the key, made by the backend and 2 of its `max_parallel_maintenance_workers` workers (`col_promote` plus the merge in a call, without blocking readers or writers). - `CREATE INDEX` and `REINDEX` (also `CONCURRENTLY`) are parallel on large tables with the workers PostgreSQL plans (`max_parallel_maintenance_workers`, `min_parallel_table_scan_size`, the table's `parallel_workers`, 32 MB of `maintenance_work_mem` per process). A `key_cut` index (the default is chosen from the data) is built by one process: the participants' segments overlap, the merge that follows reads every input again in each slice, and on 1,000,000 rows of 13 columns it took longer with 4 or 8 workers than alone (3.8 and 4.0 s against 3.2 s). Any other index: each process writes the segments of the superblocks it took, so the index has a segment per process until a merge and its file is longer by the unused end of each process's last run (free space, up to 64 MB each); rows served equal a serial build's. **Aggregate pushdown.** A `GROUP BY` over a table with a `sift_col` index (or a lateral unnest of an indexed `jsonb` array) with `count`, `count(DISTINCT)`, `sum`, `avg`, `min`, `max`, filters and `IN` lists runs as `Custom Scan (SiftAgg)` when the planner can prove the result equals the plain plan's; anything else keeps its plan ([docs/COL-PLAN.md](COL-PLAN.md), [docs/AGG.md](AGG.md)). `SET sift.enable_agg = off` compares the two; `scripts/coldiff.sh` does it for a whole corpus. A partitioned table works the same when every partition the query reads has a `sift_col` index of the same columns and options (per partition, or once on the parent: `CREATE INDEX ON parent USING sift_col (...)` builds one per partition, also later ones); `EXPLAIN` lists the `Partitions:` ([AGG.md](AGG.md), "Partitioned tables"). A `GROUP BY` of the calendar (`date_trunc('month', ts)::date`, `extract(isoyear FROM d)`, `ts::date`, a week or a quarter) and a date range are answered from the calendar cells each unit stores for its `date`, `timestamp` and `timestamptz` columns: no generated month column or expression index is needed ([COL-PREAGG](COL-PREAGG.md) section 2). On a `timestamptz` write `AT TIME ZONE 'UTC'` in the key (the session time zone does not give the cells' UTC days). A `sift_col` index gets the same cells for the columns of its reloption `calendar_columns = 'ts, d'` (or `'all'`). **`sift_table`.** `CREATE TABLE t (...) USING sift_table` is a table access method with native MVCC, btree and `sift` indexes, VACUUM, generic WAL and `pg_dump` support ([docs/COL-TAM.md](COL-TAM.md)). Every type is kept exactly. Limits: [Limits](#limits); publishing one under `wal_level = logical`: [Replication](#replication). - New rows go to row-format write pages; when a write buffer fills, or VACUUM or autovacuum runs, settled rows become compressed column segments with the same TIDs. `sift.table_segments('t')` lists them with their column format minor, `sift.table_space('t')` the file and its free blocks, `sift.table_maintain('t')` does VACUUM's work on segments and file without index cleanup ([COL-TAM §7](COL-TAM.md)). Settings: `sift.table_write_buffer_limit`, `sift.table_inline_promote`, `sift.table_compression`, `sift.table_dead_rewrite_pct` (table in [Maintenance and operations](#maintenance-and-operations)). - VACUUM clears deleted rows out of the column segments and rewrites a segment once more than `sift.table_dead_rewrite_pct` percent is dead. Under the exclusive lock it can reuse released space and relocate tail segments into interior free runs before returning the free tail to the operating system, in the same VACUUM (on by default; `vacuum_truncate = false` or `VACUUM (TRUNCATE FALSE)` disables this reuse, relocation and truncation while other maintenance continues; conditions in COL §2.7). `VACUUM FULL` and `CLUSTER` write the table anew as column segments. - The order of the load is the order of the table. A unit holds the rows of consecutive TIDs and a table cannot reorder rows once they have one, so a date window prunes units only when the rows went in date order: `COPY` the file sorted by the date, or load with `INSERT ... SELECT ... ORDER BY created_at` or `CREATE TABLE ... USING sift_table AS SELECT ... ORDER BY created_at`. 100,000, 300,000 and 1,000,000 orders, instructions of the `big-recent-orders` question (the last 30 days): 0.67 M, 0.66 M and 0.89 M loaded by `created_at`, 1.5 M, 3.5 M and 10.7 M loaded by `id`. ```sql CREATE TABLE orders (id bigint PRIMARY KEY, customer_id int, country text, category text, status text, amount numeric(10,2), created_at timestamp); CREATE INDEX orders_col ON orders USING sift_col (customer_id, country, category, status, amount, created_at, (date_trunc('month', created_at)::date)); SELECT category, count(*), sum(amount) FROM orders WHERE country = 'DE' AND created_at >= '2025-06-01' GROUP BY category; -- Custom Scan (SiftAgg) on orders_col SELECT date_trunc('month', created_at)::date AS month, count(DISTINCT customer_id), avg(amount) FROM orders WHERE status = 'shipped' GROUP BY 1 ORDER BY 1; -- the month is an indexed expression of its own SET sift.enable_agg = off; -- the plan PostgreSQL has without sift, to compare results and times CREATE TABLE events (id bigint, kind text, price numeric(10,2), at timestamp) USING sift_table; -- the table itself in column format ``` Index what you filter and group by; an expression such as the month above is a column of its own and a query must write it the same way. ### What CREATE INDEX decides for the columns A `sift_col` index chooses these from the table at `CREATE INDEX`; `CREATE INDEX`, `REINDEX` and `REINDEX CONCURRENTLY` say all of them in one NOTICE (the count of the choices, the advice and the limits, and their values on two lines); each is a default that an explicit option replaces, and none changes an answer (the aggregates are the normal plan's). `SELECT * FROM sift.index_choices('index')` lists the choices of an index with the reason, the cost measured on it and the command that turns each off, and `SELECT * FROM sift.help('key-lists')` returns the row of one of them; the slug of a refusal (`SiftAgg not used: spine_rows: ..`, the list is `sift.refusal_slugs()`) is a valid argument too and answers with the reason and what to change. Every message and choice, with its trigger, benefit, trade-off and switch, is listed in [MESSAGES.md](MESSAGES.md); the table below is the short form. | Decision | Rule | The NOTICE lists | Replace it with | |---|---|---|---| | Key lists of the foreign keys (`key_columns`) | `int2`, `int4` and `date` foreign keys and `*_id` columns that are not unique and that the cube does not already hold; kept where a key list or bitmap fits 3% of a unit in at least half of the units | the columns kept (`key_columns`) | `key_columns = 'off'` or `'a, b'` | | Cube dimensions and calendar cells | the cube holds 16 dimensions: the calendar cells of the dates first, then the columns in their order (the dates' own histograms and the last columns give way) | the plain columns left without a histogram (a limit) | `preagg = 'cube(...)'`, `calendar_columns`; `preagg = 'auto'` ranks the columns by data (below) | | Paths of a `jsonb` column (`paths`) | the keys that are a scalar of one type in most of 2,000 sampled documents; a wildcard for a map of more than 4 keys of one type, a group for an array of objects, free text left out; written to the option as `auto: spec` | the paths read | `paths = '...'` by hand | | Foreign key columns of the table (not a default) | a column of a foreign key of the table that is not a column of the index | the columns (advice) | add the columns to the index: a join to the referenced table on them keeps the normal plan (`join_condition`) and the scan cannot filter the table by the keys of the rows another table's filters keep | | Covered columns that are rewritten often (not a default) | a covered column named like a timestamp of the last change (`updated_at`, `modified_at`), a counter (`*_count`, `likes`, `views`) or a version | the columns (advice: an UPDATE of them is not HOT) | leave the column out of the index, or keep it for its aggregates | Two structures are opt-in because each adds more index than the 3% a default may add: - `preagg = 'auto'`: for an index with more plain columns than the cube holds dimensions, reads 8 stretches of 256 blocks at `CREATE INDEX`, puts first the columns with at most 256 values in a unit in most of them, and records `preagg = 'auto: a, b'`. - `rollup_columns = 'auto'`: reads 8 stretches of 256 blocks at `CREATE INDEX`, takes the columns with at most 256 values in a unit in most of them (the six with the fewest) and writes the rollup of the rows by the pair with the fewest combinations that fits a unit's cells (a tenth of its rows, at most 4,096), recorded as `rollup_columns = 'auto: a, b'`. It is the default (an empty option): the cells are written only when the queries they speed up gain more than the index and the build grow ([how a default is chosen](OPTIONS.md#how-a-default-is-chosen)), and `rollup_columns = 'off'` writes none. The cells grew the index by up to 11% in the case measured. Rebuilds keep what was chosen (the options hold `auto: ...`), so `REINDEX` and a build's workers see one schema. `sift.index_advice(index)` lists per column its kind, what the index holds and, over 32 evenly spaced units, whether it qualifies as a cube dimension, and advises a `rollup_columns` pair covering 90% of the units for within 3% more index; `sift.suggest_paths(table, column)` shows the paths a build would read. Min/max, superunit and calendar structures are not chosen per column: each costs about 1% of an index of wide rows and no rule drops one while keeping its gain. ### Array columns: tests of the elements and the lists that answer them `tags @> ARRAY[..]`, `tags && ARRAY[..]`, `tags <@ ARRAY[..]`, `'x' = ANY (tags)`, `NOT` of the first three, and `unnest(tags)` in a `GROUP BY` run on the index when the index holds the array column (an `int2`, `int4`, `int8`, `text` or `varchar` array) as the list of its elements, which the build chooses from a sample (`sift.index_choices('index')` names it, `array_elements`). Over 300,000 rows of repeating pairs the four statements take 3.7 to 5.4 M cycles on the lists, against 12 to 130 M for the GIN bitmap or the heap scan of the normal plan. To put a deployed table on them: 1. An index that does not name the column holds nothing of it: `sift.explain_why` says `where` or `unnest_leaf`. `REINDEX` cannot add a column, so create the new definition (`CREATE INDEX CONCURRENTLY .. USING sift_col (.., tags)`) and drop the old index when it answers. 2. An index that holds the column as whole arrays only (the build kept the images, or `paths = 'auto:'`) answers a containment through a lookup of the image of every row, 170 instructions a row, and the planner prices it, so a containment that a GIN index answers for less keeps the normal plan (`cost`). Add the lists: `ALTER INDEX i SET (paths = '+tags[] int4'); REINDEX INDEX i` (`'+tags[] text'` for text; 0.31 s for 300,000 rows). A plain `REINDEX` keeps what was chosen, so it does not add them. 3. Indexing the column costs writes, with or without the lists (the lists are built when the write buffer is promoted, in the background): over 200,000 rows, an `INSERT` of 20,000 rows 12 to 14% more cycles, an `UPDATE` of 5% of the arrays 18 to 21%, a `DELETE` of 2% 3 to 9%, 20% more WAL. Leave the column out of an index of a table that is written much more than it is filtered by the array. ### Parallel aggregate scans and float sums An aggregate scan uses the workers PostgreSQL plans (`max_parallel_workers_per_gather`, the table's `parallel_workers`, `min_parallel_table_scan_size`); EXPLAIN shows `Workers Planned` and `Workers Launched`. Integer, numeric, count, min, max and `count(DISTINCT)` results are exact in any plan. A float `sum` or `avg` is added in lane order (16 lanes of superblocks, added in order), so its bits are the same for any number of workers; a scan with a spine or a correlated sub-select adds in the order of the rows when its plan has no workers and in lane order when it has, so only with workers can its last digits differ from a plan without, by the rounding of reordered addition: at most 2 (n - 1) 2^-53 of the sum of the absolute values of the n terms (relative to the result it is large only when the terms cancel; at most 4e-13 relative on the 1,000,000-row replay statements). A parallel PostgreSQL plan differs the same way, and between runs. To keep the serial sums of such a statement: `SET max_parallel_workers_per_gather = 0` for it (or `ALTER TABLE t SET (parallel_workers = 0)` for every statement on the table), or `SET sift.enable_agg = off` for the normal plan. There is no separate setting for the float sums: the workers are where the scan gains (2 to 3x on the wide requests of [docs/COL-PLAN.md](COL-PLAN.md), "Parallel scan"). ### DISTINCT ON and the first row of a group when the ORDER BY ties `SELECT DISTINCT ON (k) ... ORDER BY k, ts DESC` and `row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1` keep one row for each `k`. When several rows of a group tie in the ORDER BY (the same `ts`), SQL leaves the row unspecified: PostgreSQL's own plans pick a different one from run to run, and so may the aggregate scan. The scan keeps the first row of the tie it meets, one row whole (every column of the select list comes from the same row), and with workers the leader's choice among the workers' best rows may differ between runs. Add a unique column (`id`) to the ORDER BY when the row must be the same in every plan. The ON and sort columns of the result are the same in every plan. ### A mapping table read for every key of a scan: VACUUM and a covering index A correlated child of the shape `LEFT JOIN LATERAL (SELECT mapped FROM dimension WHERE = e.key LIMIT 1) m ON true` is probed in the b-tree of the dimension table, a batch of keys at a time (`sift.agg_probe_direct`). Each probe walks the index and then asks the heap for the row, unless the child is an **Index Only Scan** and the heap page is all-visible: the visibility map answers and the heap page is not read (`EXPLAIN ANALYZE`: `11388 child direct probes (.., 0 heap fetches, 10290 from the visibility map)`). Two steps give that, and no code: 1. `VACUUM` the dimension table. The `Lookup Hint` line of `EXPLAIN ANALYZE` says so when under half of its pages are all-visible. 2. Put every column the child reads into an index on its key: `CREATE UNIQUE INDEX dimension_key ON dimension () INCLUDE ()`. When a key column is an expression (`coalesce(scope, '{}')`), include the column inside it too (`INCLUDE (scope, ..)`): PostgreSQL takes an Index Only Scan only when every column the child mentions is in the index, and without `scope` it takes an Index Scan. Check `EXPLAIN` for `Index Only Scan` under the scan and `0 heap fetches` in the `Lookup:` line after the first run. Measured (a 294,000-row dimension table with a unique 4-column expression key, 120,000 events, a list of 200 entities, 11,388 probes, one backend pinned to a core, the minimum of 14 interleaved rounds, a table that fits in shared buffers): the plain index on the vacuumed table 200.8 M instructions, 88.5 M cycles, 17.5 ms; with the covering index 191.3 M instructions (-5 %), 79.5 M cycles (-10 %, the median -12 %), 14.2 ms (-18 %). The first statement of a restarted server reads 776 blocks and hits 23,261 with the plain index, 538 and 12,327 with the covering one (-31 % and -47 %). The cost is the index: 20 MB against 14 MB here (leaf pages hold the included columns), and a write to an included column is not a HOT update. On a table that is not vacuumed the covering index gains nothing (every probe fetches the heap row as before). ### Memory of a backend A backend that plans and runs statements of many aggregate arms keeps far more memory than its memory contexts hold: the planner and scan free blocks of tens of megabytes, and glibc keeps them in the process heap because its threshold for mapping a block by itself grows to the largest block freed. This matters for a server with hundreds of pooled backends. Two clients replaying 258 statement shapes of an analytics mix (40 to 60 aggregate arms for the largest, 195,000 rows, 75 seconds, same seed, run twice) hold per backend on average: | allocator setting | private memory of a backend | CPU per statement | |---|---|---| | default | 104 MB (116, 93) | 145 ms (154, 136) | | `MALLOC_MMAP_THRESHOLD_=131072` | 70 MB | 145 ms | A fixed 128 kB threshold returns every freed block above it to the system. Set it for the server process where memory is the limit (hundreds of pooled backends), not where CPU is (the cost is fresh pages the next block touches): `Environment=MALLOC_MMAP_THRESHOLD_=131072` in PostgreSQL's systemd unit, or `GLIBC_TUNABLES=glibc.malloc.mmap_threshold=131072`. The library does not call `mallopt` or `malloc_trim` itself: both change the whole process, the server's own allocations too. Other allocators (musl, jemalloc) were not measured. ### Why a query kept the normal plan The pushdown refuses most shapes by design, silently. `SET sift.explain_refusals = on` says why: for each candidate path (arm: `single`, `distinct`, `topn`, `two_level`, `rows`, and the decisions `lookup` and `cte_inline`) it reports the first reason the path gave up for, as a notice in the session that plans the query (also under `EXPLAIN`), and compares a path that was built with the normal plan (`cost`). It changes no plan and costs nothing when off. ```sql SET sift.explain_refusals = on; EXPLAIN (COSTS OFF) SELECT kind, count(*) FROM plain_table GROUP BY 1; -- NOTICE: SiftAgg not used: no_source: no sift_col index or sift_table covers the table -- DETAIL: arm: single ``` A sub-select is planned in its own query level and its arms are named `L2:single`; a statement with its shared CTE inlined, `inline:single`. The notice names the arm in its detail; the reason's detail names the leaf, expression or limit and its value (`the limit group_keys is 32`). `sift.refusal_slugs()` lists the slugs below with their class: `limit` is a query with more of something than the scan holds (a fixed limit of [LIMITS.md](LIMITS.md), lowered by `sift.test_limits` in a test build), `semantic` a shape the scan does not prove or does not need. `sift.explain_why(query)` gives the same as rows without running the query or setting anything: it plans one `SELECT` (or the `EXECUTE` of a prepared one, for the values it is given, the way its next execution is planned) (privileges to read its tables are checked) and returns one row per arm with `used` (the arm is in the plan), `slug` and `reason` (why it gave up, NULL when used), and `scan_cost` and `normal_cost` (the path built and the cheapest normal path it was compared with, both at the grouping stage, i.e. the total before an `ORDER BY` or `LIMIT` above it, while PostgreSQL chooses by the top node's cost; NULL without such a path; for the `cte_inline` row, the totals of the plan with the shared CTEs inlined and of the plan the planner makes without SiftAgg, the alternative; the arms of the plan with the CTEs inlined are `cte_cost` refusals when that plan lost). - A `join_child` refusal names the child and what it was estimated at: `sift_probe is estimated at 2 rows for each of 2700 distinct inputs, of width 3, the limit lookup_kb is 1024` (the probe form of a join to a large table: the rows its plan returns for each distinct key the scan meets, times the keys, past `sift.agg_lookup_max_kb`; the statement as written is another row of the same arm and runs). `EXPLAIN (BUFFERS)` of a SiftAgg plan prints `Planning: Buffers`, the blocks PostgreSQL and sift read while planning, the statistics and catalogs a refusal or a price reads included. - A `cost` refusal that the heap check decided says so: `the scan costs 705, the normal plan 658; 100% of the heap pages are not all-visible, which adds 163 to the scan: VACUUM (ANALYZE) the table` (a rewrite by `CLUSTER`, `VACUUM FULL` or `pg_repack` leaves the visibility map empty). - A `cte_cost` refusal says the same when the heap check of the inlined scans keeps the shared CTE (`inline costs 1654 (the scans 1654), the shared CTE 1740 (the work the scans replace 1740); 100% of the heap pages are not all-visible, which adds 547 to the scans: VACUUM (ANALYZE) the table`), and only when the inlined plan would win without that charge in both plans. - A statement with no aggregate (`SELECT * FROM t WHERE id = 5`) gets one row, the arm `index`: `used` says whether the plan reads a `sift_col` index (an index, index-only or bitmap scan), and when it does not `slug` is `no_source` (the table has no such index), `where` (no condition of the statement is one the index answers) or `cost` (the plan with a `sift_col` index is estimated dearer: `scan_cost` is its cost and `normal_cost` the chosen plan's). The statement is planned a second time with the sequential scan off to tell them apart; nothing is run (test `agg_explain_why_scan`). ```sql SELECT arm, used, slug FROM sift.explain_why('SELECT kind, count(*) FROM plain_table GROUP BY 1'); ``` The notice of `sift.explain_refusals` carries the last column of the table as its `HINT`: one line on what to change in the statement, the index or a setting, or `None needed` when the normal plan is the answer. | slug | class | by design | the query kept the normal plan because | what to do | |---|---|---|---|---| | `other` | semantic | | the shape is not one the aggregate pushdown proves, and no more specific reason is recorded | Try a simpler form of the statement (one table, a plain GROUP BY) to find the part that is refused, and report the statement if it should be accepted. | | `disabled` | semantic | yes | sift.enable_agg is off | SET sift.enable_agg = on (it is off in this session, role or database). | | `query_kind` | semantic | yes | the query has a set-returning function in the select list, row locks or a recursive CTE | None needed, the normal plan is the answer; take the set-returning function, FOR UPDATE or the recursive CTE out of the statement to get the scan. | | `from_shape` | semantic | | the FROM list is not one table with the inner, left, semi or anti joins and lateral functions the scan reads | Write the FROM list as one table with inner or left joins and lateral functions, or move the other sources into a sub-select the scan can read. | | `table_kind` | semantic | yes | the table is inherited, sampled, row-secured or not a plain, materialized or partitioned table | None needed, the normal plan is the answer; the scan reads plain, materialized and partitioned tables that are not inherited, sampled or row-secured. | | `no_source` | semantic | yes | no sift_col index or sift_table covers the table | CREATE INDEX .. USING sift_col on the table; sift.index_choices shows what it would hold. | | `partition_source` | semantic | | a partition has no column index the plan can use, or the partitions do not share a layout | Create the same sift_col index (same columns and options) on every partition, as CREATE INDEX on the parent does. | | `partition_count` | limit | | the table has more partitions left by PostgreSQL's pruning than a scan reads | Filter on the partition key so that fewer partitions are left, or query the partitions that matter. | | `join_child` | semantic | | a joined sub-select, VALUES list, CTE or function is not one the scan can read or run as a child | Join a table the scan reads, or write the sub-select, VALUES list or CTE as a single-table SELECT with a WHERE; the detail names the child. | | `outer_join` | semantic | | an outer join of the scanned table keeps rows the scan cannot reproduce | Use an inner join if the unmatched rows are not needed, or aggregate the joined table in a sub-select first. | | `unnest_leaf` | semantic | | the array or object a lateral function unnests is not a leaf of the column index | Add the array or object path to the paths option of the sift_col index (WITH (paths = '..')) and REINDEX. | | `where` | semantic | | a condition of the WHERE or ON clause is not one the scan can test | Test a column of the index with a comparison, IN, BETWEEN, LIKE or IS NULL, or add the column or expression the condition reads to the sift_col index. | | `jsonb_path_not_declared` | semantic | | a containment (@>) or key test (?, ?|, ?&) on a jsonb column names a path the index does not declare in paths, so no leaf holds its values | Declare the path in the paths of the index (sift.suggest_paths writes the spec), or test a path it declares; the detail names the path. | | `join_condition` | semantic | | a join condition is not an equality or filter the scan can look up | Join on an equality of columns of the scanned table and the other side, or an IN list, and filter the rest in the WHERE. | | `group_key` | semantic | | a group key is not a leaf, or an expression of one, that the scan reads | Add the column the key reads to the sift_col index, or group by the column itself. | | `too_many_keys` | limit | | the query groups by more columns than the scan holds | Group by fewer columns; the detail names the limit. | | `error_parity` | semantic | | the scan could raise, or not raise, an error for a value where the normal plan does the other (a cast of a JSON null before PostgreSQL 18, a guard of jsonb_typeof around a read) | None needed, the normal plan keeps SQL's errors; guard the cast so that it returns NULL, or use PostgreSQL 18, to get the scan. | | `output_expr` | semantic | | the select list or HAVING reads something besides group keys and the aggregates the scan computes | Select the group keys and the aggregates only, and compute the expression over them in an outer query. | | `aggregate` | semantic | | an aggregate, or its FILTER, DISTINCT or ORDER BY, is not one the scan computes | Use aggregates the scan computes (the list in USAGE) without the clause that is refused, or compute the rest in an outer query. | | `distinct_on` | semantic | yes | DISTINCT ON keeps a row the scan cannot pick: more than four sort columns after the ON columns, a sort or select-list column of a joined table, or a volatile select list | None needed, the normal plan is the answer; keep the sort columns after the ON columns to four and the select list on the scanned table to get the scan. | | `grouping_sets` | semantic | | the arm does not group by grouping sets | Use a plain GROUP BY for this arm; the single-scan arm answers ROLLUP, CUBE and GROUPING SETS. | | `dirty_units` | semantic | | some unit holds values a leaf the query reads cannot store, and the scan could not read those rows again as SQL does | Find the leaf with values it cannot store in sift.col_leaves, fix the data or the type of its path, and REINDEX. | | `dirty_order` | semantic | yes | a condition casts a path whose value some unit cannot store, and PostgreSQL may test it before a condition that would drop the row whose value raises, or after one that raises too | None needed, PostgreSQL's order of the conditions decides; put the guard in a CASE or a sub-select so that it runs first. | | `volatile` | semantic | yes | the query calls a volatile function, which PostgreSQL evaluates for every row and the scan cannot reproduce | None needed, the normal plan is the answer; compute the volatile function outside the statement or in an outer query to get the scan. | | `first_memory` | limit | | the first rows of the groups would not fit in the memory of a hash aggregate | Raise work_mem or group by fewer values; the detail names the limit. | | `group_memory` | limit | yes | the groups would not fit in the memory of a hash aggregate, and the scan cannot spill them, or the normal plan, which spills them, is estimated cheaper than the scan with its spill | Raise work_mem so that the groups fit a hash aggregate, or leave the normal plan, which spills them. | | `lateral_limit` | semantic | | a LATERAL sub-select with LIMIT 1 stays a child that runs for each key: no unique index holds its key, or its ORDER BY leaves ties | Add a unique index on the key of the LATERAL sub-select, or an ORDER BY that makes its LIMIT 1 unique. | | `distinct_set` | semantic | | count(DISTINCT x) keeps the distinct values of each group: x is not a NOT NULL column of the scanned table under a unique single-column index, so its rows cannot be counted in its place | Count DISTINCT over a NOT NULL column under a unique single-column index, or leave the normal plan. | | `two_arms` | semantic | | the arms of the two level aggregate are not UNION ALL branches of aggregates over one scan, each grouped by a unique row id | Write the arms as UNION ALL branches of aggregates over one scan, each grouped by a unique row id. | | `two_keys` | semantic | | a key of the two level aggregate is not one every arm groups by | Group every arm by the keys the outer aggregate groups by. | | `two_aggs` | semantic | | an aggregate over the arms is not one the two level scan computes | Use sum, count, min or max over the arms, or compute the other aggregate in an outer query. | | `spine_shape` | semantic | | the join of the arms to a spine is not an equality of their keys, or the WHERE reads the arms | Join the arms to the spine on an equality of their keys, and keep the WHERE off the arms. | | `shape_width` | limit | | the query has more joined tables, value combinations, leaves, arms or columns than the scan holds: the detail names the limit and its value | Reduce the joined tables, value combinations, leaves, arms or columns below the limit the detail names. | | `spine_rows` | limit | | the rows of the spine are estimated above sift.agg_lookup_max_kb | Raise sift.agg_lookup_max_kb, or filter the spine to fewer rows. | | `rows_shape` | semantic | | the grouping of the rows of a SiftAgg plan has no count(DISTINCT) of integers, or a key or aggregate it cannot group | Group by integer keys with aggregates the scan computes (count(DISTINCT) of integers), or leave the normal plan. | | `topn_shape` | semantic | | the ORDER BY .. LIMIT is not over the scanned table's columns with a condition the scan tests | ORDER BY columns of the scanned table, with a WHERE the scan tests, then LIMIT. | | `window_first` | semantic | | the first rows of each group, asked with a window function (rn = 1, rn <= N), are not a lone row_number(), rank() or dense_rank() with PARTITION BY and ORDER BY in a sub-select that does nothing else, over a table with a column index for N > 1 (rank and dense_rank: up to 1,000) | Write the first rows of each group as a lone row_number(), rank() or dense_rank() OVER (PARTITION BY .. ORDER BY ..) in a sub-select that does nothing else, kept with rn = 1 or rn <= N. | | `cost` | semantic | yes | the normal plan is estimated cheaper than the scan | None needed, the normal plan is estimated cheaper; ANALYZE the table if the estimates look wrong, and compare with sift.agg_force = on (the row shows both costs). | | `cte_cost` | semantic | yes | the shared CTE plan is estimated cheaper than the plan with the CTE inlined | None needed, the cheaper form was chosen; MATERIALIZED or NOT MATERIALIZED on the CTE picks the other. | PostgreSQL 17-19 table access methods cannot define their own `WITH (...)` options (a patch targets PostgreSQL 20), so a `sift_table` is tuned with PostgreSQL settings (`SET`, `ALTER DATABASE ... SET`, `postgresql.conf`), not per table; a `sift_col` index has real options stored with the index (`CREATE INDEX ... WITH`, `ALTER INDEX ... SET`). Background promotion by `VACUUM`, autovacuum and the worker uses the server or database value of the setting, not your session's; the exact rule is under [Limits](#limits). | option | `sift_col` (per index) | `sift_table` (setting) | |---|---|---| | compression of the column chunks | `compression` (`auto` default, `none`, `lz4[:N]`, `zstd[:N]`, per column `'auto; col=zstd:9'`; needs `REINDEX`) | `sift.table_compression` (empty = `auto`; same grammar) | | size of the write buffer before it is sealed | `write_buffer_limit` (kB, `ALTER INDEX ... SET`) | `sift.table_write_buffer_limit` (kB) | | promotion while rows are inserted | not applicable (the index follows the heap) | `sift.table_inline_promote` (`on`; `off` leaves it to `VACUUM`) | | dead rows | `dead_rewrite_pct` (per index) | `sift.table_dead_rewrite_pct` (percent of a segment) | A `sift_table` has no `paths` or rows-per-unit setting: a `jsonb` column is stored as its exact bytes and units follow the 2 MB superblocks. The chunk codec is built into the library; `make SIFT_NO_LZ4=1` or `SIFT_NO_ZSTD=1` build without one. ### The choice for a shared CTE is kept per backend A CTE written `MATERIALIZED` takes part in the choice like any other: the keyword fixes where PostgreSQL computes the rows, not which rows there are, so sift may replace the shared copy by scans of the index, one for each aggregate that reads it, when they cost less than the copy (a CTE with a volatile function or a write is never replaced). A shared CTE that yields the rows of an aggregate, a window or a `DISTINCT` (its own, or of a sub-select or CTE it reads) is not replaced: it stays one CTE with the scans inside it, computed once. Written inline in each reader it would run the aggregate, the window and the sorts once per reader, for no scan saved (the top entities of a group: the rows and the ranking read the same grouped scores; 303 ms with a copy of the pipeline per reader, 173 ms with the one CTE, 1,000,000 events). Choosing between a shared CTE and its inline plan takes PostgreSQL's planner several runs of the statement (the shared plan, the inline plan, the larger sizes of `sift.agg_scale_horizon`, the flattened form and the form as written). A backend therefore keeps the choice for up to 4,096 statements (under 1 MB), keyed by the statement without its constants, the size class of each table it reads (half an octave of its pages) and the settings that move a plan (the planner's, `work_mem` and the `sift.*` ones), and plans only the chosen variant when it sees the statement again. - The entry is dropped when a table it reads is invalidated (DDL, an index created or dropped, `ANALYZE`, `VACUUM`) and is checked again by a plan in full at every eighth hit. A stale verdict (the constants select other rows than the ones it was made for) costs the other plan, never other rows: both variants plan the same query. - A new backend (a pool that resets its sessions, a first connection of the day) has no entry, and its first statement of each kind would be planned in full. The choices are therefore also kept in a table in shared memory (`sift.verdict_cache_kb`, 256 kB by default, 0 turns it off; allocated at that size by the first backend that plans such a statement after the server starts; superuser) that every backend of the database reads when its own cache has no entry. The key of a shared entry also carries what makes a verdict stale for the others without a message: the database, the live tuples (in half octaves) and the visible share of the pages of each table, and the files of its column indexes, so that an `ANALYZE`, a rewrite or a `REINDEX` moves the key and the old entry goes unread. - The table is one for the server, whatever the number of backends: 56 bytes an entry, up to three entries a statement, 1 MB at most (`sift.verdict_cache_kb` above 1,024 is the same); a backend's own cache stays under 1 MB as before. It is a cache and never fails a statement: if the allocation fails (no room in `/dev/shm`, no more dynamic shared memory segments) the backend logs it once and plans in full, as it does with `sift.verdict_cache_kb = 0`. The table lives in the registry of the running server: a crash restart or a restart starts it empty, a hot standby has its own, and `pg_upgrade`, `DROP EXTENSION` and `ALTER EXTENSION UPDATE` leave nothing behind (the name of the segment carries the layout of the entries, so backends of another library version never read it). - The plan of the variant that won must cost within a factor of 4 of the plan the verdict was made from: constants that move the cost further (a request for 3 keys and one for 300) use the verdict made for plans of that cost, kept in bands of a factor of 4, or are planned in full once and kept as a new one. The entry is checked at every eighth read, in any backend. - `sift.explain_why` shows a `verdict_cached` row for a statement planned from it, and its `cte_inline` row has no `scan_cost` or `normal_cost` (they would be those of the statement that made the verdict; a statement far from that size is planned in full and reports its own); `sift.stats()` counts `verdict_cache_hits`, `verdict_cache_misses`, `verdict_cache_flips` (the plans in full that chose the other plan) and `verdict_cache_shared_hits` (the hits on a choice another backend made). The first plan of a statement after a start of the server, and after a change of a table, is made in full. ### When the cost model keeps the normal plan and the scan is faster A `cte_cost` refusal (`sift.explain_why`: the `cte_inline` row, `scan_cost` against `normal_cost`) compares the inline plan's scans with the normal plan, which PostgreSQL prices from guessed row counts: a join on a range that is not a list of constants, or a top-N cut, leaves a CTE it expects to hold one row (a join of a column with a `VALUES` list of ranges is counted from the column's statistics) where the scans read hundreds of thousands, and everything above it (nested loops that rescan an aggregate included) is priced for that row. On 10x data (31.7 million rows, 32 keys spread over the table, `work_mem` 4GB, 27 statements of the nested-list families) the planner took the scan for 3 statements and refused 25 with a cost ratio between 10 and 54: 107.2 s in all against 114.5 s for the normal plans, 6.3 s with the scan forced (8 to 22 times faster per statement). The model priced the normal plan a median 180 times below its measured time (57 to 554 times for 80% of statements) at 32 keys and 240 to 380 times at 64 to 250 keys; the scan's price was within 0.2 to 2 times of its time at 32 keys. No cost setting fixes a miss that size: a plan whose join order and strategy come from guessed rows cannot be priced from the rows alone. For a role or database with an analytical workload (requests that read thousands of rows or more), `sift.agg_force = on` takes the scan wherever one is possible: ```sql ALTER ROLE reporting SET sift.agg_force = on; -- new sessions of that role; SET LOCAL sift.agg_force = on for one statement ``` It does not make the scan cheaper, it stops comparing. A statement whose normal plan takes 15 to 120 ms is slower with it (the scan is planned, with several CTE-decision plans, and opened for nothing): on 0.1x data (353,000 rows) two minimum-per-bucket statements went from 25 and 119 ms to 106 and 270 ms, two empty-bucket statements from 18 and 31 ms to 47 and 45 ms, while statements with a normal plan of 130 ms to 5 s ran 1.4 to 3 times faster. Do not set it for a role that also runs small requests; `SET LOCAL` it in statements known to be large. Two cases where the normal plan is slow or mispriced whatever the scan does: - A nested loop over a CTE the planner expects to hold one row (the `Nested Loop` reruns a `HashAggregate` or `GroupAggregate` per real outer row; the plan shows `CTE Scan ... rows=1` with thousands of rows actual) is slow with or without the scan, which cannot reprice it. Without `sift`, a grouping-sets chain filtered by two columns without statistics ran in 9.7 s at 128 keys and 268 ms with `SET LOCAL enable_nestloop = off` (32,887 ms and 732 ms at 256 keys; the slope of time against keys goes from 1.9 to 1.0). That setting changes every join of the statement: test before keeping it. - An unclustered key read through an index (correlation near 0) is priced as if its heap pages were cached: with `random_page_cost` 1.1 and `effective_cache_size` above the table, a heap fetch costs about a sequential page. When the table does not fit `shared_buffers` the fetches miss and the normal plan runs slower than its price, so the refusal of a scan that would have been faster is the planner's optimistic estimate, not the scan's price (top-3-per-group over 1.5 million rows, a 213 MB table, `work_mem` 4GB, `random_page_cost` 1.1, no workers; scan price 284,288 against 114,152 for the normal plan, refused in both rows): | `shared_buffers` | normal plan | scan forced | cost units for each ms of the normal plan | |---|---|---|---| | 64 MB (the table does not fit) | 4.0 to 4.5 s | 2.3 s | 28 to 32 | | 1 GB (the table is cached) | 1.35 to 1.45 s | 1.9 s | about 90 | The normal plan is faster when the table is cached, so no price change is right for both cases. For a role that reads tables larger than `shared_buffers`, the supported overrides are `sift.agg_force` (above), `sift.enable_agg` per role, or a `random_page_cost` that reflects the storage. A diagnosis that starts with `EXPLAIN (ANALYZE)` in a session that has not preloaded the library shows the plan the next statement gets (see Install above). ### Approximate distinct counts `count(DISTINCT x)` is exact in every plan, whatever the settings and index options. When an estimate is enough (the distinct visitors of a dashboard), `sift.approx_count_distinct(x)` is an ordinary aggregate returning a `bigint`: a HyperLogLog sketch of 16,384 registers, relative standard error 0.81% (within 1.6% of the count in 95 cases of 100, within 2.4% in 99.7). On 40 sets of each size the mean error was 0.44% (300 distinct values), 0.43% (6,000) and 0.53% (120,000), the largest 1.33%, 1.35% and 1.96%; 2,000 distinct values gave 1,987. - It takes any type with a hash operator class (`int`, `bigint`, `text`, `uuid`, `date`, `timestamptz`, ...); a type without one is an error. A `NULL` is not counted; no row, or only `NULL`s, gives 0 (not `NULL`). `DISTINCT` and `FILTER` in the call are allowed. - The number is a function of the set of values (their extended hash with seed 0): the same in every plan, with workers or without, by partition, from a heap or column-index scan, in any row order. Partial states are mergeable (parallel and partitionwise aggregates and `sift_col` units share one combine function). - An index with `hll_columns = 'col, col'` stores each unit's sketch of those columns; the aggregate over units a query reads whole merges sketches instead of hashing every row. On 100,000 to 1,600,000 rows of two columns (50,000 to 200,000 distinct values): 0.8 to 5.4 ms, against 12 to 63 ms for the normal plan and 38 to 153 ms for the scan of the rows; `count(DISTINCT)` took 58 to 880 ms by the normal plan. A sketch costs 8 bytes and 3 per register (169 bytes for 50 values, 8,209 for 2,700, 12,307 above 4,096) a unit and column; four columns of a million rows made the index 23% larger and the build 70% longer. Without the option the aggregate hashes the rows (same number from scan and normal plan). - `EXPLAIN` of the scan says `Approximate Distinct` and `Units Without The Sketch` (units read by their rows: a page not yet all-visible, a test that cuts the unit, an older unit); `sift.explain_why` and `EXPLAIN (VERBOSE)` say `approx_no_sketch` when the index keeps no sketch of the column or the query groups the aggregate. `sift.upgrade_commands()` lists an index whose option is newer than its units as a gain from `REINDEX`. ```sql CREATE INDEX visits_col ON visits USING sift_col (day, site_id, visitor_id) WITH (hll_columns = 'visitor_id'); SELECT site_id, sift.approx_count_distinct(visitor_id) FROM visits WHERE day >= date '2026-01-01' GROUP BY site_id; -- an estimate, 0.81% relative standard error SELECT count(DISTINCT visitor_id) FROM visits; -- exact, always ``` ### Set filters A filter on a set of ids (the members of a group, the entities that have an attribute) runs in `Custom Scan (SiftAgg)` as an array parameter, a literal `IN` list, or a join to the table that holds the set. [docs/SET-FILTERS.md](SET-FILTERS.md) has the measurements and settings. The array parameter is the fast one: send the ids as one `int4[]` of a prepared statement on a generic plan (`SET LOCAL plan_cache_mode = force_generic_plan`); keep a small set (about 100 ids) on a custom plan. The array form pushes down for these key types (the array's element type is the column's type; cast the array, `ANY($1::int8[])`, `ANY($1::text[])`, `ANY($1::date[])`): | key column | array | how `SiftAgg` tests it | |---|---|---| | `int2`, `int4`, `date`, `int8`, `timestamp`, `timestamptz` | the same type of array (an `int4[]` for an `int8` column too) | a byte table over the range of the array (up to 4 MB), else a sorted array (32-bit types) or a hash table (64-bit types) | | `text` | `text[]` | PostgreSQL evaluates `col = ANY($1)` once per distinct value of the column, with a hash table of the array built on the first value | | `uuid`, `bytea` | | not supported: a `sift_col` index refuses these columns, so the query keeps the normal plan | A `NULL` element matches nothing (`= ANY`) and makes `<> ALL` empty; an empty array keeps no row (`= ANY`) or every row (`<> ALL`), as in SQL. Timings by type: [docs/SET-FILTERS.md](SET-FILTERS.md), section 2. ```c /* libpq: one text parameter of type int4[] (OID 1007) */ Oid types[1] = {1007}; const char *vals[1] = {"{101,102,103}"}; PGresult *r = PQexecParams(conn, "SELECT channel_id, count(*) FROM events WHERE dim_a_id = ANY($1::int4[]) GROUP BY 1", 1, types, vals, NULL, NULL, 0); ``` ```ruby # Sequel: the pg_array extension binds the array as one parameter of a server-side prepared statement DB.extension :pg_array ps = DB[:events].where(Sequel.lit('dim_a_id = ANY(?::int4[])', :$ids)).select_group(:channel_id).select_append { count.function.* }.prepare(:select, :by_set) DB.transaction { DB.run 'SET LOCAL plan_cache_mode = force_generic_plan'; ps.call(ids: Sequel.pg_array(ids, :int4)) } ``` ### Is the index clustered, and what did the scans do `sift.col_clustering(index, width_pct => 1)` tells, per integer, date and timestamp leaf of a `sift_col` index, whether sorting the table by it would let scans skip more units. It reads the zone maps (min and max of each unit) of the immutable segments, no chunk, and reports the units a filter on a value of the data touches, as `Units` of `EXPLAIN ANALYZE` counts them: | column | meaning | |---|---| | `units`, `zoned_units` | units of the index; those whose zone is a bounded range of the leaf (a unit with an overflow value is always read) | | `useful_pct` | share of them whose range is narrower than the span of the leaf: the zone can exclude a value | | `point_units` | units an equality on a value of the data touches, on average | | `range_units`, `range_ideal` | the same for a range of `width_pct` percent of the span, and what a column sorted by itself would touch | | `depth_max` | the most units whose zones hold one value: the deepest overlap. `point_units` is the average and hides a few deep values; rows that arrive out of order (a late date, a key from the whole range) raise it by one unit per batch written | | `correlation` | order of the units against the order of their values: 1 ascending, -1 descending, near 0 scattered | | `verdict` | `clustered` (an equality touches 2 units or 2% of them), `partial` (up to 25%) or `scattered` | A `scattered` leaf that queries filter on is the one to sort by: `CLUSTER`, load the rows in its order, or `INSERT .. SELECT .. ORDER BY` into a fresh table, then `REINDEX`. Rows still in the write buffer are not counted. `sift.cluster_advice(rel => NULL, min_rows => 100000, key => NULL)` says which order is worth a rewrite, one row per `sift_col` index of the table (or index, or every index you may read): the `order_by` to sort by, `units_now` an equality or window on it reads and `units_after` a table sorted by it would, the `saved_rows` of every query, `churn_pct` since the last `ANALYZE`, a `verdict` (`recommended` or `not recommended`), its `reason` and the `pg_repack` call followed by the `VACUUM (ANALYZE)` the rewrite needs. - `key => 'a, b'` asks about an order of your own (the workload's filter keys; alone, the function proposes the best single leaf, and a leaf it would scatter can make it say `not recommended` for it): `order_by` is that list, `units_now` and `units_after` are those of an equality on its first leaf, and leaves outside the list are the ones the sort can scatter. A reason ends with a note when under half the heap pages are all-visible. - Not recommended when: the table is small (a query saves under `min_rows` rows, about a millisecond of scan, or the index has under 16 units); it is already ordered; the sort would scatter a leaf that is ordered now and saves about as much; or over 10% of the rows change a day (the order is lost faster than a repack restores it). - A rewrite restores the order at once: the planner and scans read the rebuilt index's zone maps. `CLUSTER`, `VACUUM FULL` and `pg_repack` leave the visibility map empty and autovacuum does not come for it (a rewrite counts no inserts). With `sift.col_auto_merge` on, the first scan that checks rows in the heap starts the worker that vacuums the table (500,000 rows, 200 ms `sift.maintenance_naptime`: map set 1.6 s after the `CLUSTER`, a grouped sum 12 ms before and 0.15 ms after); with it off, run `VACUUM (ANALYZE) t` right after the rewrite. - A scan checks the heap for every row of a page that is not all-visible, and the planner prices that (`relallvisible` of `pg_class` is 0): on a 200,000-row table a scan of 133,660 rows read 33,415 heap pages and took 320 ms instead of 82 ms. It charges each page of the kept units that holds a selected row by `shared_buffers`, `effective_cache_size` and table size, as an index scan does: about 1.3 us from shared buffers, 3 us from the OS cache, a sequential or random page read from disk, plus 4 ns per row. A clustered table of wide rows whose scan costs 531 with the map set costs 705 without it for a twentieth of the units, against 658 for the normal plan, which wins until the `VACUUM`; the charge grows with the share of the table read, and a table that does not fit in memory pays the disk. A table never vacuumed or analyzed has no row count either (`reltuples` -1): the planner's own estimate from the pages and the row width decides whether a join or sub-select to it is planned as a probe of its btree, as it does for the plans around it, so a bulk load plans the same as a vacuumed table (the probes still ask the heap for the rows of the entries until the first `VACUUM`: a NOT IN over a 1,000,000-row table 4.0 ms and 4,289 blocks analyzed only, 1.4 ms and 227 blocks vacuumed). - Switch the advice off with the setting `sift.cluster_advice` (off: every index answers `not recommended`) or for one index with `ALTER INDEX ... SET (cluster_advice = off)`. A rewrite is not the only answer to a key that reads many units. A clustered table keeps its key tight only until rows land in the holes `UPDATE`, `DELETE` and `VACUUM` leave (one foreign row widens a unit's min/max zone to the whole key range): in a test of 400,000 rows, churning 0.2% of the rows took an equality on the key from 2 to 74 of 112 units; `REINDEX` and the merges left it there, only a rewrite restored it. `key_cut` cuts the units by the key, so the equality stays at 1 or 2 units through any churn, with no rewrite. It costs the other leaves that follow the table's order (an id or arrival timestamp), whose zones it widens, and the unit address lists in the index. - The advice names `key_cut` only for the key the workload looks rows up by, and only when no leaf is ordered now: the first leaf of `key =>`, else the leaf of `point_columns`, `point_tid_columns` or `key_columns` (not unique) that would save the most units. It gives `key_cut = 'k'` with the `ALTER INDEX` and `REINDEX` that apply it, `key_columns = 'k'` (a key list in every unit, skipping units a few foreign rows widen) for a key of over 256 values, and how to choose a leaf when none qualifies. - A table whose leaves such as `id` or a date follow the arrival order is left alone (`competing leaves`); one already ordered by the key says `already ordered`. - `CREATE INDEX` and `REINDEX` print the same advice as a `NOTICE` for the key of those options when its equality reads over a quarter of the units and no other leaf is ordered; the setting and the index option `cluster_advice` silence both. `key_order` names the key a table is stored in. `WITH (key_order = 'ts')` stands for `key_cut` and `order_array_columns` on that column while you have not set them, so one word builds what two options build (4.3: no `point_tid_columns`, whose directory made the index 71 to 95% larger for lists of the key alone); `'tenant, ts'` stands for `order_array_columns` on the key and `order_array_group` on the group (the latest rows of each tenant or entity), and leaves `key_cut` to the build and the point directory out. An option you wrote wins, and the `NOTICE` at `CREATE INDEX` lists what it set. When to choose between the three (100,000 rows, 11 columns, 12 rounds of 1% inserts, 1% updates, 0.33% key moves and 1% deletes with maintenance, instructions of the backends): | | default | `key_cut` alone | `key_order = 'key'` | |---|---:|---:|---:| | random keys: churn, WAL, index | 6,957M, 12.1 MB, 1,880 KB | 5,661M, 11.1 MB, 1,536 KB | 5,711M, 12.3 MB, 1,808 KB | | tail keys: churn, WAL, index | 5,065M, 8.2 MB, 1,848 KB | 4,110M, 6.5 MB, 1,320 KB | 4,207M, 8.5 MB, 1,776 KB | | monotone keys: churn, WAL, index | 9,703M, 18.1 MB, 1,968 KB | 5,624M, 9.9 MB, 1,232 KB | 5,743M, 12.2 MB, 1,936 KB | | table in arrival order: range of the key, top-20, OFFSET 10,000 | 2.09M, 0.25M, 5.4M | 0.53M, 0.50M, 36M | 0.53M, 0.25M, 5.4M | | table sorted by the key: OFFSET 10,000 | 22.2M | | 5.5M | Against the default `key_order` never loses on the churn rows (18 to 41% fewer instructions, the same WAL within 3%) and keeps the ordered and `LIMIT` shapes `key_cut` alone loses (top-20 2x, OFFSET 10,000 6.7x fewer instructions); against `key_cut` alone it pays the order arrays, 10 to 24% more WAL and an index 18 to 57% larger. Choose `key_cut` alone for a table in arrival order that only looks keys up and ranges over them and where the size of the index and the WAL of churn count; choose `key_order` when the queries order by the key with a `LIMIT` or an `OFFSET`, or the table is stored in the order of the key (a sorted load, `CLUSTER`, an append-only table in time order), where it is 4 times cheaper than the default on an `OFFSET` of 10,000 (the default's ends of the order answer an `OFFSET` below about 8,000 as fast) and 21% smaller on a sorted table (14% smaller on one in arrival order). Add `point_tid_columns` on the key for lists of its values: an `IN` list of 100 values takes 5.7 to 11.8 times fewer instructions (82.9M to 11.7M at 5,000,000 rows) for an index 71 to 95% larger, and a b-tree still takes 1.7 to 3.8 times fewer; 12 rounds of churn cost within -2.9 to +12.7% of the instructions without it. The group form is for one big multi-tenant table (300,000 rows, 1,000 tenants, the top one 21%, arrival order): the latest row of each of 100 tenants takes 4.1M instructions instead of 13.9M (0.51 against 1.93 ms; a b-tree 3.3M, 0.42 ms), the latest 5 of each 6.0M instead of 25.5M, the latest 20 of a tenant 0.35M instead of 0.46M to 1.12M and a keyset page of the top tenant 0.42M instead of 1.16M, a global `ORDER BY ts DESC LIMIT 20` 0.29M against 0.27M; the index is 21% smaller than the default's (8.4 against 10.5 MB), the build 52% cheaper (3,437M against 7,144M instructions) and `VACUUM` and the insert into a hot tenant cost the same. Why it is not the default for a table whose key follows the heap (a correlation of 0.9 or more): the formula weighs 9 to 14 shapes equally against the default of 3.8 (B = mean of log2(S) / log2(1.05) over the shapes, C = G / 3 + W / 5), on a table sorted by the key, instructions of one backend: | sorted table | B | index G | C | verdict | |---|---:|---:|---:|---| | 11 columns, int key, 100,000 / 300,000 / 1,000,000 rows, with `point_tid_columns` (the 4.2 bundle) | 11.4 / 11.6 / 16.3 | +30% / +36% / +45% | 9.9 / 11.8 / 14.9 | thin (+1.5, -0.2, +1.4) | | the same without it (the form since 4.3) | 4.6 / 5.7 / 8.4 | -21% / -20% / -20% | -7.0 / -6.6 / -6.6 | pays on the shapes; 12 rounds of churn cost 15.9G against the default's 6.8G at 100,000 rows, and within -2.9 to +12.7% of the bundle with the directory at 1,000,000 and 5,000,000 rows (4 columns) | | 4 columns, int key, 300,000 rows | 24.9 | +48% | 16.1 | pays | | 11 columns, timestamp key, 300,000 rows | 11.7 | -16% | -5.5 | pays | The margin depends on the width of the table and the kind of the key, and half of B on a list of points the build cannot see, so the build does not choose it; on a table in arrival order (correlation under 0.9) it would not engage and the option stays where you put it. The group form serves the queries that name the group: a query that does not (`ORDER BY ts OFFSET` over the whole table, `ORDER BY entity, ts OFFSET`) is not what it is for and costs what the grouped order alone gives. 300,000 rows, 2,000 entities, 11 columns, arrival order, `entity, ts` against the default and a b-tree on (entity, ts), absolute (ms, M instructions, blocks read warm): | query | b-tree | default | `key_order = 'entity, ts'` | |---|---:|---:|---:| | latest 20 of an entity | 0.08 ms, 0.23M, 23 | 0.23 ms, 1.10M, 136 | 0.13 ms, 0.35M, 35 | | a keyset page of an entity | 0.13 ms, 0.33M, 30 | 0.33 ms, 1.41M, 168 | 0.16 ms, 0.42M, 35 | | latest 10 of an entity with a filter | 0.10 ms, 0.25M, 30 | 0.29 ms, 1.13M, 132 | 0.19 ms, 0.43M, 53 | | latest row of 100 entities (`LATERAL`) | 0.38 ms, 2.30M, 404 | 2.71 ms, 19.9M, 2,294 | 0.64 ms, 4.47M, 893 | | latest 5 of 100 entities | 0.50 ms, 4.21M, 809 | 4.56 ms, 39.2M, 5,184 | 0.78 ms, 6.56M, 1,298 | | `count(*)` of 100 entities | 0.54 ms, 6.19M, 334 | 0.50 ms, 3.86M, 287 | 0.51 ms, 3.86M, 289 | | a range of entities (1,000 rows) | 0.83 ms, 6.84M, 989 | 0.80 ms, 6.11M, 1,049 | 1.08 ms, 8.83M, 436 | | an aggregate over a range of entities | 14.9 ms, 59.1M, 11,004 | 0.93 ms, 8.97M, 718 | 0.86 ms, 8.97M, 718 | | a list of 10 entities | 0.40 ms, 3.61M, 533 | 0.38 ms, 3.50M, 573 | 2.22 ms, 12.6M, 431 | | a list of 60 entities | 3.55 ms, 20.2M, 2,820 | 3.38 ms, 19.8M, 2,731 | 4.99 ms, 27.6M, 431 | | entity and one week of `ts`, the rows | 0.06 ms, 0.21M, 8 | 0.10 ms, 0.35M, 36 | 0.12 ms, 0.59M, 68 | | entity and one week, `count(*)`, `sum` | 0.07 ms, 0.21M, 8 | 0.16 ms, 0.72M, 66 | 0.16 ms, 0.72M, 71 | | entity and one week, `min`, `max` | 0.09 ms, 0.45M, 8 | 0.21 ms, 1.13M, 60 | 0.20 ms, 1.13M, 65 | | 10 entities and one week, `sum` per entity | 0.11 ms, 0.53M, 63 | 0.24 ms, 1.62M, 101 | 0.23 ms, 1.56M, 109 | | 60 entities and one week, `sum` per entity | 0.24 ms, 2.04M, 369 | 0.28 ms, 2.10M, 101 | 0.27 ms, 2.03M, 109 | | `LATERAL` count of one week, 100 entities | 0.24 ms, 1.80M, 307 | 0.72 ms, 7.45M, 90 | 0.69 ms, 7.40M, 98 | | `LATERAL` latest 3 of one week, 100 entities | 0.26 ms, 2.07M, 310 | 3.77 ms, 38.4M, 4,084 | 0.63 ms, 6.23M, 421 | | join on 1,000 (entity, ts) pairs | 3.4 ms, 12.9M, 4,011 | 78 ms, 211M, 11,650 | 18.9 ms, 173M, 44,250 | | *not served:* `ORDER BY entity, ts OFFSET 10,000` | 1.7 ms, 18.1M, 10,054 | 0.43 ms, 4.84M, 195 | 14.9 ms, 128M, 433 | | *not served:* `ORDER BY ts OFFSET 10,000` (100,000 rows) | 23.4 ms, 142M, 3,884 | 0.41 ms, 4.16M, 77 | 4.49 ms, 34.6M, 61 | The rows with `ts BETWEEN` (a day, a week, a tenth of the entity's rows give the same cost; 1,000,000 rows, a `date` second key and a table sorted by the entity give 0.33M to 0.62M for the rows, 0.5M to 0.8M for the aggregates) are the default's cost, 2 to 3.5 times a b-tree's in instructions and 5 to 15 times in blocks, except the latest rows of a range, which come from the grouped order (6x fewer than the default's): a range with no `LIMIT` reads the unit of the group (30,368 rows scanned for 2 found) and not two positions of the grouped order, so a `count(*)` of a range takes the same 0.7M whatever its width. `key_order` takes one column or `group, key` (two); a third column is an error (`at most two columns`), the group must be an integer, date or timestamp (a text first key is an error), and a `date` key behaves as a `timestamp` one. The lists of entities are the one within-key loss: the entities are not an array of their own, so a list of 10 reads the entries of the grouped order one entity after the other (3.5x the default's instructions, 1.3x at 60, none at 500). A point directory on the entity gives the list back (3.3M and 18.0M instructions, the index +16%: 6.25 to 7.24 MB) but a list of 500 then costs 198M (ms 90.6 against 19.9) and `count(*)` of one big entity 5 to 18 times more; an array of its own for the entity gives it back for +22% index: by the weighted formula neither pays (B +3.6 against C +11 for the array), so the loss is documented, not fixed. The index is 47% larger than the default's on a table in arrival order (6.25 against 4.25 MB; +18% at 100,000 rows) and 15% smaller on a table sorted by the entity (5.62 against 6.59 MB), where the latest row of 100 entities takes 3.9M instead of 15.8M and the other rows of the table 1.3x to 1.2x fewer instructions. Weighing these ten within-key rows equally (B = mean of log2(S) / log2(1.05), C = G / 3 + W / 5): in arrival order B 9.3 against C 6.2 at 100,000 rows and 8.5 against 15.7 at 300,000, so the form is not the default there; on a table sorted by the entity B 6.4 against C -4.9, where it pays. Set it where the queries are the latest rows of each entity; leave the default where they are lists of entities or whole-table pages. On one big multi-tenant table (300,000 rows, 1,000 tenants, the top one 21%, default against `key_order = 'tenant, ts'` against a b-tree on (tenant, ts), M instructions) the same holds: the latest row of 100 tenants 14.45M to 2.84M (b-tree 3.34M), the latest 5 26.0M to 4.98M (5.11M), the latest 20 of a small tenant 1.12M to 0.36M (0.24M), a keyset page of the top tenant 1.14M to 0.41M (0.25M); `count(*)` of the top tenant is 0.31M by default, 0.25M with `key_order` and 41.0M through the b-tree (the entries of the second order carry the group boundaries), a list of 60 tenants 7.58M for both (b-tree 60.0M), and the index is 8.4 MB against 11.5 MB (-27%; b-tree 9.5 MB). What it still costs: `ORDER BY ts OFFSET 1000` of the whole table, which names no tenant, 1.12M to 1.64M (b-tree 622M). On the composite table of 100 entities (`count(*)` per entity of 100 entities, 300,000 rows) the three are 3.83M by default, 3.81M with `key_order` and 6.22M through the b-tree. **A few big keys** is the other shape, and the one a foreign key of a big events table has: 3,000,000 narrow rows (`dim_a_id` int, `created_at` timestamp, `grade`, `score`, `note`), 60 keys of 50,000 rows each (uniform), the heap in arrival order (keys interleaved), one backend, no parallel workers, instructions of that backend, blocks read warm / cold (the relations evicted from shared buffers; the OS cache is not dropped, so a cold read here is a device read only on a host that has not cached the table). The server has its default settings (`sift.col_auto_merge` on, autovacuum on). The b-tree arms are (key, ts) and (key) alone; the sift arms index (key, ts, grade, score), the default one and `key_order = 'dim_a_id, created_at'`. Three states of the table: fresh and vacuumed (all-visible); just after 10% of its rows were updated and 5% inserted, before the automatic `VACUUM` (the writing and reading sessions held `sift.col_auto_merge` off to pin that moment: with the default the first writes ask for a worker, which `VACUUM`s the table and rewrites the index, and settling took minutes on a table of this size); and settled, the default after that `VACUUM` finished. The b-tree arms are not vacuumed in the last two states: 15% changed rows is under the 20% that autovacuum waits for. State fresh and vacuumed: | query | b-tree (key, ts) | b-tree (key) | default | `key_order` | |---|---:|---:|---:|---:| | `count(*)` of the biggest key | 4.8 ms, 32.2M, 198 / 198 | 2.9 ms, 24.4M, 46 / 46 | 0.2 ms, 0.4M, 34 / 36 | 0.2 ms, 0.3M, 31 / 36 | | `count`, `avg`, `sum` of the biggest key | 151.7 ms, 159.6M, 23,652 / 23,652 | 161.7 ms, 153.3M, 23,500 / 23,500 | 0.2 ms, 0.7M, 42 / 9 | 0.2 ms, 0.7M, 42 / 12 | | `count`, `sum` of 10 keys | 373.7 ms, 677.2M, 29,957 / 29,957 | 317.1 ms, 614.3M, 28,462 / 28,462 | 0.2 ms, 0.7M, 42 / 9 | 0.2 ms, 0.7M, 42 / 12 | | `count`, `avg` of all 60 keys (IN list) | 410.6 ms, 2897.4M, 28,038 / 28,038 | 409.4 ms, 2870.2M, 28,038 / 12,647 | 0.2 ms, 0.9M, 30 / 8 | 0.2 ms, 0.9M, 31 / 8 | | one key, a window of 10% of its rows (`ts` range): `count`, `avg` | 4.6 ms, 11.2M, 2,392 / 2,392 | 147.2 ms, 143.1M, 23,500 / 23,092 | 0.4 ms, 2.0M, 133 / 87 | 0.4 ms, 2.0M, 143 / 105 | | one key, a window of 50% | 25.5 ms, 54.8M, 11,835 / 11,835 | 97.3 ms, 151.5M, 23,500 / 23,500 | 0.7 ms, 6.2M, 378 / 161 | 0.8 ms, 6.2M, 388 / 179 | | one key, a window of one month | 0.8 ms, 5.0M, 1,032 / 1,032 | 82.9 ms, 142.0M, 23,500 / 23,500 | 0.3 ms, 1.4M, 99 / 76 | 0.3 ms, 1.4M, 112 / 95 | | `GROUP BY` key over the 60 keys | 553.6 ms, 4485.3M, 28,041 / 28,038 | 574.6 ms, 4475.6M, 28,041 / 12,806 | 0.3 ms, 1.5M, 45 / 9 | 0.3 ms, 1.5M, 45 / 12 | | every row of one key `ORDER BY ts` | 203.2 ms, 266.5M, 23,655 / 23,652 | 187.1 ms, 260.6M, 23,503 / 23,500 | 52.5 ms, 235.8M, 3,141 / 2,945 | 61.0 ms, 235.8M, 3,143 / 2,951 | | one key `ORDER BY ts LIMIT 100` | 0.2 ms, 0.5M, 73 / 52 | 121.6 ms, 173.6M, 23,503 / 23,500 | 0.3 ms, 0.9M, 58 / 65 | 0.2 ms, 0.6M, 98 / 83 | | one key `ORDER BY ts LIMIT 100 OFFSET 10000` | 11.2 ms, 15.6M, 6,389 / 4,704 | 161.2 ms, 194.0M, 23,503 / 23,500 | 7.3 ms, 28.2M, 600 / 658 | 0.9 ms, 6.9M, 149 / 99 | | `LATERAL` latest 20 of each of 30 keys | 0.6 ms, 3.2M, 100 / 64 | 4436.4 ms, 5759.0M, 702,868 / 702,861 | 3.2 ms, 15.7M, 1,151 / 49 | 0.7 ms, 3.0M, 113 / 82 | | join with a table of 30 keys: `count`, `sum` | 482.5 ms, 2706.3M, 28,039 / 28,038 | 520.9 ms, 2698.3M, 28,039 / 12,762 | 13.6 ms, 73.0M, 1,302 / 1,207 | 14.4 ms, 73.0M, 1,302 / 1,209 | State just after the writes, before the automatic `VACUUM`: | query | b-tree (key, ts) | b-tree (key) | default | `key_order` | |---|---:|---:|---:|---:| | `count(*)` of the biggest key | 187.4 ms, 212.4M, 37,858 / 27,201 | 108.0 ms, 202.6M, 27,037 / 27,037 | 101.5 ms, 130.4M, 29,346 / 28,709 | 125.5 ms, 149.4M, 29,848 / 28,728 | | `count`, `avg`, `sum` of the biggest key | 97.6 ms, 175.5M, 26,524 / 26,524 | 126.3 ms, 172.8M, 27,036 / 27,036 | 239.2 ms, 142.7M, 31,153 / 29,628 | 227.0 ms, 142.5M, 31,158 / 29,631 | | `count`, `sum` of 10 keys | 181.5 ms, 735.7M, 34,321 / 34,321 | 179.7 ms, 667.3M, 32,732 / 32,732 | 199.0 ms, 224.6M, 35,889 / 34,625 | 353.0 ms, 224.8M, 35,892 / 34,628 | | `count`, `avg` of all 60 keys (IN list) | 344.8 ms, 3306.7M, 32,243 / 32,243 | 303.4 ms, 3313.4M, 32,243 / 32,243 | 215.2 ms, 576.0M, 35,889 / 34,143 | 210.3 ms, 576.0M, 35,892 / 34,289 | | one key, a window of 10% of its rows (`ts` range): `count`, `avg` | 2.1 ms, 11.6M, 2,580 / 2,580 | 77.2 ms, 161.8M, 27,036 / 27,036 | 6.7 ms, 11.8M, 4,144 / 4,137 | 6.9 ms, 12.0M, 4,166 / 4,160 | | one key, a window of 50% | 27.2 ms, 57.1M, 12,697 / 12,697 | 164.5 ms, 170.2M, 27,036 / 27,036 | 40.7 ms, 48.2M, 15,719 / 15,621 | 38.4 ms, 48.4M, 15,741 / 15,641 | | one key, a window of one month | 1.0 ms, 5.2M, 1,125 / 1,125 | 171.5 ms, 160.7M, 27,036 / 27,036 | 3.4 ms, 6.9M, 2,488 / 2,493 | 1.8 ms, 7.0M, 2,510 / 2,516 | | `GROUP BY` key over the 60 keys | 575.1 ms, 4981.3M, 32,246 / 32,243 | 532.6 ms, 4973.6M, 32,246 / 21,848 | 282.6 ms, 659.3M, 35,888 / 33,604 | 245.0 ms, 659.4M, 35,895 / 34,628 | | every row of one key `ORDER BY ts` | 112.9 ms, 289.8M, 26,527 / 26,524 | 117.8 ms, 287.1M, 27,039 / 27,036 | 144.6 ms, 371.8M, 29,281 / 28,720 | 187.0 ms, 371.8M, 29,283 / 28,721 | | one key `ORDER BY ts LIMIT 100` | 0.2 ms, 0.5M, 87 / 57 | 112.9 ms, 194.1M, 27,039 / 27,036 | 0.7 ms, 2.3M, 1,590 / 1,529 | 0.3 ms, 0.8M, 1,228 / 1,214 | | one key `ORDER BY ts LIMIT 100 OFFSET 10000` | 5.8 ms, 16.9M, 7,238 / 5,031 | 117.1 ms, 216.3M, 27,039 / 27,036 | 18.0 ms, 45.7M, 7,712 / 7,728 | 13.0 ms, 21.2M, 8,952 / 6,308 | | `LATERAL` latest 20 of each of 30 keys | 0.8 ms, 3.9M, 499 / 80 | 3546.8 ms, 6352.0M, 808,357 / 808,350 | 11.2 ms, 40.6M, 8,723 / 1,394 | 60.1 ms, 234.5M, 1,679 / 1,278 | | join with a table of 30 keys: `count`, `sum` | 388.5 ms, 3113.4M, 32,244 / 32,243 | 397.1 ms, 3105.1M, 32,244 / 19,540 | 233.0 ms, 386.7M, 35,872 / 34,627 | 308.3 ms, 386.7M, 35,872 / 34,625 | State settled, after the automatic `VACUUM`: | query | b-tree (key, ts) | b-tree (key) | default | `key_order` | |---|---:|---:|---:|---:| | `count(*)` of the biggest key | 253.6 ms, 161.8M, 26,639 / 26,639 | 244.9 ms, 158.9M, 27,149 / 27,149 | 1.3 ms, 8.6M, 1,040 / 437 | 8.8 ms, 14.9M, 1,239 / 411 | | `count`, `avg`, `sum` of the biggest key | 181.1 ms, 176.2M, 26,639 / 26,639 | 157.1 ms, 173.3M, 27,149 / 27,149 | 1.0 ms, 8.9M, 1,024 / 468 | 1.8 ms, 8.9M, 1,033 / 478 | | `count`, `sum` of 10 keys | 332.7 ms, 739.1M, 34,478 / 34,478 | 274.6 ms, 670.3M, 32,874 / 32,874 | 1.5 ms, 9.6M, 1,052 / 495 | 1.6 ms, 9.4M, 1,051 / 504 | | `count`, `avg` of all 60 keys (IN list) | 504.0 ms, 3329.8M, 32,384 / 32,384 | 511.7 ms, 3304.4M, 32,384 / 17,991 | 1.3 ms, 7.3M, 912 / 287 | 1.3 ms, 7.3M, 921 / 287 | | one key, a window of 10% of its rows (`ts` range): `count`, `avg` | 6.2 ms, 11.6M, 2,580 / 2,580 | 133.6 ms, 162.4M, 27,149 / 27,149 | 0.8 ms, 4.1M, 296 / 238 | 0.7 ms, 4.2M, 320 / 271 | | one key, a window of 50% | 29.4 ms, 57.1M, 12,697 / 12,697 | 129.2 ms, 170.8M, 27,149 / 27,149 | 2.4 ms, 11.7M, 683 / 347 | 1.3 ms, 11.9M, 709 / 386 | | one key, a window of one month | 0.8 ms, 5.2M, 1,125 / 1,125 | 98.2 ms, 161.1M, 27,149 / 27,149 | 0.6 ms, 3.1M, 250 / 224 | 0.5 ms, 3.3M, 274 / 257 | | `GROUP BY` key over the 60 keys | 469.9 ms, 5005.1M, 32,387 / 32,384 | 701.0 ms, 4995.4M, 32,387 / 17,461 | 2.2 ms, 13.2M, 1,045 / 482 | 1.6 ms, 13.2M, 1,054 / 491 | | every row of one key `ORDER BY ts` | 120.3 ms, 291.0M, 26,642 / 26,639 | 124.6 ms, 288.2M, 27,152 / 27,149 | 42.9 ms, 221.3M, 3,922 / 3,501 | 32.7 ms, 221.3M, 3,933 / 3,510 | | one key `ORDER BY ts LIMIT 100` | 0.1 ms, 0.5M, 87 / 57 | 97.0 ms, 194.6M, 27,152 / 27,149 | 0.4 ms, 1.7M, 243 / 228 | 0.2 ms, 0.8M, 199 / 204 | | one key `ORDER BY ts LIMIT 100 OFFSET 10000` | 5.7 ms, 16.9M, 7,238 / 5,031 | 92.8 ms, 216.9M, 27,152 / 27,149 | 4.8 ms, 34.7M, 901 / 918 | 1.4 ms, 10.0M, 1,316 / 1,190 | | `LATERAL` latest 20 of each of 30 keys | 0.5 ms, 3.8M, 484 / 79 | 2974.9 ms, 6381.7M, 811,883 / 811,876 | 5.6 ms, 25.1M, 4,628 / 241 | 0.8 ms, 5.9M, 745 / 377 | | join with a table of 30 keys: `count`, `sum` | 363.0 ms, 3128.1M, 32,385 / 32,384 | 383.1 ms, 3118.9M, 32,385 / 17,594 | 15.2 ms, 84.3M, 2,004 / 1,533 | 10.3 ms, 84.3M, 2,007 / 1,648 | Where the b-tree is slow and sift is not: everything that counts, sums or groups many rows of a key. A key of 50,000 rows is 23,652 blocks read for `count`/`avg` through a b-tree (152 ms; 28,038 blocks and 554 ms for `GROUP BY` over the 60 keys, 483 ms for the join of 30 keys, 26 ms for half a key) and 0.2 to 14 ms through the pre-aggregates of the sift index (31 to 1,302 blocks): 100 to 3,200 times fewer instructions on a table that is vacuumed (the milliseconds of this run were taken while the host was busy; the instructions and blocks do not move). Settled after the churn the same numbers return (`count(*)` of the biggest key 1.3 ms, 8.6M instructions, 1,040 blocks against 254 ms, 161.8M, 26,639 through the b-tree; `GROUP BY` 2.2 ms against 470 ms; the join of 30 keys 15 ms against 363 ms). The advantage is smaller in the window before the `VACUUM`: the units hold dead rows, the pre-aggregates cannot answer and the rows are checked in the heap, so the whole key costs 130M instructions (b-tree 212M), 101 against 187 ms, `count` of 10 keys 225M against 736M, `GROUP BY` 659M against 4,981M instructions and 283 against 575 ms; on a table that is written all the time the index is in this state between two worker runs, and the b-tree in it for as long as autovacuum waits. The b-tree on (key) alone is slower than the one on (key, ts) for a window (134 against 6.2 ms) and takes 3.0 s for the latest 20 of 30 keys. Where the b-tree wins: a list of one key (`LIMIT 100` 0.1 ms, 0.5M instructions against 0.2 ms, 0.8M with `key_order` and 0.4 ms, 1.7M by default) and the latest 20 of each of 30 keys (0.5 ms, 3.8M against 0.8 ms, 5.9M with `key_order` and 5.6 ms, 25.1M by default); the default's `ORDER BY ts LIMIT 100` of a key reads the units and `key_order` alone matches the b-tree on both within 1.6 times. Where `key_order` wins on this table: a deep `OFFSET` of a key (1.4 ms, 10.0M against 5.7 ms, 16.9M for the b-tree and 4.8 ms, 34.7M by default) and every row of a key in order (33 ms against 120 ms). What it costs: `count(*)` of the biggest key settled is 14.9M instructions against 8.6M by default (8.8 against 1.3 ms), and just after the writes the latest 20 of 30 keys is 234M instructions until the index is maintained. Other shapes (measured the same way): 1,000,000 rows and 30 keys (33,000 rows a key) give the same ranking (settled: whole key 0.5 ms, 2.7M instructions and 365 blocks by default against 20.6 ms, 67.0M and 10,566 through the b-tree; all keys 0.4 ms against 123 ms); with a skew (3,000,000 rows, 30 keys, the biggest one about a third of the table) a count of the biggest key is 67 ms and 3,708 blocks through the b-tree on the fresh table and 0.1 ms and 17 blocks through sift (just after the writes 1,916M instructions and 408 ms against 266M and 189 ms; settled 1,112M and 319 ms against 7.5M and 1.1 ms by default, and 255M and 45 ms with `key_order`); `ORDER BY ts` of every row of the biggest key is 350 to 1,070 ms for all four; a table sorted by the key makes the b-tree faster (whole key 3.5 ms and 988 blocks settled at 1,000,000 rows) and sift not slower (0.2 ms, 136 blocks). The index of the default is 52 MB at 3,000,000 rows (b-tree (key, ts) 95 MB, (key) 21 MB), `key_order` 51 MB, and after the churn 64 and 62 MB. This is the shape the automatic default for a key chosen by `key_cut` is weighed on; the table of a few hundred or thousand keys with a week of rows is the one above. A partitioned table gets nothing from it. Queries that name the partition key (hash by tenant into 64 partitions, 300,000 rows, one index per partition, default against `key_order = 'tenant, ts'`, ms, M instructions, blocks): the latest 20 of the top tenant 0.29 ms, 2.82M, 85 against 0.28 ms, 2.82M, 87; a keyset page 0.25 ms, 2.01M, 72 against 0.24 ms, 2.01M, 74; `OFFSET 1000` of one tenant 0.93 ms, 12.0M against 0.86 ms, 12.0M; `count(*)` of the top tenant 0.13 ms, 0.46M against 0.12 ms, 0.46M; 60 tenants 2.54 ms, 20.8M against 2.12 ms, 20.9M; the index +11% (9.85 to 10.92 MB). Range by month (12 partitions, the partition key is the month): the latest 20 of the top tenant 0.96 ms, 8.84M against 1.12 ms, 8.88M, a keyset page 0.55 ms, 5.00M against 0.60 ms, 5.02M, the index +19.5% (7.4 to 8.9 MB). The partition already holds one key range, so the cut and the arrays add bytes and no speed; a `LATERAL` latest-1 per tenant over a partitioned table is a scan of every partition for each outer row (hash by tenant: 273M instructions with or without `key_order`, a b-tree 8.9M; 12 monthly partitions: 23.2M by default, 6.2M with `key_order`, a b-tree 2.35M). Dropping a month partition costs what deleting a tenant of 1% of the rows costs in instructions (8.9M against 9.7M to 12.6M) and leaves no `VACUUM` debt. The table that gains is the clustered one, where the key is in every query: one big table with `key_order = 'tenant, ts'`, whose latest rows of a tenant cost b-tree-class instructions (above). `ALTER INDEX .. RESET (key_order)` and `REINDEX` build the index without it. Rewriting the table: - `CLUSTER` and `VACUUM FULL` lock it exclusively and work for heap tables with every sift index and for a `sift_table`. - `pg_repack` (online, `--order-by`, 1.5.3 tested on PostgreSQL 18) rebuilds the `sift` and `sift_col` indexes, keeps their options and scan results, also under concurrent writes, as a non-superuser table owner and on a standby; it needs a primary key or a not-null unique index. Its `INSERT .. SELECT .. ORDER BY` copy fills a page's tail through the free space map, so a table of rows of unequal width keeps a small share out of place (0.5% of a 900,000-row test table of wide rows, enough to double the units of a key and year window); `CLUSTER`, `VACUUM FULL` and `CREATE TABLE .. AS .. ORDER BY` write the exact order. It cannot rewrite a `sift_table` (it swaps in a heap copy): an event trigger refuses it, and `pg_repack --only-indexes` is fine. The rebuilt `sift_col` index keeps `track_changes` complete (`sift.col_tracking` stays `t,t,t,t`). Run no `REINDEX .. CONCURRENTLY` of the table or its indexes while `pg_repack` runs on it (it does not support DDL on its table): the two deadlock, the `REINDEX` fails and leaves its invalid `*_ccnew` indexes and, for a `sift_col` index, two extra triggers until you `DROP INDEX` them, while `pg_repack` finishes; a plain `REINDEX` waits for it and then runs as usual. A pg_repack that is killed or loses its connection while it builds the copy leaves what it had committed: `repack.log_` and `repack.table_` with the copy's indexes (a `sift_col` index among them, with the two triggers of its own on the copy table, which nobody writes and which never fire), and its `repack_trigger` on the table, which logs every change of the table until it is gone (a single-row `UPDATE` took 0.2 to 0.3 ms in that trigger against 0.04 to 0.05 ms in the triggers of the `sift_col` index; the log reached 475,000 rows and 100 MB in the test), and which makes the next `pg_repack` of the table refuse with a warning. The table's own index, its triggers and its reads are untouched. Do what that warning says: `DROP EXTENSION pg_repack CASCADE`, then `CREATE EXTENSION pg_repack` (it removes the copy with its index and triggers, the log and the trigger at once; `test/t/col_pg_repack.sh`). - `pg_squeeze` only supports heap tables (tested with 1.9 on PostgreSQL 18: `wal_level = logical`, and `output_plugin_libraries = 'pg_squeeze'` where the minor has that setting; `test/t/col_pg_squeeze.sh`). It builds its copy `tmp_` in the schema of the table, with an index of its own, and applies what the writers did meanwhile by logical decoding, without statements, so no trigger hears it: after a squeeze the log of the index is not taken for complete (`sift.col_tracking`: `triggers` false, `heap_free` false), the copy's triggers go with it, and the table keeps the two of the index. Reads check the heap and answer as it does (one snapshot read through the index and through the heap gave one answer all through a squeeze under a writer); from 4.2 the next `VACUUM`, the maintenance worker or a read that has to check the heap re-baselines the log (`sift.col_tracking` shows `t,t,t,t` again), before 4.2 `REINDEX INDEX i` does. A squeeze that orders by an index does the same. - After any of them run `ANALYZE t` (`pg_repack` does), and `VACUUM t` too with `sift.col_auto_merge` off. `sift.stats()` returns what the session's `Custom Scan (SiftAgg)` scans did, one `(name, value)` row per counter; `sift.stats_reset()` zeroes them. The counters are plain backend memory (no shared memory, no `shared_preload_libraries`): not shared between sessions, reset by a restart or reconnection (read them before a pooled connection is returned, or log them with `auto_explain`). A scan adds its numbers once, when it ends, from the counters `EXPLAIN ANALYZE` shows; a failed statement adds none. A query that kept the normal plan adds `refused.` while `sift.explain_refusals` or `sift.count_refusals` is on (the second counts without the notice), and for a refusal by a fixed limit also `limit.`, the id of [LIMITS.md](LIMITS.md): `SELECT name, value FROM sift.stats() WHERE name LIKE 'limit.%'` lists the limits the session hit. | name | counts | |---|---| | `scans` | `Custom Scan (SiftAgg)` scans that ended (the arms of one fused pass are one) | | `workers_launched` | parallel workers those scans launched | | `units` | units the scans read or answered (not the pruned ones) | | `units_pruned_zone` | units skipped before they were read: their zone maps hold no match | | `units_pruned_set` | units skipped before they were read: only their summary of `key_columns` holds no match | | `units_pruned_search` | units skipped before they were read: none of their rows is in the search selection | | `units_preagg` | of the units, those answered from their pre-aggregates | | `rows_decoded` | rows of the units that were scanned | | `rows_vetted` | rows checked against the heap for visibility | | `rows_rechecked` | rows of dirty units read again from the heap | | `units_rechecked` | units that had such rows | | `rows_dirty_units` | rows of units with a dead row that were read as units (what a rewrite of the index would spare the reads) | | `read_penalty_kinstr` | thousands of instructions the reads of this backend spent on what maintenance would spare them: rows checked in the heap, rows of dirty units | | `maintenance_asks` | VACUUMs and rewrites this backend asked a worker for because its reads owed them | | `units_no_rollup` | exact units scanned for lack of their rollup | | `units_rollup_cost` | exact units scanned because their rollup cost more than their elements | | `lookup_cache_hits` | lookup tables taken from the cache of an earlier query | | `lookup_cache_misses` | lookup tables the cache could have held and that were loaded | `sift.col_rollup_misses()` lists the scans of the session that the pre-aggregates of their index did not answer, by index (`rel`) and reason, most scans first, fixable reasons first. `reason` is a slug (`no_preagg`, `no_rollup`, `key_not_a_dim`, `dim_not_stored`, `measure_not_stored`, `key_expression`, `joined_table`, `union_arms`, `grouping_sets`, `aggregate_filter`, `aggregate_distinct`, ..., `unit_without_rollup` for scans that had them and read units without their rollup); `fixable` is true when another `rollup` or `preagg` option of the index would answer it (the dims and measures the queries asked for are the ones to name); `units` counts the units scanned for lack of their rollup. The list holds up to 256 pairs of index and reason. ### Reading without the heap (track_changes) Between an UPDATE or DELETE and the VACUUM that follows, a `sift_col` index still holds the entries of the old versions, and a read asks the heap which are live. With `track_changes` (a column with a unique index of its own) the index keeps a log of the entries that stopped being live, and a read of an MVCC snapshot takes the log for the whole truth: the pages of the heap it would have visited are not read. `sift.col_tracking(index)` says whether the log can be taken for complete. `track_changes` is on by default (`'auto'`): a new or reindexed index that holds the primary key of its table (one column) tracks it, and without one (or on a partition, a table with inheritance children or a subscription target, which a statement trigger does not hear) the index is built as before with a NOTICE that says why. Tracking installs two internal statement triggers on the table, AFTER UPDATE and AFTER DELETE (dropped with the index, not written by pg_dump), and writes a baseline of the versions already dead in a pass over the heap; `CREATE INDEX`, `REINDEX` and `ALTER INDEX .. SET` say so in one NOTICE. A table that becomes a partition or an inheritance child (`ATTACH PARTITION`, `INHERIT`) ends the completeness of its log at once, and it stays ended after `DETACH PARTITION` or `NO INHERIT` (the statements of the parent were not heard) until `REINDEX INDEX i`; a table attached before 4.2.1 needs the `REINDEX` at the `DETACH` too. `WITH (track_changes = 'off')` keeps the index as it was before 4.0 (no trigger). An index of an earlier release is not changed by `ALTER EXTENSION UPDATE`: `REINDEX INDEX i` gives it the default. The log of an index that 4.0.0 to 4.2.1 wrote is checked against the heap by 4.2.1 and later (`sift.col_tracking`: `complete` true, `heap_free` false) until the next `VACUUM`, the first read or the maintenance worker re-baselines it (docs/UPGRADING.md). ```sql ALTER INDEX i SET (track_changes = 'off'); -- no triggers, nothing complete; the reads check the heap as before 4.0 ALTER INDEX i SET (track_changes = 'id'); -- the triggers and a new baseline (rows written while it was off carry no stamp until REINDEX) ``` What ends the completeness of the log (a read then checks the heap, and the answers stay right) and what brings it back: - `REINDEX INDEX i` gives the index a new baseline at once. A log that is not complete is also made complete again by itself, once the triggers are the option's (both there, enabled for all sessions): the next `VACUUM` of the table, a read that had to check the heap (it asks the maintenance worker; `sift.col_auto_merge` on) or the worker that a write of the table asks for re-baselines it from the heap, with a tombstone for every address no version is live for (`ShareUpdateExclusiveLock`, the writers go on; 71 to 120 ms for 600,000 rows). That is the repair after `CREATE INDEX CONCURRENTLY` and `REINDEX CONCURRENTLY` (writers run beside the build), after `pg_upgrade` and a restore (the triggers are made again, so the log names other ones; `sift.col_tracking` shows `triggers` false until then), and after `DETACH PARTITION` or `NO INHERIT` of a table that was a child (a statement on the parent fires none of the child's triggers, so reads leave the log while it is a child; `ATTACH PARTITION` and `INHERIT` end the completeness at once). After `ALTER TABLE .. DISABLE TRIGGER ALL` followed by `ENABLE TRIGGER ALL` (what `pg_restore --disable-triggers` does; `DISABLE TRIGGER USER` leaves it) the triggers fire for the origin only, and a session with `session_replication_role = replica` would write unheard: the worker (not `VACUUM`) enables them for all sessions again first, holding the lock of `ENABLE TRIGGER` for that catalog change only if nobody holds or waits for it, and re-baselines in the next transaction. Triggers that are disabled stay so, the decision of the user, and the reads check the heap. After `pg_squeeze` of the table (it applies what the writers did meanwhile without statements, so no trigger hears it; `sift.col_tracking` then shows `triggers` false and reads check the heap) run `REINDEX INDEX i`. - A dump names the key (`track_changes=id`) and restores the index before the key when `pg_restore -j` or `pg_upgrade` runs, so `CREATE INDEX` and `REINDEX` accept a column without its unique index yet, with a NOTICE; reads check the heap until the constraint exists, and a row changed before that is not in the log: once it does, the next `VACUUM` or the worker re-baselines the log (`REINDEX INDEX i` does it at once). `ALTER INDEX .. SET (track_changes = 'col')` keeps the error. - `DROP INDEX CONCURRENTLY`, `DROP INDEX` and `DROP TABLE` of a tracked index work as for any index, with no `ALTER INDEX .. SET (track_changes = 'off')` first: the index depends on its two triggers and the transaction that deletes it removes them (a rollback leaves both, a drop that times out leaves the invalid index and its triggers, and the next drop removes both). Writers beside the drop go on, and no query queues behind it: the triggers are removed by the transaction that deletes the index when the table's `ACCESS EXCLUSIVE` lock is free at that moment (a table nothing uses then, as the drop has just waited for its sessions), and otherwise by a background worker that asks for the lock a moment at a time until it has it (the triggers do nothing meanwhile; `pg_stat_activity` shows the worker as `sift maintenance`). `DROP TRIGGER` of one of the two triggers is refused. An index made by a release before 4.2.1 has the old dependency until `ALTER EXTENSION pg_sift UPDATE` (which runs `sift.col_track_convert()`) and its concurrent drop still fails until then (everything else works on it meanwhile). `pg_repack --only-indexes` works: its copy (`index_`) owns no trigger of its own, the triggers stay with the index that keeps its oid, and the log is not complete after the swap until the next `VACUUM` re-baselines it (the reads check the heap meanwhile). A table that is never hard-deleted from (soft deletes) pays for the delete trigger too, but it never fires: only the baseline pass at the build is paid. A read may leave the heap out only when the log is complete, and without the delete trigger a hard DELETE writes no tombstone; there is no setting that trusts the UPDATE trigger alone. Numbers (300,000 rows, the recent 20% updated and not vacuumed, one backend, warm for instructions and ms, cold for MB read; the index with the option off to the default; every row equals the heap's): | recent 20% of 300,000 rows updated, not vacuumed | instructions off to default | ms warm | heap blocks | MB read cold | |---|---|---|---|---| | grouped count of the table | 21.77 M to 10.66 M | 2.28 to 0.84 | 1241 to 2 | 10.09 to 0.41 | | top 20 by a key | 2.28 M to 0.80 M | 0.30 to 0.18 | 151 to 2 | 1.35 to 0.23 | | count of one tenant | 5.46 M to 5.69 M | 0.72 to 0.53 | 1241 to 2 | 10.09 to 0.41 | | point read | 2.48 M to 2.59 M | 0.30 to 0.33 | 2 to 2 | 0.82 to 0.81 | | index-only scan | 0.36 M to 0.37 M | 0.12 to 0.13 | 1 to 1 | 0.15 to 0.15 | | index-only range scan | 0.21 M to 0.22 M | 0.09 to 0.08 | 0 to 0 | 0.14 to 0.14 | | 20% updated at random, not vacuumed | instructions off to default | ms warm | heap blocks | MB read cold | |---|---|---|---|---| | grouped count of the table | 56.32 M to 36.75 M | 7.78 to 3.59 | 3717 to 2 | 29.79 to 0.70 | | top 20 by a key | 2.29 M to 0.81 M | 0.33 to 0.19 | 160 to 9 | 1.38 to 0.27 | | count of one tenant | 14.08 M to 11.72 M | 2.21 to 1.47 | 3717 to 2 | 29.79 to 0.71 | | point read | 2.39 M to 2.48 M | 0.26 to 0.35 | 2 to 2 | 0.80 to 0.86 | | index-only scan | 0.36 M to 0.37 M | 0.14 to 0.14 | 1 to 1 | 0.15 to 0.17 | | index-only range scan | 0.21 M to 0.22 M | 0.08 to 0.12 | 0 to 0 | 0.14 to 0.16 | After a VACUUM no read gains (the same to 1%). The price (100,000 rows, one statement, instructions of the backend, medians of 3): UPDATE of 5% of 100,000 rows covered by the index 144.6 to 207.7 M instructions (+43.6%, WAL +1.7%); 100 rows 3.51 to 4.77 M (+35.9%); one row 0.36 to 0.41 M (+14%); key change of 1% 66.4 to 86.9 M (+30.8%); DELETE of 2% 53.6 to 72.3 M (+34.9%); INSERT +0.6%; CREATE INDEX and REINDEX +187 M (672 to 858 M: the baseline pass over the heap and the log); each further index of the table adds its own probe and log (an UPDATE of 5% with 1, 2 and 3 indexes 207.7, 222.2 and 233.4 M against 144.6 with the option off on one index). Weighted as the defaults are (B = R log2 S / log2 1.05 against C = G/3 + W/5), the option pays from about 1.1 reads for each write of that kind when every read comes while rows are unvacuumed, and never for point reads, index-only scans or a table that is vacuumed as it changes: for those, `WITH (track_changes = 'off')`. When it pays: updates of recent rows in a key-ordered table, read often by grouped aggregates and top-N over the changed part. The fast path holds for recent rows and is lost for scattered updates (the grouped count gains 1.5 at random 20%, against 2.0 at the recent 20%). When it does not: a table that is vacuumed as it changes, a point-read workload, and a backend that connects, runs one query and leaves: it pays the first read of the log, about 5 M instructions and 2 ms for 60,000 entries (1.9 MB; VACUUM drops the entries it removes, so the log is bounded by the changes not yet vacuumed). A count of one tenant repays it in 2 reads (random 20%) to 5 (recent 20%), a grouped count or a top-N in the first, a point read never. ## Replication **Physical:** the index goes through generic WAL, so a hot standby (`pg_basebackup -R`) replays builds, inserts, VACUUM, `CREATE INDEX CONCURRENTLY` and `REINDEX`, and serves `@@>`, ranked `sift.score()`, `count(*)` and pushed-down filters read-only with the primary's rows and scores; a promoted standby takes writes at once. No `shared_preload_libraries`, but install the same pg_sift version on every node that queries. `sift.*` settings are per node. `hot_standby_feedback = on` avoids retryable "segment recycled during scan" errors. Without feedback, `sift.standby_conflicts` (on by default, superuser) makes the primary log a recovery conflict when it reuses space a merge freed: standby scans are waited for (`max_standby_streaming_delay`) or cancelled like any standby query, never served recycled pages, and replay of other long snapshots waits as a heap conflict makes it wait. `off` skips the record: such a scan fails at once with the retryable "segment recycled during scan". **Logical:** only table rows replicate, never indexes or `CREATE EXTENSION`. On the subscriber install pg_sift, create the table and its own sift index (before or after the initial sync) and the apply worker maintains it, with a primary key or `REPLICA IDENTITY FULL` (a sift index is never a replica identity). Matches and counts equal the publisher's, and so do scores while both indexes saw the same changes: BM25 counts postings not yet vacuumed, so an index rebuilt on one side only (`REINDEX`) scores slightly differently. ```sql CREATE PUBLICATION docs_pub FOR TABLE docs; -- publisher CREATE EXTENSION pg_sift; -- subscriber CREATE TABLE docs (id int PRIMARY KEY, title text, body text, published date, category int); CREATE INDEX docs_body ON docs USING sift (body); CREATE SUBSCRIPTION docs_sub CONNECTION 'host=primary dbname=app' PUBLICATION docs_pub; ``` **sift_table:** replicates physically like any table (generic WAL, no preload). To *publish* one under `wal_level = logical`, `pg_sift` must be in `shared_preload_libraries` on the publisher and every standby of it: the logical change records use a custom WAL resource manager, which a server can only register at preload time. Without the preload a DML on the published table fails with `0A000` (`sift_table table "t" is published ... but pg_sift is not in shared_preload_libraries`, hint: preload or publish a heap staging table); unpublished sift_table tables never need it. INSERT, UPDATE, DELETE and TRUNCATE reach a heap or sift_table subscriber, UPDATE and DELETE with a primary key or `REPLICA IDENTITY FULL` as for heap. `scripts/replication.sh` checks all of it on a primary, a standby and a subscriber in temporary clusters; `--container` puts every node in its own docker container. ## Limits Every fixed limit of the code, with its value, what happens at it and the setting that changes it, is in the generated table [LIMITS.md](LIMITS.md); the list below adds context. - PostgreSQL 17, 18 and 19; x86-64 tested, arm64 tested with PostgreSQL 18 (Linux in CI, macOS by the formula's test), other architectures untested; heap and sift_table tables; BLCKSZ 8192; database encoding UTF8 or SQL_ASCII. - `sift_table` (full list in [docs/COL-TAM.md](COL-TAM.md), section 9): - at most 127 columns (`CREATE TABLE`, and an `ALTER TABLE` that adds a column or brings declared paths back past it, fail with `54000`; `DROP COLUMN` is not checked), 128 directory entries, `BLCKSZ` 8192; - a single `COPY` or `INSERT ... SELECT` writes any number of rows in one transaction (its write buffers become column groups that carry their writer while it runs; `VACUUM` freezes them once committed or drops them if aborted; a 10,000,000-row `COPY` takes about 180 MB in the backend). A transaction of many commands (a loop of single-row `INSERT`s, savepoints, several sessions into the same write buffers) is not bounded either: once the directory is half full every write buffer it closes becomes such a group, and only a write buffer holding a row deleted since the last `VACUUM` or a speculative insertion in progress waits (the insert that cannot open another write buffer then fails with `54000`, saying to `VACUUM` or end the transaction); writers merge the column segments as they go; - `0A000` for a DML on a published table without the preload ([Replication](#replication)), and for an aggregate scan that reads a column holding a non-canonical float NaN (`SET sift.enable_agg = off` for that query); - no pre-aggregate rollups of array elements; a jsonb path is read only once it is declared and every segment holds its leaf (`VACUUM FULL`); no HOT, and shared row locks degrade to exclusive; `xmax` of a row locked by several transactions is the last locker's, not a MultiXactId; - no per-table options (PostgreSQL 17 through 19 give a table access method no reloptions: `WITH` knows heap's and TOAST's only and `amoptions` is for indexes; the patch that adds them, commitfest entry 5631, targets PostgreSQL 20). `sift.table_write_buffer_limit`, `sift.table_inline_promote`, `sift.table_compression` and `sift.table_dead_rewrite_pct` are ordinary settings that the backend doing the work reads when it does it: - inline promotion and the closing of write buffers follow the inserting session's settings (`SET`, `SET LOCAL`, the role's or database's default); a manual `VACUUM` follows those of the session that runs it; - autovacuum has no session: it applies the server settings (`postgresql.conf`, `ALTER SYSTEM`), `ALTER DATABASE ... SET`, and the `ALTER ROLE ... SET` defaults of the bootstrap superuser (the role it runs as), never the writer's session or the owner's role; the maintenance worker does not touch a `sift_table`; - the writing session's settings are not kept for the VACUUM that promotes the rows later, so set what background promotion should use at server or database level; a session `SET` shapes only that session's inserts and its own VACUUM (written segments keep their codec until a merge or rewrite); - `sift_col` indexes have per-index options (`compression` and the rest, above), so a column index needs no setting for it. Tested by `test/t/010_tbl_settings.pl`. - Memory that follows the data of one statement is bound: `sift.facets` (the values of the keyword index), a ranked search without a `LIMIT` (a hit for each match) and a search ordered by keys and then by score (the rows of the first round and the ties of the last key) hold it within `work_mem` and fail with `54000` naming it past it (the planner leaves the keyed scan out when its first round does not fit); `sift.verify` and `sift.segments` keep the length of the documents of the superblocks a term touches within `maintenance_work_mem`; `CREATE INDEX CONCURRENTLY` on a `sift_table` holds the TIDs of its first pass a batch at a time within `maintenance_work_mem`, and `VACUUM` of a `sift_table` holds its dead rows (and the aborted rows of its groups) a batch at a time within half of it, one pass over every index for each batch; the recount of the dead postings of a `sift` index (once 5% of a segment was cleared since the last count) holds the dead rows of the superblocks a window at a time within half of it, and reads the dictionary again for each window. A `sift_table` is read from the tombstone log each backend keeps in memory, up to the `tomb_log_kb` limit ([LIMITS.md](LIMITS.md)): past it a read fails with `54000` naming the log and `VACUUM`, which is not bound by it, and the background pass is asked for at a quarter of it. - One key per index: multicolumn, `INCLUDE` and unique indexes and non-text keys are rejected (build a key of several columns with `sift.sections`). No plain or index-only scans. - Queries: at most 256 leaves, nesting depth 32, phrases of 128 tokens, boosts up to 10000. `fields`: at most 14 field names, 128 bytes of names in all; `prefix_index` up to 15. - A word over 255 bytes is stored as its first 236 bytes and a hash of all of it (`REINDEX` makes an index built before 3.3 follow: it holds such words cut at 255 bytes, so a query for one finds nothing there); `tf` saturates at 255 and document length at 65535. At most 128 segments per index (writers merge the smallest closed segments from 112 entries; with the directory full and merges failing, the open write buffer takes the writes and `sift.workers` shows the failed step until a merge succeeds). A merge reads at most 16 GB of segments: a segment that size is not merged again (VACUUM still clears its dead postings through the liveness bits). - Analyzers: the built-in English stemmer (no stop words) or one text search configuration, fixed per index (REINDEX to change); no n-gram or CJK tokenizer. - `positions = on` is the default and makes the index about 2.5x larger (`positions = off` restores the small index). Phrases are rechecked in the heap for documents without positions: indexes built by earlier versions until `REINDEX`, `positions = off`, rows inserted since the build, and phrases under `NOT`. - `sift.score()`: the rules for `WHERE` are under [Ranking, filters and joins](#ranking-filters-and-joins); it works with `@@> ANY (array)` ([Queries](#queries)) but not in `UPDATE`, `DELETE`, `MERGE` and `FOR UPDATE`. - Under `SERIALIZABLE` a sift scan locks the whole relation (the access method has no finer predicate locks), so concurrent writers can make a serializable transaction fail with `40001`: retry. - Row-level security: a role under a policy gets no ranked scan, no `count(*)` pushdown and no index estimate, so `sift.score()` raises an error and matches come from a sequential scan, unless `sift.rls_scores = on` ([Row-level security](#row-level-security); [SIZING.md](SIZING.md#features-and-their-costs)). Owners and roles that bypass RLS are unaffected. Security barrier views stay refused. - Partitions: - `sift.score()` needs a sift index on every partition the query reads (otherwise an error; unranked matching and `count(*)` still work); - BM25 statistics are per partition, so scores can differ across partitions; - `sift.max_score()` is not available through a parent (call it on a partition); - the score cannot be used inside an aggregate or a non-nested-loop join; compute it in a subquery with `OFFSET 0` or `LIMIT`; - ranked partition scans are never parallel, and neither are scans with a pushed-down filter. - Keyword facets (`sift.facets`): text keys only (use an expression index for integers), cost linear in the distinct values, tables with row-level security refused. `sift.facet_counts` has none of these limits; its array facets and filters on columns outside the `sift_col` index use the ordinary plan. Field weights: [Field weights](#field-weights). - pg_upgrade to a newer PostgreSQL major keeps the files as they are ([UPGRADING.md](UPGRADING.md), tested from the previous major when both are installed). `pg_sift_test` (built with `SIFT_TESTING=1`) is for the test suite only.