MemCP Engineering Update (Mar 9 – Aug 26, 2026)

The last months moved MemCP much closer to its original goal: not just being a fast database in benchmarks, but running real applications reliably and making their slowest queries feel immediate.

The biggest news is practical: the first real-world project is now preparing to use MemCP. On representative application queries, MemCP already delivers up to 10x the performance of PostgreSQL and MariaDB. The work merged today is the final stretch behind much of that gain.

Highlights First (Non-Technical)

1) The First Real-World MemCP Project Is Close

MemCP has reached the point where an existing application with real data, complex permission rules, pagination, and concurrent users can move from compatibility testing toward actual use.

This is different from a synthetic benchmark. The queries come from an application that was built for established SQL databases, not rewritten to make MemCP look good.

On several representative queries, MemCP is already up to 10x faster than PostgreSQL and MariaDB.

Gain: faster user interactions without redesigning the application around the database.

2) Up to 10x Faster on Real Application Queries

The hardest screens in a real application combine access rules, counts, joins, filters, and pagination. These interactions used to take several minutes in MemCP while the database repeated the same work many times.

On a production-copy dataset with approximately 855,000 main records, the exact saved application interaction now completes in:

• 3.86 seconds on the first run
• 1.69 seconds on repeated runs

Before this work, the same interaction could exceed 300 seconds, while a related count had exceeded 800 seconds. The returned result remained identical. Across the representative queries measured for the upcoming project, MemCP is now up to 10x faster than PostgreSQL and MariaDB.

Gain: complex application screens become responsive without rewriting the application.

3) Safer Maintenance Under Load

Fast queries are not useful if background maintenance can lose data. MemCP can now reorganize its storage while users continue to insert, update, and delete records. If that reorganization fails, the previous valid state remains available.

Gain: performance maintenance no longer has to trade speed for data safety.

4) Deployment and Migration Are Easier

MemCP now has installable Linux packages, a container image, configuration files, and automated release builds.

PostgreSQL database exports can be imported directly, while compatibility with existing MySQL applications and administration tools has expanded substantially.

Gain: less custom work between evaluating MemCP and running an existing application on it.

What You Feel Immediately

Faster application screens: permission checks, counts, filters, and repeated pagination avoid redundant work.

Less wasted work: MemCP narrows large tables earlier and avoids recalculating the same intermediate results.

More predictable latency: the database adapts its execution strategy to the query and the amount of data involved.

Safer operations: maintenance, restarts, query cancellation, and failure paths have much stronger regression coverage.

Better visibility: the dashboard shows current activity, errors, users, and query state, and lets administrators stop stuck work directly.

Simpler migration: existing MySQL clients keep working, while PostgreSQL dumps can be loaded without manually rewriting the archive first.

Technical Deep Dive

How Complex Generated Queries Got Faster

Real applications often hide their hardest work inside correlated subqueries, EXISTS, CASE, COALESCE, joins, counts, access-control predicates, and pagination.

The newest planner work recognizes these pieces as one logical problem instead of optimizing each fragment in isolation. It recursively follows compound boolean expressions across AND, OR, NOT, comparisons, nullable expressions, and nested query outputs. The physical strategy is then chosen at the point in the join tree where MemCP knows how often the result will actually be needed.

Depending on the expected work, the planner can choose a direct index probe, an ordinary scan, a cached key table, or a compact row-ID structure called a RecSet. Query-local memoization lets filters and projections reuse the same bounded result instead of rebuilding it for every row.

This also works for ordered scans and streamed UNION branches, while user- or session-dependent access rules remain isolated to the current query. On the production-copy workload, small follow-up lookups were observed around 0.17–0.27 ms and ACL existence checks around 0.29–0.53 ms.

RecSets: Skipping Rows Before Reading Them

A RecSet, short for “record set”, is a temporary set of physical row IDs from one table, created for the current query. It does not copy complete rows or column values. It only records which rows match a condition, such as an access-control check, an EXISTS subquery, or one branch of a larger boolean expression.

Once MemCP knows this set, later operators can scan only those rows. Multiple conditions can be combined with fast union and intersection operations before the engine loads the actual columns. A RecSet can also be projected through join keys, allowing a small matching set in one table to restrict a much larger table early.

The data structure adapts independently for every shard:

• Ranges store clustered matches as (start, length) pairs. Thousands or millions of neighboring row IDs may need only one pair.
• Sorted ID lists store sparse, scattered matches without allocating space for rows that did not match.
• Bitmaps use one bit per possible row when matches are dense and scattered, making membership checks cheap.

MemCP changes representation as the result develops. Sorted lists and ranges can be merged with linear sweeps, while ranges also enable bulk, sequential column reads. Union and intersection run in parallel across shards.

The memory difference can be dramatic. In a focused benchmark, a contiguous match of 200,000 rows within a 500,000-row shard required 62,500 bytes as a bitmap but only 8 bytes as one range — a 7,812x reduction. Specialized union and intersection paths are up to 9.6x faster than converting both inputs into full bitmaps.

The large end-to-end application gain comes from the planner as a whole: it must discover the useful RecSet, build it at the right point, reuse it, and avoid more expensive scans and joins. In the production-copy example above, all of these decisions together reduced an interaction from more than 300 seconds to 3.86 seconds on the first run and 1.69 seconds on repeated runs. RecSets are one of the central execution tools behind that gain, but not its only cause.

Gain: less memory, fewer column reads, and far less repeated work on large filtered tables.

COUNT Queries Can Work on Groups Instead of Rows

A common application pattern counts a large fact table after applying filters through small dimensions such as permissions, tenants, locations, or categories.

MemCP can now group the driver by those low-cardinality keys first, evaluate the expensive filter stages once per group, and then sum the partial counts. It does this only when statistics predict a clear benefit.

In the reproducible 10,000-row engineering benchmark merged today, this changed the query from:

• 788.40 ms to 150.40 ms cold — 5.24x faster
• 745.04 ms to 8.57 ms warm — 86.97x faster

The production comparison is more conservative: across the relevant real-world queries, MemCP reaches up to 10x the performance of PostgreSQL and MariaDB.

Gain: dashboard counters and permission-aware totals can become interactive instead of background work.

Big List of Changes

Query Planner and Real-World Performance

• Rebuilt the planner around a strict parser → logical optimization → physical lowering pipeline.
• Added Neumann-style decorrelation for scalar, EXISTS, IN, NOT EXISTS, aggregate, ordered, and nested subqueries.
• Added holistic top-down dependent-join unnesting so wide generated queries do not repeatedly rebuild equivalent subquery trees.
• Added cost-based join ordering from row-count and distinct-value statistics.
• Added adaptive join reordering with exact search for small join graphs and bounded strategies for larger graphs.
• Preserved logical join trees through physical lowering so planner decisions remain executable rather than becoming flat source lists.
• Added physical choices between direct probes, scans, cached key tables, and RecSets.
• Added query-local memoization for repeated scalar recipes and plan reuse across pagination and safe literal variants.
• Hoisted query-invariant presence probes and deferred nullable projection work until after LIMIT where possible.
• Eliminated dead projections, unused joins, duplicate stage preparation, and redundant generated access checks.
• Added boolean folding for tautologies and contradictions such as A OR NOT A, removing the corresponding physical probe work entirely.
• Added low-cardinality aggregate pushdown for permission-aware and dimension-filtered COUNT(*) queries.
• Fixed a RecSet scalar-probe application bug that could silently treat every row as a match; the corrected path now has dedicated semantic regression coverage.

RecSets (Compact Row-ID Sets), Indexes, and Scan Efficiency

• Introduced adaptive RecSet execution for query-local membership and correlated probes, using ranges, sorted row-ID lists, or bitmaps according to the shape of each shard’s result.
• Added compact range encoding for clustered record sets: a 200,000-row contiguous match in a 500,000-row universe dropped from 62,500 bytes to 8 bytes in the focused representation benchmark.
• Added specialized union and intersection algorithms for every RecSet representation pairing, with isolated combine-path gains of up to 9.6x.
• Parallelized RecSet union and intersection across shards while keeping small single-task cases inline.
• Added bulk column reads for record ranges and multiple record IDs.
• Added eager arena decoding for compressed strings on bulk reads.
• Added interval skip-list indexes for non-prefix LIKE '%term%' searches; the focused 30,000-row benchmark improved from 5,300 µs to 62 µs, an 85x speedup.
• Added adaptive LIKE match sets, exact-point-prefix preference, and more complete index boundary extraction.
• Preserved ordering properties in automatically created indexes and pre-materialized sort keys before adaptive index construction.

Storage, Durability, and Concurrency

• Added atomic publication for rebuilt, repartitioned, and overflow shard generations.
• Preserved concurrent mutations across generation switches instead of dropping writes at the publication boundary.
• Added WAL records for hidden inserts, undeletes, and transaction visibility recovery.
• Hardened rebuild and repartition draining against lock inversions, hangs, partial publication, and readers blocking generation retirement.
• Added cancellable table-lock waits and safer lock release on trigger and projection failures.
• Added crash-recovery, restart, rebuild-kill, repartition, rollback, and storage-format regression suites.
• Preserved large file-backed WAL rows across restart.
• Added LZ4 string compression and zero-overhead storage for constant-value columns.
• Added garbage collection for orphaned blob and shard files without connecting background cleanup to persistent-data deletion paths.
• Improved memory accounting, cache eviction, blob reference counting, and cold-shard rebuild safety.

SQL and Migration Compatibility

• Added full SQL window-function support with partition-local materialization and incremental invalidation.
• Added UNION in addition to UNION ALL, including ordered and correlated variants.
• Added logical SQL views and broader derived-table support.
• Expanded prepared-statement support, including TEXT, BLOB, and LIKE parameters.
• Added PostgreSQL pg_dump archive import with COPY, schema retargeting, sequence handling, and nested virtual archive paths.
• Added safe batched PostgreSQL COPY FROM STDIN ingestion.
• Expanded PostgreSQL role, password, schema, identity, and common dump-DDL compatibility.
• Expanded MySQL trigger, user, grant, process-list, assignment, coercion, and client compatibility.
• Added timezone-aware SQL functions including DST-aware conversion.
• Added more complete RDF/Turtle and SPARQL parsing, filters, escaping, UUID, datatype, and blank-node behavior.
• Added parser and planner coverage for TPC-H query shapes without special-casing those queries.

Runtime and JIT

• Integrated an experimental runtime-aware JIT for amd64 builds using the patched Go toolchain, while preserving interpreter fallback on normal builds and other architectures.
• Added native support for generated map/reduce callbacks, nested lambdas, Go slices, variadic calls, match control flow, pointer-free builtin results, and stack-backed list operations.
• Published precise stack maps and safe Go callback interoperability so panic, recover, and garbage collection remain valid across native frames.
• Expanded list fusion, ownership-aware reducers, and allocation-free runtime paths used by generated query plans.
• Stabilized optimizer ownership handling so temporary list storage cannot escape its valid lifetime.

Dashboard, Packaging, and Operations

• Added active-process views with query and connection kill controls.
• The process list now shows the actual SQL body for HTTP queries instead of only the endpoint path.
• Added persistent error-query logging and a dashboard Errors view.
• Improved metrics, sparklines, navigation, credentials handling, database views, and query-plan display.
• Added Debian and RPM packaging, systemd lifecycle handling, configuration files, upgrade-safe scripts, and automated release artifacts.
• Added safer service shutdown behavior to protect data during package upgrades.
• Added a CACHE engine for reconstructible, memory-managed data whose schema survives eviction.

Testing and Engineering Discipline

• Added planner invariants that explicitly separate logical SQL meaning from physical scans, indexes, RecSets, ORC columns, and temporary tables.
• Added plan-shape and time-limit guards to catch compile-time explosions and performance regressions in CI.
• Added hardware-independent performance checks, fail-fast test ordering, isolated crash tests, and better startup diagnostics.
• Expanded coverage across SQL semantics, planner shapes, concurrency, storage formats, durability, migration, triggers, prepared statements, and JIT behavior.
• Added compile-phase timing and plan-shape metrics to make optimizer regressions observable rather than anecdotal.

Why These Highlights Matter

The first real-world project changes the standard of success. Correct results, stable latency, operational visibility, and safe recovery now matter as much as isolated scan speed.

Up to 10x over PostgreSQL and MariaDB on selected real queries shows where MemCP’s columnar storage and query compilation can create user-visible value without requiring application-specific SQL rewrites.

COUNT pushdown and generic correlated probes attack the workload that dominates many business applications: permissions, filters, badges, dashboards, and pagination over a large central table.

Adaptive RecSets let MemCP represent query-local membership in the form that fits the data: ranges for clustered hits, sorted IDs for sparse hits, and bitmaps for dense scattered hits.

Atomic shard generation publication closes a different but equally important gap: performance work can continue without weakening the durability contract.

Next Focus Areas

• Continue extending generic correlated-probe selection to more SQL shapes and physical consumers.
• Verify the first real-world deployment under sustained concurrent use and compare end-to-end latency, not only individual queries.
• Continue tuning the physical cost model with observed production cardinalities and cache behavior.
• Extend JIT coverage only where pointer lifetime, stack maps, cancellation, and garbage-collector safety are proven.
• Keep reducing cold-start and first-query latency while preserving the very fast warm path.
• Turn the new process, compile, and plan metrics into actionable operational diagnostics.

More

Visit: https://github.com/launix-de/memcp