Contents

• sift for MariaDB
  • Install
  • Quick start
  • Queries
  • Keeping the index current
  • What a query sees
  • Filters and facets
  • Index options
  • Keys
  • Replication and backup
  • Operations
  • Limits
  • MATCH … AGAINST
  • Versions, ABI and release artifacts
  • Numbers against InnoDB FULLTEXT
  • Tests
  • Layout and shared core

sift for MariaDB

BM25 full-text search for MariaDB tables: ranked top-k, exact counts, phrases, prefix / wildcard / fuzzy / regular expression terms, AND / OR / NOT, boosts, fields, filters and facets, as a user defined function library (libsift_udf.so) and two SQL files. It is the portable core of pg_sift (the files of src/portable.list, compiled unmodified, the same ones the PostgreSQL and SQLite extensions use) over a small host in mariadb/host/, so the page format, the analyzer, the ranking and the query language are those of pg_sift; for the same text and the same options the counts, the top-10 order and the scores are bit identical.

It adds no storage engine and patches nothing: a trigger-fed queue table keeps the index in step with an InnoDB table, the index lives in plain files next to the data directory, and a query is a function call. Use it when MATCH ... AGAINST is too slow or too limited (InnoDB FULLTEXT takes 0.09 to 1.0 s for a ranked top-10 on 218,058 articles, sift 0.4 to 1.3 ms), you want BM25 and phrases that behave the same on every engine of the product, or you want filters and facets inside the ranked scan. Do not use it if you need MATCH ... AGAINST syntax (it cannot be added to a UDF, see “MATCH … AGAINST”) or a MySQL server (MariaDB only).

Contents: install - quick start - queries - keeping the index current - visibility - filters and facets - options - keys - replication and backup - operations - limits - MATCH AGAINST - versions - numbers - tests - layout

Install

From a release. sift-mariadb-<version>-linux-x86_64.tar.gz holds one library per MariaDB series (lib/<series>/libsift_udf.so, SELECT VERSION() tells yours; BUILD-INFO lists the server version and glibc each was built for), sql/functions.sql, sql/sift.sql and this document. A library loads only into the series it was built for. Copy the one of your series into the server’s plugin directory and run the two SQL files as below.

From source. Needs a MariaDB server 10.5 to 13.x (built and tested on the series listed under “Versions”), its development headers (libmariadbd-dev, /usr/include/mysql/server), a C compiler and make (or CMake). Nothing else: the build needs no PostgreSQL. libmariadb is only needed for the test tools.

nice make -C mariadb -j4 OUT=/tmp/sift-build            # or: cmake -S mariadb -B /tmp/sift-build && nice cmake --build /tmp/sift-build -j4
mariadb -e "SHOW VARIABLES LIKE 'plugin_dir'"           # where the server loads UDF libraries from
cp /tmp/sift-build/libsift_udf.so /usr/lib/mysql/plugin/   # that directory (needs write access to it); a release: lib/<series>/libsift_udf.so
mariadb mydb < mariadb/sql/functions.sql                # registers the functions in the server (mysql.func), once per server
mariadb mydb < mariadb/sql/sift.sql                     # the procedures and the index table, once per database that has indexed tables

make finds the MariaDB headers with mariadb_config (or MDB_INC=<dir with mysql_version.h>). The library exports only the sift_* functions. Indexes are directories under <datadir>/.sift/<index name> (SIFT_DIR in the server’s environment moves them); index names are server wide, 1 to 63 letters, digits or underscores. Uninstall: CALL sift_detach(...) for each index, DROP FUNCTION sift_... (the list is sql/functions.sql), remove the library.

Quick start

Every statement below was run against a private MariaDB 13.0.2; the output is what it printed.

-- setup (once): the functions of the server, the procedures of the database
SOURCE mariadb/sql/functions.sql;   -- once per server (see Install)
SOURCE mariadb/sql/sift.sql;        -- once per database

CREATE TABLE articles (id BIGINT PRIMARY KEY, title VARCHAR(200), body MEDIUMTEXT, category INT, likes INT);
INSERT INTO articles VALUES
  (1, 'Solar power', 'Solar power plants turn sunlight into electricity.', 1, 10),
  (2, 'Wind farms', 'A wind farm is a power plant made of many turbines.', 1, 5),
  (3, 'Coal', 'A coal power plant burns coal to boil water.', 2, 1),
  (4, 'Pasta', 'How to cook pasta in salted water.', 3, 7);

-- index title and body of articles; the key is the primary key id; 32 documents per page
CALL sift_attach('news', 'articles', 'id', "CONCAT_WS(' ', t.title, t.body)", 32);

-- ranked top 10: the UDF in a derived table, JSON_TABLE over it, a join with the table
SELECT a.id, a.title, ROUND(j.s, 3) AS score
FROM (SELECT sift_topk('news', 'solar OR "power plant"', 10) AS r) x,
     JSON_TABLE(x.r, '$[*]' COLUMNS (n FOR ORDINALITY, id BIGINT PATH '$.id', s DOUBLE PATH '$.s')) j
JOIN articles a ON a.id = j.id ORDER BY j.n;

+----+-------------+-------+
| id | title       | score |
+----+-------------+-------+
|  1 | Solar power | 1.703 |
|  3 | Coal        | 1.050 |
|  2 | Wind farms  | 0.935 |
+----+-------------+-------+

SELECT sift_count('news', 'power AND plant') AS plants, sift_matches('news', 'water') AS water;

+--------+-------+
| plants | water |
+--------+-------+
|      2 | [3,4] |
+--------+-------+

-- changes are visible at once, before any drain
INSERT INTO articles VALUES (5, 'Hydro power', 'Hydroelectric power plants use water.', 1, 3);
UPDATE articles SET body = 'Pasta needs no power plant.' WHERE id = 4;
DELETE FROM articles WHERE id = 3;
SELECT sift_matches('news', '"power plant"') AS power_plant, sift_matches('news', 'water') AS water;

+-------------+-------+
| power_plant | water |
+-------------+-------+
| [2,4]       | [5]   |
+-------------+-------+

SELECT sift_stats('news') AS stats;

+------------------------------------------------------------------------------------------------------------------------+
| stats                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------+
| {"segments":0,"docs":0,"dead":0,"live":0,"drained":0,"pending":7,"bytes":0,"dpp":32,"docbase":0,"merges":0,"format":2} |
+------------------------------------------------------------------------------------------------------------------------+

-- write the queue into a segment (an EVENT can do this every second: CALL sift_schedule('news', 1))
CALL sift_sync('news');
SELECT JSON_VALUE(sift_stats('news'), '$.pending') AS pending, sift_count('news', '"power plant"') AS power_plant;

+--------------+
| rows_applied |
+--------------+
|            7 |
+--------------+
+---------+-------------+
| pending | power_plant |
+---------+-------------+
| 0       |           2 |
+---------+-------------+

-- a filter inside the ranked scan, and facets
CALL sift_search('news', 'power', 10, 't.category = 1');

+----+-------------+-----------------------------------------------------+----------+-------+--------------+
| id | title       | body                                                | category | likes | score        |
+----+-------------+-----------------------------------------------------+----------+-------+--------------+
|  5 | Hydro power | Hydroelectric power plants use water.               |        1 |     3 |  0.124776244 |
|  1 | Solar power | Solar power plants turn sunlight into electricity.  |        1 |    10 |  0.116445482 |
|  2 | Wind farms  | A wind farm is a power plant made of many turbines. |        1 |     5 | 0.0702005848 |
+----+-------------+-----------------------------------------------------+----------+-------+--------------+

CALL sift_facets('news', 'power', 't.category', NULL);

+-------+---+
| value | n |
+-------+---+
|     1 | 3 |
|     3 | 1 |
+-------+---+

SELECT sift_topk('news', 'power', 10, (SELECT sift_idset(id) FROM articles WHERE likes > 4)) AS likes_over_4;

+--------------------------------------------------------------------------------+
| likes_over_4                                                                   |
+--------------------------------------------------------------------------------+
| [{"id":1,"s":0.116445482},{"id":4,"s":0.0977388024},{"id":2,"s":0.0702005848}] |
+--------------------------------------------------------------------------------+

SELECT sift_tokens('news', 'Solar-powered PLANTS') AS tokens;

+----------------------+
| tokens               |
+----------------------+
| solar powered plants |
+----------------------+

SELECT JSON_VALUE(sift_merge('news', 'full'), '$.segments') AS segments;

+----------+
| segments |
+----------+
| 1        |
+----------+

Queries

The ranked top-k has one documented shape: the UDF in a derived table (so MariaDB evaluates it once), JSON_TABLE over its result, a join with the table. Written directly in the FROM clause of JSON_TABLE the argument is evaluated twice.

SELECT a.*, j.s AS score
FROM (SELECT sift_topk('news', 'solar AND "power plant"', 10) AS r) x,
     JSON_TABLE(x.r, '$[*]' COLUMNS (n FOR ORDINALITY, id BIGINT PATH '$.id', s DOUBLE PATH '$.s')) j
JOIN articles a ON a.id = j.id ORDER BY j.n;

function	returns
sift_topk(index, query, k [, filter])	JSON [{"id": key, "s": score}], best first (BM25, ties in document order)
sift_count(index, query [, filter])	the number of matching rows
sift_matches(index, query [, filter])	JSON array of every matching key
sift_tokens(index, text)	the terms the analyzer makes of a text (what the index stores)
sift_stats(index)	JSON: segments docs dead live drained pending bytes dpp docbase merges format
sift_idset(key)	aggregate: a compact set of keys, the filter argument of the functions above
sift_merge(index [, 'full'])	merges segments now ('full': all of them, dropping the deleted documents)
sift_watermark(index), sift_exists(index), sift_version(), sift_create, sift_drop	housekeeping used by the procedures
sift_drain, sift_load	aggregates that feed the index (used by sift_sync, sift_rebuild)
sift_ping(k)	k fake hits, to measure the cost of a UDF call and its JSON

The query language is SiftQL, that of pg_sift: terms (analyzed like the indexed text), "phrases", AND / OR / NOT and parentheses, {a b c} (any of), term^2.5 boosts, pre*, w?ldcard, fuzzy~1, /regular expressions/ (an ARE subset, matched against terms), and field:term for indexes with fields. A syntax error is an SQL error with the position.

Keeping the index current

sift_attach creates the index, a queue table sift_q_<index> and three triggers (insert, update, delete) on the table. A trigger writes (op, key) into the queue inside the writer’s transaction: a rolled back write leaves nothing. The queue is a log; a drain (CALL sift_sync('news')) reads the rows above the watermark of this node, fetches the current text of each row, and writes one new immutable segment atomically (temporary file, fsync, rename of the manifest). An interrupted drain is repeated harmlessly. Small drains make small segments; more than merge_fanin (6) of a size merge in the background. CALL sift_schedule('news', 1) creates an EVENT that drains every second (SET GLOBAL event_scheduler = ON) and prunes drained queue rows older than an hour (CALL sift_prune('news', seconds)).

You do not have to wait for the drain to see a change: see the next section.

What a query sees

By default (fresh) every query reads the queue rows above the watermark through a local connection of the server, builds a small in-memory live segment from them (cached while the queue does not change), and merges it with the segments on disk. The consequences, all covered by tests/fresh.sh:

• An inserted or updated row is found by the next statement; a deleted row is gone; an updated row matches its new text only. Counts equal SELECT COUNT(*) ... LIKE over the table.
• A LIMIT is never short: documents that were deleted or replaced since the last drain are excluded inside the ranked scan, and the best candidates are checked against the table (recheck) so rows removed without a trigger (TRUNCATE, foreign key cascades) cannot fill the page.
• Isolation. The index reads the queue and the rows on its own local connection of the server, not in the caller’s transaction. It never returns uncommitted data, the caller’s own uncommitted writes included (they are found after the commit). With autocommit and in READ COMMITTED the next statement sees every row committed before it started. In REPEATABLE READ the local connection may or may not share the transaction’s snapshot: a transaction that opened START TRANSACTION WITH CONSISTENT SNAPSHOT before its first call read the queue as of that snapshot in our tests, a plain START TRANSACTION read the latest committed state, and a drain by another session makes rows committed after the snapshot part of the segments. Joining with the table, as in the query above, applies the snapshot of the caller to the rows, so a row the transaction cannot see is dropped from the page; the index never invents a row. That is what an index outside the storage engine can promise: committed rows, never uncommitted ones, a snapshot-consistent final result when you join.
• A long writing transaction does not stall readers: the drain asks for a barrier row with a one second lock timeout and, if a writer is in the way, uses the barrier of the previous round and tries again on the next call.
• fresh = false indexes the committed state of the last drain only (the older, cheaper mode); recheck = false skips the existence check. Both are index options.

Filters and facets

A filter restricts the ranked scan itself, so a selective filter still fills the page. It is a set of keys: the aggregate sift_idset(key) over any SQL subquery (a bitmap), or a JSON array of keys.

SELECT sift_topk('news', 'power', 10, (SELECT sift_idset(id) FROM articles WHERE category = 1));
SELECT sift_matches('news', 'power', '[1, 2, 7]');
CALL sift_search('news', 'power', 10, 't.category = 1 AND t.likes > 3');   -- rows with their score, predicate over alias t
CALL sift_facets('news', 'power', 't.category', NULL);                     -- value, n: the same as GROUP BY over the matches
CALL sift_facets('news', 'power', 't.likes DIV 10', 't.category = 1');

tests/filters.sh compares both with the SQL baseline. sift_filter(index, predicate) leaves the bitmap in @sift_f.

Index options

CALL sift_attach_opts(name, table, key_columns, text_expression, options_json). The text expression is written over the alias t of the table. All options are those of the pg_sift index of the same name, and give the same results (tests/options.sh checks each against pg_sift):

option	default	meaning
dpp	32	documents per page of the index (a build parameter; 8 to 32 for small tables)
k1, b	1.2, 0.75	BM25 saturation and length normalization
stemmer	none	"en" (English) or none
case_folding, accent_folding	true	fold case / strip accents when analyzing
positions	true	store positions (phrases are decided by the index); false: phrases are rechecked on the text
prefix_index	0	index the first 1 to n characters of every word, so pre* is a lookup
fields	none	[{"name":"title","expr":"t.title"},{"name":"body","expr":"t.body"}]: queries can say title:term, body:(a b)^2
analyzer	"text"	"keyword": the whole value is one term
merge_fanin	6	segments of a size merged together
fresh, recheck	true	see “What a query sees”

Keys

The key is the primary key of the table: an integer column (the fast path, the key is the document id) or any other primary key, text, UUID or several columns. For those the triggers keep a dense mapping table sift_k_<index> (key to integer); the functions return the integers, and sift_search / sift_facets join them back to the rows. tests/keys.sh covers text, UUID and composite keys through insert, update, key change, delete, drain, merge, restart and rebuild.

Replication and backup

The index is derived data, local to a node. Only the table and the queue (a table, with its barrier rows) travel through the binlog (binlog_format=ROW); the triggers fire on the source only, and the queue rows replicate like any other.

• Replica. After the replica has the table and the queue, CALL sift_rebuild('news') builds its index (it creates the engine index if the node has none, and reads the table in one consistent snapshot together with the newest barrier). From then on a replica answers fresh queries from the replicated queue rows and CALL sift_sync('news') drains them with the watermark of its own node; a replica (read_only) never writes a barrier. Run sift_sync on each node: an EVENT created on the primary replicates as disabled on the replica, which should run a drain-only event (CREATE EVENT ... DO CALL sift_sync('news')), not sift_schedule, whose sift_prune deletes queue rows.
• Pruning. CALL sift_prune('news', seconds) deletes drained queue rows older than seconds; a node that is further behind than that gets “the queue was pruned past the watermark of this node” and runs sift_rebuild.
• Backup. mariadb-dump --single-transaction --routines --triggers db contains the table, the queue, the triggers and the index catalogue, not the index files (they are not InnoDB files: treat them as derived data, whatever physical backup you use). After a restore on a fresh server: register the functions (functions.sql), then CALL sift_rebuild('news') for each index.
• tests/repl.sh runs a private primary and replica with binlog replication and a third server for the restore, and checks equal counts and top-10 after catch-up, after rebuilds, after pruning, and after a dump restore.

Operations

• SELECT sift_stats('news'): segments, live and dead documents, pending (queue rows not yet in a segment), bytes.
• Crash safety. A segment, the event log and the manifest are written to temporary files, fsynced and renamed; the manifest rename is the only commit point; files the manifest does not name are removed at open. tests/functional.sh kills the server after a drain, tests/live.sh kills it at three points of a merge: the answers after the restart are those of the last published state.
• Merge. The background merge keeps the number of segments within merge_fanin; sift_merge('news', 'full') merges all of them and drops deleted documents. The postings are streamed through temporary runs whose sort buffers are bounded by maintenance_work_mem (64 MB, set in shim.c); what else stays in memory is the document map (about 70 bytes per document at the peak: 36 MB for 540,000 documents).
• Concurrency. Each index has a reader / writer lock; queries of different connections run in parallel, a drain or a merge publishes under a short write lock. tests/stress.sh runs 8 reader threads, 4 writers, a draining and a merging thread at once and then checks the index against the table.
• Memory and files. Segments are mapped from the page cache; they cost no heap. <datadir>/.sift is outside InnoDB.

Limits

• MariaDB only. One text expression per index (a CONCAT_WS or fields), over one table; no partition awareness; no ALTER TABLE story for the indexed table (detach, alter, attach).
• Triggers are the change feed: TRUNCATE, bulk loads with SET unique_checks… that skip triggers, and foreign key cascades do not reach the queue. A cascaded or truncated row is never returned (the existence check), but a TRUNCATE leaves the documents in the index until CALL sift_rebuild; rows added by a trigger-less load need the same.
• A query reads the pending queue rows: about 0.3 ms per 1,000 of them. Drain on an EVENT (see “Numbers”). The live segment holds the text of the pending rows in memory while a query uses it.
• sift_idset is limited by max_allowed_packet and the server’s UDF result size (256 MB, about 32 million keys).
• The UDF library is loaded into mariadbd (GPLv2) and built against its headers; pg_sift is under the PostgreSQL license, which is GPL compatible. Whether linking a UDF is a derivative work is a question for a lawyer.
• Not done: MATCH ... AGAINST, a storage engine, visibility inside the caller’s own uncommitted transaction, highlighting.

MATCH … AGAINST

Assessment: not feasible as a UDF, and no proof is shipped. MATCH(col) AGAINST(...) is parsed by the server into a full-text function that is bound to a FULLTEXT key of the table’s storage engine: the server calls the handler’s ft_init_ext, ft_read and ft_end. A UDF library cannot register a handler method, and a full-text parser plugin (MYSQL_FTPARSER_PLUGIN) only changes how text is split into words for an index that the engine (InnoDB, MyISAM, Aria) still builds, stores and ranks itself, so it cannot route queries to sift. The only ways to the syntax are a storage engine that owns the FULLTEXT key (the model of Mroonga’s wrapper mode: it wraps InnoDB for the rows and answers ft_init_ext from its own index) or a patch of the server. Both are C++ code against the server’s private handler interface, rebuilt for every MariaDB series, with the full handler surface (about 100 virtual methods) to wrap. The smallest next step is a read-only storage engine in the style of SphinxSE: CREATE TABLE hits (id BIGINT, score FLOAT, query VARCHAR(1024) NOT NULL, KEY (query)) ENGINE=SIFT CONNECTION='news' so that SELECT ... FROM hits WHERE query = 'solar AND wind' LIMIT 10 plans as an index lookup and joins with the table; it needs the engine skeleton (rnd_init, index_read_map, ha_* registration) and nothing of sift changes. Wrapper mode with real MATCH AGAINST comes after that. Until then use the derived-table query above.

Versions, ABI and release artifacts

• Tested on MariaDB 13.0.2 (the whole suite below, on the host’s server) and, with tests/smoke.sh (12 checks: version string, ranked top-10, count, matches, DML visible at once, prefix / wildcard / fuzzy / regular expression, sync and merge, restart, detach), on the stock Docker images mariadb:10.11 (10.11.19), 11.4 (11.4.13), 11.8 (11.8.9), 12.1 (12.1.2) and 13.0 (13.0.2), each loading the library built against the headers of that very image. The sources compile against the headers of any MariaDB from 10.5 to 13.x and refuse other versions at compile time (a #error on MYSQL_VERSION_ID, checked by tests/version.sh); the UDF structures are static_asserted. 10.5 to 10.10, 11.0 to 11.3, 11.5 to 11.7 and 12.0 have no release library and were not run. MySQL 8.4 / 9.x are not supported (the UDF interface and the server-side SQL service differ).
• ABI. The library calls symbols of mariadbd itself, so it works only with the series whose headers it was built against; at load time it compares its series with the running server’s (server_version) and every function fails with “rebuild it against the headers of this server” on a mismatch. It needs libc and libm only (no libstdc++, no libmariadb), x86-64 baseline (no POPCNT, no vector instruction outside the runtime-dispatched kernels: make isa-check), glibc 2.34 (Ubuntu 22.04 image, series 10.11) or 2.38 (the 24.04 and 26.04 images, series 11.4 to 13.0).
• sift_version() reports the release, the format of the engine, the page format and the server it was built for. An index written by another format is refused with “rebuild it with sift_rebuild”, and sift_rebuild replaces it (tests/version.sh damages the manifest in four ways). The index files do not depend on the server version: they depend on the page format, which the library checks when it opens an index.
• Release artifact: sift-mariadb-<version>-linux-x86_64.tar.gz, built by scripts/package-mariadb.sh (and the mariadb job of .github/workflows/release.yml): lib/<series>/libsift_udf.so for each series above, sql/functions.sql, sql/sift.sql, LICENSE, NOTICE, this document and BUILD-INFO (server version and glibc floor of each library). Every library is built in the stock mariadb:<series> image against libmariadbd-dev from MariaDB’s own repository (which pins the headers to that server’s exact version) and loaded into a server of the same image by the smoke test before the tarball is accepted; the tarball is reproducible (sorted names, commit time) and goes into the single SHA256SUMS of the release. The version is the tag (v1.4.0 is 1.4.0) and must equal mariadb/VERSION; CHANGELOG.md lists the changes. A distribution with an older glibc, or another architecture, builds from source (make).

Numbers against InnoDB FULLTEXT

Measured on Simple English Wikipedia (218,058 articles), top 10 by BM25, warm cache, the same queries for both systems. The numbers are those of a 3.0 development build; the port code did not change in 3.1 and later, and the shared core did, so sizes and speeds may differ.

| class | sift, joined | sift, UDF only | sift, plain index | InnoDB FULLTEXT | InnoDB / sift | | — | — | — | — | — | — | | AND top-10 | 476 | 245 | 298 | 87,191 | 183x | | OR top-10 | 1,301 | 1,196 | 1,228 | 943,532 natural, 1,032,812 boolean | 725x / 794x | | phrase top-10 | 424 | 250 | 338 | 213,772 | 504x | | count AND | 110 | | 83 | 112,650 | 1,025x | | count OR | 269 | | 247 | 1,506,334 | 5,608x | | count phrase | 172 | | 144 | 233,629 | 1,357x | | statement | before | after | after / before | | — | — | — | — | | AND top-10, joined | 466 +- 56 | 476 +- 81 | 1.05 +- 0.15 | | AND top-10, UDF only | 286 +- 36 | 245 +- 36 | 0.89 +- 0.12 | | AND top-10, plain index | 373 +- 41 | 298 +- 34 | 0.82 +- 0.09 | | OR top-10, joined | 1,488 +- 120 | 1,301 +- 152 | 0.90 +- 0.13 | | OR top-10, plain index | 1,361 +- 149 | 1,228 +- 168 | 0.94 +- 0.15 | | phrase top-10, joined | 513 +- 113 | 424 +- 52 | 0.94 +- 0.18 | | phrase top-10, plain index | 293 +- 41 | 338 +- 51 | 1.20 +- 0.18 | | count AND | 139 +- 10 | 110 +- 11 | 0.82 +- 0.12 | | count OR | 307 +- 35 | 269 +- 20 | 0.93 +- 0.14 | | count phrase | 179 +- 21 | 172 +- 21 | 0.98 +- 0.11 | | build (s) | 7.3 +- 0.8 | 6.6 +- 0.9 | 0.93 +- 0.09 | | index size (MB) | 135.9 | 135.9 | 1.00 | Honest losses. sift’s OR is its slowest class (1.2 to 1.3 ms: OR queries over common terms match most of the corpus); the joined shape

Tests

mariadb/test.sh builds, starts a private PostgreSQL (the pg_sift reference) and a private MariaDB under /tmp, loads the 218,058-article Simple English Wikipedia corpus, runs everything below and the benchmark, and deletes both servers (STEPS, KEEP, WORK, SIFT_BENCH_DATA, BENCH_QUERIES: see the header of the script). mariadb/server.sh is the private server on its own (start|stop|sql|destroy).

suite	what it checks
test.sh pgref	5,098 queries (and / or / phrase traces, prefix, wildcard, fuzzy, NOT, boosts, groups, nesting, phrases under OR/NOT including CJK, regular expressions): count, top-10 order and float32 scores equal to pg_sift
test.sh inno	match sets against InnoDB FULLTEXT (boolean mode), with the analyzer differences listed below
tests/functional.sh	67 checks: queries, DML visible at once, rollback, update / delete, drain, merge, restart, kill -9, concurrent writers, EVENT
tests/fresh.sh	freshness without sync, LIMIT never short, rollback / savepoints, REPEATABLE READ / READ COMMITTED, TRUNCATE, fresh=false
tests/keys.sh	text, UUID, composite keys, the integer fast path
tests/filters.sh	bitmap, id list and predicate filters, facets against GROUP BY
tests/options.sh	every index option against pg_sift with the same options (needs the private PostgreSQL)
tests/version.sh	version strings, refused manifests, the compile-time window, the CMake build
tests/live.sh	small segments, background merge, bounded merge memory, kill -9 during a merge, log compaction
tests/stress.sh	reader, writer, drain and merge threads at once, then index equals table
tests/regex_check.sh	the regular expression engine against PostgreSQL’s, 227,040 pattern / word pairs
tests/repl.sh	primary, replica, rebuild, prune, dump restore
tests/smoke.sh	a release library in a stock MariaDB image (scripts/package-mariadb.sh): version, queries, DML, merge, restart, detach

Against InnoDB FULLTEXT the match sets differ only where the analyzers differ: InnoDB keeps _ and an apostrophe between letters inside a word and splits at U+00AD; sift (like the PostgreSQL parser the trace comes from) splits at _ and ' and keeps U+00AD inside the token. On a copy of the text with those characters turned into spaces, 97 to 100 of 100 queries per class are equal; the rest are off by 1 or 2 documents of tens of thousands.

Layout and shared core

path	what
udf.c, sql/	MariaDB-specific: the UDFs (arguments, JSON, the local SQL connection) and the procedures / triggers
engine.c, engine.h, ix.h, query.c, feed.c, merge.c	the index: manifest, event log, live segment, queries, drains, merge driver, locks
host/host.h	the host interface of the portable core (SIFT_HOST_MARIADB in src/core/host.h): the plain-C vocabulary of sqlite/host/host.h, whose host.c, container.c, mb.c and spool.c (memory contexts, ereport as longjmp, hash tables, strings, UTF-8, temporary files, heaps) are compiled as they are, with host_stubs.c (no write buffers, no kept docmap directories)
host/store.c, host/store.h	src/core/store.h over segment files mapped as the buffer pool: the stream reader and writer, write targets
host/stubs.c	the engine’s own visibility, and the filter of a list of documents as the TID bitmap the shared filter builder reads
../sqlite/host/regex.c, regex_parse.c, regex_exec.c	pg_regcomp / pg_regexec / pg_regprefix: a matcher for PostgreSQL’s advanced regular expressions
cli.c	sift_cli: the engine without a server
Makefile, CMakeLists.txt	the build (it reads src/portable.list)
tests/, bench/, tools/, test.sh, server.sh	tests, the benchmark, the comparison tools, the private server

Compiled from src/ unmodified: every .c file of src/portable.list, except that bm25/bitmap.c is compiled from a copy whose seek limit is per thread (sed, checked by the build). No PostgreSQL header, library or code is involved: scripts/check-portable.sh guarantees that the files of the list reach their host only through core/host.h.

What is shared, and what is not. Taken from the core and not copied: the merge policy (sift_merge_pick, tiers from 1 MiB), the merge of segments through sorted runs (sift_run_from_segment, sift_runs_write), the inverted buffer of a drain (SiftInvBuf), the deletion of a document from a segment (sift_seg_delete_key: the merge deletes the engine’s dead documents from its private copy of the pages, then rewrites them), the filter builder (sift_filter_build), the extent bookkeeping and padding of byte streams (core/stream.c), the CPU dispatch, the analyzer options. MariaDB’s own: the manifest, the event log and the queue protocol (engine.c, feed.c, sql/), the local-connection host callbacks and the UDF glue (udf.c), the mapped store with its MADV_POPULATE_READ, the background merge thread, the live segment of a query. The regular expression matcher (sqlite/host/regex*.c, PostgreSQL’s advanced syntax in the C collation) is shared with the SQLite host, and tests/regex_check.sh compares it with PostgreSQL’s on 227,040 pattern and word pairs.