pg_sift 4.2.1: a patch release of 4.2.0 with fixes for wrong answers and crashes that are older than 4.2.0 (rows of the transaction itself missing from a parallel scan, memory freed under the planner and the build, a scan of a `sift_table` that counted rows its snapshot had deleted), `DROP INDEX CONCURRENTLY` of a `sift_col` index with `track_changes`, reads that stay up while a concurrent build runs, builds and reads of tables of hundreds of millions of rows without the 1 GB allocation limit, a `key_cut` that picks the key whose lookups gain most, and cheaper joins to a lookup table; extension SQL 4.2.1, no format changes: install the files, restart the server (a standby too), then `ALTER EXTENSION pg_sift UPDATE` in each database. ## Highlights - **Fixed: a parallel read of a `sift_col` index missed the rows the transaction itself had inserted (3.6.0 to 4.2.0).** Three paths: a parallel aggregate whose lookup join runs a child plan in the workers, a plan made before the rows were inserted (a prepared statement or a function) that scans the index under a `Gather`, and a parallel aggregate whose leader did not scan (`parallel_leader_participation = off`, or workers that were first to run). The rows wait in the backend until the index is read and a worker has none to flush; the statement now flushes them before it launches the workers. - **Fixed: code that held a string of an index's options across a lock or a catalog lookup read freed memory when the lookup reloaded the index's cache entry** (a `VACUUM (ANALYZE)` or an `ALTER INDEX` of another session does that): a crash, or a wrong plan or answer. First affected release by site: the `bm25` insert path, ranked scoring and the paths of a build from 3.4.2, the point column from 3.6.0, the planner check for a point lookup, the row key and the automatic key cut from 4.0.0, the choices of a build from 4.0.4 and 4.1.0. A crash seen in 4.2.0 could not be reproduced outside the test knob that provokes it. No stored data is corrupted by any of these; update is advised. - **Fixed: a scan of a `sift_table` counted rows its snapshot had deleted or updated** when a `VACUUM` of the table invalidated its cache entry in the middle of the statement (serial and parallel scans, and the heap check of a BM25 index on the table). The scan now filters with the state it opened with. - **Fixed: `DROP INDEX CONCURRENTLY` of a `sift_col` index with `track_changes` (the default) failed with `must be first action in transaction` (4.0.0 to 4.2.0).** The index now depends on its two triggers and the transaction that deletes the index removes them. `ALTER EXTENSION pg_sift UPDATE` converts the indexes of earlier releases (`sift.col_track_convert()`, safe to run again). - **Fixed: a `CREATE INDEX CONCURRENTLY` or `REINDEX CONCURRENTLY` that had not finished made `sift.workers`, `sift.col_index_status` and the functions that open an index by name fail** (`invalid page layout`, `could not read blocks`), on the primary and on a standby. The views list only indexes that are ready; a read of an index that is not ready is refused with a message. - **Fixed: builds and reads of very large tables no longer stop at 1 GB (`invalid memory alloc request size`).** The choices `CREATE INDEX` makes hold no memory by rows, a segment is cut at the memory its point directories and order arrays take, the tombstone log of a `sift_table` opens and vacuums past 16.7 million entries, and the merge, the rewrite and `VACUUM` of a `sift_col` index hold the memory they were given, whatever the rows. - **Changed: the default `key_cut` cuts by the key whose lookups gain most**, prices the zones every other leaf would lose, weighs a leaf that follows the order of the table by the rows it saves, and leaves a key alone when its statistics say the cut gains nothing; a column that is all `NULL` no longer blocks the cut of the other keys. `sift.key_cut_decision` reports what `CREATE INDEX` decides. - **Fixed: maintenance wrote an index twice.** `VACUUM` with `sift.col_auto_merge` on no longer copies the segments down into the holes of the rewrite before the rewrite, a grown overlay of new rows in a `key_cut` index in partitions no longer rewrites the whole index each time, a held snapshot keeps the file within the live bytes plus one rewrite pass, and the free space of an index no rewrite is coming for goes back to the file system. - **Improved: joins to a lookup table.** `count(x)` of a column of a joined table keeps a flag for each joined tuple (562 M instructions against 1,642 M for the normal plan, and 1.1 to 1.65 G before, on 1,000,000 child rows of 100,000 parents), lookup tables are priced as rows or as classes of their keys by their keys, a numeric lookup is cached, and the group table of a scan that cannot spill is bound by its groups. No change of the join code fixes a wrong answer of a released version. - **Changed: the tombstone log of a `sift_table` that one backend keeps in memory is bound by `sift.tomb_log_limit`, 8 GB by default**: a read past it fails with `54000` naming the setting, and `VACUUM`, which is not bound by it, drops the entries of the rows it cleared. - **Changed: `sift.col_sections`** lists the point directories (`point_dir`) and the global order arrays (`order_arrays`) in rows of their own; the rows still add up to `pg_relation_size`. ## Who is affected and what to do - **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; then `ALTER EXTENSION pg_sift UPDATE` in each database (4.2 to 4.2.1; nothing to `REINDEX`). The update takes `SHARE UPDATE EXCLUSIVE` on each table it converts, so it waits for a concurrent drop or rebuild of an index of the table. It is no longer required before other DDL: only `DROP INDEX CONCURRENTLY` of an index made by 4.2.0 or earlier needs it. Until it runs, the triggers of such an index are found and its log is trusted, as before. - **If a build of a 4.2.1 development tree ever ran in a database,** check for duplicate trigger pairs: `SELECT tgrelid::regclass, count(*) FROM pg_trigger WHERE tgname LIKE 'sift_track_%' GROUP BY 1` (two per index with `track_changes`). - **Everyone who runs parallel queries over a `sift_col` index inside a transaction that inserts, uses prepared statements or functions that scan a `sift_col` index, or reads a `sift_table` while it is vacuumed:** update. - **Known limits (new bounds, not new wrong answers):** a ranked search of a `sift` index that has to hold every match (no `LIMIT` the search can take, or a filter after it) and `sift.facets` of a keyword index with very many distinct values fail with `54000` naming `work_mem` at the default 4 MB, from about 262,000 hits and about 70,000 distinct values (raise `work_mem` or add a `LIMIT`; spilling to a sort is planned); a `BufFileSeek` past 1 GB in the runs of an aggregate that spills and the rescans of the table by `CREATE INDEX CONCURRENTLY` remain known limits; `parent LEFT JOIN child` with an aggregate over the child's columns, with a `sift_col` index on both tables, runs as the normal plan or as a scan of the parent that loads the child as rows: index the child alone and the scan reads the child, several times faster. - **Going back:** install the 4.2.0 files and restart; `ALTER EXTENSION pg_sift UPDATE TO '4.2'` runs the empty script back. An index converted by 4.2.1 has triggers that the 4.2.0 library does not find: `REINDEX` there makes them again ([docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v4.2.1/docs/UPGRADING.md)). - **Known issue:** the tap-shell test `col_index_replica` (test 7, a standby that answers the units of a merged segment from the exceptions) may fail on some runners: the standby plan can vet units by heap instead. The failure is in the test, fixed in 4.2.2; no data or answer is affected. - **Nothing else changes:** plans, speeds and answers outside the cases above are those of 4.2.0, apart from the joins and the `key_cut` choices above. ## 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.2.0 with the install and a restart; extension SQL 4.2.1 (`pg_sift--4.2--4.2.1.sql`, and an empty `pg_sift--4.2.1--4.2.sql` back); no column format minor and no BM25 metapage minor changes. ## 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.1-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.2.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.2.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.2.1/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.2.1 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.2.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.2.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.2.1/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.2.1/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.2.1 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.2.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.2.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.2.1/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.2.1/mariadb/CHANGELOG.md).