{"id":8216,"date":"2026-09-02T01:01:04","date_gmt":"2026-09-01T23:01:04","guid":{"rendered":"https:\/\/launix.de\/launix\/?p=8216"},"modified":"2026-09-02T01:01:05","modified_gmt":"2026-09-01T23:01:05","slug":"memcp-beta-release-0-9-one-database-for-transactions-search-and-analytics","status":"publish","type":"post","link":"https:\/\/launix.de\/launix\/en\/memcp-beta-release-0-9-one-database-for-transactions-search-and-analytics\/","title":{"rendered":"MemCP Beta Release 0.9: One Database for Transactions, Search, and Analytics"},"content":{"rendered":"

Today we are releasing MemCP 0.9 as a public beta.<\/p>\n\n\n\n

MemCP is a persistent main-memory database designed to run existing SQL applications without giving up the advantages of a compact, column-oriented engine. Applications can connect through the MySQL protocol, send MySQL- or PostgreSQL-style SQL over HTTP, use the built-in REST router, or query RDF data with SPARQL. Data remains durable on disk while frequently used columns, indexes, and intermediate results stay close to the CPU.<\/p>\n\n\n\n

The 0.9 release marks a change in what MemCP is ready to do. The first public release demonstrated the storage model and broad SQL support. The engineering work summarized in our August update<\/a> showed that the same architecture could handle a demanding real application. MemCP 0.9 brings those pieces together as a database people can install, connect to existing software, observe, benchmark, and begin validating for real deployments.<\/p>\n\n\n\n

This is still a beta, not a claim that every SQL edge case or operational environment is finished. It is, however, the point at which MemCP has become much more than a fast column scan: it is a complete database system with transactions, automatic physical optimization, crash recovery, memory management, migration tools, protocol compatibility, and an administration dashboard.<\/p>\n\n\n\n

What MemCP 0.9 Can Do<\/h2>\n\n\n\n

Run Existing MySQL Applications<\/h3>\n\n\n\n

MemCP exposes a MySQL-compatible TCP server and Unix socket. Existing connectors, PDO applications, command-line clients, prepared statements, authentication flows, and administration tools can talk to it without a MemCP-specific driver.<\/p>\n\n\n\n

The supported SQL surface now covers the workload expected from a modern business application:<\/p>\n\n\n\n

    \n
  • SELECT<\/code>,\u00a0INSERT<\/code>,\u00a0UPDATE<\/code>,\u00a0DELETE<\/code>, upserts, and\u00a0INSERT ... SELECT<\/code><\/li>\n\n\n\n
  • inner, outer, cross, and multi-table joins<\/li>\n\n\n\n
  • scalar,\u00a0IN<\/code>,\u00a0EXISTS<\/code>,\u00a0NOT EXISTS<\/code>, and correlated subqueries<\/li>\n\n\n\n
  • GROUP BY<\/code>,\u00a0HAVING<\/code>, distinct results, common aggregates, and\u00a0GROUP_CONCAT<\/code><\/li>\n\n\n\n
  • UNION<\/code>\u00a0and\u00a0UNION ALL<\/code>, including ordered and correlated forms<\/li>\n\n\n\n
  • derived tables, logical views, complex expressions, and JSON functions<\/li>\n\n\n\n
  • window functions such as\u00a0ROW_NUMBER<\/code>,\u00a0RANK<\/code>,\u00a0LEAD<\/code>, and\u00a0LAG<\/code><\/li>\n\n\n\n
  • triggers with\u00a0OLD<\/code>\u00a0and\u00a0NEW<\/code>\u00a0values, including query-backed trigger bodies<\/li>\n\n\n\n
  • prepared binary values, session variables, collations, date\/time functions, and timezone-aware conversion<\/li>\n\n\n\n
  • schema inspection through the common\u00a0SHOW<\/code>\u00a0and\u00a0INFORMATION_SCHEMA<\/code>\u00a0interfaces<\/li>\n<\/ul>\n\n\n\n

    Compatibility work in 0.9 was driven by unmodified application queries, database checkers, plugins, notifications, generated read models, and dump\/restore tools. This matters more than accepting isolated syntax: the parser, planner, transaction layer, and result protocol now cooperate on the shapes real software actually produces.<\/p>\n\n\n\n

    MemCP also provides HTTP endpoints for MySQL-dialect SQL, PostgreSQL-dialect SQL, Scheme, and SPARQL. Applications can register custom HTTP routes in Scheme and execute directly against the storage engine without another application-server-to-database network hop.<\/p>\n\n\n\n

    Keep Data Durable While Serving It from Memory<\/h3>\n\n\n\n

    MemCP is not an ephemeral cache. Its default safe<\/code> engine persists data with a write-ahead log and recovery path designed for power-loss safety. The logged<\/code>, sloppy<\/code>, memory<\/code>, and cache<\/code> engines offer explicit alternatives for workloads with different durability and reconstruction requirements.<\/p>\n\n\n\n

    Tables are divided into independently managed shards. Each shard stores columns separately and selects a compact representation according to the data: bit-packed integers, decimal encodings, dictionaries, prefixes, sparse values, sequences, constants, or compressed string storage. Queries read only the columns they need. Cold persistent data can leave RAM without being deleted from disk and is loaded again when required.<\/p>\n\n\n\n

    Release 0.9 substantially strengthens this operational foundation:<\/p>\n\n\n\n

      \n
    • shard rebuilds and repartitioning publish new generations atomically<\/li>\n\n\n\n
    • concurrent inserts, updates, and deletes survive generation switches<\/li>\n\n\n\n
    • WAL replay restores committed changes after a crash<\/li>\n\n\n\n
    • persistent blob references and large WAL records survive restart<\/li>\n\n\n\n
    • orphaned blob and shard files can be reclaimed without connecting cleanup to normal cache eviction<\/li>\n\n\n\n
    • memory accounting separates owned structures and gives eviction an accurate budget<\/li>\n\n\n\n
    • cold shards, indexes, computed columns, and reconstructible caches can be released under pressure<\/li>\n\n\n\n
    • background maintenance avoids deleting persistent data and keeps the previous valid generation if publication fails<\/li>\n<\/ul>\n\n\n\n

      The result is a hot working-set database rather than a requirement that every byte remain permanently resident. MemCP can use available memory aggressively while retaining an explicit durability contract.<\/p>\n\n\n\n

      Optimize Queries Without Manual Index Administration<\/h3>\n\n\n\n

      MemCP builds and maintains physical access paths from the workload. Users do not have to predict every useful compound index in advance or run a manual ANALYZE<\/code> cycle before the optimizer can improve.<\/p>\n\n\n\n

      The query compiler separates SQL meaning from physical execution:<\/p>\n\n\n\n

        \n
      1. The parser builds the SQL syntax tree.<\/li>\n\n\n\n
      2. Correlated subqueries are decorrelated into a combined logical graph.<\/li>\n\n\n\n
      3. Logical optimization and join reordering consider the complete query, including formerly dependent joins.<\/li>\n\n\n\n
      4. Physical lowering chooses scans, ordered scans, probes, cached groups, computed columns, and row-ID sets using a calibrated cost model.<\/li>\n\n\n\n
      5. The resulting Scheme program is optimized and cached as an executable query-plan callable.<\/li>\n<\/ol>\n\n\n\n

        This separation is one of the central achievements of 0.9. A permission check expressed as nested CASE<\/code>, COALESCE<\/code>, EXISTS<\/code>, and scalar subqueries is no longer forced down one hard-coded execution path. The compiler can choose independently at different nodes of the plan: probe a unique key, scan an index range, build a reusable group, project a candidate set through a join, or evaluate a residual predicate in batches.<\/p>\n\n\n\n

        Physical decisions use table cardinality, selectivity, distinct counts, ordering, limits, projected work, and runtime guards. When better statistics become available or data growth crosses a decision boundary, a cached plan can be recompiled instead of remaining tied to an obsolete assumption. A calibration suite measures the real storage operators and generates the constants used by the cost model; performance-sensitive pull requests are also compared against master across a checked-in workload pool.<\/p>\n\n\n\n

        Cached plans no longer expire merely because the regular background rebuild published a new statistics generation. The common path compares an immutable generation token; after a generation change, a precomputed coarse fingerprint checks whether planner-relevant dimensions such as cardinality, distinctness, null fraction, width, and confidence are still in the same cost class. Similar data keeps the compiled plan. Material growth or a crossed cost boundary still falls through to recompilation. This removes the periodic cold-plan pause without freezing a plan across meaningful data changes.<\/p>\n\n\n\n

        Search, Filter, and Paginate Large Tables<\/h3>\n\n\n\n

        MemCP 0.9 combines several mechanisms for interactive search over large business datasets:<\/p>\n\n\n\n

        Adaptive automatic indexes<\/strong> recognize equality, range, ordering, and indexable matcher boundaries. Non-prefix LIKE '%term%'<\/code> predicates can use a shard-local bigram candidate index. The original SQL predicate remains the correctness authority, so an approximate candidate set can skip most rows without changing the result.<\/p>\n\n\n\n

        RecSets<\/strong> represent query-local sets of physical row IDs. A RecSet chooses ranges for clustered matches, sorted ID lists for sparse matches, or bitmaps for dense scattered matches. Sets can be intersected, united, negated, counted, scanned, and projected through join keys without copying complete rows.<\/p>\n\n\n\n

        Ordered scans with braking<\/strong> exploit an index that already supplies the requested order. For ORDER BY ... LIMIT<\/code>, the engine can stop when later candidates cannot enter the result. When a selective RecSet and a different ordering index compete, the scan operator can choose between walking the ordered index with membership checks and sorting the sparse candidates by their inverse index positions.<\/p>\n\n\n\n

        Batch acceptance<\/strong> helps when a cheap condition can produce candidates but an expensive permission predicate must still be checked. The engine pulls a bounded candidate batch, evaluates the remaining condition, and expands the batch only if it has not filled the requested page.<\/p>\n\n\n\n

        These are general operators rather than a special full-text subsystem. The same machinery can combine text search, tenant or location restrictions, access-control subqueries, an ordering requirement, and a page limit. Low- and high-selectivity searches can therefore choose different execution paths from the same SQL shape.<\/p>\n\n\n\n

        Reuse Computation Safely<\/h3>\n\n\n\n

        Main-memory execution is most valuable when repeated work is recognized across rows and queries.<\/p>\n\n\n\n

        MemCP can create computed columns for recurring expressions and scalar lookups. A lookup such as fetching a value through a stable foreign key can become a lazily computed column on the referencing table. Canonical naming allows compatible queries to reuse it, while dependency tracking invalidates the affected row when a bound key or source value changes.<\/p>\n\n\n\n

        For aggregation, group caches preserve reusable partial results and update them as base data changes. Permission-aware counts can sometimes be evaluated once per low-cardinality group rather than once per fact row. Query-local scalar and membership carriers avoid rebuilding the same subquery result for every projection or filter occurrence.<\/p>\n\n\n\n

        Reuse is always scoped by its dependencies. Session-bound permissions do not leak between users, transaction-visible values are not published as global facts, and residual correlated expressions remain inside the row scope that supplies their keys.<\/p>\n\n\n\n

        Serve Small OLTP Requests and Large Scans in One Engine<\/h3>\n\n\n\n

        Earlier MemCP versions were most obviously strong when a query touched enough data for compression, columnar access, and parallelism to dominate. Version 0.9 also reduces the fixed cost of tiny requests.<\/p>\n\n\n\n

        On a realistic WordPress-style fixture with 8,214 posts and 75,000 metadata rows, serial requests through the MySQL protocol measured:<\/p>\n\n\n\n

        Query<\/th>MemCP 0.9<\/th>MariaDB 10.11<\/th>Result<\/th><\/tr><\/thead>
        Primary-key lookup<\/td>0.06748 ms<\/td>0.06864 ms<\/td>MemCP 1.7% faster<\/td><\/tr>
        ORDER BY post_title LIMIT 5<\/code><\/td>0.15829 ms<\/td>2.44767 ms<\/td>MemCP 15.46x faster<\/td><\/tr>
        Filtered posts\/metadata join with ORDER BY ... LIMIT 20<\/code><\/td>0.24977 ms<\/td>0.26430 ms<\/td>MemCP 5.8% faster<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

        The comparison used the same data distribution, 500 warm-up requests, and seven measured blocks of 5,000 serial requests per query. These numbers are deliberately end-to-end protocol measurements rather than isolated storage calls.<\/p>\n\n\n\n

        The first real-world beta integration provides the other side of the picture: approximately 855,000 central records, generated SQL, nested permission rules, counts, pagination, direct-column search, and file-content search. Interactions that once took minutes are now generally interactive after warm-up. On representative search workloads, MemCP has measured up to 30x faster than PostgreSQL. This is not a promise that every query will be 30x faster; it demonstrates that MemCP’s automatic indexes, candidate sets, and compiled permission paths can produce a decisive gain on an application that was not designed around MemCP.<\/p>\n\n\n\n

        A final pre-release run on the current 0.9 code measured the database execution time of application-generated queries immediately after a process restart and again after their indexes, group caches, and query plans were warm:<\/p>\n\n\n\n

        Real application query<\/th>PostgreSQL<\/th>First run<\/th>Warm runs<\/th><\/tr><\/thead>
        Full 855,000-row document view, ordered page of 72 rows<\/td>10 s<\/td>1.47 s<\/td>87\u2013119 ms<\/td><\/tr>
        Exact count accompanying the full document view<\/td>10 s<\/td>1.77 s<\/td>381\u2013402 ms<\/td><\/tr>
        Permission- and tenant-filtered document page<\/td>3 s<\/td>872 ms<\/td>145\u2013253 ms<\/td><\/tr>
        Direct-column inventory search page<\/td>4 s<\/td>2.61 s<\/td>1.26\u20131.30 s<\/td><\/tr>
        Exact count accompanying the inventory search<\/td>10 s+<\/td>2.27 s<\/td>1.61\u20131.67 s<\/td><\/tr>
        Time-filtered notification\/reminder page<\/td>200 ms<\/td>551 ms<\/td>2.62\u20133.07 ms<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

        These are TracePrint database times rather than browser timings, and list and count requests run independently in the application. The distinction matters: a count can continue in parallel without delaying delivery of the first result page. It also makes cold behavior visible instead of reporting only a best cached number.<\/p>\n\n\n\n

        The latest execution-path fusion improved more than the headline scans. Across the same interaction trace, the median repeated permission probe fell by 8.6%, session lookup by 15.2%, and user\/permission setup by 5.9%. A generated page performs many of these small operations, so reducing their fixed overhead is directly visible in navigation latency.<\/p>\n\n\n\n

        The important release result is not one winning number. It is that point lookups, ordered pages, joins, broad aggregations, and complex searches can now compete inside one physical planner instead of requiring separate transactional and analytical databases.<\/p>\n\n\n\n

        Import, Package, Observe, and Operate<\/h3>\n\n\n\n

        MemCP 0.9 is distributed as more than a source checkout:<\/p>\n\n\n\n