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 <columns> 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.

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. Every change: 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:<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 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/<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. Every change: mariadb/CHANGELOG.md.