pg_sift 2.0.3: a fix for count(*) and facets running next to VACUUM, and two more hardenings of the decoder against damaged index data. Install the new files: the extension SQL does not change (no ALTER EXTENSION pg_sift UPDATE step), the on-disk format does not change (no REINDEX), the results do not change.

Highlights

  • Fixed: count(*) and sift.facets no longer count rows deleted before the query’s snapshot when a VACUUM runs at the same time. A VACUUM clears the index entries of the tuples it removes and only then marks their pages all-visible, but a count read the index liveness before it read the visibility map, so a page that turned all-visible in between was counted with its dead rows: the index count came out higher than the sequential count of the same snapshot, by up to the rows deleted and not yet vacuumed (the same after a merge or promotion replaced the segments under the scan). Seen on 2.0.2 and every earlier release: 96 differing counts in a 90 s run with 6 readers, deleters, updaters, inserters and a VACUUM/sift.merge loop, none without the VACUUM.
    • Affected: count(*) through the index, sift.facets and an unordered scan that returns no column, while VACUUM or autovacuum runs on the table. Rows returned by a search (SELECT ... WHERE body @@> ...) were never affected: the heap checks them. Nothing is stored wrong, no REINDEX.
    • Now: a VACUUM that removed rows ends with a directory update, and a scan that finds the directory changed since it read it lets the heap decide every page for the rest of the scan. A count that overlaps a merge, a promotion or a VACUUM therefore checks the heap for its remaining superblocks and costs a little for that scan only; a count with nothing running next to it is unchanged.
  • Security: damaged index data fails the query with a corruption error instead of reading past the data or crashing a backend. Two more cases the 2.0.2 checks did not cover, both found by fuzzing the decoder and both reproduced on 2.0.2:
    • a term whose stored count of tf nibbles is 4294967295: the byte counts of its tf stream were summed in 32 bits, wrapped to zero and passed the check that the stream is inside the blob, so a query read about 4 KB past the blob;
    • a term blob that ends between its codec and the next 4-byte boundary, with a large group count: the length left after the codec wrapped around, and the query crashed its backend.
    • Affected: any sift index whose pages are damaged (a bad disk or controller, a torn page, a file edited with the server stopped, a physical copy from a damaged or hostile source). There is no remote vector: no SQL role can write index pages.
    • What to do: install 2.0.3. No REINDEX is needed for healthy indexes. A damaged index fails the query with XX001/XX002 (sift: corrupt posting data: ...); rebuild it with REINDEX, and use sift.verify to find out which are damaged. Both checks are made once per term, not per posting.

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push). No extension SQL change (it stays 1.2) and no on-disk format change: existing indexes keep working, with no REINDEX. After installing the new files nothing else is required.

Packages

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

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

Every change: CHANGELOG.md.

SQLite extension

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

  • Shared engine: the posting decoder is the one hardened for PostgreSQL. A table file with damaged bytes now fails the query with a data-corruption error instead of reading past the term or crashing; the damaged table still has to be rebuilt from its source. No speed-up or slow-down is claimed. The count(*) fix does not apply: SQLite has no visibility map.
  • Needs SQLite 3.38 or newer; Linux x86-64, glibc 2.34 or newer.
  • Storage format 2: nothing to upgrade from 2.0.2.

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

  • Shared engine: the posting decoder is the one hardened for PostgreSQL. Segment files with damaged bytes now fail the query with a data-corruption error instead of reading past the term or crashing the server; CALL sift_rebuild rebuilds such an index from its table. No speed-up or slow-down is claimed. The count(*) fix does not apply: MariaDB has no visibility map.
  • 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. BUILD-INFO in the tarball lists the server version and the glibc each library needs.
  • Index format 2: an index of 2.0.2 opens as it is, nothing to rebuild.

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