pg_sift 4.1.1: a patch release of 4.1.0 that fixes aggregates over a `sift_col` index that counted old versions of rows when a transaction updated the same rows more than once in a new session or an `UPDATE` called a function that runs SQL (every release since 4.0.0), fixes a deadlock between `DROP TABLE` and the maintenance worker of a BM25 index, keeps the scan for the statements that name their entities with `IN (SELECT ..)` in a CTE, lets the probe plans reach tables that were loaded and never vacuumed or analyzed, merges the overlays of a `sift_col` index on its own within the WAL budget, and adds `track_changes = 'ctid'` for tables with no key the index holds. The library only: install the packages and restart the server; the extension SQL stays at 4.1, so there is no `ALTER EXTENSION pg_sift UPDATE` and no `REINDEX`. ## Highlights - **Fixed: the results of aggregates over a `sift_col` index with `track_changes` could count old versions of rows (every release since 4.0.0).** `track_changes` is automatic for a table with a primary key. In a new session whose first statement that needed a table the backends share (the dictionary of a text or varchar key, the statistics of the index, the choices of the planner) was an `UPDATE`, `INSERT ... ON CONFLICT DO UPDATE`, `DELETE` or `COPY`, the registry of the dynamic shared memory attached it in a subtransaction, whose commit advanced the command id under the statement; a row hit by two statements of the same transaction was then counted twice after the commit (a count of 4,000 against 2,000 rows in the heap). Reproduced on 4.0.0, 4.0.2, 4.0.3 and 4.1.0 with a text key (4.0.1 shares the code; the cases with an integer key did not show it; 3.x checks the heap on every read). A table is attached by the next statement that reads now, and the statements that write go without it. Held by `col_dsm_write_statements` (five cases in a new backend each). - **Fixed: aggregates over a `sift_col` index with `track_changes` counted rows twice after an `UPDATE`, `MERGE`, `INSERT ... ON CONFLICT DO UPDATE` or a cursor `UPDATE ... WHERE CURRENT OF` that called a function that runs SQL inside the statement (a plpgsql function with `PERFORM` or `EXECUTE`, a trigger that updates the table) (confirmed on 4.1.0, expected on every release since 4.0.0).** The function advances the command id inside the statement; the entries of the versions the statement ended were looked for, and their tombstones written, with that command id instead of the statement's, so after the first call the probe of every row no longer saw the version the statement ended and its entry stayed. The same wrong count came from a bulk `UPDATE` of a table that also has a `sift` (BM25) index, which seals its segments in a subtransaction of the statement. The command id of the statement is taken once and used for both. Held by `col_stmt_cid` (SPI functions with `PERFORM`, `EXECUTE` and `SECURITY DEFINER`, a trigger that updates the table, a CTE, `MERGE`, a cursor and a rolled back savepoint, each compared with the heap). - **Fixed: `DROP TABLE` could deadlock with the maintenance worker of a BM25 index (every release since 3.7.1, 4.0.x and 4.1.0 included).** The worker held the index and, in the middle of a merge, asked for the table, which the command held while it waited for the index; one of them failed with `deadlock detected`. Nothing was lost (the statement or the worker was aborted, and running the statement again worked). The workers of the BM25 and `sift_col` indexes and the SQL functions that maintain or report on an index now lock the table first and then the index, as a statement does; a worker whose table or index is dropped meanwhile ends without a message. Held by the TAP test `maint_drop_table`. - **Fixed: a backend could end with a segmentation fault when a statement ended whose scan probed a lookup table's btree directly through an Index Only Scan child.** The heap slot of the probe was allocated in the scan node's memory context but listed in the executor state's slot table, which is reset after that context is deleted. It was found with the cheaper price of the probe below, which makes the plan win more often; 4.1.0 contains the same allocation (the plan seldom wins there, and the test of the shape, `agg_probe_direct_slot`, runs without a crash on it); updating is recommended. The slot is now allocated in the executor state's context, and a testing build errors when a scan node ends with a slot of the table in its own context. - **Fixed: a statement that names its entities with `IN (SELECT ..)` in a CTE ran the normal plan** (two shapes: a membership table of 10,000 rows or more, and a join of the keys CTE to a large table by the key). The rewrite to probes left the CTE with no join condition of its own once the statement was flattened. A query level that reads no table with a column index (the CTE, `VALUES` list or sub-select that names the keys) is no longer rewritten to probes; the tables the scan reads are probed as before. `sift.explain_why` and `sift.explain_refusals` now name the tables that wait when no order of the joins looks each table up after the tables its keys read. - **Improved: a `sift_col` index that takes inserts all day is merged when the merge pays.** The units and segments a request for a typical key of the overlays would no longer open, times the requests the index has had, against the rows the merge rewrites; the pass is an eighth of `max_wal_size` at most and the WAL budget of the churn (`sift.col_auto_merge_wal_pct`) sets its size and does not hold it. VACUUM, the worker that the reads of a backend start and `sift.col_maintain` do it; a standby never. An aggregate scan now counts into `pg_stat_*_indexes.idx_scan` (it counted nothing before), which is what tells the rule that the index is read. Held by `col_overlay_merge`. - **Improved: a `sift_col` index asks for the VACUUM of its table** when the transaction that builds it leaves 16 pages or more out of the visibility map, or when a transaction writes it a thousandth of the table (1,000 rows at least), and starts the worker at the commit: a table that was just loaded answers from the pre-aggregates at once. 200,000 rows loaded, then indexed: a grouped sum 8.4 ms and 2,900 blocks before, 0.03 ms and 10 blocks after (`sift.col_auto_merge`, on by default, stops it). The worker takes a table for clean only when the visibility map also holds its pages, and a worker ended in the middle of a VACUUM frees its memory. - **Faster: tables that were never vacuumed or analyzed take the probe plans.** The probe rewrite refused a table whose `pg_class.reltuples` is -1; it takes the planner's own estimate of the rows now. 200,000 events and a links table of 1,000,000 rows, never analyzed: a `NOT IN` 48.5 ms and 618.9 million instructions before, 3.5 ms and 13.4 million after; a join 41.5 ms before, 7.3 ms after (test `agg_probe_unvacuumed`). The price of a run of a correlated `LIMIT 1` child that the scan probes directly is 800 operators, where it was 1,800 (the statement of 3,000 keys took the normal plan at 75.6 ms and takes the scan at 4.9 ms), and the keyed load of a lookup table is priced by what it takes (18 operators an entry): the choice was wrong by more than 5% of the instructions in 16 of 48 measured reads and is in none (`agg_lookup_keyed_crossover`). The direct probe takes an integer column compared with a key of another integer type, and an Index Only child's filter on an index column rejects an entry before the heap is asked. - **New, opt-in: `track_changes = 'ctid'`** keeps the log of ended rows for a table that has no key the index holds (no primary key, or an index without it): two AFTER ROW triggers write the heap address of each version an UPDATE or DELETE ends, so grouped aggregates over unvacuumed rows read no heap page there either. `auto` never chooses it: name it. 200,000 rows: an UPDATE of 5% in an indexed column +16.8% time and +22.1% WAL, a DELETE of 2% +2.8% time and +37.6% WAL, INSERT +0.8% time and +2.2% WAL, one-row transactions +5.7% to +9.3%. One NOTICE at `CREATE INDEX`, `REINDEX` and `ALTER INDEX .. SET` says so. Held by `col_track_ctid`, `col_track_ctid_chains` and the TAP test `col_track_ctid`. - **Docs:** the README shows the case `group_join_dim_filter_keys` (a filtered table's keys joined to a grouped sub-select: 0.34 ms · 1.27 M instructions · 32 blocks against 9.2 ms · 112.8 M · 182, 27x; the change itself is in 4.1.0) with the run behind it in `bench/claims.tsv`. ## Who is affected and what to do - **Everyone on 4.0.0 to 4.1.0 with a `sift_col` index that tracks changes (`track_changes` is automatic for a table with a primary key) and writes in transactions that update the same rows more than once:** update; these are fixes of a wrong answer, three routes, all on tables whose `sift_col` index tracks changes: (1) the first use of a shared dictionary, leaf or verdict table inside a write statement of a new session, (2) a function or trigger that runs SQL inside an `UPDATE` (a plpgsql function with `PERFORM` or `EXECUTE`, a trigger that updates the table), (3) a `sift` (BM25) index maintained by the same statement of a bulk update (its segments are sealed in a subtransaction of the statement). Confirmed on 4.1.0 (the first route also on 4.0.0, 4.0.2 and 4.0.3) and expected on 4.0.x for the others. **Until you update:** `ALTER INDEX SET (track_changes = 'off')` on the `sift_col` indexes of those tables (no rewrite and no `REINDEX`; every row is then checked against the heap, which also repairs an index that was already wrong; after the update `SET (track_changes = 'id')` turns the tracking back on). The price: on a table that has churned and was not vacuumed, the statements after an update check more rows in the heap (the instructions of the next statement rose by 27% to 53% in the cases measured), nothing after a `VACUUM`; writes get cheaper; the first statement of a session is the same. For the first route only, `sift.col_dict_shared_cache_kb = 0` (with `sift.col_leaf_cache_kb = 0` and `sift.verdict_cache_kb = 0`: each backend then parses its own dictionaries; superuser, `postgresql.conf` and a reload, `ALTER SYSTEM` or `ALTER DATABASE .. SET`, no restart) also avoids it, and `shared_preload_libraries` does not. [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v4.1.1/docs/UPGRADING.md#known-issue-of-400-to-410-aggregates-could-count-old-versions-of-rows). - **Update (the steps, in this order):** install the files and **restart the server** (a standby too), because a backend that already loaded the old library keeps it until its connection ends. There is nothing to update in the database: the extension SQL stays at 4.1, so no `ALTER EXTENSION pg_sift UPDATE` and no `REINDEX`; no column format changes. - **Correction: the update does not repair results that were already wrong.** The files fix the statements that run after the restart; an index that already counted old versions of rows keeps reading wrong until it is cleared. For every `sift_col` index that tracked changes on 4.0.0 to 4.1.0: `ALTER INDEX SET (track_changes = 'off')`, then `ALTER INDEX SET (track_changes = 'id')` (about 0.1 s per 600,000 rows, no rewrite), or `REINDEX INDEX `. A plain `VACUUM` may not clear it. An automatic repair is planned for 4.1.2. - **Everyone with a BM25 index** (`DROP TABLE` and the other commands that lock a table and then its indexes, while the maintenance worker of the index runs): this release removes the deadlock. 4.0.x and 4.1.0 are not patched; there a statement that fails with `deadlock detected` is run again. - **Going back to 4.1.0** (or older): run `ALTER INDEX SET (track_changes = 'auto')` first on every index you set to `'ctid'`; 4.1.0 does not know its row trigger and fails the `UPDATE` and `DELETE` of the table with `sift.col_track_old_rows is the statement trigger of an index option`. Nothing else is needed: [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v4.1.1/docs/UPGRADING.md), "An older release". - **New settings and their defaults:** `track_changes = 'ctid'` (opt-in, never chosen by `auto`); the overlay merge uses `sift.col_auto_merge` (on by default) and `sift.col_auto_merge_wal_pct` as before. [docs/OPTIONS.md](https://github.com/brauliobo/pg_sift/blob/v4.1.1/docs/OPTIONS.md) gives the gain, the cost and the reason of each. - **Nothing else changes:** plans, speeds and answers outside the cases above are those of 4.1.0. ## Known issues Not fixed here, each with its reason: - **The deadlock between `DROP TABLE` and the maintenance worker of a BM25 index is fixed in 4.1.1 only** (not backported to 4.0.x or 4.1.0). - **`track_changes = 'ctid'` queues an event for each row a statement updates or deletes, about 42 bytes a row that PostgreSQL does not spill:** one statement over 10,000,000 rows raises the peak memory of the backend by about 420 MB; run such a statement with `track_changes = 'off'`. - **A float sum stays out of the eager rewrite** (the sum of the amounts of each key first): float addition depends on the order, so the rewrite would change the bits of the result. A numeric sum takes it. - **A `key_cut` index takes only integer, date, timestamp, numeric and float keys** (the columns `key_cut` takes); the build by key ranges needs the same, so a text key keeps the runs in the order of the table. - **Scans of 6 to 18 ms can get slower with workers** (as with literals): a scan this short does not repay starting them, and the planner's price is not exact at that size; `max_parallel_workers_per_gather = 0` for such a statement. - **The planning of a `NOT EXISTS` that the scan keeps as the plain form grows with `default_statistics_target`:** PostgreSQL's `eqjoinsel` compares the most common values of both join columns ([docs/AGG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.0/docs/AGG.md)); this is the native planner's cost, which the scan does not change. - **A `key_cut` index is built by one process, also with `max_parallel_maintenance_workers`:** by design, because the segments of parallel participants overlap in the key and the merge that follows rewrites every row, which took longer than one writer; the build by key ranges is serial too. - **The passes cost about twice the in-memory plan for float sums at a small `work_mem`:** a statement with float sums is read as before (the groups spill to partitions and are merged back), because float sums add in the order of the scan; raise `work_mem` or `hash_mem_multiplier` for such a report. - **Still as before (from 4.0.0, not fixed here):** `DROP INDEX CONCURRENTLY` of an index with `track_changes` and `pg_repack --only-indexes` fail with `must be first action in transaction` (run `ALTER INDEX SET (track_changes = 'off')` first); after `ENABLE TRIGGER ALL` following a `DISABLE TRIGGER ALL` (what `pg_restore --disable-triggers` does), and after `REINDEX CONCURRENTLY` the reads of the table go to the heap, which is slower and not wrong, until a plain `REINDEX INDEX `. A table attached (`ATTACH PARTITION`, `INHERIT`) before 4.0.2 and written through its parent needs `REINDEX INDEX ` at its `DETACH PARTITION` or `NO INHERIT`. ## Tests `col_dsm_write_statements` (the wrong count in a new session: five write patterns in a new backend each, rows against the heap), `col_stmt_cid` (the wrong count after a function that runs SQL: 17 checks, each compared with the heap), `col_stmt_cid_bm25` (the same with a BM25 index that seals its segments inside the UPDATE), `maint_drop_table` (TAP: `DROP TABLE` against the worker held at a gate), `agg_probe_direct_slot` (the direct probe in three backends one after the other), `agg_probe_keys_cte` and `agg_probe_keys_shapes` (the keys CTE), `col_overlay_merge` and the TAP tests `col_auto_merge_vacuum` and `col_penalty_maintenance` (the merge of overlays), `col_vacuum_request` and the TAP test `col_vacuum_request_hold` (the request for the VACUUM), `agg_probe_unvacuumed`, `agg_lookup_keyed_crossover`, `agg_lookup_keyed`, `agg_lookup_in_keyed`, `agg_join_probe_cost` and `agg_probe_direct` (the plans and the prices), `col_track_ctid`, `col_track_ctid_chains` and the TAP test `col_track_ctid` (the log by heap address: HOT chains, rollback, savepoints, a standby, `kill -9`, `FREEZE`). `rank_ties_group` no longer lets a background merge run between its promotes and its reads. ## Compatibility - PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with `make USE_PGXS=1`). A replacement of 4.1.0, 4.0.3, 4.0.2, 4.0.1 and 4.0.0 with the install and a restart: extension SQL 4.1 (no change of an object since 4.1.0); column format minors 3 to 42 are read and written as in 4.0.0, BM25 metapage minors 1.0 to 1.5. The SQLite and MariaDB extensions: no change since 4.0.0. ## 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.1.1-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.1.1-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.1.1-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.1.1/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.1.1 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.1.1 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.1.1-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.1.1/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.1/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.1.1 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.1.1 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:` 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.1.1-linux-x86_64.tar.gz` (below, covered by `SHA256SUMS` with the packages), unpack it, copy `lib//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.1.1/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.1.1/mariadb/CHANGELOG.md).