pg_sift usage

Reference for everything beyond the README quick start. The examples use the docs table created there. Which of the options to touch, and which to leave alone: 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.

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/).
  • Docker, Docker Compose, CloudNativePG: CONTAINERS.md (the image recipes are in packaging/docker/); a five-minute tour: QUICKSTART.md. Supported versions: 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 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).

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

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.

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).
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:

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.
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:

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). 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).

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.

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. 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):

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).

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.

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.

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).

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.

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.

  • 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).
  • 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_naptimes, 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
sift.count_refusals sift_col, sift_table off diagnostic bool session Counts in sift.stats() why queries kept the normal plan (refused. a few microseconds a query that reaches a candidate path USAGE
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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

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).

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
b sift 0.75 default real 0..1 BM25 document length normalization REINDEX to change; no size effect SIZING
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
stemmer sift ‘’ opt-in none or en Stemmer: en (Snowball English) or none REINDEX to change SIZING
case_folding sift true default bool Fold case REINDEX to change SIZING
accent_folding sift true default bool Strip accents REINDEX to change SIZING
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
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
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
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
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
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
build_segments sift 0 diagnostic int 0..64 Segments written by CREATE INDEX, 0 for one per build participant a serial build USAGE
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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

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 VACUUMs 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), 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).
  • 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 VACUUMs with the readers and the writer running (1.01 times the live bytes); with the code before, the same VACUUMs 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 VACUUMs 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 VACUUMs 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 VACUUMs, 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 VACUUMs 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 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) 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) 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: <measure>' 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, docs/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, “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 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). Every type is kept exactly. Limits: Limits; publishing one under wal_level = logical: 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). Settings: sift.table_write_buffer_limit, sift.table_inline_promote, sift.table_compression, sift.table_dead_rewrite_pct (table in 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.
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; 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), 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, “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 <key> = 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 (<key columns>) INCLUDE (<columns the child returns or filters on>). 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.

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, 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).

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.

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:

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 NULLs, 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.
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 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, section 2.

/* 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);


# 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 VACUUMs 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_<oid> and repack.table_<oid> 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_<oid> 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.<slug> 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.<id>, the id of 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).

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_<oid>) 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.

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; 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, 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 INSERTs, 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), 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): 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; it works with @@> ANY (array) (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; SIZING.md). 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.
  • pg_upgrade to a newer PostgreSQL major keeps the files as they are (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.