pg_sift 3.0.2: the aggregate pushdown now returns the normal plan’s rows in the remaining cases of values a sift_col index cannot store (a NaN in a numeric(p, s) column included), a text test on a member of a free-key object runs in it, and ORDER BY <column>, sift.score(ctid) DESC LIMIT n is much cheaper. Install the new files: the extension SQL does not change (it stays 3.0), so there is no ALTER EXTENSION pg_sift UPDATE step, and no REINDEX.

Highlights

  • Fixed: values a sift_col index cannot store no longer raise “a column of the aggregate scan holds values its index cannot store” where the normal plan answers. 3.0.1 covered a value already in a segment; 3.0.2 covers the rest. A value still in the write buffer, or written after a prepared statement’s generic plan was made, keeps the normal plan (the directory entry of a write buffer records the leaves it holds such a value of, and the table’s plans are invalidated in every session; a prepared statement plans again on its next use). After the rows that held the value were repaired, the pushdown is back even when VACUUM skipped the vacuuming of the indexes (dead rows on under 2% of the heap pages). A JSON number out of the range of its int2, int4, int8, float4 or float8 path keeps the normal plan, its rows or its error, in every aggregate shape. A scan that is already running, or planned, when another session writes such a value returns the rows of its snapshot: the writer in flight, rolled back or committed after the snapshot no longer fails the query. Queries that read other leaves stay on the scan.
  • Fixed: a numeric(p, s) column with a NaN raised that error for the kernel shapes (min, max, sum, avg, count, count(DISTINCT), a group key, ORDER BY ... LIMIT, a comparison, n + 1). The scan now follows PostgreSQL, in segments and in the write buffer: NaN is greater than every number and equal to itself, sum, avg and arithmetic give NaN for a NaN operand, and the cast of a NaN to an integer raises cannot convert NaN to integer for the rows the query takes. A sift_table table still raises for it.
  • Fixed: a text test (=, <>, <, IN, LIKE 'x%') on a member of a free-key object (m.* text, m->>'k1' = 'v4') raised sift_col: predicate on leaf N of N instead of running in the aggregate pushdown.
  • Faster: WHERE col @@> q ORDER BY <columns>, sift.score(ctid) DESC LIMIT n. A pass over the matches in heap order keeps the rows of the best n leading keys and those tied with the last, and only those are scored; the answer is the one of ranking every match and sorting afterwards (ties, NULLS FIRST/LAST, DESC, OFFSET, joins and prepared statements, checked against that on 4,700 generated queries). EXPLAIN shows Order: name, score DESC and Key Passes. User-space instructions of the transaction at 100K rows (40,122 matches): ORDER BY name, score DESC LIMIT 5 170.5M to 53.9M, ORDER BY created_at DESC, score DESC 184.3M to 51.0M, through a join 160.6M to 55.2M; at 1M rows (399,781 matches) 2124M to 543M, 2261M to 504M and 2023M to 544M. ORDER BY score DESC LIMIT n and the unordered scans are unchanged. A ranked scan that nothing limits (a Sort above it, or no LIMIT) ranks every match in one search instead of searching again for 1024 hits, then 16 times as many, each from scratch (a drained ORDER BY score DESC on 40,122 matches: 108,994 heap checks to 80,244).

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push). No extension SQL change (it stays 3.0) and no change of the BM25 or sift_col formats: 3.0.0 and 3.0.1 indexes and sift_table tables open as they are, and going back to 3.0.1 needs nothing. After installing the new files nothing else is required.
  • Plans change, results do not: the keyed ranked scan is picked by cost for the queries above, and queries that read a leaf holding a value the index cannot store run the normal plan in more cases.

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.0.2-1_amd64.deb
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresql18-server): sudo dnf install ./pg_sift_18-3.0.2-1.el9.x86_64.rpm
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 3.0.2 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.0.2 release: no change to the storage format, the SQL interface or the shared core except one file, bm25/sift_eval.c.

  • One change in the shared core: a ranked query with no limit now ranks every match in one search instead of searching again for 1024 hits, then 16 times as many; the ranked rows and their scores are the same. No speed-up or slow-down is claimed for this extension. The other fixes of this release are in the column engine, the aggregate pushdown and the ranked scan of PostgreSQL, which are not in this extension.
  • 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.0.1 open as they are.

Download sift-sqlite-3.0.2-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.0.2 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.0.2 release: no change to the index format or the SQL files; the shared core changed in one file, bm25/sift_eval.c.

  • One change in the shared core: a ranked query with no limit now ranks every match in one search instead of searching again for 1024 hits, then 16 times as many; the ranked rows and their scores are the same. No speed-up or slow-down is claimed for this extension. The other fixes of this release are in the column engine, the aggregate pushdown and the ranked scan of PostgreSQL, which are not in this extension.
  • 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.0.1 opens as it is, nothing to rebuild.

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