pg_sift 1.3.0: faster joins and tenant-scoped ranked searches, a new benchmark suite with Elasticsearch, and a SQLite extension built from the same engine. For PostgreSQL install the new files; no ALTER EXTENSION step and no REINDEX is needed unless you used phrase_index = pairs (see Compatibility).

Highlights

  • Better plans for ranked searches under joins. A search restricted to a tenant (id IN (SELECT ...), semi joins) is now planned from the index’s real selectivity and from the true cost of draining a ranked scan. On 1M names a rare term among a tenant’s 10% of the names went from a 2.6 s hash join to an 18 ms nested loop semi join; a broad term with a 0.3% tenant went from 190 ms to 68 ms. Tenants where the hash plan wins keep it.
  • Benchmarks you can rerun in minutes. bench/bench all now runs a slim tier (50,000 articles, 34,165 places, about 9 minutes, about 2 GB of disk) and --full the full-size one. Seven engines are measured one at a time, each with all CPUs of the host: sift, sift with positions = off, pg_search, pg_textsearch, rum, pgx-bm25 and Elasticsearch. The README table shows queries per second and latencies, not ratios. sift leads AND and phrase queries and ties mixed ones; Elasticsearch leads filtered queries and joins (its join is a denormalized field) and ties count(*); pg_textsearch and Elasticsearch are ahead on ranked OR. The slim tier index is 31 MiB against 50 MiB for pg_search.
  • Removed phrase_index = pairs. Word positions (the default since 1.2.0) decide phrases from the index; the pairs index added only a small count(*) gain for a much larger index.

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push). No on-disk format change.
  • Breaking: CREATE INDEX ... WITH (phrase_index = ...) now fails like any unknown parameter, and an index built with phrase_index = pairs is refused (“built with other analyzer options”) until ALTER INDEX ... RESET (phrase_index) followed by REINDEX. Other indexes open as before.

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

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 1.3.0 (sift, a loadable extension; a separate component from the PostgreSQL extension above). First release: the pg_sift search engine as a SQLite virtual table.

  • Search: boolean, phrase, prefix, wildcard, regex and fuzzy queries, exact BM25 ranking with block-max pruning pushed into ORDER BY rank LIMIT k, sift_count, highlights, snippets and facets.
  • Speed against FTS5: ranked AND top-10 59x, OR 287x, phrase 18x faster, with an index 1.05x larger and a similar build time.
  • Storage: shadow tables with a bounded page cache, segments published in the SQLite transaction (rollback, savepoints, crashes and WAL readers stay consistent), streaming tiered merges, external-content and contentless modes (73 percent less space).
  • Safe by construction: threads (one connection per thread, or a shared one in serialized mode), AVX2/AVX-512 chosen at load with identical scores on every path, ASan/UBSan/TSan runs in CI.
  • 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; this is the first release, so there is nothing to upgrade.

Download sift-sqlite-1.3.0-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.