pg_sift 3.0.0: a column engine next to the BM25 index (`sift_col`, `sift_table`, aggregate pushdown), one name for the write buffer, and the 2.0.3 fixes. Install the new files, then run `ALTER EXTENSION pg_sift UPDATE TO '3.0'` in every database. Existing BM25 indexes keep working with no `REINDEX`. ## Highlights - **Column engine:** `sift_col` is an index access method that stores the columns of a heap table once more, in compressed columns with zone maps; `sift_table` (`CREATE TABLE ... USING sift_table`) is a table access method with native MVCC that also takes BM25 indexes. `GROUP BY` queries with `count`, `count(DISTINCT)`, `sum`, `avg`, `min` and `max`, filters, `GROUPING SETS`, `ROLLUP`, `CUBE` and a text search as the row selection run as `Custom Scan (SiftAgg)` on the column data when the planner can prove the result is the plain plan's; anything else keeps its plan, and `SET sift.enable_agg = off` turns the pushdown off. Every unit is written with its pre-aggregates. - **Smaller columns, same results at every CPU level:** the chunk codec picks the smallest cascade of encodings per chunk (dictionaries of a segment included, column format 1 minor 3), filters run on the encoded form with scalar, AVX2 and AVX-512 kernels that give identical results (`sift.simd`), and `CREATE INDEX` and `REINDEX` of a `sift_col` index are parallel. - **BM25 after a `VACUUM`:** a segment keeps its live documents, so a vacuumed index that is not yet merged counts and scores as its live documents: `count(*)` of a term costs 1.16 to 1.28 times the clean index's instructions at 10 to 45% deleted (it was 6.6 to 6.7 times), and `sift.score()` is the same before and after the merge. `WHERE col @@> q ORDER BY LIMIT n` is answered from the column units. - **The write buffer:** "mutable segment", "mutable lane" and "delta segment" are one term. The reloption `mutable_limit` is now `write_buffer_limit`; `mutable_limit` stays accepted as a deprecated alias. - **Fixes of 2.0.3, in the column scans too:** `count(*)`, `sift.facets` and the aggregate pushdown no longer count rows deleted before the query's snapshot when a `VACUUM` runs at the same time, and a damaged index raises a corruption error instead of reading past the data or crashing a backend (no remote vector: it takes damage on disk). - Benchmarks are refreshed for 3.0; the source tree is split into `src/core`, `src/bm25`, `src/col`, `src/agg` and `src/table`. ## Compatibility - PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push). - **Upgrade:** after installing the new files run `ALTER EXTENSION pg_sift UPDATE TO '3.0'` (the extension SQL goes from 1.2 to 3.0). - **BM25 indexes:** no `REINDEX`. New segments are written in segment format 2 and the metapage becomes format 1.2 with the first one; **a 2.x library refuses such an index**, so going back to 2.x needs a `REINDEX` there. Older segments are read as they are and rewritten as format 2 when merged. - **Column indexes** are new in 3.0, so no 2.x index needs a rebuild; one built by a pre-release build of 3.0 is refused with the hint `REINDEX`. - **Known issue (fixed in 3.0.1):** a single transaction can write about 500 MB (about 4 million rows of the orders layout) into a `sift_table`; a larger `COPY` fails with `Too many write buffers waiting for promotion`. Load larger data in several transactions, for example 1,000,000 rows each. ## 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.0-1_amd64.deb` - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresql18-server`): `sudo dnf install ./pg_sift_18-3.0.0-1.el9.x86_64.rpm` - Arch Linux: AUR `pg_sift`. Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.0.0/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 3.0.0 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt on the shared core of the PostgreSQL 3.0.0 release: no change to the storage format or the SQL interface. - **Shared engine:** the posting decoder is the one of the PostgreSQL release, with the 2.0.2 and 2.0.3 hardening against damaged data. The column engine and the aggregate pushdown are PostgreSQL features and are not in this extension. No speed-up or slow-down is claimed. - 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 2.0.x open as they are. Download `sift-sqlite-3.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/v3.0.0/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.0.0/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 3.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 3.0.0 release: no change to the index format or the SQL files. - **Shared engine:** the posting decoder is the one of the PostgreSQL release, with the 2.0.2 and 2.0.3 hardening against damaged data. The column engine and the aggregate pushdown are PostgreSQL features and are not in this extension. No speed-up or slow-down is claimed. - 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:` 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 2.0.x opens as it is, nothing to rebuild. Download `sift-mariadb-3.0.0-linux-x86_64.tar.gz` (below, covered by `SHA256SUMS` with the packages), unpack it, copy `lib//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.0.0/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.0.0/mariadb/CHANGELOG.md).