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](https://github.com/brauliobo/pg_sift/blob/v1.3.0/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](https://github.com/brauliobo/pg_sift/blob/v1.3.0/sqlite/README.md).
Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v1.3.0/sqlite/CHANGELOG.md).