Contents

• The column engine on other hosts (SQLite, MariaDB)
  • 1. What is host independent
  • 2. What a useful feature is on each host
  • 3. Licensing and build
  • 4. Open blockers
  • 5. Recommendation
  • 6. Decisions

The column engine on other hosts (SQLite, MariaDB)

The engine’s encoding, codec, kernels, shredder and segment writer/reader compile and run on both host shims (verified below); no host feature uses them yet. Verdict and phases: section 5. Phase 0 is done (sqlite/test.sh runs col-smoke); neither port has a column feature.

1. What is host independent

src/portable-col.list names the files that reach their host only through src/core/host.h (scripts/check-portable.sh enforces the includes). Verified by building them (make -C sqlite col-smoke, tests/col_smoke.c):

Part	Files	Lines	On the SQLite host
chunk and unit encoding, zones, dictionaries, batches	col/enc.c, col/{schema,dict,batch,chunk,ctest,unit}.c	2.1k	builds, round trip passes
chunk codec (cascades, FOR/delta/RLE/dict/ALP), LZ4/zstd layer	col/codec*.c, col/compress.c	3.6k	builds, round trip passes
segment writer/reader of units over the page store	col/seg*.c	0.9k	builds, linked (not yet exercised)
column kernels (filters, zone tests, unpack)	col/kern*.c	1.7k	builds
shredder core (JSON paths into leaves, no JSON parser)	col/shred*.c	1.6k	builds
aggregation kernels (sum/min/max/count, group-by)	agg/kern*.c	1.2k	builds
runtime SIMD (AVX2/AVX-512 tables, core/cpu.c)	*_avx2.c, *_avx512.c, *_popcnt.c		baseline objects have no POPCNT/vector instruction (same rule as make isa-check); SIMD=none builds and passes

The MariaDB host (same C-library shim) syntax-checks all of them with its flags. aarch64: clang --target=aarch64 accepts them with no vector files (the dispatchers are __x86_64__-guarded); nothing was linked or run there.

PostgreSQL-bound, and staying so:

• the page/buffer store behind core/store.h (PG: core/store.c, buffer manager, generic WAL; SQLite implements the same interface in sqlite/store.c over a BLOB table);
• MVCC (visibility map, Snapshot, heap rechecks: col/scan/scan.c, the SiftColVisibility callbacks) and write buffer lanes and WAL (col/wbuf/wbuf.c);
• index AM/build/maintenance/verify (col/am/am.c, col/build/build.c, col/maint/maint.c, col/verify.c, 2.5k lines) and the jsonb driver (col/shred_pg.c);
• planner and custom scan (agg/*.c, 12k lines, docs/COL-PLAN.md) and the table AM (table/*.c, 5.6k lines, docs/COL-TAM.md).

The MVCC hook is one interface: the segment’s liveness bits and an atomically published manifest give a single-writer host a snapshot by construction (as the BM25 SQLite host already works).

2. What a useful feature is on each host

System	Shape	Lesson for sift
SQLite FTS5, R-Tree, sqlite-vec vec0	virtual table; state in shadow tables (_data BLOB rows, _idx, _config…) declared by xShadowName, so DROP/RENAME/VACUUM/backup/PRAGMA defensive treat them right	the standard SQLite design; sqlite/ already does it for BM25 (_chunks, _segs, _meta, _config)
SQLite Parquet vtab, DuckDB sqlite scanner	read-only view of a foreign columnar file	interchange only: no write path and no zone maps of its own to maintain
DuckDB / chDB embedded in a host (pg_duckdb, pg_mooncake)	a whole second engine; the host feeds it	not applicable: sift is the engine; it would only be the host-side store
MariaDB ColumnStore, S3 engine, MyRocks	storage engines (C++ handler, GPLv2, in-tree or INSTALL SONAME)	the only route to real table semantics and to aggregate pushdown (group_by_handler/select_handler, as ColumnStore and Spider use)
MariaDB UDF/aggregate over an InnoDB table	row-at-a-time calls (about 7 us each, mariadb/README.md)	a vectorised engine behind a row API loses the point; only a set-returning call (JSON like sift_topk) works

SQLite: a sift_col virtual table. CREATE VIRTUAL TABLE t USING sift_col(a INT, b TEXT, ts INT); shadow tables as in the BM25 vtab (t_chunks BLOB rows holding segments, t_segs manifest with per-segment min/max, t_meta, t_config; xShadowName declared).

• Writes buffer in memory and flush as one immutable segment of units at commit (xSync), published in the manifest in the same transaction; deletes set liveness bits; merge reuses bm25/merge.c.
• Reads: xBestIndex turns =, <, <=, >, >=, IN, IS NULL, LIKE 'p%' and LIMIT into SiftColPreds; zone maps and dictionaries prune units, kernels build the selection vector, xColumn yields values.
• Limit: SQLite calls xColumn per value and cannot push an aggregate into a vtab, so the win is filtered scans and a table-valued function sift_col_agg(t, 'sum(b) group by a where ...') over sift_aggkern (eponymous vtab, one pass).
• Row key: rowid maps to blk = r >> 8, off = (r & 255) + 1 (decision 5, section 6).

MariaDB: a storage engine, not a UDF. A UDF library (what mariadb/ ships) can serve sift_col_agg(...) as a JSON-returning call over side segment files fed by a trigger queue (2 weeks, same model as BM25, no new tables); it cannot own a column table, a transaction or the optimizer. The standard shape (as ColumnStore and S3) is ENGINE=SIFTCOL: a C++ handler class with write_row, rnd_*/index_*, condition and group-by pushdown, append-only segments published by manifest swap, table-level locking and no multi-statement rollback at first.

3. Licensing and build

• Core: PostgreSQL License (permissive, GPL-compatible). MariaDB server and handler API are GPLv2 with no plugin exception for the server (the FLOSS exception covers client libraries); a loaded plugin is a combined work, so the plugin binary ships under GPLv2 terms (source offered) while src/ stays PostgreSQL-licensed. The existing UDF library makes the same assessment (mariadb/README.md); an engine is a stronger link. Decided (section 6): proceed, in the post-3.1 phase, no work now.
• Plugin ABI is per server series (MYSQL_VERSION_ID checked at compile time, tests/version.sh); an engine adds a CI matrix (10.6, 10.11, 11.4, 11.8) of C++ builds and mtr runs; the UDF library needs only headers.
• Compression: LZ4 library is BSD-2, zstd is BSD-3/GPLv2 (take BSD). col/compress.c compiles to “not available” with neither (chunks then use the lightweight cascades only; a chunk written elsewhere with a wrapper fails to open with a clear error). A single-file SQLite extension must not depend on system .sos and static libs may be absent from the build host: vendored (6.2).
• SQLite loadable extension limits: only the sqlite3ext.h API (no pager, no mmap of the DB file: one memcpy per cache miss through sqlite3_blob_read, as store.c); no intra-query parallelism, but an own thread pool works (done for BM25); distributions and mobile builds that set SQLITE_OMIT_LOAD_EXTENSION need static linking (sqlite3_auto_extension, done for the stress test); WASM has no AVX (SIMD=none). Object size: the column list adds about 170 KiB of x86 code including the vector kernels.

4. Open blockers

Each is a work item of the phases below:

• no host owns a sift_colz build option (vendored LZ4/zstd, 6.2);
• col/seg/seg.c is linked but its writer/reader path is tested only on PostgreSQL;
• the unit key space is heap TIDs (MaxHeapTuplesPerPage = 291 in the host shim, 256-page superblocks); each host must choose its rowid mapping (6.5);
• col/shred_pg.c has no counterpart (SQLite needs a driver over json_each/JSONB text, MariaDB over its JSON text);
• SiftColVisibility has no non-PG implementation (a manifest-only one is trivial);
• the aggregate planner/executor (agg/*.c) and partial-state types are PG;
• make isa-check and the sanitizer targets do not cover the column objects yet.

5. Recommendation

Phase	Work	Size	Risk
0 (done)	portable subset compiles on both shims; header split; col-smoke (ASan/UBSan clean, SIMD=none)	0.5 kLOC	none
1	col-smoke on aarch64 (syntax only today; x86 and SIMD=none run in sqlite/test.sh), isa-check over the list, vendored LZ4/zstd (6.2), a sift_col_seg round trip in the smoke test	1-1.5 weeks	low
2	SQLite sift_col vtab: write/flush/delete/merge, xBestIndex pushdown, sift_col_agg TVF, differential test against plain tables (the repo’s difftest pattern)	3.5-4.5 kLOC, 5-7 weeks	medium: per-value xColumn cost, rowid reuse after delete, update semantics
3a	MariaDB UDF sift_col_agg over side segments	1.5 kLOC, 2 weeks	low; limited value
3b	MariaDB ENGINE=SIFTCOL with group-by pushdown (post-3.1; GPL approved, 6.3)	3 kLOC C++, 8-12 weeks, ongoing per-series upkeep	high: handler API drift, crash recovery, GPL packaging

Verdict: no column feature on SQLite or MariaDB yet. Phase 0 keeps the portable subset buildable and tested on both shims, so the engine cannot drift into PostgreSQL-only code. Next is phase 1, then phase 2 as an opt-in preview (SQLite has the shadow-table pattern and the infrastructure: store.c, merge, thread pool). Decide on 3b only after phase 2 shows demand and the licensing question is settled; 3a is optional, worthwhile only if MariaDB hosts need it first.

6. Decisions

1. Header split: col/pg.h holds the PostgreSQL-only declarations; col/col.h stays host independent.
2. Vendor LZ4 and zstd in the SQLite and MariaDB artifacts (phase 1; PostgreSQL keeps probing system libraries): LZ4 v1.10.0 (lib/ only: lz4.[ch], lz4hc.[ch], BSD-2) and zstd v1.5.7 (BSD-3 of its dual license; the tagged tarball’s single-file amalgamation, build/single_file_libs, legacy formats and multithreading off).
   • Location: vendor/lz4/ and vendor/zstd/ with a VERSION and a SHA-256 of the tarball checked by the package scripts, outside src/ and the portable lists.
   • Build flag SIFT_VENDOR_COLZ=1 (on in the release artifacts) defines SIFT_HAVE_LZ4/SIFT_HAVE_ZSTD and compiles them with hidden visibility so they never clash with a libzstd the host process loads.
   • NOTICE gains both license texts. The streams are standard LZ4/zstd, so a segment is readable by any build with either.
   • Gates: isa-check must pass on the vendored objects (zstd’s runtime BMI2 dispatch is acceptable only if baseline objects stay clean, else DYNAMIC_BMI2=0); the added size (zstd dominates) is recorded in the SQLite README.
3. GPLv2 for a MariaDB storage engine: approved to proceed; phase 3b follows the SQLite preview.
4. typid is an opaque type tag in the column format (the schema hash covers the tag, not a PostgreSQL OID). Done: the leaf type is the host-independent SiftColType (column format version 1); PostgreSQL OIDs are mapped at the PG boundary only.
5. SQLite rowid mapping: blk = rowid >> 8, off = (rowid & 255) + 1.
   • Format constraints: an offset must be 1..MaxHeapTuplesPerPage (291; col/batch.c rejects others, the offset mask is 512 bits) and a unit is one 256-block superblock, so a unit holds at most 256 x 291 = 74,496 rows; >> 9 or more is invalid.
   • >> 8 gives 256 offsets: a power of two (no division per row), dense blocks (offsets 1..n store no sparse bitmap) and 65,536 rows per unit, 88% of the maximum; zone maps and dictionaries are per unit and chunk, so bigger units compress better and still prune on rowid-ordered data. rowid / 291 would reach 100% but costs a division per row for 12% and unaligned keys: rejected. >> 5 (the BM25 mapping, sized for 32-document scoring blocks) gives 8,192-row units.
   • Measured (make -C sqlite col-smoke, 262,144 dense rowids, an increasing integer and a 6-value string, chunk codec): >> 5 32 units, 0.81 bytes/row; >> 8 4 units, 0.61 bytes/row (the test asserts >> 8 is smaller).
   • Range: the block is 32 bits, so rowids 0..2⁴⁰-1 (BM25: 2³⁷); others are an error. Deleted rowids leave sparse blocks, which the format handles. The mapping is a host policy, not format: it is recorded in the table’s _config (keymap=8), so another host’s unit size (PostgreSQL’s depends on tuples per page) and a later change by rebuild need no format change. The BM25 virtual table keeps >> 5.