pg_sift 3.1.0: smaller BM25 indexes, rollups of the elements of a list, a `sift_table` write path with per-row versions, field weights and facet counts, calendar and `jsonb` shapes in the aggregate pushdown, and an opt-in Vulkan backend. The extension SQL goes from 3.0 to 3.1: install the new files, then run `ALTER EXTENSION pg_sift UPDATE TO '3.1'` once per database. No `REINDEX` is needed to upgrade; a 3.0.x library refuses what 3.1 writes (see Compatibility).

## Highlights

- **Smaller BM25 indexes (segment format 3).** On 1M short titles the index goes from 11.27 to 7.93 MiB (7.88 to 4.54 MiB with `positions = off`); on 50,000 articles from 31.13 to 30.21 MiB (12.95 to 12.02 MiB). The document length rows take 4 or 8 bits where a superblock allows, the posting containers of mid-frequency terms are packed (about 9 bits per posting instead of 16), and dictionary entries lose a byte of lengths and one of document frequency per term. Queries cost the same or less (count queries 17% fewer instructions on the titles, ranked top-10 within 1%). Segments of format 2 are read next to format 3 ones; new segments, merges and `VACUUM` rewrites write format 3, and `sift.merge(index, true)` rewrites every segment at once.
- **Rollups of the elements of a list, refreshed on demand.** A `sift_col` unit keeps the distinct tuples of up to 6 low-cardinality leaves of an array of objects, an array of scalars or a map, with the element count and the count, sum, min and max of each numeric leaf. `GROUP BY` any subset of those leaves over `jsonb_to_recordset`, `jsonb_array_elements` or `jsonb_each`, tests on them and `count`, `sum`, `avg`, `min`, `max` are answered from the tuples for the units that are all visible, and scanned in the same plan for the others. 100,000 documents: `GROUP BY kind` 3.1 to 0.12 ms, a two-key `GROUP BY kind, flag HAVING` 3.9 to 0.13 ms; 1,000,000 documents: 28.9 to 2.0 ms and 33 to 1.3 ms; the sidecar grows 2.0% of the index. The `rollup` index option names the leaves; a `SELECT` that finds a unit with a missing or outdated rollup queues it and a background worker rewrites the segment (`Rollup Refresh Queued` in `EXPLAIN ANALYZE`), without blocking the query, `VACUUM` or writers.
- **`sift_table` write path with per-row versions.** A transaction that writes through many commands (a loop of single-row `INSERT`s, `INSERT ... ON CONFLICT`, savepoints, several writers) no longer stops at the 128 entries of the write buffer directory: the rows that are not final keep their transaction and command in the group, every scan, fetch and aggregate reads their visibility from it, and `VACUUM` freezes or drops them. 10,000,000 single-row `INSERT`s into a table with a primary key in one transaction take 58 s with a backend resident size of 189 MB; the same load rolled back is gone after `VACUUM`. `UPDATE`, `SELECT ... FOR UPDATE` and a point read after a transaction that wrote cost about 1.2M instructions instead of 9M (100,000 rows).
- **Field weights and facet counts.** `sift.facet_counts(table, key, query, facets [, filter [, hits]])` returns the counts of several facets of one search in one call (terms with a top N and an exact remainder, ranges, histograms, date histograms, multi-select), all exact, as `SiftAgg` scans when the columns are in a `sift_col` index and as ordinary plans otherwise. `sift.sections(...)` builds the key of an index with `fields`, and `sift.field_query(query, 'title^3, body')` weighs the fields per query.
- **`jsonb` type tags and calendar keys.** Column format minor 4 keeps the JSON type of every value a `sift_col` leaf cannot hold, so `jsonb_typeof(path)` tests (`=`, `<>`, `IN`, `IS NULL`, `'null'`, as a `GROUP BY` key or inside `CASE`/`FILTER`) run in `SiftAgg` (100,000 documents, a number among strings: 100 to 2.1 ms). `date_trunc`, `::date` and `extract` of a `timestamp` column are evaluated once per step of the data instead of once per row: revenue per month on 1M orders of a `sift_table` 244 to 9.1 ms. Also in `SiftAgg`: `jsonb_array_elements_text` of an array of strings, `?`, `?|`, `?&` and `@>` on it, `jsonb_array_length`, and the path type `astext`.
- **Aggregates from the bitmap of a chunk.** `count`, `sum` and `avg` (with or without `FILTER`, ungrouped or grouped by text columns) are aggregated from the bitmap of the rows of a chunk instead of a list of tuples (`Dense Chunks` in `EXPLAIN ANALYZE`): `count(*) FILTER (WHERE ..)` per flag over 387,593 elements costs 10.9M instructions instead of 34.0M. The sums add in the same order, so results do not change.
- **Opt-in Vulkan backend (experimental, off by default).** `make VULKAN=1` builds a backend that runs grouped and filtered integer counts and sums of a `sift_col` index on a Vulkan device (`sift.gpu = auto` or `on`). The packages and the default build have no Vulkan dependency and answer on the CPU; `docs/GPU.md` has the supported shapes and the measured table.
- **Fixes.** A wide wildcard, regex or fuzzy leaf next to an optional phrase no longer scores 0 for the documents that match it; a comparison of a `numeric(p, s)` leaf with a constant no stored value can equal returned wrong rows when units answered from their pre-aggregates, and no longer does.

## Compatibility

- PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push).
- **Extension SQL:** 3.0 to 3.1 (`pg_sift--3.0--3.1.sql`): `sift.col_segments` and `sift.table_segments` report the format of each segment, `sift.col_segments` the bytes of its pre-aggregates, and the facet, field and rollup functions are new. Run `ALTER EXTENSION pg_sift UPDATE TO '3.1'` in every database after installing the files.
- **Formats** (`docs/FORMATS.md`): a BM25 index becomes metapage format 1.3 with its first segment of format 3; a `sift_col` index writes column format minor 4 (type tags, kinds, rollups); a `sift_table` becomes metapage 1.3 with its first group that carries a version section. Existing segments are read as they are, so there is nothing to rebuild.
- **A 3.0.x library refuses the new structures** with a format error and a `REINDEX` hint, and returns no row (the version section would otherwise show rows of running or aborted writers as final). Install 3.1 on every server that opens the data, standbys included, before the first write.
- **Going back to 3.0.x:** first rewrite every `sift_table` with the 3.1 library under `sift.col_format_minor = 3` (`VACUUM (FULL)`), which a 3.0 library reads again; then install the 3.0 files and `REINDEX` every BM25 and `sift_col` index with the 3.0 library. There is no 3.1 to 3.0 extension script.
- Plans change, results do not: calendar keys, the new `jsonb` shapes and the rollups plan as `SiftAgg` by default (`sift.enable_agg = off` restores the normal plan).

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

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

## SQLite extension

pg_sift for SQLite 3.1.0 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.1.0 release: no change to the storage format or the SQL interface; the shared core changed for the PostgreSQL index format 3 and one ranking fix.

- **One fix in the shared core:** a wide wildcard, regex or fuzzy leaf next to an optional phrase no longer loses its score for the documents that match it; counts and the set of matches were right. No speed-up or slow-down is claimed for this extension. The other changes of this release (the column engine, the aggregate pushdown, the `sift_table` write path, field weights and facet counts, the Vulkan backend) are PostgreSQL features and 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.2 open as they are, nothing to rebuild.

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

## MariaDB extension

pg_sift for MariaDB 3.1.0 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.1.0 release: no change to the index format or the SQL files; the shared core changed for the PostgreSQL index format 3 and one ranking fix.

- **One fix in the shared core:** a wide wildcard, regex or fuzzy leaf next to an optional phrase no longer loses its score for the documents that match it; counts and the set of matches were right. No speed-up or slow-down is claimed for this extension. The other changes of this release are PostgreSQL features and 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.2 opens as it is, nothing to rebuild.

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