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](https://github.com/brauliobo/pg_sift/blob/v4.2.0/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 `VACUUM`s 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](https://github.com/brauliobo/pg_sift/blob/v4.2.0/docs/UPGRADING.md#4-an-older-release), "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](https://github.com/brauliobo/pg_sift/blob/v4.2.0/docs/UPGRADING.md#what-42-changes), "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](https://github.com/brauliobo/pg_sift/blob/v4.2.0/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](https://github.com/brauliobo/pg_sift/blob/v4.2.0/sqlite/README.md).
Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.2.0/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](https://github.com/brauliobo/pg_sift/blob/v4.2.0/mariadb/README.md).
Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.2.0/mariadb/CHANGELOG.md).