pg_sift 2.0.0: a major release for PostgreSQL with bounded-memory merges, a faster query path over vacuumed indexes, per-index maintenance tuning and a hardened build and privilege model. **Run `ALTER EXTENSION pg_sift UPDATE` in every database after installing the new files** (extension SQL 1.1 to 1.2); the on-disk format is unchanged, so there is no `REINDEX`. Several behaviors change: read "Breaking / behaviour changes" below before upgrading.

## Highlights

- **Merges no longer grow with the segments.** A merge (`sift.merge`, the maintenance worker, `VACUUM`'s rewrite of a segment with dead documents) streams its segments: merging 4 segments of 3M documents (690 MB) at 16 MB of `maintenance_work_mem` took 140 MB of anonymous memory and 17.4 s and now takes 16 MB and 8.3 s (0.4M documents: 22 to 10 MB). The segments written are byte for byte the ones written before. `CREATE INDEX` is bounded by its budget too, and no longer fails with `segment dictionary exceeds 4 GB`, `postings of one term exceed 4 GB` or `string buffer exceeds maximum allowed length`; it reports its progress in `pg_stat_progress_create_index`.
- **Queries over vacuumed indexes are 2.4 to 8 times faster.** The liveness of a superblock is read once instead of once per document. 200,000 documents with a quarter deleted and vacuumed, ms per query: ranked AND 1.36 to 0.26, ranked OR 2.39 to 0.70, unranked filtered scan 2.11 to 0.65, `count(*)` 1.82 to 0.17. Clean indexes are unchanged.
- **Planner and ranker.** Prepared statements get the filter pushdown of the literal query (filter class, user cycles per query: 3.23M to 1.66M, -49%), the estimate of a statement is computed once (`count(*)` class -12%), a join that keeps a small share of the rows searches fewer times (-18%), and a filter on rows stored in one late stretch of the heap is pushed down (filtered one-letter prefix 3.52M to 0.67M). Materialized views get the ranked scan and the count pushdown; `col @@> ANY (array)` plans and runs. Rows, scores and ties are unchanged.
- **Maintenance you can tune per index and per server.** Reloptions `vacuum_segments`, `merge_fanin`, `dead_rewrite_pct` and `vacuum_truncate` (`ALTER INDEX ... SET`, no `REINDEX`), and settings `sift.maintenance_naptime` and `sift.maintenance_cost_delay`. `VACUUM` merges an index built in many segments down in one run, gives the free blocks at the end of the file back (722 blocks to 83 in the measured example), and the worker no longer leaves up to three sealed segments unpromoted after a bulk load. A long merge no longer stalls inserters: the throughput of 8 bulk writers equalled that of one before.
- **Fixes at scale.** `LIMIT`/`OFFSET` above 67 million rows, wildcard/regex/fuzzy leaves over 512 terms, wide prefixes in bounded memory (cancel and `statement_timeout` work during the scan), merges and promotions of segments above 8 GB, a failed promotion or merge returning its blocks and backing off, and keyword values over 255 bytes.
- **Operations and security.** `sift.index_info(index)` and `sift.version()`, `sift.standby_conflicts` for hot standbys, long promotions and merges logged (`sift.log_min_duration`), and the maintenance functions restricted to the table owner or `MAINTAIN`.

## Breaking / behaviour changes

- **Extension SQL 1.1 to 1.2 (action: `ALTER EXTENSION pg_sift UPDATE`).** `sift.segments` gains `extents` and `dead_docs` (it is dropped and recreated: drop a view that selects from it before the update and recreate it after); `sift.index_info` and `sift.version` are new. The packages' smoke test installs 1.0, updates it to the packaged version and runs its queries again.
- **`sift.max_expansions` and `sift.prefix_cache_size` are superuser settings.** A role that sets them in a session gets `permission denied to set parameter`. Set them in `postgresql.conf`, `ALTER SYSTEM`, `ALTER DATABASE ... SET` or `ALTER ROLE ... SET`.
- **`sift.promote`, `sift.merge`, `sift.verify`, `sift.segments` and `sift.segment_postings` need the table owner or `MAINTAIN`** on the index's table (`GRANT MAINTAIN ON table TO role`), as `VACUUM` and `REINDEX` do. Grant it to the roles that call them.
- **A regular expression leaf cannot have back-references** (`/(a+)\1/`); the query fails at parse time. Rewrite it without one.
- **`sift.score()` in `UPDATE`, `DELETE`, `MERGE` and `FOR UPDATE` fails when the statement is planned.**
- **`VACUUM` merges until the policy asks for no more**, down to `vacuum_segments` (default 2), instead of one step: a `VACUUM` of an index left in many segments takes about 4 times as long (0.08 s to 0.33 s at 60 segments of 50,000 documents) and the queries after it are faster. Raise `vacuum_segments` to keep `VACUUM` short. A segment more than 8 times the size of the ones before it is left alone, so an index may keep more segments than the target.
- **`VACUUM` truncates the index file** when 1000 blocks or a 16th of the index are free at its end, if it gets `AccessExclusiveLock` on the index without waiting; a hot standby cancels the queries that hold the index when it replays the lock, like for a heap. Turn it off per index with `ALTER INDEX ... SET (vacuum_truncate = false)`.
- **The maintenance worker stays up to `sift.maintenance_naptime` (2000 ms)** after it started and holds a background worker slot for that time: leave room in `max_worker_processes`.
- **Wide wildcard, regex and fuzzy leaves match instead of failing.** A leaf that expands to more than `sift.max_expansions` (512) terms matches every word it matches and scores as one term; `sift.max_expansions` is now the number of terms a leaf scores individually.
- **`CREATE INDEX` at a small `maintenance_work_mem` writes more, smaller segments** instead of growing its memory or failing; at the default budget the layout is the same. A merge reads at most 16 GB of segments at a time.
- **Source builds link without LTO** unless `SIFT_LTO=1`; the deb, rpm and AUR packages set it, so they are built with LTO as before.

## Compatibility

- PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push). No on-disk format change: existing indexes keep working, with no `REINDEX`. `ALTER EXTENSION pg_sift UPDATE` is required (see above).

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

Then, in each database: `ALTER EXTENSION pg_sift UPDATE;`

Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v2.0.0/CHANGELOG.md).

## SQLite extension

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

- **Shared engine fixes:** a wildcard, regex or fuzzy leaf over 512 terms matches instead of failing, `ORDER BY rank LIMIT` above 67 million rows works, the score bounds hold for queries with more than 256 scored terms, dictionaries whose terms share their first 8 bytes are searched by bisection, and an index with damaged offsets in a sparse term reports a corrupt term instead of crashing.
- **Behaviour change:** a regular expression cannot have back-references (`/(a+)\1/`); the query fails at parse time. Rewrite it without one.
- **Merges:** unchanged (the extension merges with its own streaming code); the 140 MB to 16 MB figure above is the PostgreSQL extension's.
- 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: nothing to upgrade from 1.4.0.

Download `sift-sqlite-2.0.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/v2.0.0/sqlite/README.md).
Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v2.0.0/sqlite/CHANGELOG.md).

## MariaDB extension

pg_sift for MariaDB 2.0.0 (`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.0 release: no change to the index format or the SQL files.

- **Shared engine fixes:** a wildcard, regex or fuzzy leaf over 512 terms matches instead of failing, the score bounds hold for queries with more than 256 scored terms, dictionaries whose terms share their first 8 bytes are searched by bisection, and an index with damaged offsets in a sparse term reports a corrupt term instead of crashing.
- **Behaviour change:** a regular expression cannot have back-references (`/(a+)\1/`); the query fails at parse time. Rewrite it without one.
- **Merges:** unchanged (the extension merges with its own streaming code, bounded by the number of segments merged at once); the 140 MB to 16 MB figure above is the PostgreSQL extension's.
- 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; a server of another distribution or of a series not listed is built from source (`make -C mariadb`). MySQL is not supported.
- Index format 2: an index of 1.4.0 opens as it is, nothing to rebuild.

Download `sift-mariadb-2.0.0-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](https://github.com/brauliobo/pg_sift/blob/v2.0.0/mariadb/README.md).
Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v2.0.0/mariadb/CHANGELOG.md).