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 INSERTs, 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 INSERTs 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.

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