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.

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