pg_sift 3.2.1: fixes for bugs of 3.2.0: a sift_table row that could be lost, a new sift_table that could be left unreadable, wrong or missing results, errors and a crash in queries answered by the SiftAgg scan, two slowdowns against 3.1 (large IN lists, planning), and a false corruption report of sift.col_verify on 3.1. Install the new files and restart the server: the extension SQL does not change (it stays 3.2), so there is no ALTER EXTENSION step and no REINDEX is required.

Highlights

  • Fixed: a sift_table could be left unreadable by its first writes. The first write to a new sift_table first writes the metapage and then the tombstone segment that lays the table out. A session that read the table in between (a SELECT or a VACUUM) cached it as laid out, and its own first write then added a write buffer to a directory that was never flagged as a sift_table. Every later read of the table failed with The metapage does not carry the sift_table flag. The first write now checks that the tombstone segment exists, not only that the table was read before. A test-only setting (sift.table_init_gate, in test builds only) holds the first writer at that point so that the regression test reproduces the window without timing.
  • Fixed: a wrong count on PostgreSQL 18 for two EXISTS (or IN) over the same table. Two sub-queries on the unique key of one table with different conditions (EXISTS (SELECT 1 FROM items i WHERE i.id = e.parent_id AND i.kind = 'k1') AND EXISTS (.. i.kind = 'k4')) are merged by the PostgreSQL 18 planner into one scan of the table (self-join elimination), and SiftAgg applied the conditions of only one of them: it counted 1,200 rows where the normal plan finds 0. A semi, anti or preserved join whose table the planner removed this way now keeps the normal plan. PostgreSQL 17 does not merge the joins and is not affected.
  • Fixed: ERROR: sift: top-N row (p,o) is not in the heap after a HOT update. An ORDER BY .. LIMIT n answered from the column units failed after an UPDATE that left every indexed value as it was (a NULL set to NULL, or a change of an unindexed column that stays in its page): the index entry names the root of the HOT chain, which is a redirect once the page is pruned, and the scan read the row without following the chain. It now reads the row as an index scan does. The query returned an error, never a wrong row.
  • Fixed: a crash of a parallel worker over a partitioned table. The leader hands the workers the superblock offsets of every partition of a SiftAgg scan over a partitioned table whose partitions carry the sift_col index, but a worker opened the first partition only: an item of its work in another partition ran on a source that was not there and the worker crashed (the server restarts every session). A worker now opens the partition it moves to. Hash, range and list partitioned tables, with one, two and four workers and with the leader scanning or not, are in the new test. The same cause crashed a worker of a join over a hash-partitioned table of 4,096 partitions with the leader scanning.
  • Fixed: a row inserted into a sift_table could be lost although its insert was reported done. The writer that closes a write buffer swaps it in the directory and flags its pages sealed afterwards. A session that held a page of the closed buffer kept appending to it until the flag was there, and a promotion that ran in between (VACUUM, sift.table_maintain, a maintenance worker, or the promotion a writer does when it closes a buffer) released the page with the new row on it. The promotion now flags every page sealed before it reads the buffer, as the promotion of a BM25 or column write buffer does. A test-only setting (sift.table_seal_gate, in test builds only) holds the closing writer before it flags, and the regression test reproduces the loss without timing. No format change.
  • Fixed: sift.col_verify of 3.1 reported a sift_col index written for 3.1 readers as corrupt. A column segment written under sift.col_format_minor = 4 or 5 (the setting that keeps new segments readable by 3.1) carried the zone flag of a JSON null that only 3.2 knows, and the zone check of 3.1 compares the flags exactly. The flag is now written from column format minor 6 on. The answers of 3.1 on such an index were right; only its col_verify failed.
  • Fixed: a query with a LEFT JOIN LATERAL (.. LIMIT 1) child returned no rows. 3.2.0 reads such a child (no ORDER BY, a key the table does not repeat) as a lookup table (First Row Lookup Tables). When the query grouped by a column of the lookup row and summed or averaged a float8 column, also with a FILTER (SELECT l.tier, count(*), sum(e.amount) FROM events e LEFT JOIN LATERAL (SELECT d.tier FROM dims d WHERE d.id = e.parent_id LIMIT 1) l ON true GROUP BY 1), the scan chose the order of its lanes before the child became a lookup table, and every group was dropped: the query returned no rows where the normal plan returns them. count(*), count(col), count(DISTINCT ..), min, max, integer sum and avg, and a group key from the fact rows were never affected. The scan now chooses the order after the child is a table.
  • Fixed: large IN lists on a sift_col index were slower than in 3.1. A set filter (col = ANY($1), col IN (..)) of thousands of ids ran up to 12 times slower in 3.2.0 than in 3.1.0, because the sorted members of every list were sent along with the zone test and the column scan rebuilt that test (copy, sort) at each range of units it began. A list of more than 256 members meets the zone of nearly every unit and is tested by its range again, as in 3.1. On 3 million fact rows and 100,000 ids: a generic plan 125.7 ms to 9.8 ms, a literal list 239 ms to 123 ms (3.1.0: 9.5 and 122 ms), IN (SELECT ..) of 100,000 members 141 ms to 27 ms (3.1.0: 71 ms).
  • Fixed: planning of queries on a table with a sift index took a few percent more time than in 3.1. The check of a sift.score(ctid) > c cutoff read the operator class of float8 from the catalog before it looked at the operands of each clause, so every query on such a table scanned pg_opclass several times while it was planned. A short ranked query cost 6% to 23% more CPU than in 3.1.0; it is now within 1% to 3%.

Who is affected and what to do

  • sift_table first write: every 3.2.0 server that creates sift_table tables (CREATE TABLE .. USING sift_table) while other sessions already read them. The window is the time between the first writer’s metapage and its tombstone segment, on a table that has never been written; a table whose first write has completed, and sift_col, BM25 (sift) and heap tables, cannot hit it. If the first write of a new table is always done before any other session reads it (the table is created and loaded in one deployment step, for instance), nothing can go wrong. Check an existing table by reading it: SELECT count(*) FROM t; (a table in this state fails on every read with the message above; sift.verify and sift.col_verify check the indexes of a table, not the table’s own layout). The fix prevents the state; it does not repair a table that is already in it, because the check that raises this error is unchanged. Restore such a table from a backup, or reload it from its source rows into a new sift_table; do not rely on REINDEX or VACUUM (FULL), which read the table first.
  • Lost sift_table row: sift_table users with concurrent writers while a promotion runs (VACUUM, sift.table_maintain, a worker, or another writer closing a buffer). The window is the time between a writer’s swap of a closed write buffer and its flagging of the pages, usually short, longer if that writer is cancelled in between. No setting removes it: sift.table_inline_promote = off and running VACUUM and sift.table_maintain outside heavy concurrent inserts narrow it. A row lost before the upgrade cannot be found from the table; compare a table that took concurrent inserts with its source row count.
  • Zone flag at minor 4 or 5: only servers that set sift.col_format_minor to 4 or 5 to keep indexes readable by a 3.1 library (a standby, or a downgrade path). Segments written by 3.2.0 keep the flag: REINDEX the index under the same setting after installing 3.2.1 if a 3.1 library has to col_verify it. Nothing changes for an index at minor 6, the default.
  • LATERAL .. LIMIT 1 with float sums: queries on a table with a sift_col index with a LEFT JOIN LATERAL (.. LIMIT 1) child without ORDER BY, grouped by a column of the child, that sum or average a float8 column. There is no setting that covers this shape alone; until the new library is installed, SET sift.enable_agg = off runs such a query with the normal plan and its correct answer. The failure is an empty result, not a wrong number.
  • Large IN lists and planning time: queries with IN or = ANY lists of more than 256 members on a sift_col index, and every query on a table with a sift index (the planning cost is small, a few percent of a short ranked query). No setting is needed; installing the new library fixes both.
  • Two EXISTS over one table: PostgreSQL 18 servers with a sift_col index that run a query of this shape. Until the new library is installed, SET sift.enable_agg = off runs the query with the normal plan and its correct answer.
  • Top-N after a HOT update: queries ORDER BY <column> LIMIT n on a table with a sift_col index and UPDATEs that change no indexed value. Until the new library is installed, SET sift.enable_topn = off avoids the error.
  • Parallel workers over partitions: queries on a partitioned table whose partitions carry the same sift_col index and that run with parallel workers. Until the new library is installed, SET max_parallel_workers_per_gather = 0 keeps the query out of the workers.

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental. A drop-in replacement of 3.2.0: extension SQL 3.2, no change of the BM25, sift_col or sift_table formats. Install the new files (a library swap) and restart the server; going back to 3.2.0 needs nothing.

Packages

x86-64 packages for PostgreSQL 18 are attached below (SHA256SUMS covers every asset); other versions build from source with make USE_PGXS=1. The packages depend on liblz4 and libzstd.

  • Debian 13, Ubuntu 24.04 (PGDG postgresql-18): sudo apt install ./postgresql-18-pg-sift_3.2.1-1_amd64.deb
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresql18-server): sudo dnf install ./pg_sift_18-3.2.1-1.el9.x86_64.rpm
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 3.2.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.2.1 release: no change to the storage format, the SQL interface or the shared core.

  • No change: the fixes of this release are in sift_table and the aggregate pushdown, which are PostgreSQL features and are 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.2.0 open as they are, nothing to rebuild.

Download sift-sqlite-3.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 3.2.1 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.2.1 release: no change to the index format, the SQL files or the shared core.

  • No change: the fixes of this release are in sift_table and the aggregate pushdown, which are PostgreSQL features and are 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.2.0 opens as it is, nothing to rebuild.

Download sift-mariadb-3.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.