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 <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.
  • 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 <index> SET (track_changes = 'off'), then ALTER INDEX <index> SET (track_changes = 'id') (about 0.1 s per 600,000 rows, no rewrite), or REINDEX INDEX <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 <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, “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 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); 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 <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 <index>. A table attached (ATTACH PARTITION, INHERIT) before 4.0.2 and written through its parent needs REINDEX INDEX <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.

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. Every change: 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:<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.1.1-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.