pg_sift 1.4.0: the search engine now runs on MariaDB too, from one portable core shared with the PostgreSQL and SQLite extensions. For PostgreSQL install the new files; nothing changes in behavior, SQL or the on-disk format, so there is no ALTER EXTENSION step and no REINDEX.

Highlights

  • One core, three databases. The engine (analyzer, codec, dictionary, evaluator, kernels, segment merge) is built once from src/portable.list behind a single host header, src/sift_host.h, and compiled unmodified for PostgreSQL, SQLite and MariaDB. The PostgreSQL query path compiles to the same instructions as 1.3.1; interleaved benchmark runs of the slim tier show no change beyond noise.
  • make check in about 1.5 minutes on 8 cores (it took about 4): one job queue over up to 8 lanes, faster differential and TAP tests, same coverage (same tests, assertions, mutants and leak rules).
  • Build tooling: scripts/build-bench.sh compares CREATE INDEX time, memory and temporary files of two builds at several budgets, and the runs merged into each segment are logged at debug2.

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.

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

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 1.4.0 (sift, a loadable extension; a separate component from the PostgreSQL extension above). The extension now runs on the shared core; no change to the storage format, SQL or results.

  • Same results, same speed: index tables are byte identical to 1.3.1’s and scores are bit equal (1,242 queries); queries and merges run at the same speed (merge 3.55 s against 3.65 s, peak memory 62 MiB against 68 MiB).
  • Documented against FTS5 (218,058 Wikipedia articles, one thread, warm cache, no CPU pinning): ranked top-10 AND 89x, OR 242x and phrase 10x faster, with an index 1.05x larger and a similar build time. Install and usage are in the README.
  • 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.3.x.

Download sift-sqlite-1.4.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 1.4.0 (sift, a UDF library and two SQL files; a separate component from the extensions above). First release: the pg_sift search engine for any InnoDB table, with no storage engine and no server patch.

  • Search: boolean, phrase, prefix, wildcard, regex and fuzzy queries, exact BM25 ranking with block-max pruning (sift_topk), sift_count, sift_matches, filters and facets, over tables with an integer, text, UUID or composite key. Counts, top-10 order and float32 scores are bit identical to pg_sift’s (5,098 queries on 218,058 articles, 0 differences).
  • Speed against InnoDB FULLTEXT: ranked top-10 AND 183x, OR 725x to 794x, phrase 504x faster; count AND / OR 1,025x / 5,608x; build 8.9x faster; index 1.7x smaller (135.9 MB against 232.0 MB).
  • Freshness: a trigger-fed queue keeps the index in step with the table and reads see committed DML at once (fresh, recheck); sift_sync drains the queue into immutable segments, tiered streaming merges bound memory by the live documents, commits are crash safe, and replicas rebuild from the binary log.
  • 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 (2.34 for 10.11, 2.38 for the others); 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: this is the first release, so there is nothing to upgrade.

Download sift-mariadb-1.4.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.