pg_sift 4.2.0: the aggregate scan answers more of what an application runs (jsonb presence, containment and key tests, HAVING with sub-selects, FETCH FIRST .. WITH TIES, array and range tests) and is chosen more often, VACUUM gives the free blocks of a sift_col index back online, and ten changes are worth reading before the update; extension SQL 4.2 (update chain 4.0 -> 4.0.3 -> 4.1 -> 4.2): install the files, run the preflight scripts/upgrade-check.sh, restart the server (a standby too), then run ALTER EXTENSION pg_sift UPDATE in each database.

Highlights

The figures are those CHANGELOG.md states for this release, with their cases and tests, as the normal plan (PostgreSQL’s plan without the scan) then the scan, or as before then after.

  • Faster: jsonb tests on the index (NOT, NULL, containment and key forms). NOT (doc @> '{"b": "x"}'), (doc @> ..) IS FALSE, NOT (doc ? 'k'), (a AND b) IS FALSE and FILTER (WHERE NOT (a OR b)) of containments, the same in a CASE, and doc IS [NOT] NULL of a jsonb column that may be NULL are answered by the index, where they ran on the normal plan. The index keeps a boolean column for each jsonb column with paths that may be NULL (written by CREATE INDEX and REINDEX; an index built before it has none and keeps the normal plan for those forms until REINDEX). 150,000 documents, 1% to 50% of them without a declared member: NOT of a containment 85 M to 121 M cycles down to 7 M to 10 M, the count of doc IS NULL 35 M to 1.9 M; it costs 2.0% of the index of 1,000,000 rows after a merge and 3.2% of its build (insert +0.4%, promotion +4.6%, merge +5.0%). A containment is also answered as IS NOT TRUE, in a CASE pivot, in a FILTER and over an AND / OR with other conditions (M cycles of one backend: WHERE (doc @> ..) IS NOT TRUE 69.9 to 3.5, a CASE pivot by group 138.3 to 8.9, FILTER (WHERE (doc @> ..) IS NOT TRUE) 133.1 to 8.0). doc ? 'k', doc ?| array[..] and doc ?& array[..] are answered in a FILTER, an OR or a CASE too, and the value of a key read beside its test (sum((doc ->> 'k')::float8) FILTER (WHERE doc ? 'k')) as well: attrs ? 'a' AND dim_a_id < 500 over 300,000 documents of 400 bytes takes 15.4 M instructions and 1.1 ms where the normal plan takes 250 M instructions and 14,252 blocks. A segment written before 4.2 does not say whether a document that is a string was a missing key, so a table of old segments stays on the normal plan for doc ? 'k' until VACUUM flags them (about 140 ms for 600,000 documents of 400 bytes). A containment with a boolean, a JSON null or a string that reads as a boolean or a number as a member ({"flag": true}, {"k": null}) is answered too, and a containment or ? test that names a path the index does not declare keeps the normal plan under the new reason jsonb_path_not_declared, which names the path.
  • Faster: the planner chooses the aggregate scan more often. For a grouped aggregate of one table (no lookup join, no search, no top-N) whose normal plan is a sequential scan and a hash of the groups, the scan’s own work per row is now counted in the units of the plan it is compared with (PostgreSQL’s estimate of that plan runs at about 210 cost units a millisecond, the scan’s price at 833), so a scan that was up to 4 times faster is no longer refused as cost when its rows are not few: max(ends - starts) by tenant over 150,000 rows 18.6 to 7.1, over 18,750 rows 2.7 to 1.4, COALESCE measures over 20,000 events 3.4 to 2.1, NOT lower_inc(r) 0.25 to 0.04 (milliseconds of the statement); a statement the scan does not win stays on the normal plan. A grouped aggregate with lookup joins (facts LEFT JOIN links ON links.parent_id = facts.dim_id GROUP BY ..) takes the scan more often when the normal plan reads more than 2,000 cost units from the scanned table: on 250,000 rows of a sift_table in a tuned profile (seq_page_cost 100, 8 workers, 4 GB work_mem) 193.6 to 107.9 ms and 361,019 to 851 buffers, and no statement of the 132 plan shapes in 8 settings gets slower. The scan prices an expression that PostgreSQL evaluates (max(ends - starts)) at 21 ns for each tuple read and 69 ns for each distinct combination of its columns, so its estimate is about 3 times higher on 150,000 rows of 10 distinct pairs (291 to 907 units for 3.1 ms). The price of the normal plan that probes the keys of a lookup is multiplied by the whole skew of the key column, where it was multiplied by its square root: a statement of 12 hot keys of a column of skew 7 was refused with cost and took 282 million cycles, 42 ms, with the normal plan against 8.2 million, 3.4 ms, with the scan. A NOT EXISTS (or EXISTS, or a scalar sub-select) over a table with a condition that its index does not take is planned by what the child costs for every row the scan reads, where a probe priced at a hundredth of what it takes was chosen and ran several times longer than the normal plan. The heap fetch of a top-N is priced at a page of the b-tree plan with a LIMIT, so a list of rows of one entity (ORDER BY .. LIMIT n over WHERE entity = k) keeps the b-tree plan on (entity, key) where that plan is faster: the scan took 8 of 18 such lists measured on 600,000 rows and 11 of 12 on 200,000, at 1.7 to 2.4 times the instructions of the b-tree plan, and all 30 are the b-tree’s now (1.09 million instructions and 0.09 ms against 1.88 million and 0.16 to 0.25 ms of the scan); the lists the b-tree cannot give in order keep the scan, which takes 4 to 80 times fewer instructions. The direct probe of a correlated LIMIT 1 child is priced at 55 operators where it was 800, so the probes win up to a fifth of the rows of the table as keys: of 60 choices between them 21 were wrong by instructions (1,470 million lost) and none are now.
  • Faster: HAVING column IN (SELECT ..) and EXISTS (SELECT ..), sub-selects of the groups, joins to small tables and lookups. In a plain GROUP BY such a HAVING condition is always answered as a filter of the rows, as a semi join the scan runs, where PostgreSQL keeps it above the aggregate and the sub-select ran for each group (GROUP BY kind_id HAVING kind_id IN (SELECT id FROM kinds WHERE enabled): 300,000 rows, 1,000 keys, 100 of them in the list, 40.1 to 0.58 ms and 342 to 5.7 M instructions); the rows form was the cheaper in all 40 cells measured, at 0.09 to 0.78 of the cycles. A sub-select in the select list or the HAVING that reads the group keys or the aggregates is evaluated by the scan once for each group (300,000 rows and 1,000 groups: 36.8 to 1.85 ms and 350 to 23.4 M instructions). NOT IN, NOT EXISTS, an expression or a cast of the key, a correlated IN, grouping sets and a HAVING without GROUP BY keep their HAVING. A table left-joined to a join of two small tables (FROM kinds k CROSS JOIN regions r LEFT JOIN events e ON ..: every kind and region with its count, zero for the pairs of no event) runs in the scan for count, sum of integers and numerics, min and max, and count(e) and count(e.*) of the whole row of the scanned table are answered as count(*). The table of a lookup join that has its own bounds (dim_id IN (SELECT id FROM dims WHERE id <= 2000)) is read by an index scan with the bounds as its keys: a lookup table of 200,000 rows read for id <= 2000 loads in 0.24 ms for 2,000 rows read (was 10 ms for the whole table), and a grouped statement over 300,000 rows that keeps those ids runs in 16.8 M cycles (was 39.1 M). The direct probe of a correlated child (LEFT JOIN LATERAL (.. LIMIT 1)) over a uuid, a date, a timestamp or a timestamptz key compares it in the probe itself: 40,000 probes into a lookup table of 300,000 rows take 585 M instructions for a uuid key where they took 756 M, 247 M for a date key where they took 347 M and 261 M for a timestamptz key where they took 360 M; over a key of several columns 452 M where 549 M.
  • Faster: top-N, ties and ranks. A correlated min() or max() sub-select of a table ((SELECT max(ts) FROM events WHERE entity = e.id) in a select list or a condition) is the first row of its entity in the order of the column, where it was a pass over the rows of the entity for each row of the query around it, or the table grouped and joined: it is planned a second time as ORDER BY ts DESC LIMIT 1 over the non-NULL values, and the planner keeps it where it runs in the scan and costs less; the rows are those of the normal plan, NULLs, empty entities and NULL keys included. FETCH FIRST n ROWS WITH TIES by integer, date or timestamp columns of the index is a top-N of the scan, which gives the n best rows and every row that ties with the n-th: ORDER BY dim_a_id DESC FETCH FIRST 10 ROWS WITH TIES over 300,000 rows takes 10.3 M instructions and 0.94 ms where the normal plan takes 387 M and 44.2 ms. rank() OVER (PARTITION BY k ORDER BY o) <= N and dense_rank() .. (N up to 1,000, N = 1 included) keep in the scan the rows of rank <= N of each group, and SELECT .. FROM (SELECT .., row_number() OVER (PARTITION BY k ORDER BY o) rn FROM t) s WHERE rn <= N (N > 1, and up to 1,000 rows of each group) keeps only the first N rows of each group as the rows pass, where the plan sorted every row; the rows are those of the window. The rows of a top-N per group whose first sort key is a plain column are dropped on that key alone once they come after the last kept one of their group.
  • Faster: array tests and CREATE INDEX on array columns. NOT (tags && ARRAY[..]), NOT (tags @> ARRAY[..]) and NOT (tags <@ ARRAY[..]) over an array column the index holds run on the index (21 of 30 statements of the matrix of negations ran on the index before, all 30 now), with the same rows as the normal plan over NULL and empty arrays, NULL elements, duplicates and multidimensional arrays. CREATE INDEX on a table with an array column (text, varchar, int2, int4 or int8) whose arrays repeat keeps the elements beside the whole arrays, where the build kept the arrays alone and @>, &&, = ANY and unnest cost 150 to 250 instructions a row: on 300,000 rows of two-element integer arrays (441 different), cycles of the statement with the arrays alone, with the elements, and with the normal plan: tags && ARRAY[3, 7] 24.1 M (6.8 ms) to 5.3 M (2.5 ms), normal plan 19.4 M (9.8 ms); tags @> ARRAY[5] 23.3 M (7.4 ms) to 3.7 M (2.0 ms), normal plan 11.6 M (6.0 ms); 7 = ANY (tags) 23.8 M (6.7 ms) to 4.0 M (2.2 ms), normal plan 67.3 M (18.6 ms); unnest(tags) grouped 12.6 M (3.7 ms) to 1.5 M (0.8 ms), normal plan 128.5 M (49.9 ms). The index grows by 5.0% (5.57 to 5.85 MB, the build 15% longer). An index built earlier keeps its layout: ALTER INDEX i SET (paths = '+tags[] int4') and REINDEX INDEX i add the elements (0.31 s for 300,000 rows). A test of the elements of an array column that the index holds as whole arrays only (paths = 'auto:') is now priced for each row it reads, 170 instructions a row: a containment of one element over 300,000 rows of repeating pairs took 24.2 M cycles on the scan against 13.6 M on the GIN bitmap of the normal plan and now keeps the normal plan (sift.explain_why: cost).
  • Faster: tests of ranges, wide values, wrapped columns and IN lists. lower(r) and upper(r) of a range column as an aggregate argument, a group key or a condition are read from the two bounds the index keeps for the containment operators, where the image of each distinct range was decoded (75,000 spans, M cycles: min(lower(r)), max(upper(r)) 3.7 (was 31.3), a month of lower(r) 5.0 (was 97.6), a WHERE on lower(r) 1.2 (was 26.6)). isempty(r), lower_inf(r), upper_inf(r), lower_inc(r) and upper_inc(r) and their NOT are tests of the index too, and a test of a range, a network or a wide value (numeric of more than 18 digits, money, interval) runs there as a branch of an OR, in a FILTER, in a CASE and as IS NOT TRUE (150,000 rows, M cycles before and after: count(*) FILTER (WHERE isempty(r)) 66.4 and 2.8, FILTER (WHERE n > ..) of a numeric(30,4) 97.6 and 3.8). A condition that wraps a column in coalesce(col, c), nullif(col, c), col IS [NOT] DISTINCT FROM k or an integer widening cast (int4_col::bigint = k) is run as tests of the column, where it was an expression evaluated for every row. An IN list tested against the key summaries of the units is cut to the keys of each unit’s range (dim_a_id IN (75 keys) over 300,000 rows: 3.4 M instructions and 0.33 ms where it took 5.2 M and 0.50 ms).
  • Faster: percentiles, mode(), variance and correlation, CASE aggregates and grouping. The values of an ordered set that outgrow work_mem go to sorted runs in a temporary file, where every value went through a tuplesort: the median and the 95th percentile of 300,000 amount::float8 in 40 groups take 188 M instructions and 32 ms where they took 1,205 M and 86 ms, mode() 229 M and 33 ms where it took 1,231 M and 106 ms. The values of a percentile_cont or percentile_disc that are nearly all distinct go to an array of pairs (200,000 distinct float8 values: 49 M instructions and 5.1 ms where it took 805 M and 76.5 ms), mode() WITHIN GROUP (ORDER BY x) of a type with a hash operator counts each group in a hash (300,000 rows: 163 M instructions and 13.6 ms where it took 903 M and 79.6 ms), and percentile_cont(array[0.25, 0.5, 0.75]) of a constant array of fractions is one aggregate for each fraction on the same values. variance, var_pop, var_samp, stddev, stddev_pop and stddev_samp of a float8, int4 or int2 column take 60 M instructions and 3.8 ms over 300,000 rows where they took 187 M and 11.2 ms, and corr, covar_pop, covar_samp and the regr_* functions 137 M and 11.1 ms where they took 347 M and 20.4 ms, in the state of PostgreSQL’s own transition function. sum, avg, min, max and count over CASE WHEN p THEN x run as the aggregate of x with a filter on p (sift.agg_case_filter, on), also with an ELSE that is a value (avg(CASE WHEN status = 'paid' THEN qty ELSE 0 END): a group where the condition never holds is 0, not NULL, and the zeros count in an average, as in the CASE). array_agg(DISTINCT x ORDER BY x) and string_agg(DISTINCT x, ',' ORDER BY x) sort the distinct values of a group once. A grouped statement numbers the groups of an integer key from the value by pages of ids while the values lie in a dense range (GROUP BY id / 2 over 300,000 rows 433 M instructions and 37 ms where it took 475 M and 53 ms); a grouped statement whose keys are sparse takes one hash table that holds 16 bytes for each key, a little more memory than the two tables did, so a scan with a small work_mem may spill a stage earlier.
  • Smaller files and fewer reads of the heap: free blocks given back online by VACUUM, and maintenance that reads pay for. A VACUUM of a sift_col index whose AccessExclusiveLock stays refused for the whole wait (readers that overlap all the time, a writer) gives the free blocks back online, as it does with a standby that streams and without one: it seals and promotes a write buffer above the end the file can have, moves the segments above that end down and cuts the free end under an ExclusiveLock (readers are never kept out, a writer waits for the cut only: longest statement 18 ms in a run of two overlapping readers and a writer); a file of 42.3 MB for 10.8 MB live was 10.9 MB after a few VACUUMs with them running, 28.2 MB with the code before, and the VACUUM says what it did once: index "t_idx": a lock is held on it (waited 5000 ms): 2628 blocks moved, 0 given back online. A VACUUM of an index that is due for a rewrite no longer leaves the free blocks to the automatic rewrite when it waits for its WAL budget (sift.col_auto_merge_wal_pct) or for snapshots, or when only the write buffer is due: a file of 27.0 MB for 11.4 MB live is 11.6 MB after one VACUUM, and under two overlapping readers the file stayed at 2.0 times the live bytes for 56 s of writes and is 1.19 times on average after. The cut of a sift_col index or a sift_table asks again every 50 ms for up to 5 seconds without queueing, like the truncation of a heap, where it asked once and gave up on a table that is read all the time (a shorter lock_timeout ends the wait). The background worker runs each step in a transaction of its own, so the blocks a step freed are reusable by the next: after 40 rounds of churn with the cut off the file is 11,015 blocks for 7,408 live where it was 14,608 for 7,822. A VACUUM that cannot cut says why, once, with the free blocks that stay in it (a lock another session holds, a standby that streams, or a snapshot that may still read the blocks): INFO for VACUUM (VERBOSE), LOG for an autovacuum that logs, for example index "u_idx": not truncating, a lock is held on it (waited 5000 ms): 1809 free blocks stay. The write buffer of a sift_col index is promoted once the backends that read it have paid for its promotion (18,600 instructions and 36 bytes of WAL a row, once): the first statement of a new backend over 200,000 rows with 25,000 of them in the buffer takes 3.79 million instructions and 2.3 ms for one entity where it took 96.4 million and 10.3 ms, 4.47 million for 75 entities where it took 98.2 million, and 19.2 million for the grouped sum where it took 120.3 million. A scan keeps what the tombstone log says of each heap block it asks about twice (a churned 600,000-row index with 59,768 entries in the log and no VACUUM since: the grouped sum of the table 142.2 M instructions on a backend’s second statement where it took 207.7 M), and the merge of the small overlay segments of a key_cut index counts the rows of the units a request scans and also serves an index that is not cut into partitions (one entity of 200,000 rows after 16 promotions: 0.93 million instructions and 110 buffers with 17 segments, 0.48 million and 31 buffers after the merge of the overlays).
  • Faster: BM25 (sift) search over the write buffer. The write buffer of a BM25 index is opened in buffer format 6, whose full pages have a bloom of term hashes of 8,192 bits (2 probes, 4% false positives at 900 terms) where format 0 has 256, so a search for a term that none of the buffered documents has no longer reads every page of the buffer: 220,000 documents and a buffer of 2,000, a term in no document 1.96 to 1.23 M cycles, a buffer of 5,000 2.60 to 1.24 M cycles; a term of 5% of the documents is read as before. The documents that searches walk in the open buffer are added up, and once they cost half of its promotion the worker seals and promotes it (a 5,000-document buffer: a count of a term of 5% of the documents 3.3 M cycles with the buffer, 1.2 M after; promoted after 110 searches that walk all of it; a buffer of fewer than 2,000 documents is left for write_buffer_limit, VACUUM and the seal). A bitmap heap scan over a sift index with a stemmer or any analyzer other than the default (body @@> 'qaaaf' AND category_id = 3 with a btree on category_id) checks the match again only on the rows of a lossy page: 200,000 documents, the count of one word in one of 50 tenants 28.1 to 6.8 M cycles and 60.7 to 11.8 M instructions, and the correlated sub-select that counts it for each of 50 tenants 123 to 69 M cycles. The first statement of a backend that has not loaded the library (no shared_preload_libraries) gets the same recheck. A BM25 index written by 4.2 (once a buffer of format 6 is open) is not readable by 4.1 or older; see item 2 below.
  • Changed and added (the rest, compactly). sift.workers lists the sift_table tables the caller can read next to the indexes (kind is sift_table; failures is 0 and the columns that note one are NULL, as is due) and sift.worker_state takes a sift_table; the update replaces the view with the same columns, so a view of yours that reads it still works. sift.help(slug) answers the slug of a refusal of sift.explain_why and of the notice SiftAgg not used as well (kind refusal). sift.merge, sift.promote and sift.verify take a sift_col index too and run sift.col_merge, sift.col_promote and sift.col_verify on it. CREATE INDEX and ALTER INDEX .. SET of a sift index with the reloption mutable_limit say NOTICE: reloption "mutable_limit" is the older name of "write_buffer_limit"; nothing else changes. sift.agg_lookup_max_rows (deprecated since 3.2; sift.agg_lookup_max_kb replaces it) warns once per session, at the first aggregate plan that its limit decides, with WARNING: sift.agg_lookup_max_rows is deprecated and will be removed in 5.0, and keeps limiting the rows as before. DROP INDEX CONCURRENTLY of a sift_col index that tracks its changes is refused before it changes anything, with ALTER INDEX i SET (track_changes = 'off') first, or DROP INDEX without CONCURRENTLY, in the hint. A correlated lookup (LEFT JOIN LATERAL .. LIMIT 1 into a large mapping table) no longer asks the OS for the whole table and its index once its probes have read a sixteenth of it: the direct probe asks for the heap blocks of the entries it has found, together, and only while the statement is reading heap pages from the OS. The settings and reloptions removed or renamed are items 7 to 10 below. Added: sift.refusal_actions() returns what to change for each slug of sift.refusal_slugs(), so that sift.explain_why(q) w JOIN sift.refusal_actions() a USING (slug) lists it; options of one sift_table as functions, because a table access method has no reloptions before PostgreSQL 20 (sift.table_set_option(table, name, value), sift.table_get_option, sift.table_reset_option, sift.table_options; the rows live in sift.table_option_values, which pg_dump writes with the database, and follow the table through a rename, TRUNCATE, VACUUM FULL, a restore, pg_upgrade and a standby; a grouped read of 20,000 rows costs 299,000 instructions with and without options, and a backend pays 328,000 instructions once, when it first opens the table of options); sift.explain_why accepts the EXECUTE of a prepared SELECT, the notice of sift.explain_refusals carries a HINT for each of the 38 refusal slugs, and the Lookup: line of EXPLAIN ANALYZE says what the direct probes of a correlated child did and how well the lookup tables are vacuumed (99 % of the lookup tables' pages all-visible, and a Lookup Hint: VACUUM the lookup tables .. where a lookup table of 64 pages or more has under half of its pages all-visible). pg_squeeze and a killed pg_repack are tested and documented as they behave: the log of a sift_col index with track_changes is not complete after a squeeze, the reads check the heap and answer as it does, and the next VACUUM re-baselines it. docs/TUNING.md is new: the ten things worth touching, the diagnostic options and when to pick which of two pairs of options that look alike (point_tid_columns and point_columns, order_array_columns and order_columns).
  • Fixed. On PostgreSQL 19, corr, covar_pop, covar_samp and the regr_* aggregates of float8 columns failed in the aggregate scan with float8_regr_accum: expected 8-element float8 array, because the state of the transition function has 8 elements from 19; the scan runs PostgreSQL’s function for them on 19 (17 and 18 keep the kernel), and var_* and stddev_* are unchanged. pg_repack --only-indexes of a sift_col index with track_changes failed with DROP INDEX CONCURRENTLY must be first action in transaction and left its index_<oid> copy behind (4.0.0 to 4.1.3); it works now, and the log is not complete after the swap until the next VACUUM re-baselines it. sift.index_advice, sift.index_choices, sift.key_cut_decision, sift.col_verify, sift.col_clustering, sift.col_tracking, sift.col_stamps, sift.col_rollup_refresh, sift.col_refresh, sift.col_unit_agg and sift.col_unit_hist lock the table before the index, as DROP TABLE and ALTER TABLE do: a function that held the index and then asked for the table waited for a DROP TABLE that waited for the index, and PostgreSQL aborted one of them with deadlock detected; no result was wrong. The sift_col options calendar_columns and rollup (rollup_leaves) take the words their docs promised: calendar_columns = 'auto' is the default like the empty value, and rollup = 'auto' or 'default' is the byte-budget rule. scripts/upgrade-check.sh --to 4.1 (or any release before 4.2) listed a BM25 index whose write buffer is format 6 (index format 1.6) and a sift_col index with the presence leaf of a jsonb document as ok, though 4.1 and older refuse both; they are blockers now. The log of a track_changes index whose triggers do not vouch for it is made complete again by itself (item 5 below). sift.table_maintain() asks for the lock of the cut once and never waits for a reader (it waited up to the 5 seconds of the VACUUM retry, or its statement_timeout, where its contract is to take only what is free now; VACUUM keeps the retry). A worker that looked the table of an index up before the transaction that created the index committed locked the index without the table, the reverse of the order of DROP TABLE; a server built with assertions stopped the worker, and it locks the table first now. GROUP BY k % 3 HAVING k % 3 IN (SELECT ..) on PostgreSQL 18 and 19 moved the condition to the rows because the grouped expression looked like a column; it keeps its HAVING as before 18, and a varchar key cast to text does the same on 17. On PostgreSQL 18 a WHERE id IN (SELECT id FROM t ..) over the table itself by its unique key said SiftAgg not used: outer_join, a label for an outer join the query does not have; it names the merge of the tables (the query keeps the normal plan as before). The VACUUM fixes of the cut of the free end are in the space highlight above.

Who is affected and what to do

  • Update (the steps, in this order): 4.2 is default_version = '4.2' and the update chain is 4.0 -> 4.0.3 -> 4.1 -> 4.2, which ALTER EXTENSION pg_sift UPDATE runs in turn. (1) Install the files. (2) Run the preflight scripts/upgrade-check.sh (--all for every database; it changes nothing; --to 4.1 also lists what 4.1 could not read, for a standby or a node that stays on 4.1): it lists as blockers a stored setting of items 7, 8 and 9 below and, with --to 4.1, the indexes of items 1 and 2. (3) Restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends. (4) Run ALTER EXTENSION pg_sift UPDATE in each database. The update runs pg_sift--4.1--4.2.sql, the first SQL of 4.2 (the options of a sift_table and sift.refusal_actions()); only items 7, 8 and 9 below ask for a step (remove a stored setting before the update, step 2), and the new library reads the indexes of 4.1 and older as they are.
  • Going back: docs/UPGRADING.md, “An older release”. Only items 1 and 2 leave files that an older library refuses, and both go back the same way: install the older release and REINDEX the index with it; there is no switch for either.
  • The ten changes worth reading (each says what changes, what an older library does with it, and the switch; docs/UPGRADING.md, “What 4.2 changes”, has the measurements and the queries):
  • 1. The index keeps a presence bit for the jsonb columns that may be NULL. CREATE INDEX and REINDEX of a sift_col index write one boolean leaf for each jsonb column that has paths and no NOT NULL constraint on the column of the table (an index expression counts as nullable). No option, no NOTICE, no column format minor changes; an index built before has no leaf, keeps the normal plan for those forms and is not wrong, and REINDEX writes the leaf. An older library (3.2, 4.0 and 4.1, tried with 3.2.3, 4.0.5 and 4.1.4) refuses an index that has the leaf: a read fails with sift_col index "x" does not match its column layout (hint REINDEX the index.), a write with index "x" was built with other column definitions, and sift.col_verify reports The metapage does not describe this column layout. The way back is REINDEX INDEX x with the older release; there is no switch. scripts/upgrade-check.sh --to 4.1 lists the indexes with the leaf as blockers.
  • 2. The write buffer of a BM25 (sift) index is format 6. The buffer that is open at the update keeps format 0 and is read as it is; the next one is format 6, and the first of them raises the metapage of the index to minor 1.6, which nothing lowers (an index that has taken no insert since the update is still format 0; sift_col indexes and sift_table tables have no such buffer). An older library (4.1 and older) refuses a BM25 index of 1.6 on a read, a sift.verify and an insert: has on-disk format 1.6 where that build reads up to 1.5, with the hint to install the release that wrote the index on every node that reads it, or REINDEX it; a standby or a node that stays on the older release is such a node. To go back, install the older release and REINDEX the index there; there is no switch. scripts/upgrade-check.sh --to 4.1 lists the index as a blocker.
  • 3. The aggregate scan is chosen more often for a grouped aggregate of one table with no lookup join, search or top-N whose normal plan is a sequential scan and a hash. It changes plans, not files: an older library plans a statement as it did, REINDEX is not needed, and no switch separates this choice; SET sift.enable_agg = off (a session, a role or a database) plans the statement as PostgreSQL does without pg_sift, and sift.explain_why(query) says why a statement did or did not take the scan.
  • 4. One rule for HAVING column IN (SELECT ..) and EXISTS (SELECT ..) of a plain GROUP BY, and sub-selects of the groups. The condition is a filter of the rows, and a sub-select in the select list or the HAVING that reads the group keys or the aggregates is evaluated by the scan; the rows are the same as with the scan off (46 statements). Both are changes of plans, not of files: an older library and REINDEX are not involved, and sift.enable_agg = off is the only switch.
  • 5. A sift_col index with track_changes repairs its own log in more cases. The log of an index whose triggers do not vouch for it (after pg_upgrade, pg_restore --disable-triggers, DISABLE TRIGGER ALL then ENABLE TRIGGER ALL, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, DETACH PARTITION) was checked against the heap on every read until REINDEX or ALTER INDEX; now the next VACUUM, the first read that checks the heap or the maintenance worker that a write asks for re-baselines it, with a ShareUpdateExclusiveLock only. Triggers enabled for the origin only (what ENABLE TRIGGER ALL leaves) are enabled for all sessions by the worker, which asks for the lock without waiting; triggers you disabled stay disabled and the reads keep checking the heap. No format change and nothing to run; answers are right before and after. An older library (tried with an earlier 4.1 release) reads and writes an index that 4.2 repaired without an error and sift.col_verify is clean, but it does not take the log for complete, so its reads check the heap as before the repair. REINDEX is not needed; there is no switch for the repair, and ALTER INDEX x SET (track_changes = 'off') drops the log and its triggers for an index that should not have them.
  • 6. sift.refusal_actions() is a new function. sift.refusal_slugs() keeps its three columns, and pg_sift--4.1--4.2.sql drops or replaces no function that a view, a default or a function of yours may read, so ALTER EXTENSION pg_sift UPDATE does not fail for a database that has a view over sift.refusal_slugs(). No files, no REINDEX, no switch.
  • 7. sift.eval_methods no longer takes latest_hint, snapshot_masks, unit_rewrite and skip_trigger_lookup (nor the -name form); penalty_maintenance is the only name left, and skip_trigger_lookup is now always on. With the library loaded, a SET, ALTER ROLE .. SET, ALTER DATABASE .. SET or ALTER SYSTEM SET that names one fails with invalid value for parameter "sift.eval_methods" and Unknown evaluation method, so a script that runs one breaks; a value already stored (postgresql.conf, postgresql.auto.conf, a role or database setting) does not stop the server or a login, but every session that loads the library and the log say the same text as a WARNING, and the setting is ignored. Remove the names before updating (docs/UPGRADING.md has the queries; scripts/upgrade-check.sh lists each as a blocker with the command, for any release of the library). Segments an earlier release wrote with unit_rewrite stay readable. No REINDEX; the names are gone, leave the setting empty.
  • 8. The setting sift.enable_col is removed. It never changed anything (sift.enable_col_scan and sift.enable_agg are the switches). With the library loaded, a SET, ALTER ROLE .. SET, ALTER DATABASE .. SET or ALTER SYSTEM SET that names it fails with invalid configuration parameter name "sift.enable_col" ("sift" is a reserved prefix.); a value already stored does not stop the server or a login, but every session that loads the library and the log say invalid configuration parameter name "sift.enable_col", removing it, and SHOW sift.enable_col is unrecognized configuration parameter. Remove it from postgresql.conf and from the settings of roles and databases before updating (docs/UPGRADING.md has the queries; the preflight lists each as a blocker). No files, no REINDEX; to compare a plan with the normal one, SET sift.enable_agg = off and SET sift.enable_col_scan = off.
  • 9. The setting sift.table_auto_defrag_dead_pct is removed (no effect since 4.0). It is refused and dropped exactly as sift.enable_col of item 8, with the same queries, the same advice and the same blocker in the preflight. No files, no REINDEX; sift.table_auto_defrag = off turns the background pass of a sift_table off.
  • 10. The sift_col reloptions rollup and rollup_dims are rollup_leaves and rollup_columns. The old names stay accepted through 4.x: CREATE INDEX and ALTER INDEX .. SET with one say NOTICE: reloption "rollup" is the older name of "rollup_leaves" ("rollup_dims" and "rollup_columns" for the other) and read it as the new one, the new name wins when both are given, ALTER INDEX .. SET and RESET of the old name work, and an index that carries the old name keeps it in pg_class.reloptions (pg_dump writes it as it is, pg_upgrade and ALTER EXTENSION UPDATE need nothing). The messages, sift.index_choices (it reports rollup_columns) and the errors name the new spelling, so a script or a view of yours that matches the text of pg_class.reloptions for rollup_dims should match rollup_columns too; the limit ids of sift.stats() keep their names (limit.rollup_dims). The files hold no option names, so no REINDEX; there is no switch, and the old names go away in 5.0.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). Extension SQL 4.2 (pg_sift--4.1--4.2.sql, reached from 4.0 through 4.0.3 and 4.1); no column format minor changes; a BM25 index takes metapage minor 1.6 once a buffer of format 6 is open (item 2).

Packages

x86-64 and arm64 packages for PostgreSQL 17, 18 and 19 are attached below (SHA256SUMS covers every asset); other versions build from source with make USE_PGXS=1.

  • Debian 12 and 13, Ubuntu 22.04 and 24.04 (PGDG postgresql-N, N = 17, 18 or 19): sudo apt install ./postgresql-N-pg-sift_4.2.0-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.2.0-1.el9.x86_64.rpm (.aarch64.rpm on arm64)
  • Amazon Linux 2023 (the distribution’s postgresqlN-server, N = 17 or 18): sudo dnf install ./pg_sift_N-4.2.0-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.2.0 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.2.0 release: no change to the SQL interface or the shared format.

  • No change: the changes of this release are in the PostgreSQL column index, which is not in this extension. No speed-up or slow-down is claimed.
  • Needs SQLite 3.38 or newer; Linux x86-64, glibc 2.34 or newer. Built on Debian 12, loaded into the SQLite of Debian 12, Ubuntu 24.04 and AlmaLinux 10 before release.
  • Storage format 2: tables of 3.4.0 to 3.7.0 open as they are, nothing to rebuild.

Download sift-sqlite-4.2.0-linux-x86_64.tar.gz (below, covered by SHA256SUMS with the packages), unpack it and .load ./sift from the sqlite3 shell (sha256sum --ignore-missing -c SHA256SUMS). Documentation: sqlite/README.md. Every change: sqlite/CHANGELOG.md.

MariaDB extension

pg_sift for MariaDB 4.2.0 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.2.0 release: no change to the SQL files.

  • No change: the changes of this release are in the PostgreSQL column index, which is not in this extension. No speed-up or slow-down is claimed.
  • One library per MariaDB series, because it calls the server’s own symbols and refuses to load into another series: 10.11, 11.4, 11.8, 12.1 and 13.0; Linux x86-64. Each was built in the stock mariadb:<series> image against that server’s headers and loaded into a server of the same image before release. BUILD-INFO in the tarball lists the server version and the glibc each library needs.
  • Index format 2: an index of 3.4.0 to 3.7.0 opens as it is, nothing to rebuild.

Download sift-mariadb-4.2.0-linux-x86_64.tar.gz (below, covered by SHA256SUMS with the packages), unpack it, copy lib/<series>/libsift_udf.so of your server’s series (SELECT VERSION()) into its plugin_dir, then SOURCE sql/functions.sql once per server and sql/sift.sql once per database (sha256sum --ignore-missing -c SHA256SUMS). Documentation: mariadb/README.md. Every change: mariadb/CHANGELOG.md.