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).
  • 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.

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