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](https://github.com/brauliobo/pg_sift/blob/v1.4.0/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](https://github.com/brauliobo/pg_sift/blob/v1.4.0/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v1.4.0/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:` 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//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/v1.4.0/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v1.4.0/mariadb/CHANGELOG.md).