Version claims cross-checked against official release channels on 2026-07-15. Feature statements describe the versions listed in the table below. Where a database lacks a capability, or has it only in part, the closest practical alternative is given as Alternative:.
| Database | Stable Version | Released | Support | Storage / Execution Model |
|---|---|---|---|---|
| SQLite | 3.53.3 | Jun 26, 2026 | Format/API support pledged through 2050 | Row-oriented B-trees, in-process, single file |
| DuckDB | 1.5.4 | Jun 17, 2026 | 1.4.5 is the LTS line | Columnar, in-process, vectorized |
| PostgreSQL | 19 (Beta 1) | Jun 4, 2026 — GA expected Sep/Oct 2026; current stable is 18.4 | 5 years per major; quarterly minors | Row-oriented, process-per-connection, MVCC |
| MariaDB | 12.3.2 (LTS) | May 28, 2026 | 12.3 LTS maintained to Jun 2029; yearly .3 LTS; rolling quarterlies supported only until the next release |
Row-oriented, pluggable storage engines, InnoDB clustered B+trees |
| ClickHouse | 26.6.1 | Jun 25, 2026 | Two LTS lines per year (currently 25.8 and 26.3), each supported ~1 year | Columnar, distributed, immutable sorted parts |
| MongoDB | 8.3.4 | Jun 11, 2026 | 8.3 supported to Oct 31, 2029 | BSON documents in WiredTiger B+trees |
SQLite — Table. One database is one file of fixed-size pages (512 B to 64 KB, default
4 KB). Each table is a B-tree keyed by the implicit 64-bit rowid, or by the declared primary
key for WITHOUT ROWID tables (which stores the whole row in the primary-key B-tree, saving a
lookup); each index is another B-tree in the same file. Schema evolution is deliberately
narrow: ALTER TABLE covers rename, add/drop column, and add/drop of NOT NULL/CHECK
constraints — anything else is a documented rebuild-and-swap pattern.
DuckDB — Table. Lives in a single database file organized as row groups (about 122 880 rows each) of independently compressed column segments. Queries prune at row-group granularity before reading. The format carries a backward-compatibility guarantee, and the whole file — including its write-ahead log and temp files — can be encrypted with AES-256-GCM.
PostgreSQL — Table. Heap files of 8 KB pages, row-oriented. Values that would push a row past roughly 2 KB are handled by TOAST ("The Oversized-Attribute Storage Technique"): the value is compressed and, if still large, moved to a companion table and replaced in the row by a pointer — transparent to queries. The table storage layer is pluggable through the table access method API, though heap remains the only in-core implementation.
MariaDB — Table, inside a storage engine chosen per table. The ENGINE= clause selects the
storage and concurrency implementation: InnoDB by default, where a table is its primary-key
B+tree — rows live in the leaf pages of a clustered index on 16 KB pages, one .ibd
tablespace file per table. Other engines (Aria, MyRocks, ColumnStore, MEMORY, CSV, S3, Spider)
store the same logical table entirely differently. No other engine in this comparison lets two
tables in one schema use different storage architectures.
ClickHouse — Table. Defined by a table engine, almost always from the MergeTree family.
Each insert creates an immutable part: a directory holding one compressed file per column,
rows sorted by the table's ORDER BY key. Background merge threads continually combine small
parts into larger ones. There is no in-place row update — all change flows through new parts,
merges, and mutations.
MongoDB — Collection. BSON documents (a binary JSON encoding with extra types such as
ObjectId, dates, and raw binary), 16 MB maximum each — a hard limit that shapes schema design.
Each collection and index is a separate WiredTiger B+tree file. Clustered collections store
documents physically ordered by _id; capped collections are fixed-size circular buffers that
preserve insertion order.
SQLite — Foreign keys with cascades are supported but enforcement is off by default:
each connection must run PRAGMA foreign_keys = ON, a backward-compatibility decision kept
since 2009. JSON functions (including binary jsonb variants that avoid re-parsing text on
every access) and generated columns handle semi-structured data.
DuckDB — Reads Parquet, CSV, and JSON from local disk, S3, Azure, GCS, and HTTP without an
import step; Iceberg and Delta Lake table formats are supported for both reading and writing,
including Iceberg REST catalogs and schema evolution on Iceberg tables. FOREIGN KEY
constraints are declared and enforced. STRUCT, LIST, MAP, and VARIANT types cover
nested and heterogeneous data. Not capable of: high-frequency small-transaction foreign-key
workloads — its concurrency control is optimized for bulk changes.
PostgreSQL — Foreign keys with full referential actions (ON DELETE CASCADE, SET NULL,
RESTRICT) are enforced by the engine. JSONB — a decomposed binary JSON type, distinct from
the plain-text json type — supports GIN indexing, containment operators (@>),
subscripting, and SQL/JSON path queries (jsonb_path_query, JSON_TABLE for shredding JSON
into rows). Generated columns (stored and virtual) derive values from other columns.
Relational and document columns mix freely in one table. Not capable of: schemaless
collections — every row conforms to the table definition.
MariaDB — Foreign keys enforced by InnoDB (with a gap: not on partitioned tables). JSON
is an alias for LONGTEXT with a validity CHECK — there is no binary JSON type, so every
JSON access re-parses text; the function library (JSON_EXTRACT, JSON_TABLE, JSON_VALUE,
->/->> operators, the SQL-standard IS JSON predicate) is nonetheless complete. Virtual
and persistent generated columns derive and optionally index computed values. Alternative
to a binary JSON type: extract hot fields into indexed virtual columns.
ClickHouse — Composite types (Nested, Array, Map, Tuple), a Variant type for
values of alternating types, Dynamic for columns whose type is unknown up front, and a JSON
type that stores each JSON path it observes as its own typed, compressed subcolumn — querying
one field of a large document reads only that field. Joins work but the engine rewards
denormalized wide tables. No foreign key enforcement. Alternative for dimension lookups:
dictionaries — in-memory key-to-attribute maps refreshed from any source and queried with
dictGet, avoiding a join entirely.
MongoDB — Embedding related data in one document is the primary modeling tool: data read
together is stored together, eliminating the join. $lookup provides left-outer joins across
collections at query time. There are no foreign keys and no referential integrity enforcement —
dangling references are the application's problem. Alternative: $jsonSchema validation
rules on collections, plus multi-document transactions to keep related writes consistent.
SQLite — Inner, left, right, and full outer joins; recursive CTEs; window functions;
UPDATE ... FROM. No LATERAL. The planner is simpler than PostgreSQL's: it relies on
ANALYZE statistics (the STAT4 format samples value distributions so the planner can estimate
selectivity of specific values) and executes every join as a nested loop over indexes — there
are no hash or merge joins. Adequate for normalized schemas at embedded scale; joins over
millions of un-indexed rows degrade sharply.
DuckDB — Full analytical SQL: all join types, ASOF JOIN (matches each row to the nearest
earlier timestamp in the other table — the standard tool for aligning event streams),
POSITIONAL JOIN (pairs rows by position), PIVOT/UNPIVOT, window functions, and
systematic subquery decorrelation — the optimizer rewrites correlated subqueries into joins
rather than executing them once per row, which makes patterns that are slow elsewhere run at
join speed.
PostgreSQL — Handles arbitrarily normalized schemas: any join tree under a cost-based
optimizer, recursive CTEs, LATERAL (which lets a subquery reference columns from tables
earlier in the FROM list — effectively a correlated join), window functions, grouping
sets/cube/rollup. Hash joins parallelize across worker processes, including FULL and RIGHT
outer joins. This is the workload the engine is built for.
MariaDB — Cost-based optimizer with block nested-loop, block hash join, and batched key
access strategies; recursive CTEs, window functions, INTERSECT/EXCEPT, derived-table
merging and condition pushdown. A broad set of MySQL-style /*+ ... */ optimizer hints
(JOIN_ORDER, BKA/BNL, semijoin and materialization control, MAX_EXECUTION_TIME) pins
plans per query, and UPDATE/DELETE can read from CTEs. Gaps a designer must
know: no FULL OUTER JOIN (emulated with
LEFT JOIN ... UNION ALL ... RIGHT JOIN ... WHERE IS NULL) and no LATERAL. Oracle
compatibility mode additionally accepts (+) outer-join syntax. Well suited to normalized
OLTP schemas of ordinary complexity.
ClickHouse — Multiple physical join algorithms, selectable per query: in-memory hash and parallel hash, grace hash (partitions both sides to disk so the build side need not fit in memory), full sorting merge, and direct join against dictionaries. CTEs can be materialized — computed once and reused — instead of re-inlined at each reference. Still weaker than the row stores at many-table normalized joins: statistics and join reordering are younger, and the engine assumes scan-heavy plans.
MongoDB — Normalized modeling works against the engine's design. The aggregation pipeline
provides $lookup (including the expressive let/pipeline form for non-equality join
conditions), $graphLookup, $unionWith, $setWindowFields (window functions over
documents), and $facet (several sub-pipelines over one input in a single pass). There is no
join-reordering optimizer; pipeline stage order is largely what executes, so stage ordering is
a performance decision the developer makes.
SQLite — Recursive CTEs work; the closure extension provides a virtual table that
materializes transitive closures over an edge table efficiently. For small graphs — the
typical embedded case — this is sufficient. There is no parallelism, so large traversals are
slow. Alternative: load the graph into application memory; the data is already in-process.
DuckDB — Recursive CTEs, including a USING KEY variant that keeps per-key state across
iterations instead of accumulating all intermediate rows — this makes iterative graph
algorithms (shortest path, connected components) tractable in SQL. No graph storage or
traversal operators. Alternative: the DuckPGQ community extension implements SQL/PGQ syntax.
PostgreSQL — WITH RECURSIVE covers hierarchies and transitive closure; node/edge tables
with partial indexes work at moderate depth. SQL/PGQ, the ISO SQL standard for property
graphs, is in core: tables are declared as graph vertices and edges, then queried with
GRAPH_TABLE(... MATCH (a)-[e]->(b) ...) pattern syntax — the only engine in this comparison
with standard graph query syntax in core. Storage remains relational, so deep-traversal
performance still depends on join plans. Alternative: the Apache AGE extension adds
openCypher queries.
MariaDB — Recursive CTEs, plus a mechanism none of the others has: the OQGRAPH storage
engine presents a computed graph over an ordinary edge table — queries against special latch
columns return shortest paths and breadth-first traversals as result rows, with the engine
running the graph algorithm internally. Practical for path queries over moderate graphs;
unmaintained edges of the feature (it has seen little recent development) argue for testing
before depending on it.
ClickHouse — Recursive CTEs exist but the columnar scan architecture is a poor match for
pointer-chasing traversal: each hop is a full join pass, so per-hop latency is high. Capable of
bulk analytics about graphs — degree distributions, edge-list aggregation at billions of
edges. Not capable of interactive traversal. Alternative: hierarchical dictionaries —
dictGetHierarchy and dictIsIn walk parent-child chains natively — cover tree-shaped
lookups, and fixed-depth traversals unroll into explicit self-joins.
MongoDB — $graphLookup performs bounded recursive traversal (with maxDepth and an
optional depth field) within one collection — sufficient for org charts and category trees. It
cannot traverse across collections in one stage and fans out poorly across shards, since each
hop may touch every shard. Alternative: a dedicated graph store fed by change streams.
SQLite — None. Alternative: ATTACH multiple database files (10 by default, 125 at
compile time) and route by key in SQL or application code; one file per tenant or per time
window is common practice and makes retention a file deletion.
DuckDB — No table partitioning; the engine is single-node. Alternative: Hive-partitioned
Parquet datasets — directory trees named key=value/ — which DuckDB both prunes on read
(skipping directories whose key values fail the filter) and writes via
COPY TO ... (PARTITION_BY ...); Iceberg and DuckLake catalogs add snapshot and partition
management on top.
PostgreSQL — Declarative partitioning: RANGE, LIST, and HASH schemes,
sub-partitioning, partition pruning at both plan time and execution time (so
parameter-dependent pruning works), partition-wise joins and aggregates, and
ALTER TABLE ... MERGE/SPLIT PARTITIONS for reshaping existing partitions without manual data
movement. All partitions live on one primary — there is no built-in cross-node sharding.
Alternative: the Citus extension for distributed tables, or postgres_fdw-based federation.
MariaDB — RANGE, LIST, HASH, and KEY partitioning with subpartitioning, pruning,
and EXCHANGE PARTITION for swapping a partition with a standalone table (fast archival).
Restrictions to plan around: partitioned tables cannot have foreign keys, and every unique key
must include the partitioning columns. For cross-server distribution, the Spider storage
engine maps partitions to tables on remote MariaDB servers — sharding expressed as
partitioning. Galera clusters replicate everything; they do not shard.
ClickHouse — Both local and distributed partitioning. Locally, PARTITION BY groups parts
by an expression (typically month or day), enabling instant DROP PARTITION, TTL-driven
moves, and partition-scoped operations. Across nodes, the Distributed engine fans queries
out to shards and merges results; multi-stage distributed execution adds shuffle-style plans
for queries that need repartitioning between stages; parallel replicas let all replicas of a
shard cooperate on one query.
MongoDB — Sharding is the partitioning story: a shard key (ranged, hashed, or zone-pinned)
determines document placement; config servers hold routing metadata; mongos router processes
direct queries; a balancer splits and migrates chunks automatically. Shard keys can be refined
(extended with suffix fields) or changed entirely with live resharding while the application
runs. There is no single-node table partitioning — a collection on one shard is one B+tree.
MVCC lets readers and writers proceed without blocking each other by keeping multiple versions of data and showing each transaction a consistent snapshot.
SQLite — No MVCC. In rollback-journal mode, a writer excludes readers and vice versa. In
WAL mode, readers see a stable snapshot while a single writer appends to the log — but only one
write transaction can be active at any time, regardless of mode. Contention appears as
SQLITE_BUSY errors; setting PRAGMA busy_timeout makes lock waits block-and-retry instead of
failing immediately. Alternative for higher write concurrency: keep transactions short and
start them with BEGIN IMMEDIATE to take the write lock up front.
DuckDB — Serializable MVCC using in-memory undo buffers, designed for bulk operations: one
large UPDATE is cheap, many small concurrent transactions are not the target. Concurrent
small writes from threads of one process work; concurrent writer processes are not
supported.
PostgreSQL — Row-version MVCC: each row version carries the creating and deleting
transaction IDs (xmin/xmax), and visibility is decided per transaction snapshot. Readers
never block writers. SERIALIZABLE is implemented as Serializable Snapshot Isolation, which
detects dangerous read-write dependency patterns at commit rather than taking locks. The cost
of this design is dead row versions: updates write new versions and leave the old ones for
(auto)VACUUM to reclaim — under heavy update churn, vacuum throughput is an operational
concern.
MariaDB — InnoDB MVCC through undo logs: old row versions are reconstructed on demand from
undo records rather than left in place, so there is no VACUUM equivalent — purge threads
discard undo history once no transaction needs it (a long-running transaction stalls purge and
grows the history list). Default isolation is REPEATABLE READ with gap locking, which
prevents phantom inserts but produces locking behavior that surprises developers arriving from
PostgreSQL's snapshot-only approach. With innodb_snapshot_isolation (on by default), a
REPEATABLE READ transaction that writes a row modified since its snapshot began fails with
a conflict error to be retried — PostgreSQL-style write-conflict behavior.
ClickHouse — No row-level MVCC. Each query reads the set of immutable parts that existed
when it began, which yields per-query snapshot consistency without any version bookkeeping.
UPDATE and DELETE are asynchronous mutations — background jobs that rewrite whole parts —
or lightweight deletes, which write a hidden row mask that filters future reads until merges
physically remove the rows. Not capable of: read-modify-write transactional workloads.
MongoDB — WiredTiger keeps multiple versions in its B+trees and gives snapshot-isolated
reads with document-level optimistic concurrency: two writes to the same document conflict,
are retried internally, or surface as a WriteConflict error. Long-running snapshots pin
version history in the WiredTiger cache and can pressure memory.
SQLite — Full ACID with serializable isolation. At PRAGMA synchronous=FULL, committed
transactions survive power loss; the project maintains extensive crash-injection tests, and
the test suite achieves 100% machine-code branch coverage. That rigor is not absolute proof —
corruption bugs are still occasionally found and fixed.
DuckDB — ACID per database file: write-ahead log plus checkpointing, serializable isolation. Durability is bounded by the host process and filesystem — there is no server enforcing an fsync policy beyond the engine's defaults.
PostgreSQL — Full ACID: write-ahead-log durability, Read Committed / Repeatable Read /
Serializable isolation, and two-phase commit for coordinating with external transaction
managers. Durability is tunable per transaction via synchronous_commit, from off (may lose
recent commits on crash, never corrupts) to remote_apply (commit waits until standbys have
applied the change).
MariaDB — Full ACID in InnoDB, with a structural detail the others lack: two logs must
agree. The InnoDB redo log and the binary log (replication/recovery log) are coordinated by an
internal two-phase commit; full durability requires both innodb_flush_log_at_trx_commit=1
and sync_binlog=1, and relaxing either is a common, deliberate durability-for-throughput
trade. An opt-in re-engineered binlog moves its core inside InnoDB, collapsing the two-log
coordination and its double fsync (~4× write throughput claimed). XA transactions
expose two-phase commit to external coordinators. In Galera clusters,
commit adds certification: a transaction that conflicts with one committed elsewhere aborts
with a deadlock error the application must retry.
ClickHouse — Atomic per insert: one INSERT block into one partition either fully appears
or does not. Reads are snapshot-consistent over parts. Replicated tables deduplicate retried
inserts by block hash, so at-least-once ingestion pipelines converge to exactly-once storage.
No multi-statement transactions. Not capable of: rolling back application-level multi-step
changes. Alternative: idempotent, append-only ingestion design.
MongoDB — Single-document operations are always atomic, which is why embedding related
data in one document substitutes for transactions in idiomatic designs. Multi-document
transactions work on replica sets and sharded clusters with snapshot isolation. Durability and
consistency are tunable per operation: write concern sets how many nodes must acknowledge a
write (majority survives failover), read concern sets what a read may observe (local,
majority, snapshot). Transactions default to a 60-second lifetime and degrade when long or
large.
SQLite — Unique indexes and constraints, single and composite, including partial unique
indexes (CREATE UNIQUE INDEX ... WHERE active = 1 — uniqueness among a subset of rows).
NULLs compare distinct, per the SQL standard.
DuckDB — PRIMARY KEY and UNIQUE are enforced through ART indexes (Adaptive Radix
Tree — an in-memory trie structure). Because the index is memory-resident, uniqueness on very
large tables has a real RAM cost.
PostgreSQL — Unique indexes and constraints, single and composite; deferrable to
transaction end (so intermediate duplicate states inside a transaction are legal);
NULLS NOT DISTINCT to treat NULLs as equal for uniqueness; and exclusion constraints, which
generalize uniqueness from "equal values forbidden" to any operator — e.g., "overlapping
ranges forbidden" for booking systems.
MariaDB — Unique indexes and constraints, single and composite; multiple NULLs are always
permitted in unique columns. Long string columns can be uniquely constrained via prefix
indexes (uniqueness over the first N bytes) or hash-based UNIQUE USING HASH for values
exceeding the key-length limit. On partitioned tables, every unique key must contain the
partitioning columns.
ClickHouse — Not capable of unique enforcement, by design: parts are written independently
by concurrent inserts, so there is no insert-time check against existing data. Alternative:
ReplacingMergeTree (keeps the newest row per key, judged by an optional version column) or
CollapsingMergeTree (cancels row pairs) provide eventual deduplication — visible only
after background merges complete, or immediately when querying with the FINAL modifier,
which merges on the fly at query cost. True uniqueness must be guaranteed upstream of
ingestion.
MongoDB — Unique single-field and compound indexes, including on nested fields. On sharded collections, a unique index must include the shard key as a prefix; global uniqueness on any other field cannot be enforced, because no single node sees all documents.
SQLite — B-trees are the only index structure: single and composite, ascending or
descending per column, collation-aware. WITHOUT ROWID tables store rows clustered in the
primary-key B-tree itself.
DuckDB — No B-tree. ART indexes serve point and highly selective range lookups; official guidance is that they pay off only when a filter selects well under about 0.1% of rows. Everything else is served by zone maps — automatically maintained min/max summaries per row group that let scans skip row groups whose range cannot match the filter.
PostgreSQL — B-tree is the default access method, with duplicate-key deduplication and skip scans that use a multicolumn index even when the leading column has no filter. Other access methods serve other shapes: GiST (generalized trees for geometric/range data), SP-GiST (space-partitioned tries), GIN (inverted index for multi-valued data like JSONB and text), BRIN (block-range summaries for huge naturally-ordered tables), and hash. All can be built on expressions.
MariaDB — InnoDB B+trees with a consequential layout: the table is clustered on the
primary key, and every secondary index entry carries the primary-key value as its row pointer —
so a long composite primary key silently inflates every index on the table, and secondary-key
lookups cost two tree descents. Ascending and descending index orders are supported. Indexes
can be marked IGNORED — kept up to date but invisible to the planner — to test a drop before
committing to it.
ClickHouse — No B-tree and no row-level index. The primary index is sparse: one entry
per granule (8 192 rows by default) of data sorted by the ORDER BY key, small enough to stay
in memory. It prunes ranges of rows rather than locating single rows. Skip indexes — minmax,
set (distinct values up to a limit), and Bloom-filter families (probabilistic membership
structures) — prune further within scans. Point lookups by arbitrary key are not what this
engine does well. Alternative: a projection sorted by the second lookup pattern.
MongoDB — Every index is a WiredTiger B+tree: single-field, compound with a sort direction per field, multikey (created automatically when a field holds arrays — one entry per element), hashed, text, geospatial, and wildcard variants all layer on the same structure. Indexes can be hidden from the planner to trial a removal safely, mirroring MariaDB's ignored indexes.
A partial index covers only rows matching a predicate; a functional (expression) index indexes a computed value rather than a stored column.
SQLite — Both, and they compose: partial indexes, expression indexes, partial unique
indexes, and expression indexes over json_extract for indexing inside JSON documents.
REINDEX EXPRESSIONS rebuilds expression indexes if the underlying function's behavior
changes. Fully usable in query plans.
DuckDB — Expression indexes are accepted (CREATE INDEX i ON t ((a + b))) but are not
eligible for index scans, so in practice they serve uniqueness enforcement only. No partial
indexes. Alternative: zone maps cover most filtering needs; a generated column can
materialize an expression for indexing.
PostgreSQL — Both, plus covering indexes (INCLUDE adds non-key columns so more queries
can be answered from the index alone), and all three combine. Fully planner-integrated.
MariaDB — No partial indexes. No direct expression-index syntax either; the supported
pattern is a generated (virtual) column holding the expression, with an ordinary index on it —
functionally equivalent, one indirection more verbose (and the optimizer uses such indexes
to satisfy GROUP BY/ORDER BY on the underlying expression). Prefix indexes
(indexing the first N characters of a long string) are a related MariaDB/MySQL-specific tool
for keeping index size down.
ClickHouse — Neither, in the B-tree sense. Alternative: skip indexes can be declared
over expressions (INDEX i lower(url) TYPE bloom_filter), and projections pre-compute
filtered, re-sorted, or pre-aggregated subsets that the optimizer substitutes automatically.
MongoDB — Partial indexes via partialFilterExpression; the older sparse indexes cover
only documents where the field exists. No expression indexes. Alternative: persist the
computed value as a real field — set by the application or by an update-pipeline $set — and
index that.
An index-only scan answers a query entirely from an index, never touching the table — valuable in row stores where the table fetch is the expensive step.
SQLite — Covering indexes work as in other B-tree engines — and without a visibility-map
caveat, since there is no MVCC row versioning. EXPLAIN QUERY PLAN reports
USING COVERING INDEX.
DuckDB — Not applicable as a distinct mechanism: columnar scans read only the referenced columns, and zone maps skip irrelevant row groups. There is no row store to avoid.
PostgreSQL — Supported, with a catch: row visibility lives in the table, not the index, so
the scan consults the visibility map — a bitmap marking pages whose rows are visible to all
transactions. Tables with heavy churn and lagging VACUUM have stale maps and fall back to heap
fetches; EXPLAIN exposes a Heap Fetches counter to diagnose this.
MariaDB — Covering-index scans (Using index in EXPLAIN) work directly — InnoDB's MVCC
reconstructs versions from undo logs rather than leaving dead rows in pages, so there is no
visibility-map dependency. The clustered layout adds a bonus: every secondary index implicitly
contains the primary-key columns, so queries touching only the indexed columns plus the
primary key are covered for free.
ClickHouse — Not applicable as such; pruning happens via the sparse index and skip
indexes, and two further mechanisms reduce reads: PREWHERE (evaluates cheap filter columns
first and reads remaining columns only for surviving granules — applied automatically) and
lazy materialization (defers reading non-filter columns until rows are known to qualify).
Projections can serve a query entirely from a pre-aggregated structure.
MongoDB — Covered queries: when filter, projection, and sort are all satisfiable from
index keys (and _id is excluded from the projection or itself indexed), documents are never
fetched. An explain() plan without a FETCH stage confirms coverage.
BM25, referenced below, is the standard relevance-ranking function of modern search engines: it weighs rare terms higher (inverse document frequency), caps the effect of term repetition, and normalizes for document length.
SQLite — FTS5: BM25 ranking, prefix indexes, phrase and NEAR queries, custom tokenizers
including trigram for substring search, and highlight()/snippet() functions for result
display. The index is incrementally maintained — either automatically via content tables that
mirror a source table, or through triggers. A complete embedded search engine.
DuckDB — The fts extension builds a BM25-scored index via PRAGMA create_fts_index,
with stemmers per language. The index is a static snapshot — it must be rebuilt after data
changes. Suitable for analyzing fixed corpora; not suitable as a live search index.
PostgreSQL — Built-in full-text search: documents reduce to tsvector (normalized,
stemmed lexemes with positions), queries to tsquery, with phrase search and per-field
weighting. GIN indexes make lookups fast; pg_trgm (contrib) covers substring and fuzzy
matching via trigrams. Built-in ranking (ts_rank) does not use corpus statistics — it is not
BM25. Maintained extensions close that gap: pg_textsearch (TigerData; true BM25 with an
LSM-style memtable index and Block-Max WAND top-k pruning, which skips blocks that cannot
reach the current score threshold; open source under the PostgreSQL License), pg_search
(ParadeDB; BM25 over the Tantivy search library), and PGroonga (Groonga-backed, strong for
CJK text).
MariaDB — FULLTEXT indexes on InnoDB and Aria: natural-language mode with a basic
TF-IDF-style relevance score, boolean mode with +/-/phrase operators, and query expansion.
Ranking quality and tokenization are dated — no BM25, weak CJK segmentation, minimum-token and
stopword behavior governed by server variables. Alternative: the Mroonga storage engine
(bundled) provides Groonga-backed full-text with proper CJK support.
ClickHouse — A native text index (inverted index with configurable tokenizers) aimed at
log exploration: token and phrase filtering over billions of rows. The
older Bloom-filter skip indexes (tokenbf_v1, ngrambf_v1) remain useful for probabilistic
token pruning with far smaller index footprints. Neither performs relevance ranking — results
are filtered, not scored.
MongoDB — The legacy $text index provides stemming, per-field weights, and score-based
ranking, limited to one text index per collection. The current path is Search and Vector
Search: Lucene-based indexes maintained by a separate mongot process alongside mongod,
queried through $search/$vectorSearch pipeline stages, with $scoreFusion combining
keyword and vector rankings (hybrid search). Available beyond Atlas in public preview.
SQLite — No. Alternative: expression indexes on json_extract(doc,'$.path') for known
hot paths, or indexed generated columns; json_each/json_tree enumerate arbitrary structure
at query time via a table scan.
DuckDB — No. Alternative: schema-on-read — read_json infers types at scan time,
VARIANT holds heterogeneous values; columnar scans reduce the need to pre-index every field.
PostgreSQL — No wildcard index type. Alternative: one GIN index over an entire jsonb
column indexes every key and path without enumerating them — jsonb_ops supports more
operators, jsonb_path_ops is smaller and faster for containment queries.
MariaDB — No. Alternative: indexed virtual columns over JSON_EXTRACT for known paths —
each path is a separate column and index, so genuinely unpredictable schemas have no good
answer in MariaDB.
ClickHouse — No wildcard index. Alternative: the JSON type stores each observed path
as a real subcolumn with its own compression and statistics, giving per-path pruning over
dynamic schemas without declared fields.
MongoDB — Wildcard indexes: {"$**": 1} indexes every field, a scoped form indexes a
subtree, and compound wildcard indexes mix wildcard and named fields. Suited to genuinely
unpredictable field sets, at the cost of larger indexes and limited sort support.
SQLite — The R*Tree module provides bounding-box indexes — the mechanism behind many on-device map viewport queries — and Geopoly adds polygon containment over a GeoJSON-flavored format; both ship in the standard build. Alternative for full GIS: SpatiaLite, the third-party OGC extension.
DuckDB — The spatial extension: GEOMETRY type, GEOS-backed ST_* function library,
R-tree indexes, and GDAL-based import/export for GeoParquet, Shapefile, and GeoJSON. Aimed at
analytical geo joins over files rather than serving live location queries.
PostgreSQL — PostGIS (third-party but the de facto standard): OGC-compliant geometry and geography types, spatial reference system transformations, GiST/SP-GiST spatial indexes, nearest-neighbor ordering, raster and topology support; pgRouting adds network routing (shortest path, isochrones) on top. The most complete open-source GIS stack, at the cost of an extension dependency.
MariaDB — Spatial types (POINT, LINESTRING, POLYGON, …) with SPATIAL R-tree
indexes on InnoDB, Aria, and MyISAM, and an OGC-subset ST_* function library including
GeoJSON conversion, geometry validation (ST_Validate, ST_IsValid), simplification
(ST_Simplify), the ST_Collect aggregate, and geohash functions. Spatial
reference system support is shallow (SRIDs are stored, but there is no reprojection), and
there is no geography type computing on the sphere. Serves
bounding-box and containment queries; not a GIS platform.
ClickHouse — Geo types (Point, Ring, Polygon, MultiPolygon), polygon dictionaries
for large-scale point-in-polygon classification, and H3/S2/geohash function families —
cell-based spatial bucketing systems that turn proximity problems into group-by problems.
GeoJSON and Mapbox Vector Tiles output formats serve map rendering pipelines.
Aggregation-oriented; no OGC compliance and no spatial reference system machinery.
MongoDB — 2dsphere indexes over GeoJSON geometries with $near, $geoWithin, and
$geoIntersects operating on a spherical earth model; the legacy 2d index covers flat
coordinates. Covers application geo queries (find nearby, containment); lacks GIS analysis —
no buffering, reprojection, or topology.
A vector column stores fixed-length float arrays (embeddings); vector search ranks rows by a distance metric — exactly, via brute-force scan, or approximately via ANN indexes (HNSW, IVF, DiskANN), which trade recall for speed. Quantization (float16, int8, 1-bit) shrinks storage and index memory at some recall cost. Only MariaDB and ClickHouse ship vector search in the core server; everywhere else the answer is an extension or a sidecar.
SQLite — No vector type in core. Alternative: sqlite-vec — a vec0 virtual table
storing float32, int8, or bit vectors as BLOBs, with fast brute-force KNN, metadata columns,
and partition keys for filtered search. It is still pre-1.0, with ANN indexes (DiskANN, IVF)
not yet stable, so brute force into the low millions of vectors is the supported reality —
partition keys and metadata filters narrow the scanned set enough that this covers most
embedded workloads.
DuckDB — Fixed-size FLOAT[n] array columns with built-in array_distance,
array_cosine_distance, and array_inner_product — exact search vectorizes well and needs
no extension. The vss extension adds an HNSW index (l2sq, cosine, and inner-product
metrics), with a caveat that matters: the index is memory-resident, and persisting it into
the database file sits behind an experimental flag because WAL recovery for custom indexes is
not implemented — a crash with uncommitted changes can corrupt it. The vss_join and
vss_match table macros express whole-table similarity joins in one call — the analytical
framing (match two datasets by embedding) none of the serving-oriented engines offers as
syntax. Suited to analytical re-ranking and batch similarity joins more than to serving live
ANN traffic.
PostgreSQL — pgvector, the de facto standard: vector (float32), halfvec
(float16), sparsevec (the only sparse-vector type in this comparison — for SPLADE-style
learned sparse embeddings), and bit types; HNSW and IVFFlat indexes (buildable
CONCURRENTLY); distance operators (<-> L2, <#> inner product, <=> cosine, plus L1,
Hamming, Jaccard); and iterative index scans to fix the classic filtered-ANN recall problem.
Index dimension caps fall out of the 8 KB page: 2,000 for vector, 4,000 for halfvec,
64,000 for bit — half or binary quantization is the path past large models.
pgvectorscale (TigerData) adds a disk-based StreamingDiskANN index and statistical binary
quantization for datasets that outgrow RAM-resident HNSW. Vectors ride MVCC like any column,
so heavy churn plus HNSW needs vacuum attention.
MariaDB — In the core server: a VECTOR(N) type (up to 16,383 dimensions) stored inline
in InnoDB, a SIMD-accelerated modified-HNSW VECTOR INDEX, and
VEC_DISTANCE_EUCLIDEAN/VEC_DISTANCE_COSINE functions, with extrapolation-accelerated
distance calculation suited to matryoshka embeddings.
Restrictions: the indexed column must be NOT NULL, and a table carries at most one vector
index. Because vectors are ordinary InnoDB data, they participate in transactions,
replication, and backups with no extra machinery — the operational argument for this design.
ClickHouse — Vectors are ordinary Array(Float32)/Array(Float64) (or BFloat16)
columns, and exact brute-force search with cosineDistance/L2Distance over a columnar scan
is practical at a scale that surprises row-store users. The vector_similarity skip index
(HNSW) adds ANN with quantization support and pre-/post-filtering, and does not require the
index to fit in one node's memory; the QBit type stores floats bit-sliced so search
precision is chosen per query, without re-storing data.
MongoDB — $vectorSearch through the Lucene-based mongot process (GA on Atlas; public
preview on Community/Enterprise): HNSW ANN plus an exact (ENN) mode, up to 8,192 dimensions,
cosine/euclidean/dot-product similarity, and pre-filtering on indexed fields. Automatic
quantization to int8 (4×) or binary (32×) cuts index memory; the BSON binData vector
subtype packs float32/int8/int1 vectors compactly at rest. $scoreFusion combines vector
and keyword rankings for hybrid search in one pipeline. Unique to MongoDB here: Automated
Embedding (public preview) generates and keeps embeddings in sync server-side from raw text
using Voyage AI models — declare a model in the index definition and never handle vectors in
application code.
SQLite — No. Alternative: an AFTER INSERT trigger that purges expired rows on each
write keeps expiry entirely inside the database; otherwise application-scheduled
DELETE ... WHERE ts < ?, or per-period database files deleted whole.
DuckDB — No background processes exist in an embedded library, so no TTL. Alternative:
host-scheduled DELETE, or partitioned Parquet where expiry is removing directories.
PostgreSQL — Not built in. Alternative: time-based partitioning with pg_partman
dropping expired partitions — a constant-time metadata operation that avoids both delete cost
and dead-row bloat — or pg_cron scheduled deletes; TimescaleDB retention policies where
installed.
MariaDB — Not declarative, but the server has a built-in Event Scheduler:
CREATE EVENT purge_old ON SCHEDULE EVERY 1 HOUR DO DELETE ... runs inside the server with no
external cron — the only row store here with native scheduling. The efficient pattern combines
it with partitioning: an event that drops expired partitions.
ClickHouse — The most capable expiry of the six, declared as TTL expressions on tables
or individual columns, applied during background merges: delete rows, reset a column to its
default, move data to another disk or volume (hot-to-cold tiering onto object storage),
recompress with a heavier codec, or aggregate expiring rows into a rollup
(GROUP BY ... SET). Timing is approximate — expiry executes when merges run.
MongoDB — TTL indexes: declare expireAfterSeconds on a date field and a background task
sweeps roughly every 60 seconds, so expiry is approximate, not instant. Time-series
collections have built-in expiry.
SQLite — No online build: CREATE INDEX holds the write lock for its duration (readers
continue in WAL mode). Build times are usually short at embedded data sizes. Alternative:
build during a quiet window, or build into a copy of the file and swap.
DuckDB — The question does not arise: a single writer process means nothing else can be writing during a build, and read-only attachments from other processes are unaffected.
PostgreSQL — CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY build without long
write locks, at the cost of two table scans; if the build fails it leaves an INVALID index
to drop manually. Non-concurrent builds are faster and parallelize across workers but block
writes.
MariaDB — InnoDB online DDL: most index builds run with ALGORITHM=INPLACE, LOCK=NONE,
allowing concurrent reads and writes with brief locks at the start and end; many column
additions and drops execute as INSTANT metadata-only changes with no rebuild at all. The
older reality — full table copy for any ALTER — survives only for a shrinking set of
operations.
ClickHouse — ALTER TABLE ... ADD INDEX is a metadata change; MATERIALIZE INDEX
back-fills existing data part by part in background merges. Reads and inserts continue
throughout, because parts are immutable and the index attaches to newly written or rewritten
parts.
MongoDB — All index builds use one mechanism that holds exclusive locks only briefly at the start and end; on replica sets, members build simultaneously and a commit quorum decides when the index becomes usable.
SQLite — CTEs, window functions, JSON table-valued functions, RETURNING, upsert via
ON CONFLICT, and ORDER BY inside aggregates (e.g., group_concat(x ORDER BY y)). No
PIVOT, no LATERAL, and a modest built-in function library. Alternative for heavy
analytics: open the same file from DuckDB via its sqlite scanner.
DuckDB — Full analytical SQL plus dialect conveniences: GROUP BY ALL (group by every
non-aggregated select column), SELECT * EXCLUDE(...)/REPLACE(...), ASOF JOIN,
PIVOT/UNPIVOT, list comprehensions and lambda functions over nested types, and
MERGE INTO for upsert logic on arbitrary match conditions.
PostgreSQL — Joins of any shape, recursive CTEs, LATERAL, window functions, grouping
sets, FILTER clauses on aggregates, MERGE with RETURNING, time-ordered UUID generation
(uuidv7()), and server-side functions for anything the SQL surface lacks.
MariaDB — Window functions, recursive CTEs, JSON_TABLE, sequences (standard-SQL
CREATE SEQUENCE, alongside AUTO_INCREMENT), INSERT/REPLACE/DELETE ... RETURNING,
INTERSECT/EXCEPT, and stored procedures for multi-statement logic; a single trigger can
fire on multiple events. In Oracle mode, PL/SQL
constructs (packages, %TYPE, cursors, associative arrays) run natively. No PIVOT, no
LATERAL, no FULL OUTER JOIN.
ClickHouse — Window functions plus a functional array toolkit: arrayMap, arrayFilter,
and other higher-order functions take lambda arguments; arrayJoin unnests an array into
rows. Aggregate functions compose with combinators — -If (conditional aggregation), -Array
(aggregate over array elements), -State/-Merge (produce and combine partial aggregate
states) — yielding several hundred effective aggregate functions. Strongest when computation
reduces to scans and aggregation; weakest when it needs many-way joins.
MongoDB — The aggregation pipeline is the computation engine: $lookup, $graphLookup,
$unionWith, $facet, $setWindowFields, $group with a large accumulator library, and
$merge to write results back to a collection. Computation is expressed as staged document
transforms; no optimizer reorders multi-collection work.
SQLite — One file per database; ATTACH spreads schemas across files and disks
(transactions spanning attached databases commit atomically in rollback-journal mode; WAL mode
guarantees atomicity only per file). PRAGMA temp_store and SQLITE_TMPDIR control temp
placement.
DuckDB — One file plus temp spill files; external data stays external (object storage, Iceberg, Delta) with a local cache for repeated remote reads. No tiering — the file lives where you put it.
PostgreSQL — Tablespaces place tables, indexes, and temp files on chosen volumes; the write-ahead log can sit on its own device. TOAST offloads large values automatically. No tiering policies — data placement is static unless you move it.
MariaDB — File-per-table InnoDB tablespaces, with undo logs and temporary tablespaces placeable on separate volumes, and per-engine data directories. Two engine-level options extend the ladder: the S3 storage engine converts a table to a read-only archive living in object storage, and MyRocks trades InnoDB's B+trees for an LSM tree when write amplification and space matter more than read latency.
ClickHouse — Storage policies over multiple disks and volumes: TTL- or fill-ratio-driven moves between tiers (NVMe to HDD to object storage), native S3/GCS/Azure disks, per-table policy selection. The most complete tiering of the six.
MongoDB — dbPath plus directoryPerDB and directoryForIndexes split databases and
indexes across mounts; the journal and the oplog are sized and placed separately. No storage
tiering in the server — cold data moves only if the application moves it, e.g. a scheduled
job or change-stream consumer copying aged documents to a cheaper cluster and deleting at
the source.
SQLite — None built in. Alternative: ZIPVFS (proprietary compression layer from the
SQLite team), community sqlite-zstd, filesystem-level compression, or application-side
compression of large values before insert.
DuckDB — Automatic per-segment codec selection: dictionary, run-length encoding, bit-packing, frame-of-reference for integers, FSST (Fast Static Symbol Table — substring dictionary compression that leaves strings randomly accessible) for text, and ALP (Adaptive Lossless floating-Point) for floats. The engine samples each segment and picks; no user configuration is exposed — simplicity traded against control.
PostgreSQL — Compresses only TOASTed (oversized) values — normal in-page rows are stored
uncompressed, so the on-disk footprint is typically several times larger than a columnar
system's for the same data. TOAST codecs: pglz and the faster lz4 (the default);
write-ahead-log compression supports lz4 and zstd. Alternative: TimescaleDB compressed
chunks, or archiving cold data to Parquet.
MariaDB — Per-table and per-engine: InnoDB page compression (zlib/lz4/lzma, written with
filesystem hole-punching so compressed pages occupy less disk) and the older COMPRESSED row
format; optional binlog compression. The bigger levers are engine choices — MyRocks
(zstd-compressed LSM, typically several times smaller than InnoDB for the same data) and
ColumnStore (columnar compression for analytics). Row-store ratios remain modest compared
to the columnar engines.
ClickHouse — Per-column codecs, chainable: LZ4 default, ZSTD(level), plus specialized
transforms — Delta (store differences), DoubleDelta (differences of differences, suited to
regular timestamps), Gorilla (XOR-based float encoding), T64 (bit-transpose of integer
blocks) — composed with a general compressor, e.g. CODEC(Delta, ZSTD(3)). Because parts are
sorted by the primary key, similar values sit adjacent and compress to a small fraction of raw
size; the ORDER BY choice directly determines the ratio.
MongoDB — WiredTiger block compression, configured per collection and per index: snappy (default, fast), zlib, or zstd (best ratio); index keys use prefix compression; the journal is compressed. Compression is per-block over BSON documents, so ratios sit well below columnar systems.
SQLite — :memory: per connection, shareable within a process via
file:name?mode=memory&cache=shared; the serialize/deserialize API converts whole databases
between in-memory images and disk files, which also enables loading a database from a network
fetch without touching disk.
DuckDB — :memory: databases, and ATTACH mixes in-memory and persistent databases in
one session — a common pattern stages hot intermediates in memory and persists final results.
Spilling still applies if intermediates exceed the memory limit.
PostgreSQL — No in-memory table type. Alternative: UNLOGGED tables skip the
write-ahead log entirely — much faster writes, but truncated on crash recovery and absent from
replicas; temp tables live in session-local buffers; hot data stays cached in
shared_buffers.
MariaDB — The MEMORY engine stores a table entirely in RAM with hash indexes by
default — contents vanish on restart (the definition persists). It predates the InnoDB buffer
pool's effectiveness and does not support BLOB/TEXT columns; its main modern use is small
lookup tables. Internal temporary tables use it automatically before spilling.
ClickHouse — A Memory engine exists for staging and temporary data. The intended path is
MergeTree plus the operating system's page cache; compressed columnar reads are typically fast
enough that dedicated in-memory tables buy little.
MongoDB — None in Community edition; a dedicated In-Memory storage engine exists in Enterprise Advanced. Alternative: a working set sized within the WiredTiger cache behaves close to memory-resident.
SQLite — Views are read-only; INSTEAD OF triggers make them writable. A view is a stored
SELECT in the schema — no materialization, no privilege semantics (there are no users).
DuckDB — Standard SQL views, including views over external files — wrapping
read_parquet('s3://...') in a view gives the rest of the application a stable relational
name for remote data.
PostgreSQL — Standard views; simple single-table views are automatically updatable, others
become writable through INSTEAD OF triggers. security_invoker controls whether
row-security policies apply as the caller or the view owner — relevant whenever views cross
privilege boundaries.
MariaDB — Updatable views (single-table, no aggregation) with WITH CHECK OPTION to
reject writes that would leave the view; SQL SECURITY DEFINER/INVOKER controls whose
privileges apply. ALGORITHM=MERGE folds the view into the outer query where possible;
TEMPTABLE materializes per query.
ClickHouse — Standard views plus parameterized views: a view body containing typed
placeholders ({tenant:String}) that callers bind at query time — server-side query
templates.
MongoDB — Read-only views defined by aggregation pipelines. They use the underlying collection's indexes but cannot have their own.
The term names several different mechanisms across these engines: a manually refreshed snapshot (PostgreSQL), a scheduled pipeline output (MongoDB), and an insert-time transformation (ClickHouse). They are not interchangeable.
SQLite — None. Alternative: summary tables maintained by AFTER INSERT/UPDATE/DELETE
triggers — hand-written incremental maintenance, workable at embedded scale — or
application-scheduled rebuilds.
DuckDB — None. Alternative: CREATE TABLE AS SELECT re-run by the host application;
recomputing over columnar data is often fast enough that incremental maintenance is not the
bottleneck.
PostgreSQL — CREATE MATERIALIZED VIEW stores the query result as a real table; REFRESH MATERIALIZED VIEW recomputes it on demand, and the CONCURRENTLY option swaps contents
without blocking readers (requires a unique index). No incremental or automatic refresh in
core. Alternative: the pg_ivm extension maintains views incrementally; pg_cron schedules
refreshes.
MariaDB — None. Alternative: summary tables maintained by triggers for incremental freshness, or rebuilt by the built-in Event Scheduler on a schedule — the same patterns as SQLite, with the scheduling running inside the server.
ClickHouse — Three distinct mechanisms. Incremental materialized views are insert
triggers: each inserted block passes through the view's SELECT and the transformed result
lands in a target table — typically an AggregatingMergeTree storing partial aggregate
states, giving rollups that stay current with ingestion at near-zero query-time cost.
Refreshable materialized views recompute periodically on a schedule. Projections store
alternate sort orders or pre-aggregations inside the source table, chosen by the optimizer
transparently. The caveat that matters in practice: incremental views see only inserted
blocks — updates, deletes, and merges in the source do not propagate.
MongoDB — No declarative materialized views. Alternative: a pipeline ending in $merge
upserts its results into a real, indexable collection — rerun on a schedule, or triggered from
change streams for near-real-time freshness. $out is the replace-everything variant.
SQLite — Disk-based with a small page cache, so data far larger than RAM is normal operation; large sorts build temporary B-trees on disk. There are no hash joins — a large join without a usable index runs as a nested loop, and that, not memory, is the practical scale limit.
DuckDB — Out-of-core hash joins, aggregation, sorting, and window functions under a single
memory_limit (default about 80% of RAM). Larger-than-memory execution requires no per-query
settings — the operators degrade gracefully from in-memory to partitioned-on-disk algorithms.
PostgreSQL — Sorts and hashes that exceed work_mem spill to temp files automatically.
work_mem applies per operator per query, so a complex plan may use several multiples of
it; total memory use is workload-dependent, and misconfiguration shows up as either excessive
spilling or out-of-memory kills.
MariaDB — Sorts run through a sort buffer with disk-based merge passes beyond it; internal
temporary tables (for GROUP BY, derived tables, UNION) start in memory and convert to
on-disk Aria or InnoDB tables past a size threshold; join buffers bound block joins. Knobs
(sort_buffer_size, tmp_table_size, join_buffer_size) are per-session multipliers, so the
same concurrency arithmetic as PostgreSQL's work_mem applies.
ClickHouse — Scans stream from disk by design, so raw data size is unbounded.
Memory-intensive operators (large GROUP BY, ORDER BY, joins) respect max_memory_usage.
Aggregation and sorting spill to disk by default once they consume half the allowed memory
(max_bytes_ratio_before_external_group_by and ..._sort, default 0.5); a query that exceeds
max_memory_usage anyway still fails rather than degrading further — a default that
protects shared clusters. Spilled execution runs at significant slowdown.
MongoDB — Aggregation stages that exceed the 100 MB per-stage memory limit spill to disk
where allowDiskUse applies (the default for most stages). Sorts not backed by an index are
the common spill source.
SQLite — Application-defined scalar, aggregate, and window functions registered through
the C API or any language binding (create_function in Python, and equivalents elsewhere),
plus runtime-loadable extension modules. There is no isolation boundary — UDFs run in your
process with your privileges, which is the embedded trade-off.
DuckDB — SQL macros (scalar and table-valued), Python UDFs (optionally vectorized over
Arrow arrays, which keeps them near native speed), C-API extension functions, and WASM UDFs in
the browser build. Community extensions install with INSTALL ... FROM community.
PostgreSQL — Functions in SQL and PL/pgSQL; trusted procedural languages (PL/Python,
PL/Perl, PL/Tcl); C extensions can add functions, types, operators, and whole index methods.
Volatility declarations (IMMUTABLE, STABLE, VOLATILE) tell the planner what it may
pre-evaluate or index — an IMMUTABLE function is what makes an expression index possible.
MariaDB — Stored functions in SQL/PSM — or in PL/SQL syntax under Oracle mode, including
packages and %TYPE declarations — plus a C/C++ UDF plugin API for native functions loaded
into the server. No sandboxed scripting languages: native UDFs run with server privileges, so
they are an operator decision, not a developer convenience.
ClickHouse — SQL UDFs as lambda expressions (CREATE FUNCTION f AS (x) -> x * 2);
executable UDFs — external programs the server spawns, exchanging rows over stdin/stdout in a
declared format, usable in any language; and WASM UDFs, sandboxed via the Wasmtime runtime,
still experimental and gated behind a server setting.
MongoDB — $function and $accumulator embed JavaScript in aggregation pipelines,
executed by an embedded JS engine. Materially slower than native pipeline operators and opaque
to the planner; an escape hatch for logic no operator combination expresses.
SQLite — None. Alternative: host code; triggers encode limited in-database logic — audit trails, denormalization upkeep, queue maintenance.
DuckDB — None; the host language is the procedural layer. SQL macros cover reusable query fragments, not control flow.
PostgreSQL — CREATE PROCEDURE with transaction control inside the body — a procedure can
commit and begin transactions mid-execution, which functions cannot.
MariaDB — Full stored-procedure support: CREATE PROCEDURE with SQL/PSM control flow,
cursors, handlers, and INOUT parameters — or PL/SQL under Oracle mode, including
SYS_REFCURSOR result passing (guarded by max_open_cursors) and cursors over prepared
statements. Combined with the Event Scheduler, procedural logic can both
live and be triggered inside the server, which no other engine here fully matches.
ClickHouse — None. Alternative: external orchestration; refreshable materialized views handle the scheduled-transform subset of the use case inside the server.
MongoDB — None in practice: storing JavaScript in system.js is legacy, and server-side
eval was removed. Alternative: application-tier code; inside the server, aggregation
pipelines with $merge cover the transform-and-store subset of what procedures usually do.
SQLite — None. Alternative: a queue table with BEGIN IMMEDIATE and
DELETE ... RETURNING makes a competent single-host job queue; in-process hooks
(sqlite3_update_hook, the preupdate hook) deliver change notifications to the embedding
application; the session extension produces changesets for offline synchronization.
DuckDB — None; no server process exists to broker anything. Alternative: external message systems in the host application, with DuckDB reading their landed output.
PostgreSQL — LISTEN/NOTIFY: transactional publish-subscribe with payloads up to 8 KB,
delivered only to currently connected sessions — notifications are not stored, so a
disconnected consumer misses them. Alternative for durable queues: a jobs table consumed
with SELECT ... FOR UPDATE SKIP LOCKED (each worker locks and takes different rows without
blocking the others), the pgmq extension, or logical replication slots for reliable change
feeds.
MariaDB — No notification primitive. The binary log is the change feed: row-based binlog
events carry every change, and the bundled mariadb-binlog utility decodes and streams them —
durable and complete, but consuming the feed is work that lives outside SQL. In-database
queues use the same
SELECT ... FOR UPDATE SKIP LOCKED pattern as PostgreSQL; GET_LOCK() provides named
advisory locks for coordination.
ClickHouse — Ingest-side streaming only: the Kafka table engine consumes topics continuously, almost always chained into a materialized view that transforms and stores the stream; continuous queries over MergeTree extend this model. No outbound pub-sub. Alternative: consumers poll, or capture changes at the source.
MongoDB — Change streams: a resumable cursor over data changes, cluster-wide, filterable with pipeline syntax, with optional pre- and post-change document images. Resume tokens let a consumer continue exactly where it stopped, across restarts and failovers. A genuine change-data-capture (CDC) primitive — but not a message broker: no fan-out guarantees, and history is bounded by oplog retention.
SQLite — EXPLAIN QUERY PLAN for the plan summary; bare EXPLAIN prints the virtual
machine bytecode the statement compiles to — SQLite executes queries on a register-based VM
called VDBE, and the program is inspectable; the CLI's .expert mode proposes indexes for a
given query; ANALYZE gathers planner statistics, and PRAGMA optimize re-analyzes only
what changed.
DuckDB — EXPLAIN and EXPLAIN ANALYZE render plan trees with per-operator timing and
cardinalities; PRAGMA enable_profiling = 'json' emits structured profiles for tooling.
PostgreSQL — EXPLAIN (ANALYZE, BUFFERS, TIMING, WAL, FORMAT JSON) shows the plan with
actual row counts, buffer traffic, and per-node timing; auto_explain logs slow plans in
production; pg_stat_statements aggregates timing and I/O per normalized query across the
workload — the standard first stop for "what is slow."
MariaDB — EXPLAIN and EXPLAIN FORMAT=JSON for estimates, and — distinctively — the
ANALYZE statement: ANALYZE SELECT ... executes the query and reports estimated versus
actual row counts side by side, exposing optimizer misestimates directly. Optimizer trace
shows the plan search, and the /*+ ... */ hint set turns what the analysis suggests into
enforceable per-query plan decisions; the slow query log covers workload analysis;
performance_schema exists but ships disabled by default.
ClickHouse — EXPLAIN AST|SYNTAX|PLAN|PIPELINE|ESTIMATE cover the query at different
compilation stages; system.query_log and system.query_thread_log record every executed
query's resource use for after-the-fact forensics; a sampling profiler produces stack-level
flame graphs of where CPU time went. Extensive but low-level; interpretation is on the
operator.
MongoDB — explain() at three verbosity levels (queryPlanner, executionStats,
allPlansExecution) shows candidate plans, the winning plan, and per-stage keys and documents
examined — the ratio of examined to returned is the key efficiency signal. $queryStats
aggregates by query shape. Plan selection ranks candidates by cost estimates, with the
empirical multi-planner as backup.
How far each engine's own machinery goes before anything outside it is needed.
SQLite — The project itself supplies more than commonly assumed: sqlite3_rsync (ships
with SQLite) synchronizes a WAL-mode replica safely while the source stays live; the backup
API and VACUUM INTO produce consistent online copies for snapshot-based replicas; and the
session extension is a genuine DIY replication primitive — capture changesets on the
writer, ship them as blobs, apply them on replicas with conflict handlers, invert them to
undo. The cr-sqlite loadable extension pushes further along the same seam: it turns
tables into CRDTs (conflict-free replicated data types), so concurrent writers on different
replicas merge deterministically without coordination. What cannot be built from these
parts: automatic failover — that is where external layers (Litestream, rqlite) begin.
DuckDB — Nothing native, by design. The engine's own reach: EXPORT DATABASE or a file
copy after CHECKPOINT gives consistent snapshots, and read-only ATTACH from other
processes scales reads on one host. Shared datasets across machines come from the engine's
own lake formats — many DuckDB instances reading one DuckLake/Iceberg catalog — rather than
replication.
PostgreSQL — Two complete systems in core. Physical streaming replication ships
write-ahead-log bytes to byte-identical standbys — asynchronous, synchronous, or
quorum-synchronous (synchronous_standby_names), with cascading. Logical replication
publishes row changes at table granularity to subscribers that may differ in version or
schema, with row and column filtering and sequence synchronization. The pglogical
extension extends the logical system further — earlier-version support, row-level conflict
handling, and topologies core logical replication cannot express. The failover mechanics
are also native — pg_basebackup seeds standbys, pg_promote() promotes one, pg_rewind
(bundled) reattaches a demoted primary without a full recopy. Only the failover decision —
detecting failure and choosing the survivor — has no in-process answer; extension-plus-daemon
packages (repmgr, pg_auto_failover) keep most of that logic in the database, and
purely external orchestration (Patroni and kin) takes over from there. Multi-master does not
exist in core at all.
MariaDB — The richest native set of the six, and the least dependent on outside help.
Asynchronous binlog replication (row, statement, or mixed formats) with global transaction
IDs, multi-source replication (one replica applying several primaries — consolidation
topologies the others cannot express), parallel appliers, delayed replicas, and
semi-synchronous mode (commit waits for one replica's acknowledgment). Galera also ships
in the server: certification-based virtually synchronous multi-master clustering where every
node accepts writes and conflicting transactions abort at commit, with parallel appliers
between clusters and configurable write-set retry (wsrep_applier_retry_count). Galera
removes the failover problem for writes entirely — any surviving node is already a writable
primary. Classic replication topologies still need an external decision-maker for automatic
promotion.
ClickHouse — ReplicatedMergeTree: per-table asynchronous multi-master replication
coordinated by ClickHouse Keeper, a built-in consensus service speaking the Raft protocol —
consensus, replication, and insert deduplication all inside the product. Any replica accepts
inserts; replicas fetch and merge each other's parts; block-hash deduplication makes insert
retries safe; insert_quorum trades latency for stronger write guarantees. Cross-datacenter
replication is also native — replicas of one table can live in different regions, paying
Keeper-coordination latency. Replication protects availability and durability; it does not
add transactional consistency.
MongoDB — The most complete native story: replica sets — one primary accepts writes,
secondaries replay its oplog (a capped collection recording every write, which doubles as
the change-stream source), and elections promote a new primary automatically. Up to 50
members; hidden and delayed members serve backup and error-recovery roles; failover handling
lives in every driver. Replication, consensus, and failover need nothing outside the base
product — including in Community edition. Even cross-cluster feeds can be self-built from
change streams: a resumable cursor applied to a second cluster is a supported pattern before
reaching for the commercial mongosync.
SQLite — None. Alternative: application-routed per-tenant database files — ATTACH
joins multiple databases in one connection (10 by default, 125 at compile time), so a router
query can even span shards in plain SQL.
DuckDB — None, and none planned — single-node operation is the design. Alternative: lake architectures using the engine's own format support: many DuckDB instances reading shared Iceberg/DuckLake tables, each node a full engine over the same data.
PostgreSQL — No native sharding. Alternative: Citus (distributed tables partitioned by a
column, reference tables replicated everywhere, parallel distributed queries),
postgres_fdw-based federation, or application routing. Vertical scaling plus read replicas
covers a wide range before sharding is needed.
MariaDB — Galera provides clustering for availability: three or more nodes, all writable, near-synchronous — not for capacity, since every node holds all data. Capacity sharding uses the Spider engine, which presents remote tables on backend servers as local partitions, or application-level routing. MaxScale, the usual router/failover layer, is source-available under the BSL — not open source — a licensing fact deployments should weigh.
ClickHouse — Shards defined in cluster configuration; Distributed tables fan queries out
and merge results; multi-stage distributed execution handles plans that need data exchanged
between stages; parallel replicas apply every replica of a shard to one query. Cluster
topology is static configuration (or cloud-managed); redistributing existing data after adding
shards is a manual process — there is no automatic balancer.
MongoDB — Native sharded clusters: a config-server replica set stores routing metadata,
mongos routers direct operations, the balancer redistributes chunks automatically, zones pin
data to shard subsets for locality or compliance, and resharding runs live. Operationally the
most complete built-in sharding of the six, with matching complexity — three component types
to deploy and monitor.
SQLite — BLOBs up to 1 GB by default with an incremental blob I/O API for streaming
reads and writes inside a value; the sqlar format uses a SQLite file as a file archive. The
project's published measurements show blobs of roughly 10 KB and below reading faster
through SQLite than as separate filesystem files — small-file storage is a legitimate use, not
an abuse.
DuckDB — Treats object storage as a data source (Parquet/CSV/JSON on S3, Azure, GCS;
Iceberg and Delta tables), not as a blob service. A BLOB type holds binary values in-file.
PostgreSQL — BYTEA columns (TOASTed, 1 GB hard cap per value, practical well below that)
or Large Objects — a separate streaming API supporting seek/read/write within a value, up to
4 TB, with its own permission and cleanup handling. Common practice: files in object storage,
metadata and pointer in PostgreSQL.
MariaDB — LONGBLOB/LONGTEXT up to 4 GB, with the practical transfer ceiling set by
max_allowed_packet (1 GB maximum) — values must fit in one protocol packet, so streaming
multi-gigabyte objects through the wire protocol is not realistic. No chunked file store.
Common practice mirrors PostgreSQL's: files in object storage, metadata in the database.
ClickHouse — Object storage as table storage (S3-backed MergeTree, tiering targets) and as
query sources (s3() and url() table functions; Iceberg, Delta, and Hudi readers). Serving
individual binary objects is not a supported pattern.
MongoDB — GridFS: a driver-side convention that splits files across a chunks collection
with metadata in files, supporting range reads. It adds no server machinery — and loses to
object stores on cost and throughput at scale.
File formats are not interchangeable. CSV is the universal but weakest interchange: untyped (everything is text), unschema'd, uncompressed, flat. NDJSON (one JSON document per line) carries nested structure and streams well, but stays text. Parquet is typed, compressed, and columnar, with per-row-group min/max statistics that let engines skip data without reading it — the default format of modern analytical pipelines. Arrow is a columnar in-memory layout for zero-copy interchange between processes and libraries. Avro and Protobuf are schema'd row formats common in Kafka pipelines. Finally, each engine has a native binary format that round-trips its types with full fidelity, which text formats never do. How much of this spectrum an engine speaks is a real capability difference.
SQLite — Narrow by design: the CLI's .import --csv and output modes (csv, json,
markdown, table), the csv virtual-table extension for querying a file without import,
and .dump producing portable SQL text. JSON files are handled by loading text and shredding
with json_each/json_tree — there is no JSON file reader. No Parquet, Arrow, or Avro
support exists in the project; type affinity means undeclared CSV imports arrive as TEXT.
That is the full extent — Parquet, Arrow, and Avro are unreachable from inside SQLite.
DuckDB — The widest practical file coverage. CSV: SELECT * FROM 'file.csv' with a
sniffer that infers dialect and types, handles compressed input, globs, and schema drift
across files (union_by_name). JSON/NDJSON: read_json with type inference into
STRUCT/LIST values. Parquet: native read and write with projection and predicate
pushdown — filters reach into row-group statistics so only matching row groups are read,
including over HTTP/S3 via range requests. Arrow: zero-copy both directions with host-language
libraries. Avro via a community extension; Excel read/write via the excel extension.
COPY TO writes CSV, JSON, or Parquet (optionally Hive-partitioned). A common deployment is
nothing more than a CSV-to-Parquet compactor.
PostgreSQL — COPY is the bulk path, in three flavors: text, csv (header handling,
load-time WHERE filtering), and binary — PostgreSQL's own typed format, fastest and
lossless but not portable to other systems. COPY TO can also emit JSON, including as a
single array. JSON files are ingested by loading text into a jsonb column and shredding
with jsonb_to_recordset/JSON_TABLE. No core Parquet, Arrow, or Avro support — a real gap
for a system this extensible. Alternative: pg_parquet (Crunchy Data;
COPY ... TO/FROM 's3://…/file.parquet' with type mapping), parquet_fdw for read-only
foreign tables, or pg_duckdb to delegate file querying to an embedded DuckDB; file_fdw
exposes CSV/text files as foreign tables without loading.
MariaDB — LOAD DATA INFILE is the fast bulk-CSV path (and LOAD DATA LOCAL INFILE from
the client side); mariadb-dump/mariadb-import handle logical dump and restore, including a
parallel per-database directory mode. Two engines extend reach: the CSV engine stores a
table as a live .csv file on disk, and the CONNECT engine maps CSV, JSON, XML, and
remote ODBC/JDBC sources as queryable tables. ColumnStore bulk-loads via cpimport. No
Parquet, Arrow, or Avro support anywhere in the server.
ClickHouse — The longest format list of the six: 70+ input/output formats spanning CSV/TSV
variants (CSVWithNames, CSVWithNamesAndTypes), JSONEachRow (NDJSON) and a dozen JSON
layouts, Parquet, ORC, Arrow, Avro, Protobuf, Cap'nProto, MsgPack, MySQLDump, and its own
Native and RowBinary wire formats — the fastest and type-exact paths. Any format works
anywhere a format applies: INSERT ... FORMAT, SELECT ... FORMAT, the file()/url()/
s3() table functions, and clickhouse-local for serverless file processing. Schema
inference works across formats, so SELECT * FROM file('data.parquet') needs no declared
table, and converting between any two formats is one statement.
MongoDB — Two tool families with different fidelity. mongodump/mongorestore move
BSON — the native binary format, preserving every type (ObjectId, Decimal128, dates, binary),
the correct choice for backup and migration. mongoimport/mongoexport move Extended JSON
and CSV/TSV; Extended JSON encodes BSON types as annotated JSON ({"$oid": …}, {"$date": …})
and round-trips, while CSV export flattens and loses types — importing it back yields
strings and doubles, a classic data-corruption path. Typed CSV import needs explicit
--columnsHaveTypes annotations. No in-server file querying of any format — files must pass
through the import tools or application code to be queryable.
SQLite — Row-oriented B-trees; nothing columnar exists for it, and nothing native approximates it. The closest the engine can be pushed: covering indexes narrow what a scan reads to the referenced columns — a fraction of the benefit, none of the compression.
DuckDB — Columnar storage and vectorized execution are the core design: operators process batches of ~2048 values per column at a time, which keeps data in CPU caches and enables SIMD.
PostgreSQL — Row-oriented; no columnar storage in core. The table access method API permits columnar implementations. Alternative: Citus columnar (append-optimized, compressed), TimescaleDB compressed hypertables (row-to-columnar conversion for cold chunks), or pg_duckdb for pushing analytical queries to an embedded columnar engine.
MariaDB — Row-oriented by default, but ColumnStore ships as a storage engine: a
distributed columnar engine (separate processes for query coordination and storage) reached
through the same SQL interface — CREATE TABLE ... ENGINE=ColumnStore puts one table on
columnar storage while the rest of the schema stays on InnoDB. Heavier to operate than the
embedded columnar engines here, but unique in offering per-table row/columnar choice inside
one server.
ClickHouse — Columnar since inception: each column its own compressed, sorted file within each part, processed by a vectorized engine. The entire system assumes scan-heavy access.
MongoDB — Document storage; not columnar. Columnstore indexes never progressed past a
preview. Alternative: time-series collections store bucketed data with columnar-style
compression — the closest native approximation — and scheduled $merge rollups pre-shape
the analytical reads that columnar storage would otherwise serve.
SQLite — No time-series features. An indexed (series_id, timestamp) table with window
functions serves on-device telemetry buffering — its natural time-series habitat is the IoT
edge; retention via periodic delete or per-period files. Fleet-scale aggregation belongs
elsewhere.
DuckDB — No time-series storage features — no retention or downsampling policies. Strong
at time-series analysis: ASOF JOIN, window functions, and range joins over Parquet
archives.
PostgreSQL — Core PG offers partitioning plus BRIN indexes (block-range summaries that suit append-in-time-order data at a fraction of a B-tree's size). TimescaleDB adds hypertables (automatic time partitioning), continuous aggregates, native compression, and retention policies. Without extensions, downsampling and retention are manual.
MariaDB — Nothing dedicated: the toolkit is range partitioning by time, Event
Scheduler-driven retention (drop old partitions on schedule), and window functions for
analysis. System-versioned tables are sometimes mistaken for time-series support — they record
row history for audit and as-of queries, not metric streams. The TIMESTAMP range reaches
2106, clearing the 2038 ceiling.
ClickHouse — The workload it is most widely deployed for: MergeTree ordered by
(series, time) with delta and Gorilla codecs, TTL-driven retention and rollup, materialized
views maintaining multi-resolution downsamples at ingest, ASOF JOIN, and a dedicated
TimeSeries engine accepting Prometheus remote-write (experimental). The basis of several
observability platforms.
MongoDB — Time-series collections: documents are grouped into internal buckets by metadata and time, stored with columnar-style compression inside the buckets, expired by TTL, and queried through automatic rewrites against the bucket layout. The old pattern of manually embedding time-windowed arrays is obsolete.
SQLite — One statement runs on one thread; PRAGMA threads permits helper threads only
for sort operations. Not capable of parallel query execution. Alternative: run independent
read queries on separate connections — WAL mode supports many concurrent readers — and let
the application split one large scan into key ranges executed concurrently.
DuckDB — Morsel-driven parallelism: input is divided into morsels (small row-group-sized work units) that all cores pull from a shared queue, which keeps every core busy even when data is skewed. Combined with vectorized execution, a single query saturates a laptop or a many-core server with zero configuration. Single-node only.
PostgreSQL — Intra-query parallelism across background worker processes: parallel
sequential/index/bitmap scans, parallel hash joins including FULL/RIGHT, parallel aggregation,
with an asynchronous I/O subsystem (worker-based or io_uring on Linux) overlapping reads with
computation. Parallelism is cost-gated — small queries run serial, and the worker count is
bounded by max_parallel_workers_per_gather. JIT expression compilation exists but is
disabled by default (its cost model is unreliable); execution remains row-at-a-time, not
SIMD-vectorized, though specific hot paths (CSV parsing in COPY, checksums) use SIMD
directly.
MariaDB — No intra-query parallelism in the core server: one query executes on one thread, full stop — the same limitation as MongoDB on a single node, and the starkest gap between MariaDB and PostgreSQL for reporting workloads. Parallelism exists elsewhere: parallel replication appliers, parallel dump/import tooling, and the ColumnStore engine (which distributes and parallelizes its own queries). Alternative: route analytical queries to ColumnStore tables, which parallelize internally while staying inside the same server and SQL dialect.
ClickHouse — Parallel at four levels: SIMD within a core, all cores within a node
(max_threads), all shards across the cluster, and all replicas of each shard (parallel
replicas). Designed around few, large queries rather than thousands of concurrent small ones.
MongoDB — One query executes essentially single-threaded on a node; scaling comes from
concurrency across queries and from shard fan-out. Not capable of using many cores for one
large aggregation on one node. Alternative: pre-aggregate with scheduled $merge jobs so
heavy reads land on precomputed collections, and shard so aggregations fan out.
SQLite — Three sanctioned extension seams: loadable extensions (functions, collations, virtual tables), the virtual-table interface (FTS5 and R*Tree are themselves virtual tables — third parties can implement arbitrary backing stores), and the VFS layer, which abstracts all file I/O and enables encryption VFSes, compressed VFSes, and HTTP-range readers that query remote databases without downloading them. All operate in-process. Maintained community extensions include sqlean (a curated suite adding regex, crypto, fuzzy matching, file I/O, and statistics functions), sqlite-vec (vector search), SpatiaLite (OGC GIS), and cr-sqlite (CRDT-based multi-writer merge); the first-party session extension ships in the amalgamation for changeset capture.
DuckDB — Extensions are the primary growth mechanism, installed at runtime with
INSTALL/LOAD. Core (first-party) extensions: spatial, fts, httpfs (S3/HTTP),
iceberg, delta, azure, json, plus sqlite, postgres, and mysql
scanners that query those databases in place. The community repository
(INSTALL ... FROM community) hosts maintained third-party extensions including duckpgq
(SQL/PGQ graph queries), h3 (hexagonal geospatial indexing), vss (vector similarity),
crypto (hashing), and lindel (space-filling-curve encoding). The C API supports custom
functions, types, and file systems.
PostgreSQL — Extension APIs at most layers: custom types, operators, index access methods, table access methods, planner hooks, custom scan providers, background workers, and logical decoding output plugins — uniquely, third-party code can reach the planner and index layers. Stable, actively maintained extensions include: PostGIS (geospatial), pgvector (vector similarity indexes) and pgvectorscale (TigerData's disk-based ANN variant), TimescaleDB (time series), Citus (sharding/columnar), pg_partman (partition management), pg_cron (scheduling), pg_stat_statements and pgaudit (observability/audit), pg_repack (online bloat removal), pg_ivm (incremental materialized views), pgmq (queues), Apache AGE (graph/openCypher), pg_textsearch / pg_search / PGroonga (BM25 and text search), wal2json (CDC output), pg_duckdb (embedded analytics).
MariaDB — A plugin architecture whose flagship is the storage engine API: InnoDB,
Aria (crash-safe MyISAM successor, used for internal temp tables), MyRocks (LSM-tree,
zstd-compressed, for write-heavy workloads), ColumnStore (distributed columnar),
Spider (sharding/federation), CONNECT (external data as tables), S3 (object-storage
archive), OQGRAPH (graph computation), Mroonga (CJK full-text), plus MEMORY, CSV,
ARCHIVE, BLACKHOLE, and FederatedX. Further plugin types cover authentication (PAM, Kerberos,
ed25519), audit logging, encryption key management, and the thread pool. What plugins cannot
touch: the parser, planner, and executor — those are fixed.
ClickHouse — Little runtime pluggability; capability ships compiled-in (table engines, format readers, integrations) with external hooks limited to executable UDFs, user-defined executable dictionaries and table functions, and experimental WASM UDFs. Extending the engine itself means building from source. The maintained community ecosystem is external tooling: clickhouse-backup, the Altinity Kubernetes operator, chproxy (query routing and limits), and chDB (ClickHouse as an embedded Python library).
MongoDB — No extension API; WiredTiger is the fixed storage engine. Server functionality
grows only through vendor releases; Search and Vector Search arrive as a separate vendor
binary (mongot), not a plugin interface. Community activity centers on distributions and
tooling rather than extensions — Percona Server for MongoDB (drop-in build with encryption
and auditing), Percona Backup for MongoDB.
SQLite — In-process library with no network protocol. Multiple processes on one host can share a database file through filesystem locking; WAL mode allows many concurrent readers alongside the single writer. Alternative for remote access: an application API in front of the file — the database itself never speaks a network protocol.
DuckDB — In-process library; connections are cursor objects within the host application. One process holds write access to a database file; other processes may attach read-only.
PostgreSQL — One operating-system process per connection: strong fault isolation, but per-connection memory and scheduling costs make thousands of direct connections impractical. The comfortable direct range is low hundreds; beyond that, standard practice multiplexes many client connections onto few server connections through an external pooler — the one piece of the connection story the server does not solve itself.
MariaDB — One thread per connection by default — lighter than a process, so direct connection counts in the low thousands are workable — with a built-in thread pool mode that multiplexes connections over a bounded worker set for spiky many-client workloads (in MySQL this is a paid Enterprise feature; in MariaDB it is standard) — the native thread pool covers the load levels the other row stores need a proxy for.
ClickHouse — Threaded server speaking its native TCP protocol, HTTP, and gRPC, plus MySQL
and PostgreSQL wire-compatibility endpoints. Sized for tens of concurrent heavyweight queries,
not thousands of small ones; per-user max_concurrent_queries guards against overload.
MongoDB — Thread per connection server-side; drivers pool connections and track replica-set topology through the wire protocol, so failover redirection happens in the client automatically. External poolers are rarely needed.
SQLite — Per-connection page cache (PRAGMA cache_size, default about 2 MB) over the OS
cache; optional mmap_size switches reads to memory-mapped I/O. Shared-cache mode exists but
the project discourages it.
DuckDB — A buffer manager under one memory_limit (default about 80% of RAM) covers
caching, query intermediates, and spill thresholds; repeated reads of remote files go through
a local cache.
PostgreSQL — shared_buffers holds hot pages in front of the operating system's page
cache — data passes through both, an accepted double-buffering cost. work_mem bounds each
sort/hash operation; effective_cache_size merely informs the planner how much total caching
to assume. Balancing work_mem against connection concurrency is the recurring tuning task.
MariaDB — The InnoDB buffer pool is the central cache — and because tables are
clustered indexes, it caches data and indexes as one, typically sized to 50–75% of RAM on a
dedicated server, divisible into instances to reduce mutex contention. An adaptive hash index
builds hash lookups over hot B+tree pages automatically. Aria and MyISAM keep separate page/key
caches — Aria's page cache is segmentable (aria_pagecache_segments) to cut mutex
contention; the legacy query cache is deprecated and off (it serialized writes).
ClickHouse — No buffer pool for data — compressed column files ride the OS page cache — plus targeted internal caches: the mark cache (index entries locating granules), an optional uncompressed-block cache for short repetitive queries, a query result cache, and a filesystem cache fronting object-storage-backed tables.
MongoDB — The WiredTiger cache (default: half of RAM minus 1 GB, floor 256 MB) holds uncompressed pages; the OS file cache holds compressed blocks. Two deliberate tiers in different formats — evicting from the WiredTiger cache still leaves a warm compressed copy.
A write-ahead log (WAL) records changes sequentially before they are applied to the main data structures, so a crash can be recovered by replaying the log.
SQLite — Two journal modes: rollback journal (copies old pages aside, restores them on
crash) and WAL (appends new pages to a log, letting readers and the writer coexist; a
checkpoint folds the log back into the main file). synchronous=FULL survives power loss;
NORMAL in WAL mode trades a small durability window for speed. Crash injection is part of
release testing. VACUUM INTO and the backup API produce consistent online copies.
DuckDB — WAL per database file with automatic checkpointing; the WAL is covered by
database encryption when enabled. Backup is a file copy or EXPORT DATABASE.
PostgreSQL — WAL underpins durability, replication, and point-in-time recovery:
synchronous_commit levels from off to remote_apply, group commit amortizing fsync across
transactions, full-page writes defending against torn pages, and WAL archiving as the basis of
continuous backup. Incremental backups derive from WAL summaries.
MariaDB — Layered logging: the InnoDB redo log recovers the engine, the doublewrite buffer
defends against torn pages, undo logs serve MVCC and rollback, and the binary log — a
separate, logical log — feeds replication and point-in-time recovery. Redo and binlog are kept
consistent by an internal two-phase commit with group commit batching both; the opt-in
InnoDB-integrated binlog collapses this into one engine and one sync path. Durability knobs
(innodb_flush_log_at_trx_commit, sync_binlog) each range from strict to relaxed.
ClickHouse — Inserts write parts without fsync by default (fsync_after_insert opts in):
the design accepts possible loss of the most recent inserts on power failure, relying on
replication for redundancy — a deliberate throughput-over-durability trade. async_insert
batches many small inserts server-side into fewer parts before writing. Updates and deletes
are asynchronous mutations.
MongoDB — The WiredTiger journal groups commits and flushes on a ~100 ms cadence by
default; {j: true} write concern forces a journal fsync for a specific write, and majority
adds cross-node durability. Checkpoints every 60 seconds bound recovery time. The oplog serves
replication, not durability.
SQLite — Not applicable — no network layer exists. Transfer compression happens at
whatever layer ships the file (rsync, sqlite3_rsync, object storage).
DuckDB — No wire protocol of its own. Remote file reads use HTTP compression where offered, and Parquet's internal compression plus byte-range requests keep transfer close to the bytes actually needed.
PostgreSQL — No wire-protocol compression; TLS-level compression is disabled industry-wide (the CRIME attack made compressed TLS exploitable). Alternative: SSH tunnels or network-layer compression for bulk cross-site transfer. Protocol compression patches have been proposed repeatedly without being committed.
MariaDB — Optional protocol compression negotiated at connect time (zlib-based;
--compress client flag), off by default because it costs CPU on both ends and helps only on
slow links. Replication streams can compress binlog events. TLS is separate and standard.
ClickHouse — Compressed by default: LZ4 on the native protocol (ZSTD selectable),
Content-Encoding support on HTTP, and a setting selecting the codec for inter-server
distributed traffic.
MongoDB — Wire compression negotiated per connection — snappy, zlib, or zstd
(compressors=zstd in the connection string) — applying to client-server and intra-cluster
links.
What it takes to run each engine safely, and the recurring work it demands. Hardening guidance below follows each project's official security documentation; maintenance items are the tasks that cause incidents when skipped.
SQLite
- Hardening: there is no server, so OS file permissions are the entire access story; the real attack surface is untrusted SQL and untrusted database files. The project's own defenses:
SQLITE_DBCONFIG_DEFENSIVEblocks SQL that could deliberately corrupt the file,PRAGMA trusted_schema=OFFstops hostile schemas from invoking functions,PRAGMA cell_size_check=ONcatches malformed pages,sqlite3_limit()shrinks parser and query ceilings,sqlite3_set_authorizer()whitelists operations, a progress handler enforces query timeouts, andsqlite3_hard_heap_limit64()caps memory; for files of unknown origin, disable memory-mapped I/O (PRAGMA mmap_size=0) and runPRAGMA quick_checkbefore any other SQL. - Maintenance:
PRAGMA optimizeon connection close keeps planner statistics fresh (the project's standing recommendation); periodicPRAGMA integrity_check;VACUUMreclaims space after mass deletes; in WAL mode, watch that checkpoints keep up (PRAGMA wal_checkpoint(TRUNCATE),journal_size_limit) or the log grows without bound. - Migrations: DDL is transactional, so a whole migration wraps in one transaction and rolls back on failure. For changes
ALTER TABLEcannot express, the documentation prescribes an exact twelve-step rebuild-and-swap procedure (new table, copy, drop, rename — withPRAGMA foreign_keys=OFFaround it andPRAGMA foreign_key_checkafterward). Track applied migrations inPRAGMA user_version, an integer slot in the file header meant for exactly this; and keep migrations short — the single writer blocks all other writes while one runs. - Resource trade-offs: WAL mode trades read concurrency against disk and read CPU. A checkpoint can only reset the WAL when no reader is using it, so with continuously overlapping readers "no checkpoints will be able to complete and hence the WAL file will grow without bound" (official WAL documentation) — and read time grows with WAL size, since every reader must search the WAL for current content. Engineer deliberate reader gaps, or force the issue with
SQLITE_CHECKPOINT_TRUNCATE.
DuckDB
- Hardening: the official checklist targets embedding untrusted workloads:
enable_external_access = falsecuts offATTACH,COPY, and external file reads;allowed_directories/allowed_pathsscope what remains;disabled_filesystems = 'LocalFileSystem'blocks local disk entirely;allow_community_extensions = false(with autoinstall/autoload off) restricts extensions to signed core ones;lock_configuration = truefreezes all of it against the queries that follow. Cap blast radius withmemory_limit,threads, andmax_temp_directory_size; use prepared statements against injection; persistent secrets are written with owner-only (600) permissions. - Maintenance: deliberately minimal — no vacuum, no statistics jobs.
CHECKPOINTfolds the WAL into the file;EXPORT DATABASEproduces logical backups; staying on current patch releases is the main ongoing task. - Migrations: DDL is transactional here too, and the analytical workload favors rebuilds —
CREATE TABLE ... AS SELECTinto the new shape, then a rename, all in one transaction — over intricate in-placeALTERs; the single writer process means a migration never races concurrent DDL. - Resource trade-offs: result ordering costs memory. Loads and exports larger than RAM can fail outright unless
preserve_insertion_order = falselets the engine reorder rows freely (the documented fix for out-of-memory imports); in memory-constrained environments the same guidance says to reducethreads, trading CPU parallelism for per-thread working memory.
PostgreSQL
- Hardening:
pg_hba.confrules withscram-sha-256authentication, TLS, least-privilege roles (revoke defaultPUBLICschema rights), row-level security where tenancy demands it, a dedicated OS user. - Maintenance: the highest-stakes item in this document — vacuum. Autovacuum must keep up with dead-tuple churn, and transaction ID wraparound must be monitored (
age(datfrozenxid)): a cluster that exhausts the XID horizon stops accepting writes until vacuumed. Beyond that:ANALYZEkeeps plans honest,REINDEX CONCURRENTLYrebuilds bloated indexes, the bundledamcheckextension verifies index integrity, and data checksums catch storage-level corruption. Backups arepg_basebackupplus continuous WAL archiving for point-in-time recovery. Minor updates are a binary swap and restart; major upgrades go throughpg_upgrade.pg_stat_statementsandlog_min_duration_statementare the standing observability posture. - Migrations: transactional DDL is the headline advantage — a multi-statement migration commits or rolls back atomically. The discipline that keeps it graceful: set
lock_timeoutbefore DDL, because a blockedALTER TABLEqueues behind long transactions while itself blocking everything that arrives after it; add constraints asNOT VALIDand runVALIDATE CONSTRAINTseparately (validation takes a weaker lock); build indexesCONCURRENTLY(which cannot run inside a transaction); adding a column with a default is a metadata-only change and safe. Expand-and-contract — add the new shape, backfill in batches, move readers, drop the old — remains the pattern for anything the locks make risky. - Resource trade-offs: replication slots trade delivery guarantees against disk. A slot whose consumer stops (a dead subscriber, an abandoned CDC pipeline) makes the server retain WAL indefinitely — the default
max_slot_wal_keep_size = -1places no cap — untilpg_walfills the volume. Setting a cap converts the failure from disk-full to slot invalidation (wal_status = unreserved), which sacrifices the lagging consumer to save the primary; monitorpg_replication_slotseither way.
MariaDB
- Hardening:
mariadb-secure-installationremoves anonymous users and the test database and locks down root; from there, least-privilegeGRANTs, TLS, at-rest encryption through the key-management plugin API, the audit plugin for who-did-what, and binding to trusted interfaces. - Maintenance: mariadb-backup (bundled) takes hot, non-blocking physical backups and records the binlog position for point-in-time recovery; binlog retention is bounded by
binlog_expire_logs_seconds(unbounded logs fill disks).ANALYZE TABLErefreshes statistics;OPTIMIZE TABLEreclaims space and defragments after mass deletes. Runmariadb-upgradeafter every server upgrade — it repairs system tables, and skipping it causes subtle breakage. Watch the InnoDB history list length: a long-running transaction stalls purge and bloats undo storage silently. - Migrations: DDL is not transactional — every DDL statement implicitly commits, so a half-failed migration leaves partial state; migration scripts must be re-runnable. State intent explicitly:
ALTER TABLE ... , ALGORITHM=INSTANT(orINPLACE), LOCK=NONEmakes the server fail the statement if it cannot satisfy it, instead of silently falling back to a locking table copy. DDL also replicates through the binlog and executes serially on replicas — a longALTERon the primary becomes replica lag, so schedule heavy changes accordingly. - Resource trade-offs: Galera's virtual synchrony makes cluster write throughput a function of the slowest node. When any node's receive queue exceeds
gcs.fc_limit, it broadcasts a Flow Control pause and every node stops replicating new transactions until the laggard's queue drains belowgcs.fc_limit × gcs.fc_factor— one undersized replica, one node doing a heavy local query, or one slow disk throttles writes cluster-wide. Watchwsrep_flow_control_paused; size all nodes identically.
ClickHouse
- Hardening: a fresh install's
defaultuser has no password and is also the internode account — set a password or restrict it to localhost, create SQL-driven users and roles (CREATE USER,GRANT), then disabledefaultfor applications once internode credentials are configured. Settings profiles pinreadonlyand resource constraints per user; quotas rate-limit; passwords belong in config as SHA-256 hashes, never plaintext; TLS covers both native and HTTP endpoints. - Maintenance: native
BACKUP/RESTOREwrites full or incremental backups to File, Disk, S3, or Azure targets — and the documentation itself warns that an untested backup should be assumed broken. Watchsystem.parts(part-count growth throttles inserts),system.mutations(stuck mutations), andsystem.replication_queue(replica lag); do not runOPTIMIZE TABLE ... FINALas routine housekeeping — it rewrites entire partitions. Keeper is a second stateful service with its own disk and monitoring needs. Upgrades roll replica by replica. - Migrations: adding and dropping columns are cheap metadata changes, but
ALTER TABLE ... UPDATE/DELETEare asynchronous mutations — issue them, then confirm completion insystem.mutationsbefore depending on the result. Changing theORDER BYkey or engine means building a new table (INSERT ... SELECT) and atomically swapping it in withEXCHANGE TABLES; on clusters, run DDL everywhere in one statement withON CLUSTERand verify it applied on every replica. - Resource trade-offs: the insert pattern determines merge cost, because every
INSERTwrites an immutable part. The official guidance is batches of at least 1,000 rows (ideally 10,000–100,000): ten thousand single-row inserts create ten thousand parts, each paying disk I/O and metadata writes now and merge CPU and re-read/re-write I/O later, until "too many parts" errors stop ingestion entirely.async_insertmoves the batching into server memory instead — the same trade, relocated from client code to server RAM.
MongoDB
- Hardening: the official security checklist: enable authorization (SCRAM or X.509; LDAP and Kerberos are Enterprise-only), unique least-privilege users per person and application, TLS on every connection,
bindIplimited to trusted interfaces withclusterIpSourceAllowlistfor intra-cluster traffic, encryption at rest (WiredTiger native encryption is Enterprise-only; filesystem encryption is the Community path), disable server-side JavaScript (--noscripting) unless$where/$functionare used, run as a dedicated OS user, and excludedbPathand log paths from antivirus scanning (scanning can corrupt files). Auditing is Enterprise-only. - Maintenance: size the oplog so secondaries survive maintenance windows without resyncing;
compactreclaims collection space; index builds roll across replica-set members. Upgrades step through one major version at a time, raisingfeatureCompatibilityVersiononly after the new version proves stable — FCV is the rollback safety net. Track CVE alerts and end-of-life dates as part of the routine. - Migrations: schemaless does not mean migration-free — it means the database will not run the migration for you. The documented pattern is schema versioning: a version field on each document, application code that reads every version and writes the newest, and lazy or batched rewriting of old documents. Pair it with
$jsonSchemavalidation set tovalidationLevel: "moderate", which enforces the new shape on new and already-valid documents while letting legacy documents through until they are rewritten; validators change in place withcollMod, no downtime. - Resource trade-offs: WiredTiger cache pressure is paid in operation latency, not just cache misses. When the cache passes its eviction trigger (95% full by default, or 20% dirty), application threads are drafted into doing eviction work before their own operations proceed — client latency spikes are the symptom — and at 100% full, operations stall outright. Working-set size and write-burst shape are therefore latency decisions, not just capacity ones; watch the eviction statistics in
serverStatusbefore the stalls arrive.
SQLite Capable of: full-ACID relational storage inside an application process; zero-administration deployment (one file, no server); partial and expression indexes; FTS5 text search; R*Tree spatial indexes; operation on data larger than RAM; verified crash durability. Not capable of / weak at: concurrent writers (one write transaction at a time); parallel query execution; network access; replication and clustering without third-party layers; compression; large analytical scans (row store, nested-loop joins only).
DuckDB Capable of: analytical SQL on local and object-storage files (Parquet, CSV, JSON, Iceberg, Delta) without a server or import step; out-of-core execution beyond RAM; multi-core vectorized queries; embedding inside applications with zero-copy access to host data structures; database file encryption. Not capable of / weak at: multi-process writes; replication, clustering, or any high-availability story; OLTP-style concurrent small transactions; live incremental search indexes; background jobs of any kind (TTL, refresh, scheduling).
PostgreSQL Capable of: transactional workloads with enforced integrity; complex normalized queries; mixed relational and JSON schemas; extension-based specialization (GIS, time series, vectors, search, columnar); physical and logical replication; property-graph queries. Not capable of / weak at: native sharding; columnar analytics at large scale (row storage, row-at-a-time execution); wire compression; automatic failover without external tooling; storage tiering.
MariaDB
Capable of: general OLTP with drop-in MySQL compatibility; per-table storage engine selection
(row, LSM, columnar, federated, archive); the most flexible replication here (GTID,
multi-source, semi-sync) plus Galera multi-master clustering in the base server;
system-versioned (temporal) tables; in-core vector search with HNSW indexes; online and
instant DDL; stored procedures with a built-in event scheduler; Oracle PL/SQL compatibility
mode; MySQL-style optimizer hints; an opt-in InnoDB-integrated binary log for write-heavy
workloads.
Not capable of / weak at: intra-query parallelism (one thread per query); binary JSON (text
type with functions); partial and expression indexes; FULL OUTER JOIN and LATERAL;
materialized views; analytics outside the ColumnStore engine; built-in failover (external
tooling required); pub-sub (binlog CDC only).
ClickHouse Capable of: high-throughput ingest with aggregation maintained at ingest time; large-scale scan-and-aggregate queries across shards and replicas; heavy compression with per-column codecs; TTL-driven retention and tiering to object storage; log, time-series, and event workloads; text filtering at scale. Not capable of / weak at: unique constraints and upserts; multi-statement transactions; point lookups and row-level updates; many-way normalized joins; stored procedures; strict single-node durability by default.
MongoDB
Capable of: flexible-schema document storage; built-in replication with automatic failover;
native sharding with live resharding; change streams for CDC; time-series collections;
client-side field-level encryption that remains queryable; text and vector search via the
mongot sidecar.
Not capable of / weak at: referential integrity; multi-core execution of a single query;
columnar analytics; expression indexes; storage-engine extensibility; joins as a primary
access pattern.
Sources: SQLite "Limits In SQLite", DuckDB operations manual ("Limits"), PostgreSQL Appendix K ("PostgreSQL Limits"), MariaDB documentation (identifier, InnoDB, and server limits), ClickHouse knowledge base and Cloud usage limits, MongoDB "MongoDB Limits and Thresholds". Values marked recommended are operational guidance, not enforced by the engine (ClickHouse Cloud does enforce some of them per service tier). Values marked compile can be raised only by recompiling.
| Limit | SQLite | DuckDB | PostgreSQL | MariaDB | ClickHouse | MongoDB |
|---|---|---|---|---|---|---|
| Max database size | 281 TB | No practical limit (15 TB+ files in production use) | Unlimited (per-table limits apply) | No limit (filesystem-bound) | No limit (storage-bound) | No documented limit |
| Max table size | Equal to database size | Bounded by file size | 32 TB (8 KB pages; larger via partitioning) | 64 TB per InnoDB tablespace; more via partitioning | No limit | No limit (sharding distributes) |
| Max rows per table | 2⁶³−1 rowids; database size binds first | No fixed limit | No fixed limit (ceiling: ~4.29 B pages) | No fixed limit | No fixed limit (trillions in production) | No limit |
| Max row / document size | 1 GB default; 2 GB−1 compile | No fixed row limit | 1.6 TB theoretical; oversized values TOAST out of the 8 KB page | 65,535 bytes excluding BLOB/TEXT (InnoDB in-page ~half of 16 KB page; long values overflow to external pages) | No fixed limit | 16 MB (hard) |
| Max single value (field/BLOB) | 1 GB default; 2 GB−1 compile | 4 GB (BLOB and VARCHAR) | 1 GB per field | 4 GB (LONGBLOB/LONGTEXT); transfer capped by max_allowed_packet (1 GB) |
No declared limit (memory-bound) | Bounded by 16 MB document (GridFS chunks beyond) |
| Max columns per table | 2,000 default; 32,767 compile | No hard limit; very wide tables degrade | 1,600 (hard; fewer with wide types) | 4,096 (hard); InnoDB effective limit 1,017 | No hard limit; ~1,000 recommended (Cloud enforces ~1,000) | Not applicable — fields bounded by 16 MB document, 100-level nesting |
| Max indexes per table/collection | No fixed limit | No documented limit (each ART index is RAM-resident) | Unlimited | 64 secondary indexes (InnoDB) | No fixed limit (skip indexes, projections) | 64 (hard) |
| Max columns per index | Up to the column limit | Not documented | 32 | 32; key length ≤ 3,072 bytes (InnoDB) | No fixed limit on the ORDER BY tuple |
32 fields per compound index |
| Max identifier length | No fixed limit | No fixed limit | 63 bytes | 64 characters | No fixed limit | Database name 64 chars; namespace 255 bytes |
| Max databases / schemas | One schema per file; 10 attached files default, 125 compile | One catalog per file; multiple files via ATTACH |
~4.29 B databases per cluster | No fixed limit (filesystem directories) | ~1,000 databases recommended per service | No documented limit |
| Max tables | No hard limit; entire schema is parsed on open | No hard limit | ~1.43 B relations per database | No fixed limit | ~5,000 tables per service recommended | No hard limit; ≲10,000 collections per replica set advised |
| Max partitions per table | Not applicable | Not applicable (Hive partitions are directories) | No hard limit; planner cost grows — thousands are practical | 8,192 (including subpartitions) | 1,000–10,000 recommended; 100 per insert block (default setting) | Not applicable (chunk distribution is automatic) |
| Max tables in one join | 64 (hard) | No fixed limit | No fixed limit; beyond 12 tables the planner switches from exhaustive search to a genetic heuristic (geqo_threshold) |
61 (hard) | No fixed limit | 1,000 aggregation pipeline stages |
| Max query text size | 1 GB default | No fixed limit | No fixed limit | max_allowed_packet: 16 MB default, 1 GB max |
256 KiB default (max_query_size, configurable) |
16 MB (a command is a BSON document) |
| Max bind parameters per statement | 32,766 | No documented limit | 65,535 (wire protocol) | 65,535 (wire protocol) | Not applicable | Not applicable |
| Max concurrent connections | Not applicable (in-process) | Not applicable (in-process) | max_connections setting (default 100; one process each) |
max_connections setting (default 151; one thread each, or pooled) |
max_connections setting (default 4,096) |
65,536 incoming by default |
Other notable per-engine limits:
- SQLite — 1,000 maximum expression tree depth; 127 arguments per function; 500 terms in a
compound SELECT;
LIKEpattern length 50,000 bytes; all limits adjustable downward at runtime (sqlite3_limit) and many upward only at compile time. - DuckDB — execution operates on 2,048-value vectors and ~122,880-row row groups; these shape performance rather than impose caps.
- PostgreSQL — 1,664 columns per result set; 100 function arguments (default); 32 partition keys; identifiers longer than 63 bytes are silently truncated, not rejected.
- MariaDB — unique keys on partitioned tables must include the partitioning columns;
foreign keys are unsupported on partitioned tables;
VECTORcolumns hold up to 16,383 dimensions; prefix indexes cap at the 3,072-byte InnoDB key limit. - ClickHouse — ~50,000 partitions per service recommended across all tables;
max_partitions_per_insert_blockdefaults to 100 (an insert touching more partitions fails unless the setting is raised); part counts approaching ~100,000 per table trigger insert throttling while merges catch up. - MongoDB — 100 levels of document nesting; 100 MB of RAM per aggregation stage before spill rules apply; shard-key values at most 512 bytes for ranged keys.
Features in this section exist in only one of the six engines, or carry the same name elsewhere but behave differently enough that treating them as equivalent leads to design mistakes.
The database as an application file format. One file, byte-identical semantics across platforms and architectures, readable by any SQLite build since 2004, with a published pledge of format support through 2050. This is why it serves as the storage layer of browsers, phones, and desktop applications — a role none of the other five is built for.
Session extension changesets. Changes to a database can be captured as compact changeset or patchset blobs, transported, replayed, inverted, and merged with conflict handlers — an offline-synchronization primitive unique among these engines, and the foundation cr-sqlite builds its CRDT merge on.
Flexible typing with opt-out. Type affinity permits any value type in almost any column —
a documented design decision, not an accident — with STRICT tables restoring rigid typing
per table where wanted. Every other engine here enforces column types unconditionally.
Runtime-adjustable limits. sqlite3_limit() lowers ceilings (SQL length, expression
depth, parameter count) per connection at runtime — a hardening tool for applications that
execute untrusted SQL. The others configure limits globally, if at all.
Inspectable execution bytecode. EXPLAIN prints the actual virtual-machine opcodes a
statement compiles to, and the CLI's .expert mode proposes indexes for a given query. Other
engines show plan trees; none exposes its execution program.
Public domain. No license at all — not permissive licensing, but dedication to the public domain. Unique among the six; the others' licenses, however liberal, still carry terms.
Replacement scans over host-language objects. Inside a Python or R process,
SELECT * FROM my_dataframe queries a pandas/Polars/Arrow object by variable name, zero-copy
where the format allows. SQLite is also embedded but cannot scan host objects; the
client-server engines require data to be shipped to them.
Scanners for other databases. The sqlite, postgres, and mysql extensions attach
those systems as queryable catalogs, letting one SQL statement join a Parquet file, a
PostgreSQL table, and a SQLite file. PostgreSQL's foreign data wrappers and MariaDB's CONNECT
engine are the nearest equivalents but run server-side; here the federation point is the
client process itself.
The full engine compiled to WebAssembly. DuckDB-Wasm executes analytical SQL in a browser tab against remote Parquet over HTTP range requests. SQLite also runs as WASM, but no other analytical engine here does.
Zero-configuration out-of-core execution. Joins, aggregations, sorts, and window functions
spill under one memory limit with no per-query settings. ClickHouse spills aggregation and
sorts by default but still fails queries that exceed max_memory_usage;
PostgreSQL and MariaDB spill but need per-session buffer tuning.
Extensibility that reaches the planner and index layers. Third-party code can add index access methods, planner hooks, custom scan providers, and background workers — which is why PostGIS, TimescaleDB, and Citus can exist as installable packages. MariaDB's plugin API is the closest competitor but stops at the storage engine boundary; the planner and executor are closed.
SQL/PGQ property graph queries. Standard ISO graph-pattern syntax (GRAPH_TABLE, MATCH)
over relational tables declared as graphs. No other engine here has any standard graph query
language — MariaDB's OQGRAPH is an engine trick, not query syntax.
Exclusion constraints with range types. EXCLUDE USING gist (room WITH =, period WITH &&)
declaratively prevents overlapping intervals — enforced uniqueness generalized beyond
equality. Combined with temporal primary keys (WITHOUT OVERLAPS) and FOR PORTION OF
updates, this covers scheduling and validity-interval constraints no other engine here can
express declaratively.
Row-level security policies. Per-row predicates attached to tables and evaluated for every query by role. MongoDB approximates this only through cloud services; the embedded engines have no user model at all.
Transactional, deferrable constraint checking. Constraint checks can be deferred to commit, letting mutually referencing rows be inserted within one transaction — absent in all five others (InnoDB foreign keys check immediately, always).
Per-table pluggable storage engines. ENGINE=InnoDB|MyRocks|ColumnStore|Spider|S3|...
swaps the entire storage and concurrency architecture per table — an LSM tree for a write-hot
log table, columnar for a reporting table, an object-storage archive for cold data, all in one
schema and one SQL dialect. No other engine in this comparison has anything like it;
PostgreSQL's table access methods are the API without the ecosystem.
System-versioned (temporal) tables. WITH SYSTEM VERSIONING makes the server retain every
row version automatically, queryable with SELECT ... FOR SYSTEM_TIME AS OF ...; combined
with application-time periods it supports bitemporal schemas in standard SQL:2011 syntax.
PostgreSQL's temporal keys constrain validity intervals but do not keep history for you;
everyone else offers nothing declarative.
Galera in the base server. Certification-based, virtually synchronous multi-master clustering ships in the standard distribution — every node writable, conflicts aborted at commit. PostgreSQL has no core multi-master at all; MongoDB's replica sets are single-primary; ClickHouse's multi-master is eventual-merge, not conflict-checked.
Oracle compatibility mode. sql_mode=ORACLE switches the dialect: PL/SQL procedure
syntax, packages, %TYPE, SYS_REFCURSOR, (+) outer joins, associative arrays,
TO_DATE/TO_NUMBER/TRUNC, and a basic XMLTYPE — a migration path off Oracle that no
other open-source engine here attempts.
In-core vector search in a transactional row store. ClickHouse also indexes vectors in
core, but only MariaDB does it inside a general-purpose OLTP engine — VECTOR columns
participate in transactions, replication, and backups like any other column, with no
extension, sidecar, or separate sync pipeline.
A built-in event scheduler. CREATE EVENT runs SQL on a schedule inside the server —
retention jobs, summary refreshes, and maintenance without cron, pg_cron, or an application
timer.
Merge-time semantics as schema. The MergeTree engine family encodes what happens when
parts merge: ReplacingMergeTree keeps the newest version of a row, SummingMergeTree adds
numeric columns, CollapsingMergeTree cancels row pairs, AggregatingMergeTree combines
partial aggregate states. Behavior that other systems implement in application code or
triggers is declared in CREATE TABLE and executed during background merges.
Partial aggregate states as stored values. -State combinators produce serialized
intermediate aggregates — for example, a half-finished quantile sketch — that are stored in
columns, merged incrementally, and finalized with -Merge at query time. This is the
mechanism behind incremental materialized views and multi-level rollups; no other engine here
can store and compose aggregate state.
Chainable per-column compression codecs. Specialized transforms (Delta, DoubleDelta, Gorilla, T64) composed with general compressors, chosen per column. The other five offer at most one algorithm choice per column, table, or collection.
TTL with actions. Expiry can delete, move data to another disk or volume (including object storage), recompress with a heavier codec, or aggregate rows into a rollup — declared per table or per column.
SAMPLE clause and approximate aggregate functions. Deterministic sampling integrated
with the primary key — SAMPLE 0.1 reads a reproducible tenth of the data — plus sketch-based
uniq, quantileTDigest, and topK functions designed for billions of rows. Others have
TABLESAMPLE (PostgreSQL, DuckDB) but not sampling wired into the physical sort order.
Wire compatibility endpoints. The server answers MySQL and PostgreSQL wire protocols directly, so clients for those systems can connect without a proxy.
Queryable Encryption. Fields are encrypted client-side with randomized ciphertext, yet the server can evaluate equality, range, and prefix/suffix/substring predicates against them — without ever holding decryption keys. No other engine here offers searchable client-side encryption; the others encrypt at rest or in transit only.
Cluster-wide resumable change streams. A cursor over all data changes with resume tokens that survive client restarts and failovers, filterable with pipeline syntax, including document pre- and post-images. PostgreSQL's logical decoding and MariaDB's binlog provide the raw material but require slot/offset management and an external consumer; the others have nothing comparable server-side.
Live resharding. Changing a collection's shard key in place while the application keeps running. Every other system on this list treats redistribution of already-written data as a manual migration.
Driver-integrated topology behavior. Retryable writes, causal-consistency sessions, and automatic primary discovery are part of the wire protocol and every official driver, not an external proxy layer.
Same name, different behavior: MongoDB's TTL deletes documents (a sweep roughly every 60 seconds); ClickHouse's TTL can also move data to another volume, recompress it, or roll it up into aggregates at merge time. They are not interchangeable retention designs.