MemCP - User contributions [en]

Main Page

2026-08-28T10:14:06Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= MemCP Database – Fast, Compressed SQL for OLTP and OLAP =

<div style="padding:2.5rem 2rem; margin:0 0 1.5rem; border-radius:14px; background:linear-gradient(135deg,#3d6208 0%,#64910c 52%,#76b512 100%); color:#fff; text-align:center;">
<div style="font-size:2.6rem; line-height:1.1; font-weight:700; margin-bottom:.7rem;">MemCP</div>
<div style="font-size:1.45rem; line-height:1.35; font-weight:600; margin:0 auto .9rem; max-width:56rem;">A fast, compressed, MySQL-protocol-compatible columnar database for modern OLTP and OLAP workloads</div>
<div style="font-size:1.05rem; line-height:1.6; margin:0 auto 1.4rem; max-width:54rem;">MemCP is an open-source SQL database written in Go. It combines persistent in-memory operation, adaptive column compression, parallel query execution and direct application APIs so operational data can be queried and analyzed without maintaining a separate analytical copy.</div>
<div style="margin-bottom:1.5rem;">Beta Open Source MySQL Protocol + HTTP APIs</div>
<div class="plainlinks">[[Install MemCP with Docker|Get started with Docker]] [[Supported SQL|Explore SQL support]] [https://github.com/launix-de/memcp View on GitHub]</div>
</div>

[[File:Memcp-Load.png|center|frameless|1000px|alt=MemCP database dashboard and workload overview]]

<div style="padding:.85rem 1rem; margin:1rem 0 2rem; border-left:5px solid #d99b00; background:#fff7d6; color:#332600;">
'''Development status: Beta.''' MemCP is under active development. Check [[Supported SQL]], [[Current Status and Open Issues]] and the durability requirements of your workload before migrating production data.
</div>

<div style="padding:1rem 1.15rem; margin:1rem 0 2rem; border-left:5px solid #76b512; background:#f5f8f0; color:#17202a;">
'''Your MySQL queries are too slow?''' Run the same representative workload on MemCP instead of assuming another index or a larger MySQL server is the only answer. Compare results, durability and authenticated end-to-end latency; if the migration gates pass, move the performance-critical workload to MemCP. [[MySQL is too slow|Start the MemCP performance evaluation →]]
</div>

<div style="padding:1rem 1.15rem; margin:1rem 0 2rem; border:1px solid #b7c99a; border-radius:9px; background:#f5f8f0; color:#17202a;">
'''Observed performance profile:''' MemCP has achieved '''speedups of 10× and more over MariaDB/PostgreSQL''' in measured OLAP and search-oriented workflows, where RecSets and compressed column scans avoid wide row materialization. In one filtered-list workflow over roughly one million documents, the same query took around '''30 seconds on PostgreSQL''' and '''1.6 seconds on MemCP'''. Isolated OLTP paths currently take about '''1.3–2.0× as long''', but in complete WordPress- and wiki-style page builds this has made '''no significant difference to overall page-loading time''' in the measured application workflows. These are workload observations, not universal guarantees; [[Performance Measurement|reproduce them on your data]].
</div>

== What is MemCP? ==

'''MemCP Database''' is a persistent, compressed, column-oriented SQL database for mixed OLTP and OLAP workloads. It speaks the MySQL client protocol, also exposes SQL over HTTP, and executes supported queries through a functional compiler and a parallel column-storage engine.

The name on this site refers to the database project. MemCP Database is '''not''' a Model Context Protocol (MCP) memory server and is unrelated to the C/C++ <code>memcpy()</code> memory-copy function. When writing about or linking to the project, the unambiguous name is '''MemCP Database'''.

== Problems MemCP solves ==

<div style="display:grid; grid-template-columns:repeat(auto-fit,minmax(240px,1fr)); gap:1rem; margin:1rem 0 2rem;">
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.1rem; font-weight:700; margin-bottom:.4rem;">Slow filtered lists on millions of rows</div>
Complex <code>WHERE</code>, joins, membership tests, <code>ORDER BY</code> and <code>LIMIT</code> can make a row store inspect or materialize far more data than the page returns. MemCP uses RecSets, late materialization, adaptive indexes and ordered braking to keep the working domain compact. [[MySQL is too slow|Evaluate the query]] · [[RecSets|How RecSets work]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.1rem; font-weight:700; margin-bottom:.4rem;">Slow GROUP BY, COUNT and dashboards</div>
Column scans read only referenced compressed values. Parallel shard-local aggregation, group caches and computed structures target repeated analytical queries over fresh operational data. [[Columnar Storage]] · [[Temporary Computed Columns]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.1rem; font-weight:700; margin-bottom:.4rem;">Slow database writes caused by fsync</div>
Durability is selectable per table. <code>safe</code> protects commits through power loss; <code>logged</code> has measured about 10× its write throughput when process-crash recovery is sufficient. [[Persistency and Performance Guarantees|Choose a write strategy]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.1rem; font-weight:700; margin-bottom:.4rem;">Database writes wear out an SD card</div>
For reconstructible edge data, <code>sloppy</code> avoids a continuous WAL and normally publishes one compressed generation every 15 minutes, reducing constant flash writes while making the loss window explicit. [[Hardware Requirements]] · [[Persistency and Performance Guarantees]]
</div>
</div>

== Choose your path ==

<div style="display:grid; grid-template-columns:repeat(auto-fit,minmax(220px,1fr)); gap:1rem; margin:1rem 0 2rem;">
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.15rem; font-weight:700; margin-bottom:.4rem;">Evaluate MemCP</div>
Understand the workload model, current status, hardware needs and differences from MySQL.

[[What is OLTP and OLAP|OLTP and OLAP]] · [[Comparison: MemCP vs. MySQL|Compare with MySQL]] · [[Hardware Requirements|Hardware requirements]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.15rem; font-weight:700; margin-bottom:.4rem;">Build an application</div>
Use the MySQL protocol, SQL over HTTP, RDF or application-specific endpoints inside MemCP.

[[Advanced SQL Tutorial|SQL tutorial]] · [[SQL over REST|SQL over HTTP]] · [[Database Tools compatibility with MemCP|Client tooling]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.15rem; font-weight:700; margin-bottom:.4rem;">Operate MemCP</div>
Plan deployment, migration, persistence, storage backends, settings and performance measurement.

[[Deployment]] · [[Persistency and Performance Guarantees|Durability]] · [[Migration from MySQL and PostgreSQL|Migration]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.15rem; font-weight:700; margin-bottom:.4rem;">Understand and contribute</div>
Explore the storage engine, optimizer, embedded Scheme runtime and project internals.

[[Query Planner and Physical Lowering|Query planner]] · [[RecSets]] · [[Columnar Storage]] · [[Contributing]]
</div>
</div>

== Quickstart ==

=== Run with Docker ===

<pre>
docker run --name memcp \
-e ROOT_PASSWORD='choose-a-password' \
-p 4321:4321 -p 3307:3307 \
-v memcp-data:/data \
carli2/memcp:latest
</pre>

Then open <code>http://localhost:4321</code> and connect a MySQL application to <code>127.0.0.1</code> on port '''3307'''. The volume mounted at <code>/data</code> retains persistent table data across container replacement. See [[Install MemCP with Docker|the complete Docker guide]] before deploying it as a service.

=== Build from source ===

<pre>
git clone https://github.com/launix-de/memcp
cd memcp
go mod download
make
./memcp --api-port=4321 --mysql-port=3307 lib/main.scm
</pre>

Connect with MySQL tooling:

<pre>
mysql -h 127.0.0.1 -u root -p -P 3307
# Enter the password selected for this data directory.
</pre>

The development default for a fresh data directory is <code>admin</code>. Change it before exposing MemCP to another machine. MemCP can also be supervised with PM2:

<pre>
pm2 start ./memcp --name memcp -- \
--no-repl -data ./data --api-port=4321 --mysql-port=3307 lib/main.scm
</pre>

Background deployments must use <code>--no-repl</code> so that closing standard input does not terminate the interactive console and stop the server. See [[Compile MemCP from Source|Build from Source]], [[Deployment]] and [[MemCP Console]].

== Key features ==

<div style="display:grid; grid-template-columns:repeat(auto-fit,minmax(260px,1fr)); gap:.8rem; margin:1rem 0;">
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''High performance''' Parallel, batch-oriented query execution is designed for multicore CPUs, compact working sets and fast persistent storage, serving both OLTP and OLAP workloads.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Columnar storage''' Data is stored by column for improved compression, a smaller memory footprint and fast analytical access.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Persistent in-memory operation''' MemCP is designed to keep active data in memory while offering configurable per-table durability and persistence backends.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Fast configurable writes''' Choose durability per table: <code>safe</code> protects committed data through power loss, while measured <code>logged</code> paths have reached about 10× its write throughput; <code>sloppy</code> batches reconstructible data into the normal 15-minute rebuild cycle.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Built-in APIs''' SQL over HTTP and in-database services can remove an extra middleware hop for suitable applications.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Adaptive compression''' Bit-packing, dictionary encoding and sequence compression reduce the bytes read for suitable data. Historical imports have reached reductions around 80% versus their MySQL/MariaDB representation; measure your own schema.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Simple deployment''' Start with Docker, PM2 or the native binary. The compact application has historically had an installation footprint of approximately 10 MB.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Extensible frontends''' The Go storage engine and embedded Scheme environment support SQL, RDF, REST and custom application interfaces.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #9ad32d; background:#f5f8f0; color:#17202a;">'''Cost-based query execution''' Logical decorrelation and join optimization are separated from cost-based physical selection of scans, indexes, RecSets, reusable caches and execution pipelines. [[Query Planner and Physical Lowering|How MemCP plans and lowers queries →]] · [[RecSets|How compact record sets process large domains →]]</div>
</div>

== Why MemCP? ==

Traditional relational databases were designed around spinning disks and comparatively small numbers of CPU cores. MemCP rethinks storage and query execution for modern multicore systems, large caches, fast storage and mixed workloads.

Typical use cases include:

* real-time dashboards and analytics;
* data-heavy SaaS platforms;
* embedded systems with limited resources;
* high-throughput OLTP/OLAP hybrids.

Whether MemCP is a good fit depends on the required SQL compatibility, durability, query mix, data size and operational environment. Review [[Supported SQL]], [[Persistency and Performance Guarantees]] and [[Performance Measurement]] rather than treating benchmark figures as universal guarantees.

== MemCP vs. MySQL ==

{| class="wikitable" style="width:100%;"
! Feature
! MySQL
! MemCP
|-
| Storage model
| Primarily row-based
| Column-based and compressed
|-
| Performance focus
| General-purpose relational workloads
| Parallel, in-memory-oriented execution for mixed operational and analytical workloads
|-
| In-memory capability
| Available through selected engines and caching
| Central design goal and default operating model
|-
| REST API integration
| Normally external
| Built in
|-
| Installation footprint
| Common server installations are approximately 150 MB or larger
| The native application has historically been approximately 10 MB
|-
| Open source
| ✅
| ✅
|}

MemCP provides MySQL protocol compatibility but does not claim to implement every MySQL feature. See [[Comparison: MemCP vs. MySQL]] for a detailed comparison and [[Database Tools compatibility with MemCP]] for client compatibility.

== Architecture overview ==

* '''Tables, schemas and columns''': Familiar SQL structures use a compressed columnar physical layout. See [[Databases, Tables and Columns]] and [[Columnar Storage]].
* '''Transaction model''': Delta and main storage support mixed OLTP and OLAP semantics. See [[Transactions and Isolation]] and [[Shards, RecordIDs, Main Storage, Delta Storage]].
* '''Query planning''': Logical optimization is separated from physical execution decisions. See [[Query Planner and Physical Lowering]].
* '''Persistence''': Per-table durability can use filesystem, S3 or Ceph/RADOS storage. See [[Persistency and Performance Guarantees]] and [[Storage Backends]].
* '''Operations''': The dashboard, metrics, process inspection and memory controls support day-to-day operation. See [[Dashboard and Operations]] and [[Memory Management and Eviction]].
* '''Scale-out roadmap''': MemCP is single-node today. A leaderless CRUSH/RADOS and MOESI-inspired cluster design is planned, but must not be treated as an available production feature. See [[Cluster Monitor]].
* '''Frontends''': MemCP provides multiple query and application interfaces:
** SQL through the MySQL wire protocol and SQL over HTTP;
** an RDF/graph query frontend;
** custom APIs through in-database web applications.

== Documentation ==

The following navigation remains on the start page so users and search engines can reach every major documentation area directly.

=== Introduction and evaluation ===

* [[What is OLTP and OLAP]]
* [[History of the MemCP project]]
* [[Hardware Requirements]]
* [[Persistency and Performance Guarantees]]
* [[Current Status and Open Issues]]
* [[MySQL is too slow|SQL performance problems MemCP solves]]
* [[Comparison: MemCP vs. MySQL]]

=== Getting started ===

* [[Install MemCP with Docker|Install with Docker]]
* [[With Singularity|Install with Singularity/Apptainer]]
* [[Compile MemCP from Source|Build from Source]]
* [[Contributing]]
* [[Introduction to Scheme]]
* [[Full SCM API documentation]]

=== Administration ===

* [[Deployment]]
* [[Migration from MySQL and PostgreSQL]]
* [[Settings]]
* [[Security and Authentication]]
* [[Dashboard and Operations]]
* [[Memory Management and Eviction]]
* [[Process Hibernation]]
* [[Performance Measurement]]
* [[MemCP Console]]

=== Frontends ===

==== SQL frontend ====

* [[Supported SQL]]
* [[Advanced SQL Tutorial]]
* [[JSON|JSON and SQL/JSON]]
* [[Triggers]]
* [[SQL over REST]]
* [[Database Tools compatibility with MemCP|Supported Tooling]]
* [[Query Planner and Physical Lowering|How SQL operators are implemented]]
* [[Add custom SQL operators to MemCP]]

==== RDF frontend ====

* [[Introduction to RDF]]
* [[Advanced Graph Querying]]
* [https://github.com/launix-de/rdfop RDF browser and templating example]

==== Custom frontends ====

* [[In-Database WebApps and REST Services]]
* [[MemCP for Microservices]]
* [[Websockets in MemCP]]

=== Persistence backends and storage ===

* [[File System]]
* [[Storage Backends|S3-compatible and Ceph/RADOS storage]]
* [[Cluster Monitor]]

=== Internals ===

==== How MemCP works ====

* [[Databases, Tables and Columns]]
* [[Shards, RecordIDs, Main Storage, Delta Storage]]
* [[Columnar Storage]]
* [[Transactions and Isolation]]
* [[Query Planner and Physical Lowering]]
* [[Full SCM API documentation]]

==== Scheme documentation ====

* [[SCM Builtins]]
* [[Arithmetic / Logic]]
* [[Strings]]
* [[Streams]]
* [[Lists]]
* [[Associative Lists / Dictionaries]]
* [[Date]]
* [[Vectors]]
* [[Parsers]]
* [[Sync]]
* [[IO]]
* [[Storage]]

==== Optimizations ====

* [[Query Planner and Physical Lowering]]
* [[In-Memory Compression, Columnar Compression Techniques]]
* [[Temporary Computed Columns]]
* [[Data Auto Sharding and Auto Indexing]]
* [[Parallel Computing]]

== Further reading ==

* [https://github.com/launix-de/memcp MemCP on GitHub]
* [https://www.vldb.org/pvldb/vol13/p2649-boncz.pdf VLDB Research Paper]
* [https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf LNI Proceedings Paper]
* [https://www.dcs.bbk.ac.uk/~dell/teaching/cc/paper/sigmod10/p135-malewicz.pdf Large Graph Algorithms]

Additional blog posts on design decisions, compression techniques and performance optimization are available on the [https://launix.de/launix/ Launix blog].

== Community ==

MemCP is an open-source project maintained by developers for developers. Contributions are welcome in the form of bug reports, feature requests and pull requests.

See [[Contributing]] and the [https://github.com/launix-de/memcp GitHub repository].

With Singularity

2026-08-28T10:14:06Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Run MemCP with Singularity/Apptainer =

Build the repository-provided image target:

<pre>
git clone https://github.com/launix-de/memcp
cd memcp
make memcp.sif
apptainer run memcp.sif
</pre>

Bind a persistent host directory to the container data path and expose or forward the HTTP/MySQL ports according to the local Apptainer installation. Set a strong initial root password for a fresh data directory and keep the image, application modules, and data directory versions together during upgrades.

Singularity installations can use the corresponding <code>singularity</code> command. See [[Deployment]], [[Persistency and Performance Guarantees]], and [[Security and Authentication]].

Websockets in MemCP

2026-08-28T10:14:05Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Websockets in MemCP =

An embedded HTTP handler can upgrade a request through the response object's WebSocket facility and receive messages through a callback. The <code>apps/minigame.scm</code> example in the repository demonstrates the current API and routing pattern.

WebSockets keep one bidirectional connection open for live dashboards, notifications, collaborative state, games, or change feeds. They are a delivery channel, not a durable message queue: reconnect, replay, acknowledgement, ordering, and missed-message semantics belong to the application.

== Server-side upgrade ==

<pre>
(set send ((res "websocket")
(lambda (message) (print "message: " message))
(lambda () (print "connection closed"))))
(send 1 "Hello from MemCP")
</pre>

The numeric first argument is the WebSocket frame opcode used by the current response API. Keep the sending function only for the connection lifetime.

A browser client can connect to the routed endpoint with the standard API:

<pre>
const socket = new WebSocket("wss://example.test/minigame/ws");
socket.addEventListener("open", () => socket.send(JSON.stringify({type: "hello"})));
socket.addEventListener("message", event => console.log(JSON.parse(event.data)));
</pre>

== Complete routed example ==

The repository's <code>apps/minigame.scm</code> shows the complete routing pattern: preserve the previous handler, serve an HTML/JavaScript client below one prefix, and upgrade only the WebSocket path.

<pre>
(define http_handler (begin
(set old_handler http_handler)
(lambda (req res) (begin
(match (req "path")
"/minigame/ws" (begin
(set send ((res "websocket")
(lambda (message) (begin
(print "message: " message)
(send 1 (concat "echo: " message))))))
(send 1 "Hello from MemCP"))
(regex "^/minigame/(.*)$" path asset) (begin
((res "header") "Content-Type" "text/plain")
((res "status") 200)
((res "print") "WebSocket client assets belong here"))
(old_handler req res))))))
</pre>

When this module is imported by <code>lib/main.scm</code>, the existing server uses the wrapped handler. A standalone application can call <code>serve</code> explicitly. Keep the receive callback and its connection-local <code>send</code> closure together; never store that closure after close.

Prefer JSON messages with an explicit type and version. Validate the decoded shape before reading fields, cap message size, and define how the client resubscribes or resumes after reconnect.

== Database-backed publish/subscribe ==

Prepare fixed queries during module startup, create a session per connection, and bind authenticated identity and subscription state there. A receive callback can update state atomically; query results can be emitted through <code>send</code>. On close, release subscriptions and other references so they do not keep sessions or sending functions alive.

For fan-out, use bounded per-client queues and a policy for slow consumers (drop/coalesce messages or disconnect). A database table can persist events for replay, but a <code>memory</code> or <code>cache</code> table cannot provide durable delivery after restart.

The earlier hardlife.io example used this structure at larger scale: prepare position-update, listener-membership and dirty-list queries once; authenticate a player handle with a message authentication code; keep the connection's player/location state in a session; mark affected listeners dirty after a position change; send only changed avatars; and remove listener state on disconnect. The complete historical listing coupled credentials, global <code>sloppy</code> defaults and application-specific tables, so reproducing it as a copy-and-paste server would be unsafe. The pipeline remains a useful design example.

Hosting the handler beside the database removes an application/database socket hop and repeated parsing for prepared shapes. That is a performance opportunity, not proof that every embedded WebSocket service is faster than a separate Node.js or Go service; benchmark the authenticated end-to-end path and account for the shared failure domain.

== Production considerations ==

Authenticate the upgrade, validate browser Origin, limit message and queue sizes, apply backpressure, and remove subscriptions when the connection closes. A slow client must not create an unbounded in-memory backlog. Long database operations should observe cancellation. Use TLS at a trusted proxy or transport layer and do not expose default credentials.

Storage ENGINE choice is independent of WebSocket delivery: <code>sloppy</code> can lose recent writes, and <code>memory</code>/<code>cache</code> are not durable. See [[In-Database WebApps and REST Services]] and [[Persistency and Performance Guarantees]].

Temporary Computed Columns

2026-08-28T10:14:05Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Computed Columns, Group Caches, and ORC =

<blockquote>'''Verified against commit <code>c42e19eba</code> on 28 August 2026.''' These are optimizer-managed physical structures. Applications normally write SQL; MemCP analyzes the plan, creates suitable caches, and maintains their validity automatically.</blockquote>

MemCP reuses expensive expressions and relational stages without copying complete result rows into a generic temporary table. It can attach one computed value to an existing base row, keep a narrow table of grouping keys and aggregates, or maintain an order-dependent column. The surrounding base columns remain where they already are.

This matters for queries that join or filter a million rows, sort by one derived value, and finally return only 100 rows. A conventional wide temporary result may copy every projected column before <code>ORDER BY ... LIMIT</code>. MemCP instead tries to materialize only the value, key domain, [[RecSets|record set]], or ordering dependency needed by the next physical stage.

== The three reusable structures ==

{| class="wikitable"
! Structure !! Logical meaning !! Stored shape !! Typical use
|-
| Ordinary computed column || One derived value per live row of an existing table || A lazy <code>StorageComputeProxy</code> backed by compressed main values, sparse repaired values, and a validity mask || Repeated scalar expressions, lookup results, JSON paths, correlated scalar probes, computed ordering
|-
| Group cache/keytable || One row per distinct grouping/domain key plus computed aggregate columns || A narrow internal <code>ENGINE=cache</code> table with a unique key || Reused <code>GROUP BY</code>, correlated aggregates, prepared membership/group domains
|-
| ORC (ordered-reduce column) || One value whose computation depends on partition/order predecessors || An ordered computed proxy with per-row validity and dependency-range repair || Running totals, rank/row-number-like work, reusable window/order calculations
|}

The SQL plan cache is different: it stores compiled plan formulas, not computed rows. A query may reuse a compiled formula without having a warm group cache, or reuse a computed cache after a newly compiled query recognizes the same canonical definition.

== From SQL expression to canonical cache ==

MemCP does not name reusable structures after a query's disposable aliases. During logical planning and physical lowering it analyzes the expression and constructs a canonical identity from the data the structure represents:

* table aliases are rewritten to stable source roles, while two roles in a self-join remain distinct;
* column references include physical schema, relation, and column identity;
* grouping keys, source graph, filters, aggregate recipe, partition/order definition, and relevant bounds become part of the identity;
* SELECT aliases, formatting, and unrelated output columns do not create a second cache;
* stable structural hashes produce names such as internal <code>.grp:...</code>, <code>agg_...</code>, and <code>__orc_...</code> objects.

Consequently, two queries can share a computed aggregate even when their SQL aliases or final projections differ. A second aggregate over the same group domain can add another narrow computed column to the existing keytable instead of cloning the entire intermediate relation. Conversely, a different filter, join role, ordering recipe, or semantic dependency receives a different identity rather than incorrectly sharing values.

Preparation filters are deliberately absent from a computed column's logical identity. They only say which values are likely to be consumed soon and should be warmed eagerly. Every other live row still has a well-defined value that can be computed later.

== Automatic analysis of the generated Scheme code ==

The planner emits a <code>createcolumn</code> call containing the computation lambda and its direct input columns. This is not the end of dependency discovery. The storage engine walks the resulting Scheme AST/procedure to determine what the computation reads and how source changes can reach the cached value.

The analyzer:

# removes source-location wrappers and accepts both unresolved symbols and already-resolved builtin procedures;
# descends through lambdas and nested physical expressions;
# recognizes <code>scan</code>, <code>scan_order</code>, <code>scalar_scan</code>, and <code>scalar_scan_order</code> calls;
# also examines a scan's table expression, because a dynamic physical source can itself contain dependency-producing scans;
# resolves literal table handles or canonical table expressions to schema and relation;
# reads filter and map column lists from both quoted and constructed-list forms;
# records every source column used by the filter or mapper;
# inspects conjunctions of equality predicates and maps a source scan parameter back to the outer computed-column input it equals;
# follows source columns that are themselves computed and expands them transitively to their physical input, map, partition, and sort columns;
# extracts session-variable dependencies and creates session-specific cache variants where a value must not leak between users or sessions.

For example, a generated lookup conceptually shaped like this:

<pre>
(lambda (customer_id)
(scan tx (table "shop" "payments")
'("customer_id")
(lambda (payment_customer)
(equal? payment_customer (outer customer_id)))
'("amount")
(lambda (amount) amount)
+
0))
</pre>

tells the storage analyzer that <code>payments.customer_id</code> is the reverse lookup key for the target row's <code>customer_id</code>, and that <code>payments.amount</code> is relevant to the value. An UPDATE of an unrelated <code>payments.note</code> column therefore need not invalidate the cache.

The analysis is deliberately conservative. If a scan or reverse key relationship cannot be proven from the generated code, MemCP does not guess. It generates complete-column invalidation so the next read recomputes correct values. More analyzable code improves maintenance precision; opaque code must remain correct.

== Automatic generation of maintenance triggers ==

When the computed-column signature is installed or changes, <code>createcolumn</code> calls the storage engine's trigger generator. It registers hidden system triggers on every discovered source table. Registration is idempotent, so preparing the same canonical cache again updates/reuses its metadata instead of accumulating duplicate triggers.

{| class="wikitable"
! Analyzed shape !! Generated reaction to source INSERT/UPDATE/DELETE
|-
| Direct row-local inputs || The corresponding row value is marked stale or repaired through the computed proxy.
|-
| Lookup with proven equality key || Scan only target cache rows whose key equals the source row's <code>OLD</code> and/or <code>NEW</code> key, then invalidate that exact subset.
|-
| Additive aggregate (<code>+</code>, neutral zero, analyzable map without nested scans) || Apply the mapped delta with <code>$increment:<column></code>; UPDATE subtracts OLD and adds NEW when the group key stays stable.
|-
| Additive UPDATE whose group key changes || Invalidate safely because old and new keytable membership may differ.
|-
| COUNT-style constant-one helper || Prefer robust invalidation, because the value also controls empty/non-empty group visibility.
|-
| ORC with known partition and sort key || Invalidate the affected partition from the OLD/NEW sort position onward.
|-
| Opaque or unsupported dependency || Invalidate the complete computed column.
|}

The generated triggers run after INSERT, UPDATE, and DELETE. UPDATE triggers compare only columns proven relevant by the analysis and skip maintenance if none changed. They use the trigger row dictionaries <code>OLD</code> and <code>NEW</code> to find both sides of a key-changing update.

Selective invalidation is itself batched. The internal <code>$invalidate:<column></code> callback collects matching record IDs during the maintenance scan; invalidation is applied after scan locks are released. Incremental changes are similarly accumulated per proxy and record ID before <code>$increment:<column></code> updates cached values. This avoids nested lock acquisition in the inner scan loop.

System maintenance triggers are hidden from ordinary <code>SHOW TRIGGERS</code>; they are implementation-owned rather than user DDL. User triggers and the computed-cache trigger graph still share the storage engine's defined timing and locking machinery.

=== Dependency chains and invalidation waves ===

A cached column can depend on another computed cache. The storage engine registers <code>AfterInvalidate</code> edges in addition to source DML triggers. Invalidating the lower cache synchronously propagates to its dependants.

One invalidation wave carries a query/goroutine-local visited set keyed by target table and column. This makes repeated edges idempotent and prevents a malformed cycle from recursing forever without introducing a global invalidation lock. Selective invalidation is currently propagated conservatively at column level when a downstream reverse mapping cannot be preserved safely.

=== Lifecycle cleanup ===

The generator also owns cleanup:

* dropping an internal target cache removes the triggers it installed on source tables;
* dropping a source table can drop dependent dot-prefixed helper tables so a later table with the same name cannot inherit stale data;
* group-key maintenance reacts to source INSERT, UPDATE, and DELETE;
* dropping the base table or a grouping column removes its dependent keytable;
* trigger target leases pin a cache while maintenance is using it, preventing concurrent eviction from invalidating a live pointer.

These lifecycle triggers must only remove the helper objects they explicitly own. They are not general garbage collection and must never delete unrelated user data.

== Race-free initial construction ==

Installing triggers after filling a cache would leave a lost-update window: a source write between the snapshot and trigger registration would never invalidate the new value. <code>initialize_cache_table</code> closes that race.

It resolves and sorts all source tables into a deterministic lock order, blocks source mutations, registers maintenance, fills the canonical cache from a consistent source view, optionally finalizes/rebuilds it, and only then releases the locks. Concurrent attempts to initialize the same generation wait for the shared result rather than performing duplicate fills.

Internal keytables use <code>ENGINE=cache</code> and can store this initializer as a closed <code>oninit</code> procedure. After restart or eviction, the first idempotent creation guard rebuilds the empty generation before exposing it to consumers. See [[Persistency and Performance Guarantees]] for why cache data is reconstructible rather than durable.

== Lazy values, eager warming, and repair ==

An ordinary computed column is a complete logical column even when few physical values exist:

* <code>Compress</code> can eagerly calculate all rows when the plan expects broad consumption;
* <code>CompressFiltered</code> warms only rows selected by a preparation filter;
* reading a missing or invalid ordinary value calculates that row from its current inputs and stores the result;
* valid compressed main values use the normal bulk column-reader fast path;
* repeated <code>createcolumn</code> calls for the same signature preserve valid cached values and repair only missing/dirty state;
* a materially different semantic formula receives a different canonical identity; reissuing the same signature preserves its valid cache. ORC metadata changes explicitly invalidate the affected ordered proxies.

For broad selective invalidations, each proxy compares a short measured repair sample with its last full recomputation cost. If point repair is predicted to cost more, it marks the whole column dirty and lets the next consumer perform one coherent rebuild. This adapts to the actual computor rather than relying on one global row-count threshold.

The historical documentation said computations become parallel above 60,000 rows. That threshold is no longer the public execution contract. Current preparation fans work out over active shards, while planner cost, shard count, input size, and the maintenance/recompute path determine useful parallelism. Large independent shards can compute concurrently; a tiny table or a single ordered dependency cannot gain speed merely from crossing a fixed row number.

== GROUP BY through narrow computed relations ==

A reusable group cache conceptually follows four steps:

# create a canonical internal table containing a UNIQUE key over all grouping columns;
# populate the distinct grouping keys;
# add one computed column for each aggregate or derived ordering value;
# scan the narrow group relation and join/project base values only where the final result needs them.

<pre>
CREATE TABLE t (a TEXT, b INT);
INSERT INTO t VALUES ('foo', 1), ('bar', 2), ('foo', 3);

SELECT a, SUM(b) AS total
FROM t
GROUP BY a;
-- bar | 2
-- foo | 4
</pre>

The keytable cleanup trigger inserts a missing key idempotently, removes a key after the last source row leaves the group, and handles OLD and NEW keys on UPDATE. It runs at priority 90, before aggregate invalidation at priority 100, so key membership is current when a value is repaired.

GROUP BY syntax does not force a cache. The physical lowerer compares a one-pass fused aggregation with keytable construction, expected probes/reuse, maintenance cost, memory, and available alternative carriers. Query-local or UNION-shaped groups may instead be filled in one batch, while reusable base-table groups can use storage-managed computed columns.

Different surrounding WHERE ranges can reuse the same row-local computed expression because the preparation predicate does not define the column. A group cache, however, includes the grouping domain and semantic source filter in its canonical identity: two genuinely different group populations must not share one aggregate value.

== Order-dependent computed columns (ORC) ==

An ORC is not pointwise. A running total at row 500 depends on preceding rows in its partition, so changing row 100 may invalidate a suffix rather than just row 100.

The ORC definition records sort columns/directions, the number of leading partition columns, map columns/function, reducer, and neutral value. Generated triggers:

* use equality bounds for the partition prefix;
* invalidate a range beginning at the changed OLD or NEW sort key;
* leave unrelated partitions valid;
* fall back to complete invalidation when a cross-table dependency cannot be mapped selectively;
* compare cumulative selective-invalidation cost with the last suffix/full recomputation and choose complete invalidation when that is cheaper.

On the next read, the ordered proxy repairs the dependency range under the table's ORC recomputation guard. Concurrent readers do not observe the internal “invalid” sentinel used by the reducer. If one ordered scan can compute a window directly, the planner may fuse it instead of creating an ORC at all.

== Cost, eviction, and observability ==

The physical lowerer compares:

* direct scalar/index probes;
* a fused one-pass scan or aggregate;
* a [[RecSets|RecSet]] carrier;
* a canonical computed column;
* an FK-backed/group cache;
* an ORC;
* a narrow temporary relation.

Inputs include build work, expected reuse, source and target cardinality, maintenance/invalidation work, memory, ordering, LIMIT, and available indexes. <code>EXPLAIN PHYSICAL</code> shows the selected physical family for supported decisions; it is better evidence than assuming repeated SQL text always builds a cache.

Temporary computed columns and keytables are accounted by the global memory manager. Access refreshes their lease/last-used state. Because they are reconstructible, the manager can evict them under pressure; a later query recreates or lazily repairs them. Base data remains authoritative throughout.

== Correctness boundaries ==

* A preparation filter changes warming, never the logical domain or NULL value of a computed column.
* A cached value must never outlive a source change without an invalidation/maintenance edge.
* Opaque code requires conservative invalidation; absence of a recognized equality is not evidence of independence.
* Session-dependent formulas require isolated variants and must not reuse one user's value for another session.
* ORC invalidation follows ordering dependencies and cannot be replaced by unrelated point repair.
* Internal trigger generation must be complete before source mutations are unblocked.
* Cache eviction and source-table DDL must remove their owned trigger graph without touching user data.

For exact low-level signatures see [[Storage]]. Related architecture is documented in [[Query Planner and Physical Lowering]], [[Triggers]], [[Columnar Storage]], [[Memory Management and Eviction]], [[RecSets]], and [[Parallel Computing]].

Supported SQL

2026-08-28T10:14:04Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Supported SQL =

<blockquote>Verified against development commit <code>c42e19eba</code> on 27 August 2026. MemCP is Beta and does not claim complete MySQL or PostgreSQL compatibility. Unsupported shapes should fail explicitly; test a required workload before migration.
</blockquote>
This page is a capability map for application evaluation. MemCP accepts a broad MySQL-oriented dialect through its MySQL protocol and HTTP endpoint, plus PostgreSQL-oriented syntax through a separate HTTP endpoint. Both compile into the same logical planner and storage runtime, but accepted surface syntax and returned metadata can differ by frontend.

Support is defined by tested semantics, not merely by whether the parser accepts a statement. Compatibility-only clauses may intentionally be no-ops, and uncommon combinations can remain unsupported even when their individual operators are available. Use this overview to select candidates, then verify every required query, constraint, transaction, prepared statement, and client behavior against the deployed release.

== Query language ==

* <code>SELECT</code>, aliases, <code>WHERE</code>, <code>DISTINCT</code>, <code>GROUP BY</code>, <code>HAVING</code>, <code>ORDER BY</code>, <code>LIMIT</code>, and <code>OFFSET</code>;
* <code>INNER</code>, <code>LEFT</code>, <code>RIGHT</code>, and <code>CROSS JOIN</code>;
* <code>UNION</code> and <code>UNION ALL</code>, including ordered/limited set results;
* derived tables and logical SQL views;
* scalar, <code>EXISTS</code>, <code>NOT EXISTS</code>, <code>IN</code>, and <code>NOT IN</code> subqueries, including many correlated and grouped forms;
* aggregates <code>COUNT</code>, <code>SUM</code>, <code>AVG</code>, <code>MIN</code>, <code>MAX</code>, and <code>GROUP_CONCAT</code>;
* window functions including <code>ROW_NUMBER</code>, <code>RANK</code>, <code>DENSE_RANK</code>, <code>LEAD</code>, <code>LAG</code>, <code>FIRST_VALUE</code>, and <code>LAST_VALUE</code> with partition/order clauses.


== Data modification and schema ==

* single- and multi-row <code>INSERT</code>, <code>INSERT IGNORE</code>, <code>INSERT … SELECT</code>, and MySQL or PostgreSQL upsert syntax;
* <code>UPDATE</code> and <code>DELETE</code>, including qualified and supported multi-table forms;
* <code>CREATE</code>, <code>ALTER</code>, <code>RENAME</code>, <code>TRUNCATE</code>, and <code>DROP</code> for databases and tables;
* <code>CREATE VIEW</code>, indexes, primary/unique keys, foreign keys, defaults, <code>AUTO_INCREMENT</code>, computed expressions, types, collations, and per-table ENGINE;
* persistent BEFORE/AFTER triggers for INSERT, UPDATE, and DELETE.

Foreign keys are enforced for the tested RESTRICT, CASCADE, and SET NULL paths. Some compatibility DDL is intentionally accepted as a no-op; for example, a client accepting <code>ADD FULLTEXT</code> does not imply a MySQL-compatible full-text index implementation.


== Transactions, security, and metadata ==

* <code>BEGIN</code>, <code>COMMIT</code>, and <code>ROLLBACK</code>;
* users, passwords, grants, revokes, and database access policies;
* <code>SHOW DATABASES</code>, tables, columns, indexes, create-table metadata, variables, engines, processlist, and selected <code>INFORMATION_SCHEMA</code> relations;
* prepared statements over the MySQL protocol;
* <code>KILL QUERY</code> and <code>KILL CONNECTION</code>.

See [[Transactions_and_Isolation|Transactions and Isolation]] and [[Security_and_Authentication|Security and Authentication]] for the operational contract.


== Expressions ==

MemCP implements SQL NULL/three-valued logic, CASE, COALESCE/NULLIF/IFNULL, arithmetic and bitwise operators, comparisons, BETWEEN, IN lists, LIKE/ILIKE, REGEXP/RLIKE, MATCH…AGAINST compatibility, casts, string functions, mathematical functions, hashes, date/time functions, intervals, and timezone conversion.

The exact parser and function catalog changes more often than this overview. Use the generated API reference and the SQL test taxonomy for detailed coverage.


== JSON ==

MemCP stores declared <code>JSON</code>/<code>JSONB</code> values in a native tagged BSON representation. The MySQL dialect implements JSON validation, type inspection, path extraction, constructors, aggregates, mutation functions, containment/search operations, <code>JSON_TABLE</code>, and the <code>-></code>/<code>->></code> operators. The PostgreSQL dialect provides its corresponding <code>json</code>/<code>jsonb</code> operators, constructors, aggregates, path functions, and set-returning expansion forms. Deterministic path expressions can be materialized and indexed through the ordinary computed-column/index machinery. See [[JSON]] for the detailed compatibility contract and deviations.


== Planner inspection ==

<pre>EXPLAIN SELECT ...;
EXPLAIN IR SELECT ...;
EXPLAIN REORDER SELECT ...;
EXPLAIN PHYSICAL SELECT ...;
EXPLAIN COMPILE SELECT ...;</pre>
See [[Query Planner and Physical Lowering]].


== Known limitations ==

MemCP is not a byte-for-byte MySQL server implementation. Metadata details, administrative commands, uncommon syntax, advanced locking modes, XA, stored procedures, and untested SQL-standard edge cases may be absent. Consult the [https://github.com/launix-de/memcp/issues current issue tracker] and add a must-pass and must-fail test for every application-critical construct.

Storage

2026-08-28T10:14:04Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Storage =


<div class="mw-message-box mw-message-box-notice">Generated from MemCP commit <code>c42e19eba</code> on 27 August 2026. See [[Full SCM API documentation]].</div>

The '''Storage''' module is the low-level data interface used by generated SQL plans. It manages databases, tables, columns, constraints, scans, indexes, RecSets, computed data, persistence, and schema lifecycle operations.

Most application code should use SQL instead of calling these functions directly. Planner-generated calls rely on exact transaction, locking, callback, ownership, and quoting contracts. Functions that accept table handles or transaction contexts must not be replaced with direct access to shard internals.

For the physical model see [[Columnar Storage]], [[Shards, RecordIDs, Main Storage, Delta Storage]], and [[Persistency and Performance Guarantees]].

== table ==

resolves a schema+table name pair into a table handle

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''schema''' (<code>string</code>)
* '''table''' (<code>string</code>)


=== Returns ===

* '''value''' (<code>table</code>)

== scan_estimate ==

estimate output row count for a table scan

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''table''' (<code>table</code>)


=== Returns ===

* '''value''' (<code>int</code>)

== table_planner_statistics ==

return the immutable O(1) planner-statistics snapshot for a table

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''table''' (<code>table</code>)


=== Returns ===

* '''value''' (<code>any</code>)

== scan_selectivity_estimate ==

bounded estimate of visible rows matching a table filter; stops at max_rows and does not log scan telemetry

'''Allowed number of parameters:''' 5–5


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context to use for visibility; usually ((context "session") "__memcp_tx")
* '''table''' (<code>table</code>)
* '''condition_cols''' (<code>list<string></code>): columns passed to the selectivity predicate
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''condition''' (<code>func</code>): predicate sampled to estimate matching rows
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>bool</code>): true when the sampled row matches
* '''max_rows''' (<code>int</code>)


=== Returns ===

* '''value''' (<code>list</code>)


== table_empty? ==

returns true if a table currently has no rows

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''table''' (<code>table</code>)


=== Returns ===

* '''value''' (<code>bool</code>)

== scan_recset ==

builds a query-local record-set handle from one table scan, or -- when given an existing recset instead of a table -- narrows that recset to the members which also satisfy filter, re-evaluating filter only over its existing membership. The latter is the cheap way to AND a further (possibly subscan-heavy) condition onto an already-narrowed recset without re-touching rows outside it (e.g. evaluating an expensive correlated check only over the rows a cheap selective filter already narrowed a table down to). The returned value is not persisted and can be scanned like a table

'''Allowed number of parameters:''' 4–4


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context to use for visibility; usually ((context "session") "__memcp_tx")
* '''table''' (<code>any</code>): a table, or an existing recset to narrow further
* '''filterColumns''' (<code>list<string></code>): physical columns passed to filter before map/reduce; $recset_contains supplies a row-bound RecSet membership closure
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''filter''' (<code>func</code>): lambda function that decides whether a row enters the recset
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>bool</code>): true when the row belongs in the recset


=== Returns ===

* '''value''' (<code>recset</code>)

== recset_count ==

returns the number of currently stored recids in a query-local recset

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''recset''' (<code>recset</code>)


=== Returns ===

* '''value''' (<code>int</code>)

== recset_project_join ==

projects a source recset through key columns into a query-local target-table recset

'''Allowed number of parameters:''' 5–5


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context to use for visibility; usually ((context "session") "__memcp_tx")
* '''source_recset''' (<code>recset</code>)
* '''source_key_columns''' (<code>list</code>)
* '''target_table''' (<code>table</code>)
* '''target_key_columns''' (<code>list</code>)


=== Returns ===

* '''value''' (<code>recset</code>)

== recset_key_index ==

builds an immutable lookup function for key columns of the rows contained in a query-local recset

'''Allowed number of parameters:''' 3–3


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context used while reading source keys
* '''source_recset''' (<code>recset</code>)
* '''source_key_columns''' (<code>list</code>)


=== Returns ===

* '''lookup''' (<code>func</code>): tests whether the recset contains a row with the supplied composite key
** '''Parameters'''
*** '''key''' (<code>any</code>): one value for each source key column, in the same order ''(variadic)''
** '''Returns'''
*** '''present''' (<code>bool</code>): whether the composite key occurs in the recset

== recset_union ==

combines query-local recsets from the same table and removes duplicate record IDs

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''recsets''' (<code>list</code>)


=== Returns ===

* '''value''' (<code>recset</code>)

== recset_intersect ==

intersects query-local recsets from the same table

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''recsets''' (<code>list</code>)


=== Returns ===

* '''value''' (<code>recset</code>)

== recset_difference ==

returns the records from the first query-local recset which occur in none of the following same-table recsets

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''recsets''' (<code>list</code>)


=== Returns ===

* '''value''' (<code>recset</code>)

== recset_not ==

returns the complement of a query-local recset relative to the currently visible rows of its base table

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''recset''' (<code>recset</code>)


=== Returns ===

* '''value''' (<code>recset</code>)

== scan_exists ==

returns true if a table contains at least one visible row matching the given filter; uses scan boundary analysis without map/reduce setup

'''Allowed number of parameters:''' 4–4


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context to use for visibility; usually ((context "session") "__memcp_tx")
* '''table''' (<code>table|list|recset</code>)
* '''filterColumns''' (<code>list<string></code>): physical columns passed to filter before map/reduce; $recset_contains supplies a row-bound RecSet membership closure
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''filter''' (<code>func</code>): lambda function that decides whether a row exists
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>bool</code>): true when the row satisfies the existence test


=== Returns ===

* '''value''' (<code>bool</code>)

== scan ==

does an unordered parallel filter-map-reduce pass on a single table and returns the reduced result

'''Allowed number of parameters:''' 6–10


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context to use for visibility and mutations; usually ((context "session") "__memcp_tx")
* '''table''' (<code>table|list|recset</code>): table handle, query-local recset, or a list for temporary data
* '''filterColumns''' (<code>list<string></code>): physical columns passed to filter before map/reduce; $recset_contains supplies a row-bound RecSet membership closure
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''filter''' (<code>func</code>): lambda function that decides whether a dataset is passed to the map phase. Equality and range comparisons may be translated into indexed scans
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>bool</code>): true when the row proceeds to map
* '''mapColumns''' (<code>list<string></code>): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''map''' (<code>func</code>): lambda function that extracts or produces one value from the row; it may also use documented pseudo columns for mutations or result output
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>any</code>): value passed to reduce, or returned directly when no reducer is supplied
* '''reduce''' (<code>func</code>): optional aggregation function used first within shards and then to combine shard results ''(optional)''
** '''Parameters'''
*** '''accumulator''' (<code>any</code>): current aggregate, initially the neutral value
*** '''value''' (<code>any</code>): next mapped or partially reduced value
** '''Returns'''
*** '''accumulator''' (<code>any</code>): aggregate passed to the next reducer call or returned by the scan
* '''neutral''' (<code>any</code>): (optional) neutral element for the reduce phase, otherwise nil is assumed ''(optional)''
* '''reduce2''' (<code>func</code>): optional final reducer that combines the neutral value with the result produced by reduce ''(optional)''
** '''Parameters'''
*** '''accumulator''' (<code>any</code>): current aggregate, initially the neutral value
*** '''value''' (<code>any</code>): next mapped or partially reduced value
** '''Returns'''
*** '''accumulator''' (<code>any</code>): aggregate passed to the next reducer call or returned by the scan
* '''isOuter''' (<code>bool</code>): (optional) if true, in case of no hits, call map once anyway with NULL values ''(optional)''


=== Returns ===

* '''value''' (<code>any</code>)

== scan_batch ==

does an unordered parallel filter-map-reduce pass on a single table using batchdata-backed #N pseudo columns and returns the reduced result

'''Allowed number of parameters:''' 8–12


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context to use for visibility and mutations; usually ((context "session") "__memcp_tx")
* '''table''' (<code>table|list|recset</code>): table handle, query-local recset, or a list for temporary data
* '''filterColumns''' (<code>list<string></code>): columns passed to filter; #0, #1, ... address batchdata slots
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''filter''' (<code>func</code>): lambda function that decides whether a dataset is passed to the map phase
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>bool</code>): true when this table row and batch row proceed to map
* '''mapColumns''' (<code>list<string></code>): columns passed to map; #0, #1, ... address batchdata slots
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''map''' (<code>func</code>): lambda function that extracts data from the table row and batch row
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>any</code>): value passed to reduce or returned directly
* '''stride''' (<code>int</code>): number of batchdata entries per batch row
* '''batchdata''' (<code>list<any></code>): flat batch buffer accessed via #N pseudo columns
** '''slot''' (<code>any</code>): one batch value; every stride consecutive slots form a batch row
* '''reduce''' (<code>func</code>): optional lambda function that aggregates mapped values ''(optional)''
** '''Parameters'''
*** '''accumulator''' (<code>any</code>): current aggregate, initially the neutral value
*** '''value''' (<code>any</code>): next mapped or partially reduced value
** '''Returns'''
*** '''accumulator''' (<code>any</code>): aggregate passed to the next reducer call or returned by the scan
* '''neutral''' (<code>any</code>): (optional) neutral element for the reduce phase, otherwise nil is assumed ''(optional)''
* '''reduce2''' (<code>func</code>): optional final reducer that combines the neutral value with the result produced by reduce ''(optional)''
** '''Parameters'''
*** '''accumulator''' (<code>any</code>): current aggregate, initially the neutral value
*** '''value''' (<code>any</code>): next mapped or partially reduced value
** '''Returns'''
*** '''accumulator''' (<code>any</code>): aggregate passed to the next reducer call or returned by the scan
* '''isOuter''' (<code>bool</code>): (optional) if true, in case of no hits, call map once anyway with NULL values ''(optional)''


=== Returns ===

* '''value''' (<code>any</code>)

== scan_order_batch_accept ==

incrementally scans a table or existing RecSet in scan_order order and applies a RecSet batch filter before OFFSET/LIMIT and map/reduce. The first candidate RecSet contains offset+limit rows; if too few rows are accepted, subsequent disjoint batches contain twice as many candidates until the accepted limit is satisfied or the input is exhausted. batchFilter is called as (batchFilter input_recset) and must return an exact subset RecSet of the same base table and transaction. A simple batchFilter may call (scan_recset tx input_recset filterColumns realFilter); complex filters may project input_recset to another table, apply search/ACL scans and project the result back to the input table. The returned RecSet is used only as a membership mask against the already ordered candidate vector, so output order is preserved without scanning the unordered RecSet again. For non-unique ORDER BY values, include an explicit unique tie-breaker. sortcols/sortdirs may both be empty; that path greedily collects candidates without sorting. limitPartitionCols is present for scan_order signature compatibility and currently must be 0

'''Allowed number of parameters:''' 10–14


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context used consistently by the candidate scan and every batch filter operation; usually ((context "session") "__memcp_tx")
* '''table_or_recset''' (<code>table|recset</code>): base table or complete existing query-local RecSet from which ordered candidate batches are drawn
* '''batchFilter''' (<code>func</code>): function (lambda (input_recset) accepted_recset). It may naively narrow input_recset with scan_recset, or run arbitrary RecSet projections/search/ACL operations and project back. It must return a same-table, same-transaction subset of input_recset
** '''Parameters'''
*** '''input_recset''' (<code>recset</code>)
** '''Returns'''
*** '''value''' (<code>recset</code>)
* '''sortcols''' (<code>list<string|func(columns:any...) -> any></code>): same as scan_order: columns or computed sort functions. Include a unique tie-breaker for a total repeatable order; use an empty list for greedy unsorted collection
** '''sort column''' (<code>string|func</code>): a column name, or a function of row-column values that returns the sortable value
*** '''Parameters'''
**** '''columns''' (<code>any</code>): column values used to compute the sort key ''(variadic)''
*** '''Returns'''
**** '''sort key''' (<code>any</code>): value compared at this sort position
* '''sortdirs''' (<code>list<func(left:any, right:any) -> bool></code>): same as scan_order: one relation per sort column; must also be empty when sortcols is empty
** '''direction''' (<code>func</code>): strict ordering relation such as <, >, or a collate relation
*** '''Parameters'''
**** '''left''' (<code>any</code>): left sort value
**** '''right''' (<code>any</code>): right sort value
*** '''Returns'''
**** '''ordered''' (<code>bool</code>): true when left belongs before right
* '''limitPartitionCols''' (<code>number</code>): reserved for scan_order signature compatibility; currently must be 0
* '''offset''' (<code>number</code>): number of batch-filter-accepted rows to skip; it is not the number of driver candidates already examined
* '''limit''' (<code>number</code>): finite maximum number of accepted rows passed to map; the initial candidate batch size is offset+limit and doubles for every subsequent batch
* '''mapColumns''' (<code>list<string></code>): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans; $break is reserved for internal ORC convergence and must not implement SQL OFFSET/LIMIT, which belong in the native offset and limit arguments
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''map''' (<code>func</code>): same map callback contract as scan_order; accepted record IDs are passed to its shard mapper in batches
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>any</code>): value passed to reduce or returned directly
* '''reduce''' (<code>func</code>): optional serial reducer over mapped accepted rows, with the same accumulator contract as scan_order ''(optional)''
** '''Parameters'''
*** '''accumulator''' (<code>any</code>): current aggregate, initially the neutral value
*** '''value''' (<code>any</code>): next mapped or partially reduced value
** '''Returns'''
*** '''accumulator''' (<code>any</code>): aggregate passed to the next reducer call or returned by the scan
* '''neutral''' (<code>any</code>): optional neutral element for reduce; defaults to nil ''(optional)''
* '''isOuter''' (<code>bool</code>): optional scan_order-compatible outer behavior: map one NULL row when no accepted row reaches map ''(optional)''
* '''notFoundValue''' (<code>any</code>): optional result when no accepted row reaches map and isOuter is false; defaults to neutral ''(optional)''


=== Returns ===

* '''value''' (<code>any</code>)

== scan_order ==

does an ordered parallel filter and serial map-reduce pass on a single table and returns the reduced result

'''Allowed number of parameters:''' 11–17


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context to use for visibility and mutations; usually ((context "session") "__memcp_tx")
* '''table''' (<code>table|list|recset</code>): table handle, query-local RecSet, or a list for temporary data
* '''filterColumns''' (<code>list<string></code>): physical columns passed to filter before map/reduce; $recset_contains supplies a row-bound RecSet membership closure
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''filter''' (<code>func</code>): lambda function that decides whether a dataset is passed to the map phase. Equality and range comparisons may be translated into indexed scans
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>bool</code>): true when the row proceeds to ordering and map
* '''sortcols''' (<code>list<string|func(columns:any...) -> any></code>): columns used for ordering; each entry corresponds to one relation in sortdirs
** '''sort column''' (<code>string|func</code>): a column name, or a function of row-column values that returns the sortable value
*** '''Parameters'''
**** '''columns''' (<code>any</code>): column values used to compute the sort key ''(variadic)''
*** '''Returns'''
**** '''sort key''' (<code>any</code>): value compared at this sort position
* '''sortdirs''' (<code>list<func(left:any, right:any) -> bool></code>): one ordering relation per entry in sortcols; < is ascending and > is descending
** '''direction''' (<code>func</code>): strict ordering relation such as <, >, or a collate relation
*** '''Parameters'''
**** '''left''' (<code>any</code>): left sort value
**** '''right''' (<code>any</code>): right sort value
*** '''Returns'''
**** '''ordered''' (<code>bool</code>): true when left belongs before right
* '''limitPartitionCols''' (<code>number</code>): number of leading sort columns that form the partition key for per-partition offset/limit. 0 (default) means global offset/limit.
* '''offset''' (<code>number</code>): number of globally ordered, filter-accepted items to skip before map; apply SQL OFFSET here rather than in map
* '''limit''' (<code>number</code>): maximum globally ordered, filter-accepted items passed to map; -1 means unlimited; apply SQL LIMIT here so shard-local Top-K and the global merge can brake early
* '''mapColumns''' (<code>list<string></code>): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans; $break is reserved for internal ORC convergence and must not implement SQL OFFSET/LIMIT, which belong in the native offset and limit arguments
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''map''' (<code>func</code>): lambda function that extracts or produces one value from each accepted row
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>any</code>): value passed to reduce or returned directly
* '''reduce''' (<code>func</code>): optional serial aggregation function over mapped values ''(optional)''
** '''Parameters'''
*** '''accumulator''' (<code>any</code>): current aggregate, initially the neutral value
*** '''value''' (<code>any</code>): next mapped or partially reduced value
** '''Returns'''
*** '''accumulator''' (<code>any</code>): aggregate passed to the next reducer call or returned by the scan
* '''neutral''' (<code>any</code>): (optional) neutral element for the reduce phase, otherwise nil is assumed ''(optional)''
* '''isOuter''' (<code>bool</code>): (optional) if true, in case of no hits, call map once anyway with NULL values ''(optional)''
* '''notFoundValue''' (<code>any</code>): (optional) result for no hits when isOuter is false; defaults to neutral ''(optional)''
* '''postOrderFilterColumns''' (<code>list<string></code>): optional columns for a predicate evaluated in global order before OFFSET/LIMIT are counted; use for expensive acceptance checks that cannot participate in index boundaries ''(optional)''
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''postOrderFilter''' (<code>func</code>): optional late acceptance predicate. Rejected rows do not count toward OFFSET/LIMIT and never reach map ''(optional)''
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>bool</code>): true when the ordered row counts toward OFFSET/LIMIT and reaches map


=== Returns ===

* '''value''' (<code>any</code>)

== scan_order_multi ==

does an ordered parallel filter and serial map-reduce pass across multiple tables simultaneously, merging results into a single sorted stream

'''Allowed number of parameters:''' 13–17


=== Parameters ===

* '''tx''' (<code>any</code>): transaction context
* '''tables''' (<code>list<table|recset></code>): scan sources; all per-table lists must have this length
** '''source''' (<code>table|recset</code>): base table or query-local record set for one input stream
* '''filterColumns''' (<code>list<list<string>></code>): filter column lists, one per table
** '''table filter columns''' (<code>list<string></code>): columns supplied to the matching filterFns entry
*** '''column''' (<code>string</code>): column name in the corresponding table
* '''filterFns''' (<code>list<func(columns:any...) -> bool></code>): filter lambdas, one per table
** '''table filter''' (<code>func</code>): predicate for the corresponding table and filterColumns entry
*** '''Parameters'''
**** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
*** '''Returns'''
**** '''result''' (<code>bool</code>): true when the row enters that table's ordered stream
* '''sortcols''' (<code>list<list<string|func(columns:any...) -> any>></code>): sort column lists, one per table; every inner list must match sortdirs in length and result domains
** '''table sort columns''' (<code>list<string|func(columns:any...) -> any></code>): sort expressions for the corresponding table
*** '''sort column''' (<code>string|func</code>): a column name, or a function of row-column values that returns the sortable value
**** '''Parameters'''
***** '''columns''' (<code>any</code>): column values used to compute the sort key ''(variadic)''
**** '''Returns'''
***** '''sort key''' (<code>any</code>): value compared at this sort position
* '''sortdirs''' (<code>list<func(left:any, right:any) -> bool></code>): shared ordering relations used for every table stream and for the outer merge
** '''direction''' (<code>func</code>): strict ordering relation such as <, >, or a collate relation
*** '''Parameters'''
**** '''left''' (<code>any</code>): left sort value
**** '''right''' (<code>any</code>): right sort value
*** '''Returns'''
**** '''ordered''' (<code>bool</code>): true when left belongs before right
* '''perTableOffset''' (<code>list<int>|nil</code>): optional per-table offsets; nil disables all per-table offsets
** '''offset''' (<code>int</code>): rows skipped in the corresponding table before the outer merge; -1 disables the offset
* '''perTableLimit''' (<code>list<int>|nil</code>): optional per-table limits; nil disables all per-table limits
** '''limit''' (<code>int</code>): maximum rows retained from the corresponding table before the outer merge; -1 disables the limit
* '''limitPartitionCols''' (<code>number</code>): number of leading sort columns forming partition key
* '''offset''' (<code>number</code>): number of items to skip (global)
* '''limit''' (<code>number</code>): max number of items to read (global; -1 = unlimited)
* '''mapColumns''' (<code>list<list<string>></code>): map column lists, one per table
** '''table map columns''' (<code>list<string></code>): columns supplied to the matching mapFns entry
*** '''column''' (<code>string</code>): column name in the corresponding table
* '''mapFns''' (<code>list<func(columns:any...) -> any></code>): map lambdas, one per table
** '''table map''' (<code>func</code>): mapper for the corresponding table and mapColumns entry
*** '''Parameters'''
**** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
*** '''Returns'''
**** '''result''' (<code>any</code>): value inserted into the merged stream and passed to reduce
* '''reduce''' (<code>func</code>): optional aggregation function over mapped values from the merged stream ''(optional)''
** '''Parameters'''
*** '''accumulator''' (<code>any</code>): current aggregate, initially the neutral value
*** '''value''' (<code>any</code>): next mapped or partially reduced value
** '''Returns'''
*** '''accumulator''' (<code>any</code>): aggregate passed to the next reducer call or returned by the scan
* '''neutral''' (<code>any</code>): (optional) neutral element for reduce ''(optional)''
* '''isOuter''' (<code>bool</code>): (optional) if true, emit null row when no hits ''(optional)''
* '''notFoundValue''' (<code>any</code>): (optional) result for no hits when isOuter is false; defaults to neutral ''(optional)''


=== Returns ===

* '''value''' (<code>any</code>)

== createdatabase ==

creates a new database

'''Allowed number of parameters:''' 1–2


=== Parameters ===

* '''schema''' (<code>string</code>): name of the new database
* '''ignoreexists''' (<code>bool</code>): if true, return false instead of throwing an error ''(optional)''


=== Returns ===

* '''value''' (<code>bool</code>)

== dropdatabase ==

drops a database

'''Allowed number of parameters:''' 1–2


=== Parameters ===

* '''schema''' (<code>string</code>): name of the database
* '''ifexists''' (<code>bool</code>): if true, don't throw an error if it doesn't exist ''(optional)''


=== Returns ===

* '''value''' (<code>bool</code>)

== checktablemaintenance ==

checks whether a user-initiated maintenance operation is allowed for a table

'''Allowed number of parameters:''' 3–3


=== Parameters ===

* '''schema''' (<code>string</code>)
* '''table''' (<code>string</code>)
* '''operation''' (<code>string</code>)


=== Returns ===

* '''value''' (<code>bool</code>)

== maintenance_capabilities ==

returns the server-side maintenance capabilities for a database or table

'''Allowed number of parameters:''' 1–2


=== Parameters ===

* '''schema''' (<code>string</code>)
* '''table''' (<code>string</code>) ''(optional)''


=== Returns ===

* '''value''' (<code>list</code>)

== createtable ==

creates a table, runs its oninit option and registered after-create-table lifecycle triggers synchronously, and returns only after initialization completes; concurrent if-not-exists callers wait for that same completion

'''Allowed number of parameters:''' 4–5


=== Parameters ===

* '''schema''' (<code>string</code>): name of the existing database that will contain the table
* '''table''' (<code>string</code>): name of the table to create
* '''cols''' (<code>list<list></code>): column and constraint definitions
** '''definition''' (<code>list</code>): one of ("column" name type dimensions typeparams), ("unique" name columns), or ("foreign" name local_columns referenced_table referenced_columns update_mode delete_mode). Column lists contain strings; foreign-key modes are restrict, cascade, or set null. A column definition's dimensions contains integers and its typeparams uses the same fields documented by createcolumn options
* '''options''' (<code>list|assoc</code>): table options as an alternating key/value list
** '''auto_increment''' (<code>int</code>): first automatically assigned value; must be non-negative
** '''charset''' (<code>string</code>): default character set name
** '''collation''' (<code>string</code>): default collation name
** '''comment''' (<code>string</code>): user-visible table comment
** '''engine''' (<code>string</code>): storage engine: safe, logged, sloppy, memory, or cache
** '''oninit''' (<code>func</code>): closed zero-argument initializer run synchronously once per data generation; concurrent if-not-exists callers wait for completion
*** '''Returns'''
**** '''result''' (<code>any</code>): ignored initializer result
* '''ifnotexists''' (<code>bool</code>): when true, return false instead of failing if the table exists; if another caller is still creating it, wait for that caller's after-create-table initialization before returning false ''(optional)''


=== Returns ===

* '''value''' (<code>bool</code>): true when this call created and initialized the table, false when ifnotexists reused an initialized table

== createcolumn ==

creates a new column in table

'''Allowed number of parameters:''' 5–7


=== Parameters ===

* '''table''' (<code>table</code>)
* '''colname''' (<code>string</code>): name of the new column
* '''type''' (<code>string</code>): name of the basetype
* '''dimensions''' (<code>list<int></code>): dimensions of the type, for example precision and scale for decimal
** '''dimension''' (<code>int</code>): one type-specific dimension
* '''options''' (<code>list|assoc</code>): column properties and computed-column configuration as an alternating key/value list
** '''auto_increment''' (<code>bool</code>): assign increasing values automatically
** '''collate''' (<code>string</code>): collation used for this column
** '''comment''' (<code>string</code>): user-visible column comment
** '''default''' (<code>any</code>): literal value used when an insert omits the column
** '''default_expression''' (<code>string</code>): expression evaluated when an insert omits the column
** '''filter''' (<code>func</code>): predicate limiting which rows are computed
*** '''Parameters'''
**** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
*** '''Returns'''
**** '''result''' (<code>bool</code>): true when the row should be computed
** '''filtercols''' (<code>list<string></code>): columns supplied to filter before computing a value
*** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
** '''mapcols''' (<code>list<string></code>): columns supplied to mapfn for ordered-reduce computation
*** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
** '''mapfn''' (<code>func</code>): maps one source row into a value for reducefn
*** '''Parameters'''
**** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
*** '''Returns'''
**** '''result''' (<code>any</code>): value passed to reducefn
** '''null''' (<code>bool</code>): whether the column accepts nil values
** '''partitioncount''' (<code>int</code>): number of leading sort columns that define independent reducer partitions
** '''primary''' (<code>bool</code>): whether this column belongs to the primary key
** '''reducefn''' (<code>func</code>): combines ordered mapped values into the computed-column aggregate ''(optional)''
*** '''Parameters'''
**** '''accumulator''' (<code>any</code>): current aggregate, initially the neutral value
**** '''value''' (<code>any</code>): next mapped or partially reduced value
*** '''Returns'''
**** '''accumulator''' (<code>any</code>): aggregate passed to the next reducer call or returned by the scan
** '''reduceinit''' (<code>any</code>): initial accumulator supplied to reducefn
** '''sortcols''' (<code>list<string|func(columns:any...) -> any></code>): columns or expressions defining ordered-reduce input order
*** '''sort column''' (<code>string|func</code>): a column name, or a function of row-column values that returns the sortable value
**** '''Parameters'''
***** '''columns''' (<code>any</code>): column values used to compute the sort key ''(variadic)''
**** '''Returns'''
***** '''sort key''' (<code>any</code>): value compared at this sort position
** '''sortdirs''' (<code>list<func(left:any, right:any) -> bool></code>): one ordering relation for every sortcols entry
*** '''direction''' (<code>func</code>): strict ordering relation such as <, >, or a collate relation
**** '''Parameters'''
***** '''left''' (<code>any</code>): left sort value
***** '''right''' (<code>any</code>): right sort value
**** '''Returns'''
***** '''ordered''' (<code>bool</code>): true when left belongs before right
** '''temp''' (<code>bool</code>): whether this is a query-local temporary computed column
** '''unique''' (<code>bool</code>): whether values must be unique
** '''update''' (<code>any</code>): expression evaluated when a row is updated
* '''computorCols''' (<code>list<string></code>): columns passed to computor in this order ''(optional)''
** '''column''' (<code>string</code>): column name passed to the corresponding callback parameter
* '''computor''' (<code>func</code>): lambda expression that computes this column from the values selected by computorCols ''(optional)''
** '''Parameters'''
*** '''columns''' (<code>any</code>): one value for each entry in the matching column list, in the same order ''(variadic)''
** '''Returns'''
*** '''result''' (<code>any</code>): computed column value


=== Returns ===

* '''value''' (<code>bool</code>)

== createkey ==

creates a new key on a table

'''Allowed number of parameters:''' 4–4


=== Parameters ===

* '''table''' (<code>table</code>)
* '''keyname''' (<code>string</code>): name of the new key
* '''unique''' (<code>bool</code>): whether the key is unique
* '''columns''' (<code>list</code>): list of columns to include


=== Returns ===

* '''value''' (<code>bool</code>)

== dropkey ==

drops a named unique key from a table

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''table''' (<code>table</code>)
* '''keyname''' (<code>string</code>): name of the unique key


=== Returns ===

* '''value''' (<code>bool</code>)

== createforeignkey ==

creates a new foreign key on a table

'''Allowed number of parameters:''' 7–7


=== Parameters ===

* '''table1''' (<code>table</code>)
* '''keyname''' (<code>string</code>): name of the new key
* '''columns1''' (<code>list</code>): list of columns to include
* '''table2''' (<code>table</code>)
* '''columns2''' (<code>list</code>): list of columns to include
* '''updatemode''' (<code>string</code>): restrict|cascade|set null
* '''deletemode''' (<code>string</code>): restrict|cascade|set null


=== Returns ===

* '''value''' (<code>bool</code>)

== shardcolumn ==

tells us how it would partition a column according to their values. Returns a list of pivot elements.

'''Allowed number of parameters:''' 2–3


=== Parameters ===

* '''table''' (<code>table</code>)
* '''colname''' (<code>string</code>): name of the column
* '''numpartitions''' (<code>number</code>): number of partitions; optional. leave 0 if you want to detect the partiton number automatically or copy the partition schema of the table ''(optional)''


=== Returns ===

* '''value''' (<code>list</code>)

== partitiontable ==

suggests a partition scheme for a table. If the table has no partition scheme yet, it will immediately apply that scheme and return true. If the table already has a partition scheme, it will alter the partitioning score such that the partitioning scheme is considered in the next repartitioning and return false.

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''table''' (<code>table</code>)
* '''columns''' (<code>list</code>): associative list of string -> list representing column name -> pivots. You can compute pivots by (shardcolumn ...)


=== Returns ===

* '''value''' (<code>bool</code>)

== altertable ==

alters a table

'''Allowed number of parameters:''' 3–3


=== Parameters ===

* '''table''' (<code>table</code>)
* '''operation''' (<code>string</code>): one of owner|drop|engine|collation|auto_increment
* '''parameter''' (<code>any</code>): name of the column to drop or value of the parameter


=== Returns ===

* '''value''' (<code>bool</code>)

== altercolumn ==

alters a column

'''Allowed number of parameters:''' 4–4


=== Parameters ===

* '''table''' (<code>table</code>)
* '''column''' (<code>string</code>): name of the column
* '''operation''' (<code>string</code>): one of drop|type|collation|auto_increment|comment
* '''parameter''' (<code>any</code>): name of the column to drop or value of the parameter


=== Returns ===

* '''value''' (<code>bool</code>)

== droptable ==

removes a table

'''Allowed number of parameters:''' 2–3


=== Parameters ===

* '''schema''' (<code>string</code>)
* '''table''' (<code>string</code>)
* '''ifexists''' (<code>bool</code>): if true, don't throw an error if it already exists ''(optional)''


=== Returns ===

* '''value''' (<code>bool</code>)

== dropcolumn ==

drops a column from a table

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''table''' (<code>table</code>)
* '''column''' (<code>string</code>): name of the column to drop


=== Returns ===

* '''value''' (<code>bool</code>)

== migratedropcolumn ==

drops a legacy system column during startup migration

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''table''' (<code>table</code>)
* '''column''' (<code>string</code>): legacy column name


=== Returns ===

* '''value''' (<code>bool</code>)

== invalidatecolumn ==

marks all values of a computed column as stale

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''table''' (<code>table</code>)
* '''column''' (<code>string</code>): name of the computed column


=== Returns ===

* '''value''' (<code>bool</code>)

== invalidateorc ==

invalidates ORC column rows from a sort key onwards via validMask scan

'''Allowed number of parameters:''' 3–3


=== Parameters ===

* '''table''' (<code>table</code>)
* '''column''' (<code>string</code>): name of the ORC column
* '''sortkeys''' (<code>list</code>): composite sort key values from which to invalidate


=== Returns ===

* '''value''' (<code>bool</code>)

== register_keytable_cleanup ==

registers triggers on a base table to maintain keytable entries (insert/delete group keys)

'''Allowed number of parameters:''' 4–4


=== Parameters ===

* '''base_table''' (<code>table</code>)
* '''kt_table''' (<code>table</code>)
* '''tblvar''' (<code>string</code>): table alias used in scan column prefixes
* '''key_pairs''' (<code>list</code>): list of (base_col kt_col) pairs


=== Returns ===

* '''value''' (<code>bool</code>)

== initialize_cache_table ==

registers maintenance, locks source tables for a consistent snapshot, and runs a canonical planner-cache initializer exactly once

'''Allowed number of parameters:''' 5–6


=== Parameters ===

* '''transaction''' (<code>any</code>): explicit transaction context carrying query-session ownership
* '''table''' (<code>table</code>)
* '''source_tables''' (<code>list</code>)
* '''register_maintenance''' (<code>func</code>)
** '''Returns'''
*** '''value''' (<code>any</code>)
* '''initializer''' (<code>func</code>)
** '''Returns'''
*** '''value''' (<code>any</code>)
* '''finalizer''' (<code>func</code>): optional zero-argument finalizer run under the same source-table locks after initialization ''(optional)''
** '''Returns'''
*** '''value''' (<code>any</code>)


=== Returns ===

* '''value''' (<code>bool</code>)

== touch_keytable ==

extends the lease on a keytable so CacheManager defers eviction

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''table''' (<code>table</code>)


=== Returns ===

* '''value''' (<code>bool</code>)

== locktables ==

acquires WRITE or READ user-level locks on a list of tables (LOCK TABLES); implicitly releases any previously held locks

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''locks''' (<code>list</code>): flat list of schema, table, write? triples


=== Returns ===

* '''value''' (<code>bool</code>)

== unlocktables ==

releases all user-level table locks held by this session

'''Allowed number of parameters:''' 0–0


=== Parameters ===

''This function has no parameters.''


=== Returns ===

* '''value''' (<code>bool</code>)

== get_fk_target ==

returns (ref_table ref_column) if a single-column FK exists for the given column, nil otherwise

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''table''' (<code>table</code>)
* '''column''' (<code>string</code>): column name


=== Returns ===

* '''value''' (<code>any</code>)

== renametable ==

renames a table

'''Allowed number of parameters:''' 3–3


=== Parameters ===

* '''schema''' (<code>string</code>): name of the database
* '''oldname''' (<code>string</code>): current name of the table
* '''newname''' (<code>string</code>): new name of the table


=== Returns ===

* '''value''' (<code>bool</code>)

== insert ==

inserts a new dataset into table and returns the number of successful items

'''Allowed number of parameters:''' 3–7


=== Parameters ===

* '''table''' (<code>table</code>)
* '''columns''' (<code>list</code>): list of column names, e.g. '("ID", "value")
* '''datasets''' (<code>list</code>): list of list of column values, e.g. '('(1 10) '(2 15))
* '''onCollisionCols''' (<code>list</code>): list of columns of the old dataset that have to be passed to onCollision. Can also request $update, $set:<computed-column>, or NEW.<insert-column>. ''(optional)''
* '''onCollision''' (<code>func</code>): function called for each collision. Its positional parameters are the values requested by onCollisionCols, in the same order. If omitted, collisions raise an error. ''(optional)''
** '''Parameters'''
*** '''column values''' (<code>any</code>): one value for each onCollisionCols entry ''(variadic)''
** '''Returns'''
*** '''result''' (<code>any</code>)
* '''mergeNull''' (<code>bool</code>): if true, it will handle NULL values as equal according to SQL 2003's definition of DISTINCT (https://en.wikipedia.org/wiki/Null_(SQL)#When_two_nulls_are_equal:_grouping,_sorting,_and_some_set_operations) ''(optional)''
* '''onInsertid''' (<code>func</code>): called once with the first auto_increment id assigned for this INSERT ''(optional)''
** '''Parameters'''
*** '''id''' (<code>number</code>): first assigned auto_increment id
** '''Returns'''
*** '''result''' (<code>any</code>): ignored callback result


=== Returns ===

* '''value''' (<code>number</code>)

== stat ==

return system statistics as assoc: mem_available, mem_total, process_memory, shard_memory, shard_budget, persisted_memory, persisted_budget, cache_entry_count, cache_entry_size. (stat schema) and (stat schema tbl) return a string with detailed memory usage.

'''Allowed number of parameters:''' 0–2


=== Parameters ===

* '''schema''' (<code>string</code>): (optional) database name for detailed string output ''(optional)''
* '''table''' (<code>string</code>): (optional) table name for detailed string output ''(optional)''


=== Returns ===

* '''value''' (<code>any</code>)

== totalmem ==

Returns total physical memory in bytes (from /proc/meminfo)

'''Allowed number of parameters:''' 0–0


=== Parameters ===

''This function has no parameters.''


=== Returns ===

* '''value''' (<code>number</code>)

== resolve_column_name ==

resolve a physical column name from immutable table metadata

'''Allowed number of parameters:''' 4–4


=== Parameters ===

* '''schema''' (<code>string</code>): database name
* '''table''' (<code>string</code>): table name
* '''column''' (<code>string</code>): column name
* '''ignorecase''' (<code>bool</code>): whether identifier case is ignored


=== Returns ===

* '''value''' (<code>string|nil</code>)

== show ==

show databases/tables/columns/shards

(show) lists database names (show schema) lists table names (show table_handle) lists the memoized column defs (show table_handle true) returns table metadata (show table_handle "statistics") returns index statistics (show schema true) lists tables with full info: [{name,engine,row_count,size_bytes,collation,comment},...] (show schema tbl) lists column defs (show schema tbl true) returns assoc {columns,meta,shards} (show schema tbl N) returns shard N overview assoc {shard,state,main_count,delta,deletions,size_bytes} (show schema tbl N true) returns shard N full assoc adding columns and indexes (show schema tbl "statistics") returns INFORMATION_SCHEMA index statistics (show schema tbl "indexes") returns MySQL SHOW INDEX rows

'''Allowed number of parameters:''' 0–4


=== Parameters ===

* '''schema_or_table''' (<code>string|table|recset</code>): (optional) database name or resolved table/recset handle ''(optional)''
* '''table_or_property''' (<code>string|bool</code>): (optional) table name, true for full info, or "statistics" for a handle ''(optional)''
* '''property''' (<code>int|bool|string</code>): (optional) shard index (int), true for full table info, or "statistics" ''(optional)''
* '''full''' (<code>bool</code>): (optional) true to include columns and indexes in shard detail ''(optional)''


=== Returns ===

* '''value''' (<code>any</code>)

== show_triggers ==

show triggers for a given table

'''Allowed number of parameters:''' 1–2


=== Parameters ===

* '''schema''' (<code>string</code>): database name
* '''table''' (<code>string</code>): (optional) table name, if omitted shows all triggers in schema ''(optional)''


=== Returns ===

* '''value''' (<code>any</code>)

== rebuild ==

rebuilds main storages and returns the amount of time it took; with a table handle, rebuilds only that table

'''Allowed number of parameters:''' 0–3


=== Parameters ===

* '''table_or_all''' (<code>bool|table</code>): table handle for a table-local rebuild; otherwise whether to rebuild unchanged shards globally (default: false) ''(optional)''
* '''all_or_repartition''' (<code>bool</code>): with a table: whether to rebuild unchanged shards; globally: whether to repartition (default: true) ''(optional)''
* '''repartition''' (<code>bool</code>): with a table handle, whether to repartition that table (default: true) ''(optional)''


=== Returns ===

* '''value''' (<code>string</code>)


== loadCSV ==

loads a CSV stream into a table and returns the amount of time it took. The first line of the file must be the headlines. The headlines must match the table's columns exactly.

'''Allowed number of parameters:''' 3–5


=== Parameters ===

* '''schema''' (<code>string</code>): name of the database
* '''table''' (<code>string</code>): name of the table
* '''stream''' (<code>stream</code>): CSV file, load with: (stream filename)
* '''delimiter''' (<code>string</code>): (optional) delimiter defaults to ";" ''(optional)''
* '''firstline''' (<code>bool</code>): (optional) if the first line contains the column names (otherwise, the tables column order is used) ''(optional)''


=== Returns ===

* '''value''' (<code>string</code>)


== loadJSON ==

loads a .jsonl file from stream into a database and returns the amount of time it took. JSONL is a linebreak separated file of JSON objects. Each JSON object is one dataset in the database. Before you add rows, you must declare the table in a line '#table <tablename>'. All other lines starting with # are comments. Columns are created dynamically as soon as they occur in a json object.

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''schema''' (<code>string</code>): name of the database where you want to put the tables in
* '''stream''' (<code>stream</code>): stream of the .jsonl file, read with: (stream filename)


=== Returns ===

* '''value''' (<code>string</code>)

== settings ==

reads or writes a global settings value. This modifies your data/settings.json.

'''Allowed number of parameters:''' 0–2


=== Parameters ===

* '''key''' (<code>string</code>): name of the key to set or get (for reference, rts) ''(optional)''
* '''value''' (<code>any</code>): new value of that setting ''(optional)''


=== Returns ===

* '''value''' (<code>any</code>)

== createcreatetabletrigger ==

registers a lifecycle trigger that fires synchronously after a future createtable for the given schema/table succeeds

'''Allowed number of parameters:''' 6–6


=== Parameters ===

* '''schema''' (<code>string</code>): name of the database
* '''table''' (<code>string</code>): name of the table to watch for creation
* '''name''' (<code>string</code>): name of the trigger
* '''source_sql''' (<code>string</code>): original SQL body text (for diagnostics)
* '''body''' (<code>any</code>): trigger body (Scheme procedure or deferred trigger expression)
* '''visible''' (<code>bool</code>): true = user trigger, false = internal trigger


=== Returns ===

* '''value''' (<code>bool</code>)

== dropcreatetabletrigger ==

removes a registered create-table lifecycle trigger

'''Allowed number of parameters:''' 4–4


=== Parameters ===

* '''schema''' (<code>string</code>): name of the database
* '''table''' (<code>string</code>): name of the table watched for creation
* '''name''' (<code>string</code>): name of the trigger
* '''ifexists''' (<code>bool</code>): don't throw error if trigger doesn't exist


=== Returns ===

* '''value''' (<code>bool</code>)

== createtrigger ==

creates a new trigger on a table

'''Allowed number of parameters:''' 6–6


=== Parameters ===

* '''table''' (<code>table</code>)
* '''name''' (<code>string</code>): name of the trigger
* '''timing''' (<code>string</code>): one of: before_insert, after_insert, before_update, after_update, before_delete, after_delete
* '''source_sql''' (<code>string</code>): original SQL body text (for SHOW TRIGGERS)
* '''body''' (<code>any</code>): trigger body (parsed Scheme expression)
* '''visible''' (<code>bool</code>): true = user trigger (shown in SHOW TRIGGERS), false = internal trigger (hidden)


=== Returns ===

* '''value''' (<code>bool</code>)

== droptrigger ==

removes a trigger from a table

'''Allowed number of parameters:''' 3–3


=== Parameters ===

* '''schema''' (<code>string</code>): name of the database
* '''name''' (<code>string</code>): name of the trigger
* '''ifexists''' (<code>bool</code>): don't throw error if trigger doesn't exist


=== Returns ===

* '''value''' (<code>bool</code>)

== mysql_import ==

imports schema+data from a MySQL server into MemCP

'''Allowed number of parameters:''' 4–8


=== Parameters ===

* '''host''' (<code>string|nil</code>): MySQL host (nil => 127.0.0.1)
* '''port''' (<code>int|nil</code>): MySQL port (nil => 3306)
* '''username''' (<code>string</code>): MySQL username
* '''password''' (<code>string</code>): MySQL password
* '''sourcedb''' (<code>string|nil</code>): source database (omit/nil => all non-system dbs) ''(optional)''
* '''targetdb''' (<code>string|nil</code>): target database (omit/nil => sourcedb) ''(optional)''
* '''sourcetable''' (<code>string|nil</code>): source table (omit/nil => all tables in sourcedb) ''(optional)''
* '''targettable''' (<code>string|nil</code>): target table (omit/nil => sourcetable) ''(optional)''


=== Returns ===

* '''value''' (<code>bool</code>)

== psql_import ==

imports schema+data from a PostgreSQL server into MemCP

'''Allowed number of parameters:''' 4–9


=== Parameters ===

* '''host''' (<code>string|nil</code>): PostgreSQL host (nil => 127.0.0.1)
* '''port''' (<code>int|nil</code>): PostgreSQL port (nil => 5432)
* '''username''' (<code>string</code>): PostgreSQL username
* '''password''' (<code>string</code>): PostgreSQL password
* '''sourcedb''' (<code>string|nil</code>): source database (omit/nil => all non-system dbs) ''(optional)''
* '''sourceschema''' (<code>string|nil</code>): source schema (omit/nil => all non-system schemas in sourcedb) ''(optional)''
* '''targetdb''' (<code>string|nil</code>): target database (omit/nil => sourcedb) ''(optional)''
* '''sourcetable''' (<code>string|nil</code>): source table (omit/nil => all tables in sourceschema) ''(optional)''
* '''targettable''' (<code>string|nil</code>): target table (omit/nil => sourcetable) ''(optional)''


=== Returns ===

* '''value''' (<code>bool</code>)

Settings

2026-08-28T10:14:03Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Settings =

Read, write, or list runtime settings from Scheme:

<pre>(settings "ShardSize")
(settings "ExplainWidth" 80)
(settings)</pre>
Settings are stored with the database catalog. Validate production changes in a staging workload; memory, planner, and durability-related values affect behavior.

{| class="wikitable"
|-
! Setting
! style="text-align: right;"| Default
! Purpose
|-
| <code>Backtrace</code>
| style="text-align: right;"| false
| Retain more Scheme source/backtrace information.
|-
| <code>Trace</code>, <code>TracePrint</code>
| style="text-align: right;"| false
| Runtime trace collection and printing.
|-
| <code>PartitionMaxDimensions</code>
| style="text-align: right;"| 10
| Maximum adaptive partition dimensions.
|-
| <code>DefaultEngine</code>
| style="text-align: right;"| <code>safe</code>
| One of <code>safe</code>, <code>logged</code>, <code>sloppy</code>, <code>memory</code>, <code>cache</code>.
|-
| <code>ShardSize</code>
| style="text-align: right;"| 60000
| Target rows used by shard/repartition decisions.
|-
| <code>AnalyzeMinItems</code>
| style="text-align: right;"| 50
| Minimum work before recording scan statistics.
|-
| <code>IndexThreshold</code>
| style="text-align: right;"| 5
| Minimum shard rows for a new adaptive index.
|-
| <code>MaxRamPercent</code>
| style="text-align: right;"| 50
| Overall MemCP RAM budget when byte override is zero.
|-
| <code>MaxRamBytes</code>
| style="text-align: right;"| 0
| Exact overall budget; zero uses percentage.
|-
| <code>MaxPersistPercent</code>
| style="text-align: right;"| 30
| Budget for reloadable persisted data.
|-
| <code>MaxPersistBytes</code>
| style="text-align: right;"| 0
| Exact persisted-data budget.
|-
| <code>MetricsTracing</code>
| style="text-align: right;"| false
| Periodically write performance metrics.
|-
| <code>MetricsTracingInterval</code>
| style="text-align: right;"| 60
| Metrics interval in seconds when zero/defaulted.
|-
| <code>ShutdownDrainSeconds</code>
| style="text-align: right;"| 10
| Wait for in-flight requests during shutdown.
|-
| <code>LogJIT</code>
| style="text-align: right;"| false
| Log native JIT compilation details.
|-
| <code>ScanDebugging</code>
| style="text-align: right;"| false
| Log scan boundaries, index selection, and scan details.
|-
| <code>ExplainWidth</code>
| style="text-align: right;"| 20
| Pretty-print width for EXPLAIN expressions.
|-
| <code>JoinReorderDPBudget</code>
| style="text-align: right;"| 256
| Connected subsets retained by exact join search.
|-
| <code>ErrorQueryLog</code>
| style="text-align: right;"| false
| Store failed queries in <code>system_statistic.errors</code>.
|-
| <code>MaxErrorQueryLog</code>
| style="text-align: right;"| 0
| Error-log row limit; zero is unlimited.
|-
| <code>PrintLog</code>
| style="text-align: right;"| false
| Store Scheme print/time output.
|-
| <code>MaxPrintLog</code>
| style="text-align: right;"| 0
| Print-log row limit; zero is unlimited.
|}

Use [[Memory_Management_and_Eviction|Memory Management and Eviction]], [[Query_Planner_and_Physical_Lowering|Query Planner and Physical Lowering]], and [[Dashboard_and_Operations|Dashboard and Operations]] for operational context.

Scan

2026-08-28T10:14:03Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Scan =

<code>scan</code>, <code>scan_order</code>, <code>scan_order_multi</code>, <code>scan_exists</code>, and transaction-bound variants are physical storage operators emitted after logical planning. Their exact generated signatures are listed under [[Storage]].

The physical lowerer extracts safe equality, range, IN-list, LIKE-prefix, computed-expression, ordering, and RecSet constraints. A boundary may be exact or merely a candidate superset. Candidate boundaries always retain the original SQL predicate as a residual filter.

Column values are read in batches through encoding-specific range or multi-record fast paths. Ordered scans can combine main-index and delta ordering, propagate early stop, and use offset/limit or top-k braking when semantics and cost allow. [[RecSets]] identify records for one base relation and visibility snapshot; they do not define join multiplicity or result order. Their adaptive ranges, positive-ID lists, and bitmaps let later scans reuse a narrow domain without materializing wide rows.

Application code should use SQL. Planner contributors must keep scan objects, RecSets, ORC columns, and helper tables out of the parser and logical IR. See [[Query Planner and Physical Lowering]] and the repository's <code>INVARIANTS.md</code>.

== Reading a plan ==

For a query such as:

<pre>
SELECT customer_id, SUM(total)
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id
ORDER BY SUM(total) DESC
LIMIT 20;
</pre>

the logical plan establishes filtering, grouping, ordering, and LIMIT semantics. Physical lowering can then choose a bounded date range, read only <code>created_at</code>, <code>customer_id</code>, and <code>total</code>, fuse filter/aggregation, and apply top-k braking. Use <code>EXPLAIN PHYSICAL</code> to see what was actually selected; SQL spelling alone does not force an index or RecSet.

== Low-level callback contract ==

Unordered scans can perform shard-local map/reduce work and combine partial accumulators with a second reducer. Ordered scans retain serial output order and own OFFSET/LIMIT/early stop. Update-capable callbacks receive a controlled row-update handle under the scan's transaction and locking rules. The generated [[Storage]] chapter is authoritative for parameter names and return types at the referenced commit.

{| class="wikitable"
! Stage !! Unordered <code>scan</code> !! Ordered <code>scan_order</code>
|-
| Filter/access path || Parallel per eligible shard; may use indexes/boundaries || Parallel candidate filtering and local ordering where useful
|-
| Map || Parallel per shard || Applied in requested global order
|-
| Reduce || Shard-local partial reductions plus a final combine || Serial in output order when order affects semantics
|-
| Early stop || Cancellation or consumer stop || OFFSET/LIMIT and compatible ordered braking
|}

== Scheme examples ==

These examples illustrate the callback roles; use the exact current signatures from [[Storage]] when writing low-level code.

<pre>
/* resolve the current transaction and table once */
(set tx ((context "session") "__memcp_tx"))
(set tbl1 (table "schema" "tbl1"))
(set tbl2 (table "schema" "tbl2"))

/* print key/value pairs */
(scan tx tbl1 '() (lambda () true)
'("k" "v") (lambda (k v) (print "tbl1[" k "] = " v)))

/* find a key through an indexable equality predicate */
(scan tx tbl1 '("k") (lambda (k) (equal? k 12))
'("v") (lambda (v) v))

/* shard-local sum with neutral value */
(scan tx tbl2 '() (lambda () true)
'("weight") (lambda (weight) weight) + 0)
</pre>

An ordered scan additionally receives sort expressions/directions, OFFSET and LIMIT. Filtering and local ordering may run in parallel, but output order and order-sensitive reduction remain serial. Do not use side effects as a substitute for a reducer when result order matters. See [[RecSets]] for ordered membership intersection and progressively filtered candidate batches.

SQL over REST

2026-08-28T10:14:03Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= SQL over REST =

Submit MySQL-dialect SQL to <code>/sql/<database></code> and PostgreSQL-dialect SQL to <code>/psql/<database></code>. The HTTP interface is useful for scripts, jobs, dashboards, and services that do not need a MySQL driver. It uses the same parser, planner, permissions, storage engine, and transaction machinery as the other SQL frontend.

POST keeps credentials and SQL out of the URL and avoids URL-length and escaping problems:

<pre>
curl --fail-with-body -u root:strong-password \
-H 'Content-Type: text/plain; charset=utf-8' \
--data-binary 'SELECT id, name FROM users ORDER BY id LIMIT 10' \
http://localhost:4321/sql/myapp
</pre>

GET with URL-encoded SQL after the database name remains a compatibility convenience. Prefer POST for applications and logs.

== JSONL results and errors ==

Row-producing queries return newline-delimited JSON (NDJSON/JSONL): one object per row. Clients can therefore process a large result incrementally instead of waiting for one enclosing array.

<pre>
{"id": 1, "name": "Ada"}
{"id": 2, "name": "Grace"}
</pre>

A successful write that emits no result rows returns an <code>affected_rows</code> object. Authentication failures use HTTP 401; SQL failures use a non-success status and a text error body. Always check both status and body. A fast 401 or SQL error is not successful query throughput.

== Parameters ==

URL query parameters are placed into the SQL session and can be referenced as named variables such as <code>@user_id</code>. Keep the SQL text in the POST body and URL-encode values:

<pre>
curl --fail-with-body -u root:strong-password \
--data-binary 'SELECT id, name FROM users WHERE id = @user_id' \
'http://localhost:4321/sql/myapp?user_id=42'
</pre>

This avoids SQL string concatenation, but applications must still validate type, range, and authorization. Never use a parameter supplied by the caller to choose an unrestricted schema, table, or column name.

== PostgreSQL syntax ==

Use the <code>/psql/<database></code> endpoint for PostgreSQL-style syntax and JSON operators:

<pre>
curl --fail-with-body -u root:strong-password \
--data-binary "SELECT payload->>'name' FROM events LIMIT 10" \
http://localhost:4321/psql/myapp
</pre>

This is a syntax frontend, not the PostgreSQL wire protocol and not a promise of complete PostgreSQL compatibility. See [[Supported SQL]] and [[JSON]].

== Transactions across HTTP requests ==

Without an explicit session identifier, each request uses its request-local session and automatic transaction handling. To retain transaction state across multiple HTTP requests, send the same unpredictable <code>X-Session-Id</code> header on each request. Treat it like a credential, prevent sharing between users, and always finish with COMMIT or ROLLBACK. For conventional long-lived database sessions, the MySQL protocol is often the simpler interface.

== Security and custom APIs ==

HTTP Basic authentication uses MemCP users and grants. Never expose the development default password, and terminate TLS at a trusted proxy or restrict the network before sending credentials. General SQL access is powerful; browsers and public clients should normally receive a narrowly scoped application endpoint instead.

For custom routing, response formats, authentication, or WebSockets, see [[In-Database WebApps and REST Services]]. Also see [[Security and Authentication]], [[Database Tools compatibility with MemCP]], and [[Performance Measurement]].

Process Hibernation

2026-08-28T10:14:02Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Process Hibernation =

Linux CRIU can checkpoint and restore a complete process, including RAM-only state. This makes it interesting for development experiments with <code>memory</code> tables, but it is not MemCP's supported durability, backup or upgrade mechanism. Open sockets, kernel features, file descriptors, JIT mappings and external storage connections can make restoration fail or restore an unsafe environment.

An experimental local workflow is:

<pre>sudo criu dump -t PID -j -D DUMP_DIRECTORY
sudo criu restore -j -D DUMP_DIRECTORY</pre>

Stop incoming traffic and test the exact kernel, CRIU and MemCP build before relying on this even temporarily. A checkpoint is tied to its host/runtime environment and does not replace a portable data backup. Persistent production data should use <code>ENGINE=safe</code> and tested storage-level backup/restore procedures.

See [[Persistency and Performance Guarantees]], [[File System]] and [[Deployment]].

Persistency and Performance Guarantees

2026-08-28T10:14:02Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Persistency and Performance Guarantees =

<blockquote>'''Verified against commit <code>c42e19eba</code> on 28 August 2026.''' The durability contract applies to committed changes and depends on the configured persistence backend honoring its documented synchronization/acknowledgement semantics.</blockquote>

MemCP lets every table choose its own balance between durability and write latency with <code>ENGINE=<mode></code>. The default is <code>safe</code>. This is a data-safety decision, not merely a performance hint: the modes differ in what is written, when it becomes durable, what survives a failure, and whether memory pressure may discard the data.

MemCP's performance promise is architectural: hot data is processed from compact columnar memory, independent shards can run in parallel, and aggregation is reduced from shard-local partial results. Actual latency still depends on the workload, hardware, storage backend, transaction conflicts, constraints, cache residency, and JIT coverage.

== Choose an ENGINE at a glance ==

{| class="wikitable" style="width:100%;"
! Engine !! Rows stored in !! Write path !! Survives process crash !! Survives kernel crash/power loss !! May be evicted !! Typical use
|-
| <code>safe</code> '''(default)''' || Compressed column files plus RAM/delta state || WAL, synchronized at transaction commit || Yes || Yes, when the persistence backend honors the required durable synchronization || Reloadable RAM representation only; disk data remains || Business data, accounting, orders, identities, anything that must not be lost
|-
| <code>logged</code> || Compressed column files plus RAM/delta state || WAL without <code>fsync</code>; measured write paths have reached about 10× <code>safe</code> throughput || Yes || Recent committed writes may be lost || Reloadable RAM representation only; disk data remains || High write rates with explicitly accepted power-loss risk, preferably protected by UPS/battery-backed storage
|-
| <code>sloppy</code> || Rebuilt compressed column files plus unlogged RAM delta || No WAL; normally one bulk compression/rebuild publication every 15 minutes || Main storage yes; delta since the last successful rebuild is lost || Same: recent inserts/updates/deletes may be lost || Reloadable persisted main data; an unflushed delta prevents eviction || Reconstructible telemetry, staging data, usage statistics, sensor data, and flash-backed workloads that deliberately trade a loss window for fewer continuous writes
|-
| <code>memory</code> || RAM only; schema is retained || No WAL or row files || No || No || No, because eviction would destroy the only copy || Process-lifetime sessions, transient coordination, scratch state
|-
| <code>cache</code> || RAM only; reconstructible definition/<code>oninit</code> is retained || No WAL or row files || No || No || '''Yes; the complete contents may disappear while MemCP is running''' || Rebuildable caches, observer handles, optimizer/helper data
|}

In shorthand:

* <code>cache</code> and <code>memory</code> provide the shortest RAM-only write path but no row persistence;
* <code>sloppy</code> and <code>logged</code> avoid a synchronous durability fence, but accept different loss windows;
* <code>safe</code> waits for durable WAL synchronization at commit and is the mode for irreplaceable data.

Do not infer isolation semantics from the ENGINE. Isolation and transaction visibility are documented separately under [[Transactions and Isolation]]. A transaction touching several tables inherits the durability strengths and risks of every involved table.

== Performance guarantees and read speed ==

=== Hot reads stay in the columnar execution path ===

MemCP is designed so that the active working set is read from RAM and remains fast. Filters, projections, [[RecSets]], grouping, and aggregates can consume only the referenced compressed columns, in batches, without first reconstructing wide rows. This reduces cache misses and memory traffic.

Persistent cold columns may nevertheless be unloaded from RAM. Their next access transparently reloads them from the configured filesystem or object store. Therefore “all reads come from RAM” describes the intended '''hot path''', not a promise that a cold or undersized deployment never performs storage I/O. A repeatedly evicted hot set becomes I/O-bound even though the result remains correct.

=== Writes scale across independent shards ===

INSERT and UPDATE operations without unique-key checks, foreign-key checks, hot-row conflicts, or other serializing work can execute shard-locally and scale across shards. Adding CPU cores and distributing data over enough useful shards is intended to preserve response time as the dataset and concurrent work grow.

Scaling is not automatic when all writes target one shard or key range. Unique and foreign-key probes, transaction conflicts, triggers, WAL synchronization, repartitioning, and a storage backend with limited throughput can become the governing cost.

=== Aggregates use parallel partial results ===

Associative aggregates such as <code>SUM</code>, <code>COUNT</code>, and the partial state behind <code>AVG</code> can be computed independently per shard and reduced afterwards. With enough rows and shards, this is designed to scale close to the available CPU count until another shared resource becomes limiting. Tiny inputs, ordered/window semantics, skewed shards, or a serial final stage do not benefit in the same way.

=== Memory bandwidth remains finite ===

An earlier MemCP description recorded about 40 GiB/s of memory bandwidth on the tested machines and observed that an uncompressed scan on a 128-core CPU stopped improving beyond roughly eight cores. The exact hardware, data shape, and benchmark protocol were not retained, so these figures are historical orientation rather than a current universal limit.

The design response remains important: [[Columnar Storage]] and adaptive compression reduce the number of cache lines moved per qualifying value. A compact 3-, 5-, or 12-bit representation may use more lightweight decoding but still finish sooner than an uncompressed representation that saturates memory bandwidth. This is why MemCP aims to scale useful scans beyond the core count at which a wider row representation has already exhausted the memory channels.

== Persistency guarantees and write speed ==

=== <code>ENGINE=safe</code> ===

<code>safe</code> is the default and strongest mode:

* the rebuilt main storage is kept as compressed column files;
* changes in the delta are recorded in a write-ahead log (WAL);
* a transaction succeeds only after the WALs of touched <code>safe</code> shards have been synchronized at commit;
* after a process crash, kernel crash, or power failure, the WAL is replayed and committed changes are recovered;
* one transaction can contain many changed rows while paying one commit-time synchronization round per touched WAL rather than one per row;
* the synchronization fence adds latency, especially for single-row autocommit workloads.

Use it for accounting records, customer data, orders, permissions, and any data whose loss would be unacceptable. The power-loss guarantee assumes the configured persistence backend provides the durability promised by its sync/ack operation. For example, remote/object backends must be evaluated according to their own replication and acknowledgement contract; see [[Storage Backends]].

An early system measurement reported approximately 1,700 individually synchronized writes per second. It did not retain the device, filesystem, transaction size, or commit used. Keep the number as project history, not as a fixed MemCP limit. Modern NVMe, group commit through larger transactions, storage caches, and backend behavior can all change the result substantially.

=== <code>ENGINE=logged</code> ===

<code>logged</code> keeps the WAL but deliberately omits <code>fsync</code>:

* main storage is represented by compressed column files;
* every delta change is written to WAL files;
* the operation may succeed while the operating system still holds WAL bytes in volatile page cache;
* a MemCP process crash can recover the written log;
* a kernel crash, abrupt power loss, or storage-controller loss can discard recent committed changes that were not flushed physically;
* buffering improves write latency at the price of that explicit loss window.

In project write measurements, switching the same write path from <code>safe</code> to <code>logged</code> has reached roughly '''10× the write throughput'''. The mechanism is straightforward: <code>safe</code> must wait for a durable storage fence at commit, whereas <code>logged</code> can return after writing into the operating system's buffered path. The exact gain depends on transaction size, WAL device, filesystem/backend, queueing and concurrency; a larger transaction can also amortize <code>safe</code> synchronization without weakening durability.

Use this mode only when process-crash recovery is sufficient or when external power and storage protection make the remaining risk acceptable. It is not a substitute for <code>safe</code> merely because the WAL file exists.

=== <code>ENGINE=sloppy</code> ===

<code>sloppy</code> persists compact main storage but has no WAL:

* current main columns are stored on the persistence backend;
* new inserts, updates, and deletions remain in the in-memory delta until rebuild;
* MemCP schedules a compression/rebuild pass every 15 minutes and also rebuilds during a clean shutdown;
* after an unclean stop, the last successfully published main generation is recovered;
* an unpersisted insert/update can disappear, and an unpersisted deletion can reappear;
* writes avoid WAL and synchronization latency.

This write pattern is useful on SD cards and other flash media when the data is reconstructible or a documented loss window is acceptable. Instead of producing a continuously synchronized WAL stream, MemCP normally publishes one compressed main generation during the 15-minute rebuild cycle. Fewer, batched persistence events can reduce write amplification and flash wear. The rebuild itself still writes data, and its successful completion must be monitored; <code>sloppy</code> is not a durability technique for irreplaceable records.

The old documentation described data older than 15 minutes as guaranteed persistent. More precisely, 15 minutes is the normal '''rebuild schedule'''. A long-running, blocked, failed, or postponed rebuild can make the exposure window larger. Monitor rebuild completion and errors if the acceptable-loss window matters.

This remains an extremely fast persistent-main-storage choice for replaceable data: high-volume usage statistics, sensor samples, import staging, or derived tables that can be reconstructed. It must not hold irreplaceable data merely because most rows already exist on disk.

=== <code>ENGINE=memory</code> ===

<code>memory</code> keeps rows alive for the life of the MemCP process:

* all row contents exist only in RAM;
* no WAL or persistent column files protect them;
* a normal shutdown, restart, process crash, kernel crash, or power loss empties the table;
* the table schema is retained;
* a closed <code>oninit</code> callback stored with the schema can repopulate the empty generation when the application's idempotent table-creation guard runs after restart;
* the memory manager will not evict a memory-engine shard, because RAM is its only copy.

Use it for session state, short-lived observer handles, and other process-lifetime data that must remain present while the process is running but can be recreated after restart. Its writes follow the RAM-only path, but its contents still count toward the memory budget and cannot be reclaimed safely.

=== <code>ENGINE=cache</code> ===

<code>cache</code> is also RAM-only, but more disposable than <code>memory</code>:

* row contents have no WAL or persistent column files;
* schema/reconstructible definition and a closed <code>oninit</code> callback can be retained;
* the table starts empty after restart and can be repopulated by its creation guard;
* under memory pressure, MemCP may evict the complete data generation while the server is still running;
* after eviction the schema remains usable and new rows may be written again;
* hidden optimizer/helper tables use this mode because their data can be rebuilt.

Use it only when disappearance is part of the application's contract: result caches, derived lookup structures, observer state, and other data whose authoritative copy lives elsewhere. Code reading a cache table must tolerate an empty/rebuilt generation.

== Transaction boundaries and batching ==

Every SQL statement runs in an implicit transaction unless the session already has an explicit transaction. For <code>safe</code> tables, synchronization happens at commit. Consequently:

<pre>
-- Each statement commits separately and may require a durability fence.
INSERT INTO events (id, payload) VALUES (1, 'a');
INSERT INTO events (id, payload) VALUES (2, 'b');

-- One explicit transaction amortizes commit work across both writes.
START TRANSACTION;
INSERT INTO events (id, payload) VALUES (3, 'c');
INSERT INTO events (id, payload) VALUES (4, 'd');
COMMIT;
</pre>

Batch only as far as the application's atomicity, lock duration, conflict rate, and latency budget allow. There is no universal writes-per-second guarantee: transaction size, number of touched shards, WAL device, filesystem, backend acknowledgements, constraints, and concurrency determine the result.

== Creating and inspecting tables ==

<pre>
CREATE TABLE invoices (
id BIGINT PRIMARY KEY,
total DECIMAL(18,2)
) ENGINE=safe;

CREATE TABLE sensor_buffer (
measured_at DATETIME,
value DOUBLE
) ENGINE=sloppy;

CREATE TABLE rendered_pages (
cache_key VARCHAR(255),
body TEXT
) ENGINE=cache;

SHOW CREATE TABLE invoices;
</pre>

Choose the mode table by table. A single application can keep invoices in <code>safe</code>, replaceable telemetry in <code>sloppy</code>, sessions in <code>memory</code>, and derived objects in <code>cache</code> without weakening the durable tables.


== Changing ENGINE safely ==

<blockquote>'''Irreversible data-safety boundary:''' changing a persisted table (<code>safe</code>, <code>logged</code>, or <code>sloppy</code>) to <code>memory</code> '''or <code>cache</code>''' deletes its on-disk column files and WAL immediately. The currently loaded rows remain in RAM, but there is no persistent copy and no undo after shutdown or eviction. Back up and verify the table first.</blockquote>

{| class="wikitable"
! Transition !! What happens !! Required caution
|-
| <code>safe</code>/<code>logged</code>/<code>sloppy</code> → <code>memory</code> or <code>cache</code> || WAL and column files are removed; rows continue only in RAM || Irreversible; <code>cache</code> rows may additionally be evicted while running
|-
| <code>safe</code>/<code>logged</code> → <code>sloppy</code> || WAL is closed and removed || Future delta changes are unsafe until rebuilt
|-
| <code>memory</code>/<code>cache</code> → persisted || Current RAM rows are serialized to column files || Wait for successful completion before assuming durability
|-
| <code>sloppy</code> → <code>safe</code>/<code>logged</code> || A WAL is opened || Future writes get the new guarantee; retain backups of earlier state
|-
| <code>safe</code> ↔ <code>logged</code> || Column layout and WAL stay; synchronization policy changes || Moving to <code>logged</code> accepts power-loss risk immediately
|-
| <code>memory</code> ↔ <code>cache</code> || Both remain RAM-only; cache-manager registration changes || Moving to cache permits live eviction
|}

<pre>
-- Make and verify a backup before this destructive transition:
ALTER TABLE historical_events ENGINE=memory;
</pre>

LRU cleanup is not a hidden ENGINE transition. For persistent engines it may release reloadable in-memory columns, but it must never delete their disk files. Physical removal is reserved for explicit <code>DROP TABLE</code> and an explicit persisted-to-<code>memory</code>/<code>cache</code> transition.

== Failure scenarios in plain language ==

{| class="wikitable"
! Event !! <code>safe</code> !! <code>logged</code> !! <code>sloppy</code> !! <code>memory</code> !! <code>cache</code>
|-
| MemCP process crashes, OS remains alive || Recover WAL || Recover WAL already written to OS cache || Recover last rebuilt main generation || Rows lost || Rows lost
|-
| Kernel crash or sudden power loss || Recover committed WAL, subject to backend durability || Recent changes may be lost || Delta since last successful rebuild lost || Rows lost || Rows lost
|-
| Clean MemCP shutdown/restart || Rows retained || Rows retained || Shutdown rebuild normally persists current state || Schema retained, rows start empty || Schema/rebuilder retained, rows start empty
|-
| Memory pressure while running || Reloadable data may leave RAM; disk copy remains || Same || Persisted clean main data may be unloaded; dirty delta is protected || Rows are not evicted || Complete contents may be evicted
|}

== Backups and operations ==

Durability is not a backup. <code>safe</code> protects committed writes against failures; it does not undo an accidental <code>DROP TABLE</code>, a destructive ENGINE transition, an application-level deletion, corrupted credentials, or loss of the whole storage system. Maintain tested backups and restore procedures independently.

For production operation, monitor:

* rebuild start, completion, duration, and errors, especially for <code>sloppy</code>;
* WAL write/sync latency and storage-backend acknowledgements;
* dirty delta growth and long-running transactions;
* cache eviction, cold reload latency, and whether the hot set fits the memory budget;
* query latency together with shard distribution, CPU utilization, and memory bandwidth.

See [[Memory Management and Eviction]], [[Storage Backends]], [[Hardware Requirements]], [[Performance Measurement]], [[Transactions and Isolation]], and [[Deployment]] for sizing, measurement, and operational guidance.

Performance Measurement

2026-08-28T10:14:02Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Performance Measurement =

Every published result must be reproducible and count successful work.

Record MemCP commit/build flags, comparison-system version/configuration, schema, data generator and row count, query text, ENGINE mode, hardware, filesystem or remote backend, memory limits, concurrency, warm/cold state, and raw samples.

Verify correctness before timing: HTTP status, error body, row count, values, and restart behavior. A fast 401 or other non-2xx response is not query throughput.

== Current headline observations ==

MemCP has reached '''speedups of 10× and more over MariaDB/PostgreSQL''' in measured OLAP and search-oriented workflows. The relevant mechanisms include compact RecSet domains, compressed column scans, batch execution, late materialization, adaptive physical structures and shard parallelism.

Current isolated OLTP measurements show the opposite trade-off: those paths take about '''1.3–2.0× as long''' on MemCP. In complete WordPress- and wiki-style page builds, this has made '''no significant difference to overall page-loading time''' in the measured application workflows. The application, the number and mix of queries, and the share of time spent outside SQL determine the end-to-end effect. By contrast, one filtered-list workflow over roughly one million documents took around '''30 seconds on PostgreSQL''' and '''1.6 seconds on MemCP''' for the same query. That makes search, filtering, grouping and analytical reads the clearest current migration targets while OLTP/JIT work continues.

These headline figures should be stated rather than hidden, but they do not replace the reproducibility record below. A benchmark publication must attach the exact competing versions, schema/query, dataset and distribution, hardware, durability settings, cache state, concurrency, result validation and raw samples. Do not combine the best result from one workload with the conditions of another.

Write-path measurements have also reached about '''10× the throughput''' when changing the same table/workload from <code>safe</code> to <code>logged</code>. Report that result together with the durability difference: <code>safe</code> synchronizes WAL at commit, whereas <code>logged</code> leaves recent WAL bytes in volatile operating-system buffers. A comparison that changes ENGINE without naming the changed failure guarantee is incomplete.

Use calibrated SQL performance suites for end-to-end plans and Go benchmarks for isolated storage primitives. Run multiple samples, report distributions rather than the best run, and compare against current master under identical conditions.

<pre>make test
PERF_TEST=1 PERF_EXPLAIN=1 make test
go test ./storage/ -bench 'BenchmarkName' -run '^$' -count=5</pre>
For A/B changes, save raw output for both commits and use <code>benchstat</code>. Profiles should distinguish compile time, physical preparation, execution, allocation, lock waits, storage reload, and background maintenance. Attach EXPLAIN and <code>EXPLAIN COMPILE</code> for planner-sensitive results.

== SQL performance regression framework ==

Suites below <code>tests/performance/</code> are disabled during ordinary correctness runs unless <code>PERF_TEST=1</code> is set. The runner stores machine-specific row counts and timings in <code>.perf_baseline.json</code>, performs warm-up work, repeats measured cases (five times by default), and reports the median. A result above the stored baseline plus tolerance fails the gate.

<pre>
# Run with existing baselines
PERF_TEST=1 make test

# Recalibrate row counts/times for this machine
PERF_TEST=1 PERF_CALIBRATE=1 make test

# Freeze row counts while bisecting a regression
PERF_TEST=1 PERF_NORECALIBRATE=1 make test

# Include query-plan output
PERF_TEST=1 PERF_EXPLAIN=1 make test
</pre>

Calibration aims for sufficiently long cases (currently roughly 10–20 seconds) and adjusts rows gradually. The runner also applies one protected pre-server wall-clock calibration for the execution architecture; a regression inside MemCP must not be allowed to enlarge its own time allowance.

A performance line shows the measured duration, threshold, calibrated rows, per-row time, heap and CPU utilization, for example:

<pre>Perf: COUNT (4.3s / 13s, 100,000,000 rows, 0.04µs/row, 25GB heap, 1522%/2400% CPU)</pre>

Read all fields together: lower per-row time is meaningless if the query failed, row count changed unexpectedly, the process swapped, or a different plan/durability mode was used.

{| class="wikitable"
! Variable !! Purpose
|-
| <code>PERF_TEST=1</code> || Enable performance suites.
|-
| <code>PERF_CALIBRATE=1</code> || Reset/update the local performance baseline.
|-
| <code>PERF_NORECALIBRATE=1</code> || Keep row counts fixed for commit-to-commit or bisect comparisons.
|-
| <code>PERF_EXPLAIN=1</code> || Print plans for performance cases.
|-
| <code>PERF_REPEAT=N</code> || Change the number of measured repetitions; default is five.
|}

Do not commit a baseline produced while the machine was thermally throttled, heavily loaded, swapping, or using a different build configuration. Preserve the known-good baseline before a bisect.

For a regression bisect, calibrate on the known-good commit, keep a copy of its baseline, and freeze row counts while Git tests candidate commits:

<pre>git checkout GOOD_COMMIT
PERF_TEST=1 PERF_CALIBRATE=1 make test # repeat until stable
cp .perf_baseline.json /tmp/memcp-perf-good.json

git bisect start HEAD GOOD_COMMIT
cp /tmp/memcp-perf-good.json .perf_baseline.json
git bisect run bash -c 'PERF_TEST=1 PERF_NORECALIBRATE=1 make test'</pre>

== Storage microbenchmarks and profiles ==

For a storage encoding or hot loop, benchmark the smallest relevant package and compare several samples:

<pre>
go test ./storage/ -bench 'BenchmarkEnumPerElem' -run '^$' -count=5 > /tmp/before.txt
# apply/build the candidate under the same conditions
go test ./storage/ -bench 'BenchmarkEnumPerElem' -run '^$' -count=5 > /tmp/after.txt
benchstat /tmp/before.txt /tmp/after.txt
</pre>

Microbenchmarks establish mechanism, not end-to-end query benefit. Confirm the change with SQL, result validation, representative cardinalities, and warm/cold conditions. Use CPU, memory/allocation, mutex/block, and I/O profiles to explain a difference instead of attributing every gain to the edited function.

== Coverage is not performance ==

Coverage builds are useful for finding untested paths but add instrumentation and must not supply release benchmark numbers. Build and collect coverage separately with <code>MEMCP_COVERAGE=1</code> and <code>MEMCP_COVERDIR</code> when the goal is test quality.

MySQL is too slow

2026-08-28T10:14:01Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= MySQL is too slow? Evaluate MemCP Database =

If slow SQL queries are holding back an application, evaluate '''MemCP Database''' before accepting ever larger MySQL servers, more caches, or a separate OLAP copy as the only options. MemCP is an open-source, MySQL-protocol-compatible, compressed columnar database built for querying operational data in memory.

MemCP aims to be a fast, compact alternative for MySQL-style applications whose bottleneck is scanning, joining, grouping, sorting or aggregating operational data. It combines compressed column storage, late materialization, adaptive indexes, parallel shards and a Scheme-based query compiler.

That performance ambition is part of the product, even while MemCP remains Beta. It does not mean every current query beats mature MySQL: point workloads, connector behavior and application suites such as WordPress can still lose when a plan stays interpreted, misses a JIT fast path, compiles too much work or exercises an unsupported optimization. Those results identify roadmap work rather than changing MemCP's design goal.

== Observed performance profile ==

Project measurements have shown two deliberately different sides of the current system:

{| class="wikitable"
! Workload !! Observed result !! Interpretation
|-
| OLAP, aggregation, and search-oriented workflows || '''Speedups of 10× and more''' over MariaDB/PostgreSQL in measured cases || RecSets, compressed column scans, batching, late materialization, and parallel execution match the workload
|-
| Conventional OLTP application paths || Currently about '''1.3–2.0× the elapsed time''' in measured cases || Broader JIT coverage, compile cost, point paths, and connector compatibility remain optimization work
|-
| Complete WordPress- and wiki-style page builds || '''No significant difference in overall page-loading time''' in the measured application workflows || End-to-end impact depends on application work and on the number and mix of SQL queries
|-
| Filtered list over roughly one million documents || Around '''30 seconds on PostgreSQL''' versus '''1.6 seconds on MemCP''' for the same query || The search/list path is the urgent migration candidate even if the smaller OLTP path is not yet faster
|-
| Write-heavy tables using <code>ENGINE=logged</code> || About '''10× the write throughput''' of <code>safe</code> in project measurements || Avoids commit-time <code>fsync</code>, but recent committed writes can be lost on kernel crash or power failure
|}

The practical conclusion is not that every statement must move at once. Move the workflows where physical design dominates: filtered lists, search, aggregation, dashboards, and mixed operational analytics. Keep or shadow OLTP paths until their compatibility and latency are acceptable, then expand the migration as JIT and point-query execution improve.

These figures describe observed application and benchmark cases, not portable guarantees. The detailed record should name versions, queries, data distribution, hardware, durability, concurrency, cache state, successful response counts, and raw samples as required by [[Performance Measurement]].

Durability is part of the comparison. <code>logged</code> retains a WAL and survives a MemCP process crash, but it does not promise power-loss durability. For replaceable, flash-backed data, <code>sloppy</code> avoids a continuous WAL and normally publishes compressed storage every 15 minutes, reducing SD-card write pressure at the cost of losing the unrebuilt delta after an unclean stop. See [[Persistency and Performance Guarantees]].

== When MemCP can help ==

* dashboards repeatedly aggregate fresh transactional data;
* wide tables are queried through a small subset of columns;
* repeated categories, ranges or sequences compress well;
* analytical scans should run beside writes without maintaining a separate OLAP copy;
* SQL over HTTP or an embedded endpoint can remove an application/database network hop;
* reconstructible caches and durable business tables need different persistence trade-offs.

== Common SQL performance problems ==

These are the symptoms users usually see before they search for another database. Each one should be tested as a complete query and application workflow, not reduced to an isolated operator.

=== MySQL query slow on a large table ===

A query that was fast with thousands of rows may cross a threshold at millions of rows: a scan touches too many row pages, an index returns too many candidates, or fetching wide rows dominates after lookup. Adding another index can help one predicate but cannot turn a wide row store into a compact column scan.

MemCP reads only referenced columns, keeps suitable values compressed and selects a scan/index/RecSet path from cardinality and reuse costs. This is especially relevant when a page needs a few output columns from a wide document or event table.

=== PostgreSQL WHERE plus ORDER BY LIMIT is slow ===

The difficult shape is not a plain LIMIT. It combines selective or correlated filters with an order that favors another access path:

<pre>SELECT id, title, changed_at
FROM documents
WHERE tenant_id = ?
AND state IN ('open', 'review')
AND EXISTS (SELECT 1 FROM permissions p
WHERE p.document_id = documents.id
AND p.user_id = ?)
ORDER BY changed_at DESC
LIMIT 100;</pre>

A conventional optimizer may walk the ordering index and reject rows until it finds 100 matches, or materialize/filter a large intermediate result before sorting. MemCP can represent the permission/filter domain as a RecSet, intersect it with other boundaries, read ordered candidates in batches and stop when enough accepted rows have been emitted. See [[RecSets]], [[Scan]], and [[Advanced SQL Tutorial]].

=== GROUP BY, COUNT DISTINCT or dashboard query is slow ===

Repeated dashboards often aggregate a small number of columns across a large, recently updated relation. MemCP can scan compressed values in batches, compute shard-local partial aggregates, and cost reusable group caches or computed columns. The application continues to query current operational data instead of waiting for an ETL copy merely to answer a dashboard.

=== Search and filtered lists become slower as documents grow ===

Faceted lists combine permissions, tags, states, text/prefix filters, joins, counts, sorting and pagination. Their cost can grow with the candidate domain even though the user sees only 20 or 100 rows. This is the workflow in which project measurements have produced speedups of 10× and more over MariaDB/PostgreSQL. In one concrete list over roughly one million documents, the same query took around 30 seconds on PostgreSQL and 1.6 seconds on MemCP.

RecSets are designed to carry compact membership between those operators without copying wide result rows. Ordered batch acceptance and late materialization then avoid doing full output work for every candidate.

=== MySQL INSERT or UPDATE becomes slow because of fsync ===

If durable commit latency is the bottleneck, first batch statements within the application's atomicity budget. Where process-crash recovery is sufficient and power-loss risk is externally accepted, <code>ENGINE=logged</code> removes commit-time WAL synchronization and has reached about 10× <code>safe</code> write throughput in project measurements.

This is a durability choice, not a harmless tuning flag. Keep irreplaceable data on <code>safe</code>; see [[Persistency and Performance Guarantees]].

=== Database writes are wearing out a Raspberry Pi SD card ===

For reconstructible telemetry, caches, imports or derived state, <code>ENGINE=sloppy</code> avoids continuous WAL traffic and normally publishes a compressed generation during the 15-minute rebuild cycle. This batches flash writes and makes the possible loss of the unrebuilt delta explicit. Durable business records still belong on <code>safe</code> and appropriate storage media.

== Why it can be fast ==

MemCP reduces bytes moved through the cache hierarchy, reads columns in batches, delays row materialization, executes independent shards in parallel and can fuse filter, projection and aggregation. The planner decorrelates supported subqueries before choosing direct scans, adaptive indexes, RecSets, group caches, computed columns or ordered top-k paths. Supported Scheme hot paths can be compiled by the x86-64 JIT.

This architecture attacks common SQL-performance costs directly:

{| class="wikitable"
! SQL performance problem !! MemCP approach
|-
| Large scans move whole rows || Read only referenced compressed columns in batches
|-
| Repeated GROUP BY or correlated lookups || Cost reusable group caches, computed columns, indexes, or RecSets
|-
| ORDER BY builds a large temporary result before LIMIT || Use ordered scans, late materialization, top-k selection, and braking where semantics permit
|-
| Application-to-database round trips dominate || Use the MySQL protocol, SQL over HTTP, or a narrowly scoped in-database endpoint
|-
| One row-store must serve transactions and analytics || Keep recent writes in delta storage while scanning compressed main columns
|}

== Try the slow query on MemCP ==

Start an isolated instance with persistent storage:

<pre>docker volume create memcp_eval_data
docker run -d --name memcp-eval \
-e ROOT_PASSWORD='choose-a-password' \
-v memcp_eval_data:/data \
-p 127.0.0.1:4321:4321 -p 127.0.0.1:3307:3307 \
carli2/memcp:latest</pre>

Then import a copy of the relevant schema and data, run the real query through the same client path, and inspect its physical plan:

<pre>EXPLAIN PHYSICAL
SELECT ...;
</pre>

Do not stop at a synthetic <code>SELECT 1</code>. Test the query that users actually wait for, with representative cardinalities, concurrency, authentication, durability and cold/warm states. [[Migration from MySQL and PostgreSQL]] describes a staged evaluation; [[Advanced SQL Tutorial]] explains the optimizations visible in the plan.

== Evaluate honestly ==

Import a copy, validate results, restart and recovery, then benchmark the real authenticated workload against the same data and durability requirement. Record plans and compilation time as well as execution time. A current loss caused by missing JIT coverage, planner choice or compatibility work should be published with that diagnosis, not hidden and not generalized into a claim that MemCP cannot become faster for the workload.

The outcome should be actionable: if MemCP returns the same results, satisfies the required durability and operations gates, and improves the real workload, migrate that workload instead of continuing to tune around the limitations of an unsuitable physical design. Keep MySQL available as the source of truth or rollback path until those gates have been demonstrated.

== Frequently asked questions ==

=== Is MemCP a drop-in replacement for MySQL? ===

MemCP speaks the MySQL client protocol and implements a broad, tested SQL subset, but it is not a byte-for-byte implementation of every MySQL feature. Verify all application-critical queries, metadata calls, transactions, constraints and connector behavior. See [[Supported SQL]] and [[Database Tools compatibility with MemCP]].

=== Is MemCP an MCP server for AI assistants? ===

No. MemCP Database is a relational SQL database. It is unrelated to Model Context Protocol memory servers that happen to use a similar name.

=== Is MemCP related to memcpy()? ===

No. <code>memcpy()</code> is a C/C++ library function for copying memory. MemCP Database is a server and storage engine queried with SQL, HTTP, RDF or embedded Scheme interfaces.

Start with [[Comparison: MemCP vs. MySQL]], [[Migration from MySQL and PostgreSQL]], [[Advanced SQL Tutorial]], [[Performance Measurement]] and [[Current Status and Open Issues]].

Migration from MySQL and PostgreSQL

2026-08-28T10:14:01Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Migration from MySQL and PostgreSQL =

Migration is a compatibility and operations project, not only a data copy. MemCP can import through live MySQL/PostgreSQL connections or supported dump formats, while applications connect through the MySQL protocol or the HTTP SQL endpoints. It does not expose a PostgreSQL wire-protocol server and does not claim complete syntax, metadata, type, or administration compatibility with either source system.

Keep the source database authoritative until schema translation, row counts, constraints, representative queries, writes, timezone behavior, and restart recovery have been validated. Measure import duration and application latency with successful results, retain a rollback path, and separately design ongoing change capture when the source continues receiving writes during a staged migration.

[[File:Ports.svg|thumb|none|640px|MemCP client and import connection paths]]


== Live import ==

<pre>; MySQL: host/port nil use 127.0.0.1:3306
(mysql_import nil nil "import_user" "secret" "source_db" "target_db")

; PostgreSQL: database, source schema, target database
(psql_import nil nil "postgres" "secret" "source_db" "public" "target_db")</pre>
Optional trailing arguments select or rename individual tables. Use a least-privilege read-only source account and protect credentials from shell and process listings.


== Dumps ==

<code>load_sql</code> reads MySQL SQL; <code>load_psql</code> reads PostgreSQL SQL and supported pg_dump/archive inputs. A compressed input must be decompressed (<code>zcat</code>/<code>xzcat</code>), not passed through the <code>gzip</code>/<code>xz</code> compression functions.

<pre>gzip -dk dump.sql.gz
# Then in the MemCP console:
# (load_sql "target_db" (stream "dump.sql"))</pre>

For large or live migrations, prefer the importer functions above rather than assuming every vendor-specific dump statement is accepted.


== Application connection ==

<pre>$db = new PDO(
'mysql:host=127.0.0.1;port=3307;dbname=target_db',
'root',
'strong-password'
);
echo $db->query("SELECT 'it works'")->fetchColumn();</pre>
PostgreSQL SQL syntax is available over <code>/psql/<database></code>; MemCP does not expose a PostgreSQL wire-protocol port.


== Validation checklist ==

* compare schemas, types, defaults, indexes, constraints, triggers, and views;
* compare counts and checksums per table;
* replay representative reads and writes against both systems;
* verify AUTO_INCREMENT and timezone behavior;
* restart MemCP and repeat checks;
* measure import duration, query latency, errors, and CDC lag if live replication is used;
* retain a tested rollback path until cutover gates pass.

MemCP for Microservices

2026-08-28T10:14:01Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= MemCP for Microservices =

MemCP can combine database storage, SQL-over-HTTP, and custom Scheme handlers in one process. This removes an application-to-database network hop and can simplify small services, but it is an architectural option rather than a latency guarantee.

== Deployment patterns ==

{| class="wikitable"
! Pattern !! Benefit !! Trade-off
|-
| Ordinary service plus MySQL protocol || Familiar drivers, connection pools, and separation of concerns || Network/serialization hop remains
|-
| Service plus SQL over HTTP || Minimal client dependency and streamable JSONL || General SQL endpoint needs strict network and grant controls
|-
| Embedded Scheme handler || Prepared plans and storage access in one process; custom JSON or WebSocket API || Application failure and database failure share a process and deployment lifecycle
|}

Good candidates include small internal APIs, real-time dashboards, local/edge services, event lookup, and low-latency state whose data model remains relational. A large product with independent teams, many language-specific libraries, complex application logic, or strict process-isolation requirements may be easier to operate with a conventional separate service.

== Preparing work outside the request path ==

The repository's <code>apps/keyvalue.scm</code> example creates a table and parses fixed SELECT/upsert statements once during startup. Each request creates a session, binds the key/value, and evaluates the prepared formula. That avoids parsing and planning the same statement on every request while keeping caller values separate from SQL text.

<pre>
(set item_get (parse_sql "keyvalue"
"SELECT value FROM kv WHERE key = @key"))
(set item_set (parse_sql "keyvalue"
"INSERT INTO kv(key, value) VALUES (@key, @value)
ON DUPLICATE KEY UPDATE value = @value"))
</pre>

The complete example installs a request handler and starts its own HTTP listener:

<pre>
(createdatabase "keyvalue" true)
(eval (parse_sql "keyvalue"
"CREATE TABLE IF NOT EXISTS kv(key TEXT, value TEXT, UNIQUE KEY PRIMARY(key))"))

(set item_get (parse_sql "keyvalue" "SELECT value FROM kv WHERE key = @key"))
(set item_set (parse_sql "keyvalue"
"INSERT INTO kv(key,value) VALUES (@key,@value)
ON DUPLICATE KEY UPDATE value=@value"))

(define http_handler (lambda (req res) (begin
(set session (newsession))
(session "key" (req "path"))
(if (equal? (req "method") "GET")
(begin
(set resultrow (lambda (row) ((res "print") (row "value"))))
(eval item_get))
(begin
(session "value" ((req "body")))
(eval item_set)
((res "print") "ok"))))))

(serve 1266 (lambda (req res) (http_handler req res)))
</pre>

Run the maintained repository version with <code>./memcp apps/keyvalue.scm</code>, store a value using <code>curl --data-binary value http://localhost:1266/key</code>, and retrieve it with <code>curl http://localhost:1266/key</code>. This demonstration intentionally omits production authentication and limits; do not expose it unchanged.

Use SQL constraints and one-statement atomic patterns even inside an embedded handler. In-process access removes transport overhead; it does not remove concurrent requests, transaction conflicts, authorization, or durability decisions.

== Historical HTTP microbenchmark ==

An early AMD Ryzen 9 7900X3D run used ApacheBench against a tiny embedded endpoint with concurrency 10. It reported 1,000,000 completed requests, zero failures, 42.083 seconds, 23,762.78 requests/s, 0.421 ms mean request latency (0.042 ms across concurrent requests), p95 1 ms and maximum 2 ms. The author also observed that <code>ab</code> saturated one client core while MemCP used roughly 20% across other cores.

The original record does not include the exact command, commit, response validation beyond ApacheBench's failure count, server build or repeat runs. It demonstrates that the embedded path can be very small, but it is not a database-query or current-release throughput guarantee. A new comparison must include the handler code, successful body validation, client/server CPU profiles and several raw samples.

== Production checklist ==

Choose each table ENGINE according to data-loss tolerance, configure total and persistent RAM budgets, and provide health checks, backups, monitoring, and restart validation. Set a strong root password before exposing either the HTTP or MySQL port. Persistent data should use <code>safe</code> unless the documented risks of another engine are explicitly acceptable.

Handler latency depends on query shape, compilation and plan-cache state, concurrency, memory pressure, and storage reloads. Benchmark the full authenticated request path with successful-response validation instead of quoting a fixed sub-millisecond figure.

Run embedded services under a supervisor, use <code>--no-repl</code>, implement external liveness/readiness checks, limit bodies and queues, propagate cancellation, and test restart/restore. Scaling the process horizontally requires an explicit ownership, replication, or routing design; the current local shard engine is not an automatic multi-node database.

See [[In-Database WebApps and REST Services]], [[Persistency and Performance Guarantees]], [[Memory Management and Eviction]], [[Security and Authentication]], and [[Performance Measurement]].

MemCP Console

2026-08-28T10:14:00Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= MemCP Console =

The interactive Scheme console is enabled when MemCP starts without <code>--no-repl</code>:

<pre>
./memcp lib/main.scm
</pre>

Run an expression non-interactively with:

<pre>
./memcp -c '(help)' lib/main.scm
</pre>

Background and service deployments must use <code>--no-repl</code>; otherwise closing standard input ends the REPL and may stop the process. Capture stdout/stderr through the service manager and use the dashboard, processlist, and configured logs for operations.

Authorized MySQL sessions may expose an administrative Scheme parser mode when that capability is intentionally enabled. Scheme execution has direct process and storage access and must not be offered to untrusted database users. See [[Introduction to Scheme]], [[Security and Authentication]], and [[Dashboard and Operations]].

Lists and Objects

2026-08-28T10:14:00Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Lists and Objects =

Lists are central to MemCP's Scheme dialect: they represent ordinary collections, associative objects and executable code. This tutorial explains the programming model; [[Lists]] and [[Associative Lists / Dictionaries]] provide the generated function reference.

== List values ==

A quoted list is data. Mapping and filtering return new lists; reduction combines them into one value.

<pre>
'(1 2 3)
(cons 0 '(1 2 3)) /* (0 1 2 3) */
(append '(1 2 3) 4 5) /* (1 2 3 4 5) */
(has? '(1 2 3) 2) /* true */
(filter '(1 2 3) (lambda (x) (< x 3))) /* (1 2) */
(map '(1 2 3) (lambda (x) (* x 2))) /* (2 4 6) */
(reduce '(1 2 3) + 0) /* 6 */
</pre>

Choose a correct neutral value and an associative reducer when work may run in parallel. Order-sensitive string concatenation, floating-point sums and callbacks with I/O require particular care.

== Associative lists as objects ==

An object is a flat list of alternating keys and values. Calling it with a key performs lookup. Updates are functional: <code>set_assoc</code> returns a new value instead of mutating the old list.

<pre>
(set obj '("a" 1 "b" 2 "c" 3))
(obj "a")
(set obj (set_assoc obj "a" 5))
(filter_assoc obj (lambda (key value) (not (equal? key "c"))))
(map_assoc obj (lambda (key value) (* value 2)))
(reduce_assoc obj (lambda (acc key value) (+ acc value)) 0)
</pre>

Use <code>has_assoc?</code> when absence differs from a stored <code>nil</code>. Request objects, JSON objects, SQL result rows and sessions may expose a similar callable lookup style, but they are not necessarily represented by the same physical type.

== Lists as generated code ==

Scheme code is list-shaped data. Quoting controls which computation happens while building a program and which is delayed until <code>eval</code>.

<pre>
(set program '('print "Hello World"))
(eval program)
(set add_expression '('+ 4 5))
(eval add_expression) /* 9 */
</pre>

Symbols naming delayed procedures remain quoted. Values that must be computed while building the outer expression are embedded without an extra quote. Generated lambdas need a quoted <code>lambda</code> symbol, a parameter list and exactly one body; use <code>begin</code> for several forms.

The optimizer may fuse safe list operations or JIT-compile supported hot procedures, but observable functional semantics remain unchanged. See [[Introduction to Scheme]], [[JIT Compilation]] and [[Parallel Computing]].

Introduction to Scheme

2026-08-28T10:13:59Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Introduction to Scheme =

MemCP embeds a small Scheme dialect used by the SQL parsers, query planner, modules, and application endpoints. Start the interactive console with <code>./memcp lib/main.scm</code>; background services use <code>--no-repl</code>.

Scheme represents calls and code with the same list syntax. That makes the dialect well suited to MemCP's query compiler: the SQL planner constructs Scheme programs as data, optimizes them, and evaluates or compiles them.

The console prints each expression result, which makes the evaluation model easy to explore:

<pre>> 12
= 12
> (+ 1 2 3)
= 6
> (+ 2 (* 2 4))
= 10
> (concat "Hello " "World")
= "Hello World"</pre>

Startup also loads the configured data directory and Scheme modules before opening the SQL/HTTP listeners. The exact banner and number of startup tests change between releases, so scripts should not parse that text.

== Values and calls ==

<pre>
(+ 1 2)
(concat "hello " "world")
(map '(1 2 3) (lambda (x) (* x x)))
</pre>

Numbers, booleans, strings, symbols, lists, functions, sessions, and storage objects are ordinary values. A list in expression position is a call: its first item is the procedure and the rest are arguments. Whitespace separates forms; parentheses define nesting.

{| class="wikitable"
! Form !! Meaning
|-
| <code>42</code>, <code>3.5</code>, <code>true</code>, <code>"text"</code> || Literal values
|-
| <code>(+ 1 2)</code> || Call <code>+</code> with two arguments
|-
| <code>'name</code> || The symbol <code>name</code>, not the value bound to it
|-
| <code>'(1 2 3)</code> || Construct a list value instead of calling <code>1</code>
|-
| <code>'("name" "Ada" "active" true)</code> || Flat key/value list used as an associative object
|}

== Quoting and generated code ==

Use quoting to build code or data: <code>'(+ 4 5)</code> constructs delayed code, while <code>(eval '(+ 4 5))</code> evaluates it. Values that should be computed now can be embedded into a quoted outer form; procedure symbols whose execution is delayed remain quoted. This distinction matters throughout the query planner.

<pre>
(set delayed '(+ 4 5))
(eval delayed) /* 9 */
(map '(1 2 3) (lambda (x) (* x x)))
</pre>

Do not add or remove parentheses by visual guesswork in large generated expressions. Repository Scheme files are formatted and checked with <code>python3 tools/lint_scm.py</code>.

== Functions and scope ==

Lambdas have exactly a parameter list and one body. Use <code>begin</code> when that body needs several forms:

<pre>
(define greet (lambda (name) (begin
(print "greeting " name)
(concat "Hello, " name))))

(greet "Ada")
</pre>

Functions are closures and may be passed to <code>map</code>, reducers, scanners, parsers, and handlers. <code>define</code> and <code>set</code> create a binding in the current scope in this dialect; <code>set</code> is not an imperative mutation of an outer lexical variable.

== Pattern matching and modules ==

<code>match</code> is the usual way to express alternatives and destructure lists, strings, or regular-expression results. Modules are loaded with <code>import</code>; application entrypoints typically import the required library files and then install handlers or start a server.

<pre>
(match value
'(x y) (+ x y)
(regex "^/users/([0-9]+)$" path id) id
false)
</pre>

See [[Parsers]], [[Lists and Objects]], and the matching examples in the repository for the exact pattern forms.

== State and concurrency ==

The dialect is functional by default. <code>set</code> defines a binding in the current scope; it does not imperatively mutate an outer binding. Use <code>(newsession)</code> when code intentionally needs a thread-safe mutable key/value context shared across parallel work.

<pre>
(set request_state (newsession))
(request_state "user_id" 42)
(request_state "user_id") /* 42 */
</pre>

Independent functional work may run in parallel. Supported hot procedures can be compiled by the native x86-64 JIT; unsupported shapes remain interpreted without changing language semantics. See [[Full SCM API documentation]], [[Parallel Computing]], and [[In-Database WebApps and REST Services]].

== Where to continue ==

* [[Full SCM API documentation]] lists generated chapters and signatures.
* [[Lists and Objects]] explains lists, associative objects, mapping, and reduction.
* [[IO]] covers files, HTTP request/response objects, and servers.
* [[Storage]] documents the low-level table and scan interface; application code normally starts with SQL.
* [[JIT Compilation]] and [[Parallel Computing]] explain runtime optimization boundaries.

Introduction to RDF

2026-08-28T10:13:59Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Introduction to RDF =

RDF represents facts as subject–predicate–object triples. MemCP stores and queries triples through its RDF module and exposes a tested SPARQL subset over HTTP.

Unlike a fixed relational row, a resource can gain another predicate without altering a table schema. IRIs identify resources and predicates; objects may be another resource or a literal. For example, one person can have a type, name, mailbox, and <code>knows</code> edge:

{| class="wikitable"
! Subject !! Predicate !! Object
|-
| <code>item:1</code> || <code>rdf:type</code> || <code>foaf:Person</code>
|-
| <code>item:1</code> || <code>foaf:name</code> || <code>"Ada"</code>
|-
| <code>item:1</code> || <code>foaf:knows</code> || <code>item:2</code>
|}

SPARQL graph patterns join triples by shared variables. RDF is useful for heterogeneous metadata, semantic relationships, knowledge graphs, or integrations that already exchange Turtle/RDF. A conventional SQL schema is often simpler for stable, strongly constrained business records.

Submit a query to <code>/rdf/<database></code> using HTTP Basic authentication. Load Turtle through <code>/rdf/<database>/load_ttl</code>. Supported behavior includes core triple patterns, selected FILTER expressions and escaping, OPTIONAL left-join behavior, and tested update templates.

<pre>
curl --fail-with-body -u root:strong-password \
--data-binary 'SELECT ?s ?p ?o WHERE { ?s ?p ?o . } LIMIT 20' \
http://localhost:4321/rdf/mygraph
</pre>

Do not infer support for an unlisted SPARQL feature from the standard. Validate application queries and malformed-input behavior against the deployed version. See [[Advanced Graph Querying]] and [[Security and Authentication]].

The external [https://github.com/launix-de/rdfop rdfop project] contains a larger RDF browser and templating application built around the same model.

Integer Compression

2026-08-28T10:13:59Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Integer Compression =

MemCP can store an integer column relative to its minimum and bit-pack the remaining range. A range of eight values needs three value bits instead of a fixed 32- or 64-bit slot. Constant, sparse, low-cardinality, and arithmetic-sequence representations may win for other distributions.

== Frame-of-reference layout ==

The compact structure is easiest to understand without the later batch and JIT optimizations:

* find <code>minimum</code> and <code>maximum</code> inside one rebuilt shard;
* store <code>offset = minimum</code>;
* choose <code>bitwidth = ceil(log2(maximum - minimum + 1))</code> (plus a code when the chosen NULL representation needs one);
* allocate enough 64-bit words for <code>row_count × bitwidth</code> bits;
* encode row <code>item_id</code> at bit position <code>item_id × bitwidth</code> as <code>value - offset</code>.

<pre>type packedFrame struct {
offset int64
bitwidth uint8
words []uint64
}

bitpos := uint64(itemID) * uint64(frame.bitwidth)
word := bitpos / 64
shift := bitpos % 64
// read/write the value bits; a value may cross into words[word+1]</pre>

This is a frame-of-reference, fixed-bit packed representation sometimes discussed alongside FOR/PFOR techniques. The current <code>StorageInt</code> does not need a classic Patched-FOR exception list: the observed shard range determines one lossless width, while sparse/default and sequence wrappers handle distributions for which another representation wins. See also [https://github.com/lemire/FastPFor FastPFor] and [https://wwwdb.inf.tu-dresden.de/wp-content/uploads/T_2014_Master_Patrick_Damme.pdf Patrick Damme's work on lightweight compression].

For observed minimum <code>min</code> and maximum <code>max</code>, the encoder stores <code>value - min</code> using enough bits for the complete range. Packed values cross 64-bit word boundaries when necessary; batch readers share bit-position work across consecutive RecordIDs. Binary values can therefore approach one payload bit per row, while IDs and local timestamp ranges use only the width their shard actually needs.

Representation selection happens per rebuilt physical column. Scan code uses range and multi-record batch access so unpacking remains sequential and cache-friendly. Delta values written since the last rebuild are merged with main storage visibility by the scan path.

== NULLs, defaults, and alternatives ==

NULL/default-heavy columns can store exceptions sparsely instead of paying a full value slot for every row. A constant column needs no per-row payload, while a low-cardinality column may use dictionary or entropy-oriented identifiers. A regular counter or timestamp can be represented as arithmetic runs. The analyzer chooses among these options; “integer column” therefore does not imply one fixed byte width.

When a packed range has a spare code, an implementation can reserve it for NULL; distributions where that would increase width may favor a sparse/default wrapper instead. The important contract is lossless SQL NULL round-trip, not one mandatory representation. Format choice is made from the actual shard values during rebuild.

The reduced decision looks like this:

<pre>if value == nil {
hasNull = true
return
}
observe(value)

// During final width selection, reserve a code for NULL if it fits.
// Otherwise choose a lossless wrapper/representation that keeps NULL separate.</pre>

Example: values from 1,000 through 1,031 need five payload bits after subtracting 1,000. Adding a nullable sentinel may require another code, while a column with only a handful of NULL rows can encode those exceptional RecordIDs separately.

For random values spanning most of a 64-bit range, bit packing may save little. Sorting or clustering can create smaller local ranges and longer sequences, but changing application order only for compression must be weighed against write behavior and query access paths.

Space and speed depend on range width, null/default density, ordering, shard size, and access pattern. Publish the dataset and storage statistics with any ratio. Persistent layout changes must preserve old magic/version readers.

In one early workload, nullable columns that had fallen back to the generic representation contributed to a reported 23 MiB footprint; after NULL-capable integer packing, the same workload was reported at 16 MiB. The old article described that as “40% savings”; arithmetically it is about a 30% reduction from 23 MiB. The dataset and measurement method were not preserved, so the result documents why the NULL code mattered but is not a current benchmark guarantee.

See [[Columnar Storage]], [[Sequence Compression]], and [[Performance Measurement]].

Install MemCP with Docker

2026-08-28T10:13:58Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Install MemCP with Docker =

<pre>docker pull carli2/memcp:latest
docker volume create memcp_data
docker run -d --name memcp --restart unless-stopped \
-e ROOT_PASSWORD='replace-with-a-strong-password' \
-v memcp_data:/data -p 4321:4321 -p 3307:3307 \
carli2/memcp:latest</pre>
<code>ROOT_PASSWORD</code> is used only for a fresh <code>/data</code>. The image runs with <code>--no-repl</code>, persists under <code>/data</code>, and forwards stop signals to MemCP. Pin a release tag in production instead of <code>latest</code>. Do not publish either port to an untrusted network without firewall/reverse-proxy protection.

Pass additional flags through <code>PARAMS</code>, for example <code>-e PARAMS='--disable-mysql --api-port=4321'</code>. <code>APP</code> selects the Scheme entry file and defaults to <code>lib/main.scm</code>.

Back up the volume before upgrading. Verify the container log, authenticate a query, restart the container, and verify persisted data after every upgrade.

== Build and application development ==

To test the image produced by the current checkout instead of the published image:

<pre>git clone https://github.com/launix-de/memcp.git
cd memcp
docker build -t memcp-local .
docker volume create memcp_dev_data
docker run --rm -it \
-e ROOT_PASSWORD='development-password' \
-v memcp_dev_data:/data -p 4321:4321 -p 3307:3307 \
memcp-local</pre>

Interactive mode is useful for Scheme application development; production containers should remain detached and use <code>--no-repl</code>. To load another Scheme entrypoint, mount or bake the application into the image and select it with <code>APP</code>. Use <code>PARAMS</code> only for command-line flags, not as a replacement for the application filename.

Bind mounts such as <code>-v /var/lib/memcp:/data</code> are also supported. Ensure the container user can write the directory and include it in backup and restore procedures.

Index Compression

2026-08-28T10:13:58Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Index Compression =

MemCP indexes map ordered or matched values to compact record-ID sets and ranges. Main index state is stored with the rebuilt shard; an ordered index-local delta structure covers newer rows. Iteration merges both while respecting deletions, transaction visibility, and requested direction.

Traditional trees store keys, child pointers and node slack. MemCP can exploit an immutable shard generation differently: sort a compact permutation of RecordIDs by the indexed columns, then binary-search column values through that permutation. Inserts do not splice into the immutable main array; the delta index covers them until rebuild. Wide strings therefore need not be copied into every tree entry.

For a shard whose RecordIDs fit in 16 bits, 60,000 permutation entries need roughly 120 kB before surrounding metadata. Actual size varies with representation, shard size and auxiliary boundaries; inspect current statistics instead of treating that illustration as a guarantee.

The essential data structure can be read from this reduced historical sketch. Current code adds transactions, delta ordering, richer boundary types and cost evidence, but the pointer-free permutation remains the central idea:

<pre>type StorageIndex struct {
columns []string
savings float64
sortedItems StorageInt // RecordIDs sorted by column values
inactive bool // collect evidence before building
}</pre>

Rows compare through <code>columns[0]</code>, then <code>columns[1]</code>, and so on. A lookup binary-searches the compressed RecordID permutation and reads the indexed column values from column storage. The index therefore avoids storing a second copy of wide keys and avoids one heap pointer per entry.

The planner can build adaptive equality, range, prefix, computed-expression, and compact membership structures when observed workload and cost justify them. RecSets may carry exact record sets or safe candidate supersets; candidate sets retain a residual predicate. Index selection is a physical decision after logical decorrelation and join ordering.

== Boundary extraction and reuse ==

Conjunctions can provide equality prefixes followed by at most one useful ordered range, for example <code>a = ? AND b = ? AND c BETWEEN ? AND ?</code>. Canonical column/expression identities allow related queries to share an index. A longer compatible index can serve a shorter prefix, avoiding a second physical structure. LIKE-prefix and other inexact matchers may return candidates and keep the original predicate as proof.

== Adaptive build decision ==

A syntactically possible index starts as an opportunity, not an immediate allocation. The analyzer estimates full-scan cost, build cost, indexed probe cost, shard population and expected reuse. Evidence accumulates until the projected savings amortize construction. Small shards may never build; ORDER/LIMIT benefit is weighted by the number of rows it can actually avoid.

An early postcode experiment recorded an 8.275 ms full scan, an 18.188 ms scan that also built the index, followed by indexed lookups of 43.871, 45.681 and 39.021 µs. That observation motivated the original “build on the second use” heuristic. It lacks a current commit, dataset details, repetitions and result validation, so the figures are retained as design history rather than a current 200× claim. The current cost model is richer: creation should be paid for by observed future work, not triggered by every possible WHERE clause.

Index size and performance depend on cardinality, clustering, fingerprint/range choices, update rate, and query distribution. A B-Heap layout was proposed to improve cache locality while traversing a tree; it remains an interesting future representation, not part of the supported current index. Persisted index formats obey the same permanent magic/version compatibility contract as columns.

See [[Data Auto Sharding and Auto Indexing]], [[Scan]], and [[Query Planner and Physical Lowering]].

In-Database WebApps and REST Services

2026-08-28T10:13:58Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= In-Database WebApps and REST Services =

Embedded handlers run in the MemCP process and can access Scheme and storage APIs without a separate database connection. <code>lib/main.scm</code> defines <code>http_handler</code>; application modules can wrap the previous handler and route only their own path prefix.

This is useful for small JSON APIs, dashboards, webhooks, static assets, or WebSocket gateways whose hot path is mostly database work. It reduces deployment pieces and transport overhead, but also couples application code to the database process. Keep CPU-heavy, blocking, untrusted, or independently deployed workloads outside MemCP.

== Routing a path prefix ==

<pre>
(define http_handler (begin
(set old_handler http_handler)
(lambda (req res) (begin
(match (req "path")
(regex "^/my-api/(.*)$" path rest) (begin
((res "header") "Content-Type" "application/json")
((res "status") 200)
((res "print") "{\"ok\":true}"))
(old_handler req res))))))
</pre>

Always delegate unmatched paths to the previous handler. Replacing the global handler without chaining it can hide the dashboard, SQL endpoints, or routes installed by other modules.

The request object exposes method, host, path, query fields, headers, username/password, remote address, and lazy body readers. The response object sets headers and status, writes text/lines or JSONL rows, and can upgrade a connection to WebSocket. See [[IO]] for the generated function reference and <code>apps/</code> for executable examples.

== Prepared SQL and response streaming ==

Parse and optimize fixed SQL outside the request hot path where practical; bind request values rather than concatenating untrusted SQL. The exact request/response functions are documented in [[IO]] and examples under <code>apps/</code>.

<pre>
(set find_user (parse_sql "myapp"
"SELECT id, name FROM users WHERE id = @user_id"))

/* inside a request handler */
(set session (newsession))
(session "user_id" requested_id)
(set resultrow (res "jsonl"))
(eval find_user)
</pre>

Prepared formulas capture a fixed query shape, not permission to trust their parameters. Validate request types, authorize the selected record, and do not let callers choose arbitrary identifiers. Stream rows when possible; do not collect an unbounded result merely to turn it into one JSON array.

The RDF frontend follows the same preparation pattern with <code>parse_sparql</code>. Prepare a fixed SPARQL shape outside the handler, bind request-specific values through the intended context and stream results through the response. Do not concatenate caller text into either SQL or SPARQL.

== Error handling and service boundaries ==

Set the content type and status before writing the body. Convert expected validation and not-found cases into explicit 4xx responses; log unexpected failures without returning stack traces, SQL, secrets, or internal paths. Define request timeouts and body limits at the reverse proxy and inside application logic.

== Security and lifecycle ==

Handlers execute with in-process capabilities. Authenticate before database access, validate paths, headers and bodies, enforce request-size limits, and never expose default <code>root/admin</code> credentials. Bound queues and long operations, propagate cancellation, and avoid retaining request objects after completion. Use <code>--no-repl</code> for daemon deployment. See [[Security and Authentication]], [[SQL over REST]], and [[Websockets in MemCP]].

Minimal maintained examples live below <code>apps/</code>. The external [https://github.com/launix-de/rdfop rdfop project] is a larger RDF browser and templating example; treat it as application source, not as part of MemCP's compatibility contract.

Deployment

2026-08-28T10:13:57Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Deployment =

This page covers a single-node MemCP service from build through shutdown and upgrade. A production deployment must choose a durability ENGINE, protect credentials and listeners, provide a writable data directory or configured remote backend, and install the executable together with the exact matching Scheme libraries and assets.

MemCP is currently Beta. Treat deployment as an operational workflow rather than merely starting a binary: establish backups, restart tests, resource limits, monitoring, upgrade rollback, and application-level compatibility checks before accepting production traffic. Multi-node ownership and failover are not yet provided by the [[Cluster Monitor]].


== Build and run ==

<pre>git clone https://github.com/launix-de/memcp
cd memcp
go mod download
make
./memcp -data /var/lib/memcp --no-repl \
--api-port=4321 --mysql-port=3307 \
--root-password='replace-on-first-start' lib/main.scm</pre>
<code>--root-password</code> initializes a fresh data directory only. Never expose the HTTP or MySQL port using <code>root/admin</code>. Put the service behind the intended firewall or reverse proxy and protect the data directory and configuration file.


== Interfaces ==

{| class="wikitable"
|-
! Interface
! Default
|-
| HTTP SQL/dashboard
| TCP 4321
|-
| MySQL protocol
| TCP 3307
|-
| MySQL Unix socket
| <code>/tmp/memcp.sock</code>
|-
| Data directory
| <code>./data</code>
|}

Disable unused listeners with <code>--disable-api</code>, <code>--disable-mysql</code>, or an empty socket setting. Use <code>--config=FILE</code> for one argument per line; explicit command line options override configuration entries.


== Service installation ==

MemCP supplies <code>make install</code>, Debian and RPM package targets, systemd lifecycle scripts, and <code>make memcp.sif</code> for Singularity/Apptainer. Packages install the binary and Scheme libraries in system paths and provide service configuration. Review paths and credentials before starting the service.

For a source checkout, PM2 remains a lightweight supervisor option:

<pre>pm2 start ./memcp --name memcp -- \
--no-repl -data /var/lib/memcp \
--api-port=4321 --mysql-port=3307 lib/main.scm
pm2 save</pre>

After rebuilding, restart the named process with <code>pm2 restart memcp</code>. Keep the binary, <code>lib/</code> and <code>assets/</code> from the same commit. Docker is the other common deployment path; it consumes additional image/runtime resources but provides a reproducible bundle and a persistent <code>/data</code> volume. See [[Install MemCP with Docker]].


== Shutdown and upgrades ==

SIGTERM/SIGINT stops new work, drains in-flight requests up to <code>ShutdownDrainSeconds</code>, and closes storage. Before upgrading persistent data:

# back up the data directory or remote backend;
# read serialization and durability notes for the target release;
# stop MemCP cleanly;
# install the new binary and matching <code>lib/</code>/<code>assets/</code> files;
# start it and verify logs, schema, representative reads/writes, and restart.

Do not copy only the binary while retaining incompatible Scheme libraries. See [[Install_MemCP_with_Docker|Install MemCP with Docker]], [[Storage_Backends|Storage Backends]], and [[Persistency_and_Performance_Guarantees|Persistency and Performance Guarantees]].

Database Tools compatibility with MemCP

2026-08-28T10:13:56Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Database Tools compatibility with MemCP =

MemCP speaks the MySQL wire protocol over TCP and a Unix socket. Compatibility depends on both SQL behavior and the metadata queries issued by a particular client version.

{| class="wikitable"
! Tool !! Connection !! Current status !! Notes
|-
| MySQL CLI || MySQL protocol || Supported || Core SQL, prepared statements, SHOW metadata, and process control
|-
| PDO MySQL || MySQL protocol || Supported || Use the MySQL PDO driver
|-
| DBeaver || MySQL driver || Supported with limitations || Retest schema editing and metadata against the concrete release
|-
| PHPMyAdmin || MySQL driver || Compatibility target || Do not claim a version supported without a dated test run
|-
| VS Code SQLTools || MySQL/MariaDB driver || Supported with limitations || No dedicated MemCP extension is required
|}

MemCP implements prepared statements, selected <code>INFORMATION_SCHEMA</code> relations, SHOW metadata, processlist, TCP, and a Unix socket. It does not claim byte-for-byte MySQL server behavior. Record the MemCP commit, client version, connection mode, and failed metadata query when reporting incompatibility.

== Connecting a tool ==

Select a MySQL or MariaDB driver, use the configured host and MySQL port (3307 in the documentation examples), and authenticate with a least-privilege MemCP user. Disable driver features that require an unsupported server capability rather than assuming the connection failure is network-related. For the command line:

<pre>
mysql --protocol=TCP -h 127.0.0.1 -P 3307 -u app_user -p myapp
</pre>

When a GUI opens a connection but fails while browsing schemas, capture its first failing metadata statement and reproduce that SQL through the CLI. Tool compatibility often depends on those discovery queries rather than ordinary SELECT/INSERT behavior.

''Matrix reviewed against MemCP commit <code>c42e19eba</code> on 27 August 2026; individual GUI versions still require concrete verification.''

Current Status and Open Issues

2026-08-28T10:13:56Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Current Status and Open Issues =

<blockquote>'''Verified against commit <code>c42e19eba</code> on 27 August 2026.''' Update this marker whenever the implementation-facing inventory is refreshed.
</blockquote>
MemCP is '''Beta'''. Core SQL, persistence, compression, parallel scans, REST, and MySQL-protocol access are usable, but compatibility and complex planner cases continue to evolve.

MemCP's stated ambition remains a '''fast, compact database for mixed OLTP and OLAP'''. Measured OLAP and search-oriented workflows have reached '''speedups of 10× and more over MariaDB/PostgreSQL''', particularly where RecSets and compressed column scans avoid wide intermediate rows. In one filtered-list workflow over approximately one million documents, the same query took around '''30 seconds on PostgreSQL''' and '''1.6 seconds on MemCP'''. Isolated OLTP paths currently take about '''1.3–2.0× as long''', but in complete WordPress- and wiki-style page builds this has made '''no significant difference to overall page-loading time''' in the measured application workflows. The OLTP gap is tracked as JIT, compilation, point-path and compatibility work rather than used to dilute the search/analytics performance proposition.

For write-heavy workloads, project measurements have reached about '''10× <code>safe</code> throughput''' with <code>ENGINE=logged</code>. That result is inseparable from its durability trade-off: the WAL survives a process crash but is not synchronized against kernel crash or power loss. <code>sloppy</code> instead publishes compressed main storage on the normal 15-minute rebuild cycle, which can protect SD-card lifetime for reconstructible data while accepting loss of the unrebuilt delta.

== Readiness by area ==

{| class="wikitable"
! Area !! Current position !! What adopters should verify
|-
| SQL applications || Broad tested relational subset, views, CTEs/subqueries, UNION, windows, DML/DDL, constraints, triggers, and JSON || Every production query, collation/type edge, error expectation, and metadata query
|-
| Transactions/durability || Implicit/explicit transactions, cursor stability, snapshot/OCC infrastructure, per-table engines, WAL/replay || Required isolation/conflict patterns, power-loss behavior, backup and restore
|-
| Analytics || Compressed columns, batch/parallel scans, grouping/windows, adaptive indexes and reusable physical structures || Plans, memory, concurrent write latency, warm/cold performance
|-
| Interfaces || MySQL TCP/socket, SQL/HTTP, PostgreSQL-syntax HTTP, RDF, Scheme/custom HTTP/WebSocket || Concrete connector/tool versions, authentication, protocol assumptions
|-
| Operations || Dashboard, processlist/cancellation, settings, logs, packaging, local/S3/Ceph storage || Monitoring, capacity, upgrades, restore drills, backend outages
|-
| Scale-out || Local sharding and parallel execution within one instance || Multi-node cluster/HA is not implemented as a production feature
|}

This page intentionally does not duplicate a static TODO list. Current bugs, planned work, and release blockers live in the [https://github.com/launix-de/memcp/issues GitHub issue tracker].

== Product and engineering roadmap ==

The repository also contains design documents for longer-term work. They describe direction and invariants, not promised release dates:

* '''Broader JIT and vectorized execution:''' compile more scan filters, expressions, storage decoders and reducers into tight architecture-specific loops while retaining safe interpreter fallback.
* '''Lower compile time:''' bounded planner traversals, structural sharing and recipe-based physical emission for very large application-generated queries.
* '''Remote SQL tables:''' treat MySQL/PostgreSQL sources as scannable relations with explicit capability discovery, predicate/projection pushdown, transaction limits and safe fallback.
* '''Vector search:''' combine scalar partition boundaries with local vector “bubble” clusters and hierarchical top-k search.
* '''Cluster mode:''' leaderless CRUSH directory assignment, MOESI-inspired shard caches, RADOS persistence and remote partial scans; a later distributed catalog removes global registries for extremely large deployments.
* '''Specialized indexes and [[RecSets]]:''' choose ranges, sparse IDs, bitmaps, prefix/full-text and computed access paths according to observed density and cost.

See [[Cluster Monitor]], [[Query Planner and Physical Lowering]], [[RecSets]], [[JIT Compilation]], [[Vectors]] and [[Data Auto Sharding and Auto Indexing]].


== Implemented since the old status page ==

* per-table <code>ALTER TABLE … ENGINE</code> transitions;
* configurable LRU memory management and system-pressure eviction;
* AUTO_INCREMENT persistence and tested unique/not-null/foreign-key behavior;
* main/delta ordered index merging and LIKE/pattern access improvements;
* persistent SQL triggers and computed-column invalidation hooks;
* cursor-stability transactions plus ACID snapshot/OCC infrastructure;
* UNION, windows, views, broad subquery decorrelation, and join reordering;
* cost-based physical lowering, RecSets, group caches, ORC, and plan variants;
* native x86-64 Scheme JIT for supported procedures;
* processlist, query cancellation, dashboard, metrics, logs, and packaging;
* S3-compatible and optional Ceph/RADOS persistence;
* MySQL/PostgreSQL live import and PostgreSQL dump/archive import.
* native BSON-backed MySQL and PostgreSQL JSON support, including path access, mutation, constructors, aggregates, relational expansion, and computed-index acceleration.

== Outcome of the earlier roadmap ==

The former status page was also a design record. The compact inventory below preserves what those concrete proposals became instead of silently deleting them when they left the TODO list.

{| class="wikitable" style="width:100%;"
! Earlier proposal !! Outcome
|-
| <code>ALTER TABLE … ENGINE</code> || Implemented for all five engines; persisted → <code>memory</code> is deliberately destructive and documented as such.
|-
| LRU cleanup for temporary columns || Expanded into budgets and eviction for reloadable columns, indexes, computed data, keytables, cache tables and dictionaries; memory-engine rows remain non-evictable.
|-
| Change hooks for computed columns || Implemented through SQL triggers and internal dependency/invalidation triggers for computed, grouped, prejoined and ordered data.
|-
| Enforced foreign keys || Implemented for tested CREATE TABLE RESTRICT, CASCADE and SET NULL paths; some ALTER compatibility forms remain non-operative.
|-
| One memory-mapped database-sized key/value file || Not adopted. MemCP keeps versioned per-column files and WALs, with optional object backends; selected subsystems may still use mmap internally.
|-
| Merge sorted index delta data with the main index || Implemented with a compressed main permutation plus an ordered index-local delta structure.
|-
| One multi-table <code>scan_star</code>/merge-join operator || Superseded by reordered logical join trees and costed nested, indexed, ordered, RecSet, cached and parallel physical paths.
|-
| Process IDs, request context and kill switch || Implemented through PROCESSLIST, dashboard controls, KILL QUERY/CONNECTION and cancellation-aware waits.
|-
| Transaction insert/delete overlays and conflicts || Expanded into cursor-stability handling plus snapshot/OCC infrastructure, rollback, visibility and commit conflict checks.
|-
| LIKE indexes || Implemented as prefix/match boundaries with residual predicates whenever the boundary is only a candidate set.
|-
| SPARQL updates and OPTIONAL || Implemented for the tested DELETE/INSERT/WHERE, template and OPTIONAL subset; this is not complete SPARQL coverage.
|-
| AUTO_INCREMENT persistence || Implemented, including restart and ALTER behavior.
|-
| Convert subqueries into joins || Expanded into logical decorrelation of scalar, IN/NOT IN and EXISTS/NOT EXISTS forms before join ordering.
|-
| Shard group tables and prejoin complex plans || Implemented as costed physical alternatives alongside direct scans, FK/PK reuse, group caches and RecSets.
|-
| Restrict users to databases || Implemented through system users, grants, revokes and frontend enforcement.
|-
| DBeaver/phpMyAdmin metadata compatibility || Ongoing and version-specific; protocol, prepared statements, SHOW and selected INFORMATION_SCHEMA relations exist.
|-
| Native Scheme JIT || Implemented for supported x86-64 procedures with interpreter fallback.
|-
| Arbitrary HTTP/IPFS stream filenames || Not generally implemented; local files and supported archive paths are the documented contract.
|-
| Stable native C++/GPU plugin ABI || Not implemented; internal extension hooks are not a promised public plugin ABI.
|}

The earlier multi-phase JSON plan is now represented by the implemented capability inventory in [[JSON]] and [[Supported SQL]]. Keeping it labelled as future work here would be misleading.


== Areas still requiring verification ==

* complete MySQL/PostgreSQL grammar and metadata compatibility;
* rare correlated, outer-join, UNION, window, and three-valued-logic shapes;
* high-contention transaction behavior and long-running repartition/rebuild work;
* compatibility of individual ORMs and administration tools;
* platform coverage outside the main Linux/x86-64 development path;
* reproducible performance results across representative workloads.


== Reporting a problem ==

Include the MemCP commit/version, schema, minimal query, expected result, actual result, ENGINE mode, frontend, and whether the problem reproduces after restart. For performance reports include row counts, EXPLAIN output, cache state, hardware, concurrency, raw timings, and a current-master comparison.

Compile MemCP from Source

2026-08-28T10:13:56Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Compile MemCP from Source =

Building from source is the recommended path for development, debugging, unreleased planner work, and installations that need optional build variants such as Ceph/RADOS or the native JIT. A source build produces the <code>memcp</code> executable, but a working installation also needs the matching <code>lib/</code> Scheme modules and, where used, the accompanying assets.

Use a supported Go toolchain and build from a specific release tag or recorded commit so the binary and runtime libraries remain reproducible. Run the complete test suite before deploying a locally built executable, especially after changing storage formats, transaction behavior, SQL parsing, or query planning.

== Requirements ==

MemCP requires '''Go 1.24 or newer''', GNU Make, and Git. Optional Ceph/RADOS support additionally requires the RADOS development headers.

== Build and test ==

<pre>
git clone https://github.com/launix-de/memcp
cd memcp
go mod download
make
make test
</pre>

The test command builds <code>./memcp</code> and runs the Scheme unit tests and SQL YAML integration suites. Planner changes must also respect <code>INVARIANTS.md</code>.

== Run ==

<pre>
./memcp --api-port=4321 --mysql-port=3307 lib/main.scm
</pre>

When no Scheme file is supplied, MemCP searches the working directory, executable directory, <code>/usr/local/lib/memcp</code>, and <code>/usr/lib/memcp</code> for <code>lib/main.scm</code>. Background services must use <code>--no-repl</code>. See [[Deployment]] and [[Security and Authentication]].

Comparison: MemCP vs. MySQL

2026-08-28T10:13:55Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Comparison: MemCP vs. MySQL =

MemCP is a Beta persistent main-memory, column-oriented database with MySQL protocol compatibility. It targets mixed operational and analytical workloads whose active columns benefit from compression, batch scans, and in-process APIs. It is not a complete replacement for every MySQL feature.

{| class="wikitable"
! Area !! MySQL !! MemCP
|-
| Physical layout || Primarily row-oriented InnoDB pages || Compressed columns with main and delta storage
|-
| Working set || Buffer pool and operating-system cache || Active data and query structures managed in RAM, with reloadable persistent columns
|-
| Compatibility || Reference implementation of the MySQL dialect/protocol || Tested subset plus compatibility syntax; unsupported features remain
|-
| Durability || InnoDB configuration and transaction log || Per-table <code>safe</code>, <code>logged</code>, <code>sloppy</code>, <code>memory</code>, or <code>cache</code>
|-
| APIs || Normally accessed through a connector || MySQL protocol, SQL/HTTP, PostgreSQL-syntax HTTP, RDF, and embedded Scheme handlers
|-
| Analytical scans || Row pages and secondary indexes; columnar analytics commonly use another system or service || Compressed columns, batch reads, late materialization, parallel shards, and fused scan pipelines
|-
| Operational writes || Mature InnoDB row storage, locking, replication, and tooling || Main/delta column storage, indexes, constraints, transactions, and per-table durability for mixed read/write workloads
|-
| Query optimization || Mature general-purpose MySQL optimizer || Logical decorrelation and join ordering followed by cost-based selection of scans, RecSets, caches, computed columns, and streaming paths
|-
| Extensibility || Functions, plugins, stored programs, and a large connector ecosystem || Embedded functional Scheme runtime, custom HTTP/WebSocket handlers, SQL operator extensions, and Go storage engine
|-
| Operations || Established backup, replication, HA, cloud, and observability ecosystem || Built-in dashboard/process controls and multiple storage backends; smaller ecosystem and no production multi-node cluster today
|}

== Where MemCP can be attractive ==

* '''Mixed operational and analytical work:''' recent inserts and updates can feed dashboards, grouping, and ad-hoc analysis without first exporting a second analytical copy.
* '''Wide tables and selective columns:''' a query can read only referenced compressed columns instead of moving whole rows through memory.
* '''Repeated categories and ordered values:''' dictionary, bit-packed, sparse, prefix, and sequence representations can reduce cache traffic when the data distribution fits.
* '''Application-specific services:''' SQL over HTTP and embedded Scheme handlers can remove a separate middleware-to-database connection for small, carefully secured services.
* '''Per-table trade-offs:''' durable business data and reconstructible caches can use different engines in one instance.
* '''Natural ordering:''' MemCP exposes collations that can compare embedded numeric parts naturally, so values such as 2 and 10 need not sort lexicographically as 10 before 2.
* '''Flexible identifiers:''' long logical column names are not tied to the local persistence filename; long names are represented with stable hashed filenames. Applications must still test connector and MySQL-metadata limits.

These are architectural opportunities, not automatic wins. Point-heavy workloads that already fit MySQL's buffer pool, write-heavy contention, poorly compressible high-cardinality values, cold remote storage, or an unsuitable physical plan may favor MySQL.

== Current measured trade-off ==

In project measurements, OLAP and search-oriented cases have reached '''speedups of 10× and more over MariaDB/PostgreSQL'''. RecSets are particularly important for large filtered domains and correlated membership workflows: they carry compact record-ID sets between operators, can adapt between ranges, sparse IDs and bitmaps, and let later column scans avoid materializing wide intermediate rows.

Isolated OLTP paths have taken about '''1.3–2.0× as long''' as on the established database. In complete WordPress- and wiki-style page builds, however, this has made '''no significant difference to overall page-loading time''' in the measured application workflows; the end-to-end effect depends on the application's own work and on the number and mix of SQL queries it issues. That is real optimization debt, especially for high-throughput APIs, but it is a different user impact from a filtered-list search over roughly one million documents in which the same query took around '''30 seconds on PostgreSQL''' and '''1.6 seconds on MemCP'''. MemCP's immediate performance case is strongest where users wait on search, filtering, grouping and analytical reads.

The figures are workload observations rather than a promise for arbitrary SQL. Reproduce them with the deployed versions, representative data, equal durability and successful responses; see [[Performance Measurement]].

Write performance can be chosen independently per table. Project measurements have reached about '''10× the <code>safe</code> write throughput''' after switching to <code>logged</code>, because the latter omits the commit-time <code>fsync</code>. This is not a free optimization: process-crash recovery remains, but recent commits may be lost on kernel crash or power failure. <code>sloppy</code> goes further by omitting the WAL and normally publishing one compressed generation every 15 minutes; for reconstructible data this can reduce continuous SD-card writes and flash wear.

== Where MySQL remains the safer default ==

Choose or retain MySQL when the application requires complete MySQL behavior, certified vendor integrations, mature replication and failover, a broad managed-service ecosystem, XA/distributed transactions, or operational practices that have not yet been reproduced and tested for MemCP. Protocol compatibility does not mean every SQL feature, metadata query, collation, error code, or connector edge case behaves identically.

== Migration and fair evaluation ==

Start with a copy or shadow workload rather than replacing the source of truth. Import the schema/data, run representative reads against both systems, compare results, exercise constraints and transaction conflicts, restart and restore, then measure latency and throughput. Keep MySQL available until SQL compatibility, durability, capacity, and operational rollback gates are satisfied.

Performance and compression depend on schema, cardinality, ordering, query shape, hardware, durability mode, cache state, and concurrency. Numeric comparisons must name both versions, dataset, query, hardware, ENGINE, warm-up procedure, number of successful responses, and raw samples. The observed '''order-of-magnitude OLAP/search gains'''—including the 30-second versus 1.6-second filtered-list case—and historical compression reductions are important evidence for MemCP's target workloads, but they are not universal guarantees.

MemCP nevertheless aims to outperform conventional row storage for its target workloads. Publishing current losses—including application suites that remain interpreter- or compile-time-bound—is part of reaching that goal, not a reason to erase the performance proposition.

If SQL performance is the limiting factor, the useful next step is not another abstract feature comparison: run the slow production-shaped queries against MemCP. When result parity, durability, operations and rollback gates pass and MemCP wins the workload, move that workload to MemCP. Start with [[MySQL is too slow|the hands-on evaluation]].

See [[Migration from MySQL and PostgreSQL]], [[Performance Measurement]], [[Supported SQL]], [[Database Tools compatibility with MemCP]], and [[Persistency and Performance Guarantees]].

Columnar Storage

2026-08-28T10:13:55Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Columnar Storage =

Column-oriented storage keeps the values of each column together instead of storing complete rows next to one another. For MemCP this serves two related goals:

* '''Compression:''' values of one column usually have similar types, ranges, repetition, or ordering and can use a specialized compact encoding.
* '''Cache locality:''' a query reads only referenced columns, so scans avoid moving unrelated row fields through memory and CPU caches.

Compression is not only a capacity feature. A lightweight decoder can be cheaper than fetching additional cache lines from a larger uncompressed representation.

== Main and delta storage ==

Each shard separates stable main rows from recent changes. Main storage is columnar and compressed. Inserts, updates, and deletions are represented through delta structures and transaction visibility metadata until rebuild work produces a new main generation.

Scans combine main and delta data without exposing that physical split to SQL. Ordered indexes merge their compressed main-record permutation with index-local delta entries; deleted or invisible records remain filtered according to the active transaction.

This design keeps immutable compressed columns efficient while allowing writes without rebuilding the complete column for every statement. See [[Shards, RecordIDs, Main Storage, Delta Storage]].

== Interface design ==

The storage interface has to support several competing requirements:

* concurrent random reads for point lookups;
* efficient sequential and arbitrary-ID batch reads;
* format-specific cached decoding without sharing mutable reader state between goroutines;
* analysis of incoming values before selecting a representation;
* statistics for query planning;
* stable persistence across software upgrades;
* optional native JIT emission for hot value access.

The original implementation can be understood through this deliberately reduced two-pass model. It is not the complete current Go interface, but it exposes the stable idea more clearly than the optimized implementation:

<pre>type ColumnStorage interface {
GetValue(recordID uint32) any

// analysis pass
prepare()
scan(recordID uint32, value any)
proposeCompression() ColumnStorage

// build pass
init(size uint32)
build(recordID uint32, value any)
finish()

Serialize(io.Writer)
Deserialize(io.Reader)
}</pre>

The optimized code adds batch readers, transaction-aware access, statistics, versioned serialization and JIT hooks around this model; it does not change the analyze → choose representation → build sequence.

The current read side is split into <code>ColumnStorage</code> and a per-goroutine <code>ColumnReader</code>:

<pre>type ColumnReader interface {
GetValue(recid uint32) Scmer
GetValueMulti(recids []uint32, target []Scmer, stride int)
GetValueRange(recid, count uint32, target []Scmer, stride int)
}</pre>

<code>GetValue</code> supports one random RecordID. <code>GetValueRange</code> reads consecutive IDs, allowing bit-position calculations, binary searches, or decoder initialization to be shared by a complete run. <code>GetValueMulti</code> gathers arbitrary IDs such as an index permutation and detects consecutive regions where the encoding benefits from a sequential path.

Both batch methods write into a caller-provided target with a configurable stride. A scan assembling an interleaved row buffer can therefore fetch one column at a time without allocating an intermediate list for every value.

<code>GetCachedReader()</code> returns a reader optimized for repeated access. Such a reader belongs to one goroutine and must not be shared. Transaction-dependent formats can provide a transaction-bound reader; callers should use shard/table helpers rather than reaching into column maps directly.

== Analysis and build phases ==

Column creation and shard rebuild use a two-pass process so the format can be selected from the actual data before allocating its final representation.

# '''Analyze'''
#* <code>prepare()</code> resets format-specific observations.
#* Each future value is passed to <code>scan(recordId, value)</code>.
#* The implementation records facts such as type, range, scale, distinctness, repetition, NULL/default frequency, run structure, and string dictionary potential.
#* <code>proposeCompression(size)</code> may return a more suitable lossless storage implementation.
#* If the format changes, analysis repeats for the proposed representation.
# '''Build'''
#* <code>init(size)</code> allocates the final structures.
#* <code>build(recordId, value)</code> encodes every value.
#* <code>finish()</code> releases temporary builder state and finalizes dictionaries or indexes needed by the representation.

This makes compression a data-dependent decision rather than a fixed mapping from SQL type to one physical format. The selected storage still reports a cheap distinct-count estimate for physical planning.

== Storage formats ==

The current implementation includes and composes formats such as:

{| class="wikitable"
|-
! Format
! Purpose
|-
| <code>StorageSCMER</code>
| Generic Scheme values and the universal starting representation when no narrower format is yet known.
|-
| <code>StorageConst</code>
| One repeated value without storing it once per record.
|-
| <code>StorageSparse</code>
| Sparse/default-heavy columns represented by exceptional RecordIDs and values.
|-
| <code>StorageInt</code>
| Bit-packed integers whose width and offset are derived from the observed range.
|-
| <code>StorageSeq</code>
| Arithmetic sequences and runs such as ordered identifiers.
|-
| <code>StorageDecimal</code>
| Exact fixed-scale decimals backed by an appropriate integer representation.
|-
| <code>StorageFloat</code>
| IEEE floating-point values.
|-
| <code>StorageString</code>
| Dictionary or buffer-based strings with specialized batch decoding.
|-
| <code>StoragePrefix</code>
| Prefix/suffix decomposition for strings where that representation is applicable; currently experimental for automatic selection.
|-
| <code>StorageEnum</code>
| rANS entropy coding for suitable low-cardinality values.
|-
| <code>OverlayBlob</code>
| External large binary/blob values layered over a base representation.
|-
| computed/cache proxy
| Values derived from other columns or populated on demand while retaining the column-reader contract.
|}

Formats can be nested. For example, a decimal delegates its scaled integer payload to another storage, and string or sequence metadata can itself use compressed integer representations.

== Batch execution and late materialization ==

MemCP scan operators fetch filter columns first and process RecordIDs in batches. Only surviving IDs need map, join, or output columns. Consecutive survivors use <code>GetValueRange</code>; ordered or index-derived ID lists use <code>GetValueMulti</code>.

This is late materialization: complete logical rows are assembled only at an edge that needs them. Filters, projections, aggregation, RecSet operations, and ordered scans can remain column-oriented in the hot path. It also avoids one interface call and one decoder restart per element.

For supported hot expressions, a storage can emit native access code through <code>JITEmit</code>. Unsupported JIT shapes retain the normal reader path and must preserve identical values.

== Concurrency and locking ==

A stable <code>ColumnStorage</code> pointer can be read concurrently, but the shard structures that publish it are protected by the shard lock. Scan and planner code must obtain storages through helpers such as <code>getColumnStorageOrPanic</code>, <code>ColumnReader</code>, or their transaction-aware forms instead of reading the shard's column map directly.

Read-only snapshots use the shard read lock; mutation, rebuild, log replay, delta changes, and index publication require the write lock and concurrency rights. Locks and rights must be released through panic-safe cleanup. Per-goroutine cached readers avoid a lock or mutable decoder shared inside the element loop.

== Persistence and compatibility ==

Every serialized column begins with a permanently assigned magic byte identifying its storage type. Most types then carry a layout-version byte. Binary layout changes must increment that version and retain all older deserializers so existing database files remain readable.

Legacy magic bytes <code>1</code>, <code>2</code>, <code>13</code>, and <code>40</code> have no version byte. An incompatible change to one of those layouts requires a new magic byte; the legacy assignment remains a read-only reader forever. Magic values must never be renumbered or reused.

Serialization compatibility is therefore part of the user-visible durability contract, not merely an internal implementation detail. See [[Persistency and Performance Guarantees]].

== Measuring compression ==

An early 55 MiB JSONL experiment produced the following observations. It mixed disk and RAM measurements and did not record schema, cardinalities, process baseline, engine mode, versions, warm-up or repeatability, so it is historical evidence—not a controlled MySQL comparison or a universal ratio.

{| class="wikitable"
! Representation !! Reported size
|-
| MySQL InnoDB source || 55 MiB disk
|-
| JSONL export || 55 MiB disk
|-
| Imported maps in delta storage || 233 MiB RAM
|-
| Main storage using only <code>StorageSCMER</code> || 81 MiB RAM
|-
| Integer columns using <code>StorageInt</code> || 46 MiB RAM
|-
| String columns using <code>StorageString</code> || 58 MiB RAM
|-
| Combined integer and string encodings || 23 MiB RAM
|}

A useful evaluation should report the source dataset and generator, schema and row count, per-column cardinality/range, selected storage formats, main/delta split, MemCP commit, ENGINE, process baseline, measured column sizes, indexes, and repeatable query timings. Compare equal logical data and distinguish allocated RAM, resident memory, serialized bytes, and external backend usage. See [[Performance Measurement]] and [[In-Memory Compression, Columnar Compression Techniques]].

Advanced SQL Tutorial

2026-08-28T10:13:54Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Advanced SQL Tutorial =

MemCP accepts MySQL-dialect SQL over the MySQL protocol and <code>/sql/<database></code>; PostgreSQL syntax is available through <code>/psql/<database></code>. The examples below assume a database selected by the connection or URL.

This tutorial combines SQL techniques with the corresponding MemCP planner behavior. A described optimization is a possible physical choice, not a promise for every data distribution. Statistics, selectivity, indexes, ordering, row counts, and <code>LIMIT</code> can change the selected plan.

== Atomic counters through unique-key checks ==

Consider a counter table whose key identifies the event or object being counted:

<pre>CREATE TABLE counters (
id INT PRIMARY KEY,
cnt INT NOT NULL
);

INSERT INTO counters VALUES (1, 2), (2, 4);</pre>

The MySQL form can create a missing counter or increment an existing counter in one atomic statement:

<pre>INSERT INTO counters VALUES (2, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

SELECT id, cnt FROM counters ORDER BY id;</pre>

{| class="wikitable"
|-
! id
! cnt
|-
| 1
| 2
|-
| 2
| 5
|}

The primary key already supplies the required uniqueness check. The statement avoids a client-side read-modify-write sequence such as <code>SELECT</code> followed by <code>UPDATE</code>, where another transaction could change the counter between both statements.

The PostgreSQL-syntax endpoint provides the corresponding <code>ON CONFLICT</code> form:

<pre>INSERT INTO counters VALUES (2, 1)
ON CONFLICT (id) DO UPDATE
SET cnt = cnt + excluded.cnt;</pre>

Use an explicit transaction when several statements must succeed or fail as one unit:

<pre>BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;</pre>

An atomic statement and a multi-statement transaction solve different problems: the upsert protects one logical write, while the transaction groups several dependent writes. See [[Transactions and Isolation]].

== Joins, grouping, and windows ==

The following query joins customers with their recent orders, aggregates revenue, and ranks the resulting groups:

<pre>SELECT c.id,
c.name,
SUM(o.amount) AS total,
RANK() OVER (ORDER BY SUM(o.amount) DESC) AS position
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01'
GROUP BY c.id, c.name
HAVING SUM(o.amount) > 100
ORDER BY total DESC
LIMIT 20;</pre>

This shape gives the planner several opportunities: it can derive scan boundaries from the date predicate, reorder inner joins, fuse filtering and aggregation into a scan pipeline, and use ordering information to avoid unnecessary work for the final <code>LIMIT</code>. Window expressions whose order-dependent result is reusable can be backed by an ordered-reduce computed column (ORC).

== Subqueries and decorrelation ==

MemCP supports <code>IN</code>, <code>EXISTS</code>, <code>NOT EXISTS</code>, scalar subqueries, correlated subqueries, and derived tables. Supported correlated shapes are decorrelated into logical stages before physical planning; MemCP does not use a hidden per-outer-row scalar fallback.

=== IN and EXISTS ===

<pre>SELECT o.id, o.amount
FROM orders AS o
WHERE o.customer_id IN (
SELECT c.id
FROM customers AS c
WHERE c.active = 1
);

SELECT p.id, p.name
FROM products AS p
WHERE EXISTS (
SELECT 1
FROM inventory AS i
WHERE i.product_id = p.id
AND i.quantity > 0
);</pre>

After logical decorrelation, physical lowering can implement membership through a direct indexed probe, a prepared key table, or a query-local RecSet. The cost model compares preparation cost with the expected number of probes and downstream rows. SQL NULL and three-valued-logic rules remain decisive, especially for <code>NOT IN</code>; a truth-only RecSet must not erase UNKNOWN results.

=== Correlated scalar aggregate ===

<pre>SELECT c.id,
c.name,
(
SELECT SUM(o.amount)
FROM orders AS o
WHERE o.customer_id = c.id
) AS total
FROM customers AS c
ORDER BY total DESC
LIMIT 20;</pre>

The correlated aggregate is represented as a decorrelated group stage. Depending on cardinality and reuse, physical lowering can choose direct probes, a prepared group cache keyed by <code>customer_id</code>, or a RecSet-based carrier. Identical nested work can be shared instead of being planned independently for every occurrence.

A scalar subquery returns NULL when no row matches and must reject a result with more than one row unless its shape, such as an aggregate, guarantees scalar cardinality.

=== Derived tables and UNION ===

<pre>SELECT totals.customer_id, totals.total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS totals
WHERE totals.total > 1000
ORDER BY totals.total DESC
LIMIT 10;

SELECT customer_id, amount FROM current_orders
UNION ALL
SELECT customer_id, amount FROM archived_orders;</pre>

Derived tables and set-operation branches remain logical operators through decorrelation and join optimization. Projections that are not consumed by the outer query can be removed, while required ordering, grouping, distinctness, and limits remain explicit semantic boundaries.

== Optimization forms used by MemCP ==

MemCP separates logical optimization from physical lowering. Logical planning describes relational meaning with query blocks, group stages, and union blocks. Only after decorrelation and join reordering does physical lowering choose scans, indexes, RecSets, caches, computed columns, and executable pipelines.

{| class="wikitable"
|-
! Optimization
! What MemCP can do
! Useful diagnostic
|-
| Subquery decorrelation
| Convert supported correlated scalar, <code>IN</code>, and <code>EXISTS</code> forms into joined logical stages; reuse equivalent stages and remove unused payloads.
| <code>EXPLAIN IR</code>
|-
| Join reordering
| Use selectivity and join connectivity to choose a driver and join order. Small connected join graphs can use exact DPHyp search; larger graphs use a bounded search strategy.
| <code>EXPLAIN REORDER</code>
|-
| Predicate and projection pushdown
| Apply filters near their source and delay or remove columns that are not yet required, reducing rows and materialized width.
| <code>EXPLAIN IR</code>, <code>EXPLAIN PHYSICAL</code>
|-
| Adaptive access paths
| Choose direct scans or compatible equality, range, pattern, and ordering indexes. Index-building evidence is accumulated so tiny or rarely useful indexes are not built immediately.
| <code>EXPLAIN PHYSICAL</code>
|-
| RecSet algebra
| Represent qualifying record IDs compactly and combine candidates using union, intersection, difference, and complement when SQL semantics permit it. The adaptive, shard-parallel implementation is described in [[RecSets]].
| <code>EXPLAIN PHYSICAL</code>
|-
| Group caches
| Prepare keyed aggregate results once when repeated probes amortize the build cost; reuse maintained results when their dependencies remain valid.
| <code>EXPLAIN PHYSICAL</code>
|-
| ORC columns
| Materialize reusable order-dependent reductions for supported window and ordered-computation shapes, with dependency-aware invalidation.
| <code>EXPLAIN PHYSICAL</code>
|-
| Pipeline fusion and late materialization
| Keep filter, projection, and aggregate work close to batched scans and assemble wide rows only when a downstream consumer needs them.
| <code>EXPLAIN</code>, <code>EXPLAIN COMPILE</code>
|-
| Range-based braking
| For compatible <code>ORDER BY</code>/<code>LIMIT</code> plans, stop ordered work when remaining keys cannot improve the requested top-k result. Defer expensive scalar projections until after a bounding limit when semantics allow it.
| <code>EXPLAIN PHYSICAL</code>
|-
| Parallel shard execution
| Execute sufficiently large independent shard batches in parallel while avoiding goroutine overhead for small work units.
| <code>EXPLAIN</code>
|}

These optimizations preserve SQL semantics. Physical choices can change after data growth, auto-index creation, cache invalidation, or updated selectivity evidence.

== Reading MemCP plans ==

Both SQL syntax modes expose the planner phases:

<pre>EXPLAIN SELECT ...;
EXPLAIN IR SELECT ...;
EXPLAIN REORDER SELECT ...;
EXPLAIN PHYSICAL SELECT ...;
EXPLAIN COMPILE SELECT ...;</pre>

* <code>EXPLAIN</code> shows the optimized executable Scheme plan.
* <code>EXPLAIN IR</code> shows the decorrelated logical representation before physical artifacts are introduced.
* <code>EXPLAIN REORDER</code> shows the optimized logical order and its planning facts.
* <code>EXPLAIN PHYSICAL</code> shows physical decisions and the selected executable plan, including alternatives such as direct probes, RecSets, and prepared carriers.
* <code>EXPLAIN COMPILE</code> reports phase timings and plan-shape metrics for parsing, decorrelation, reordering, physical preparation, emission, and final optimization.

For example, compare the phases of a selective ordered membership query:

<pre>EXPLAIN IR
SELECT o.id
FROM orders AS o
WHERE EXISTS (
SELECT 1
FROM customers AS c
WHERE c.id = o.customer_id
AND c.active = 1
)
ORDER BY o.created_at DESC
LIMIT 10;

EXPLAIN REORDER
SELECT o.id
FROM orders AS o
WHERE EXISTS (
SELECT 1
FROM customers AS c
WHERE c.id = o.customer_id
AND c.active = 1
)
ORDER BY o.created_at DESC
LIMIT 10;

EXPLAIN PHYSICAL
SELECT o.id
FROM orders AS o
WHERE EXISTS (
SELECT 1
FROM customers AS c
WHERE c.id = o.customer_id
AND c.active = 1
)
ORDER BY o.created_at DESC
LIMIT 10;</pre>

The logical output should contain decorrelated stages rather than parser subquery markers. The reordered output explains which relation drives the work. The physical output reveals whether current costs favor direct probes, a prepared membership carrier, an ordered index, or another valid scan source.

Do not copy plan text as a permanent performance guarantee. Validate application-critical queries against the deployed commit, representative cardinalities, current indexes, and realistic parameter values. See [[Query Planner and Physical Lowering]], [[Data Auto Sharding and Auto Indexing]], [[Performance Measurement]], and [[Supported SQL]].

Advanced Graph Querying

2026-08-28T10:13:54Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Advanced Graph Querying =

MemCP exposes its tested SPARQL subset at <code>/rdf/<database></code>. Queries can combine triple patterns, FILTER expressions, OPTIONAL blocks, bindings, and supported update forms. Turtle data can be loaded through <code>/rdf/<database>/load_ttl</code>.

<pre>
SELECT ?person ?name WHERE {
?person <http://xmlns.com/foaf/0.1/name> ?name .
OPTIONAL { ?person <http://example.org/active> ?active . }
FILTER(!BOUND(?active) || ?active = true)
}
</pre>

Update templates include tested DELETE/INSERT/WHERE forms. RDF literals and IRIs must use the supported escaping rules; application input must not be concatenated into query text without validation.

This page documents a tested subset, not complete SPARQL standard conformance or a separate general-purpose graph execution engine. Add successful and must-fail integration tests before advertising another construct. See [[Introduction to RDF]] and [[SQL over REST]].

== Property-path roadmap ==

SPARQL property paths remain a planned extension. The intended progression covers one-step alternatives/sequences, inverse paths, optional paths and transitive <code>*</code>/<code>+</code> traversal. Recursive traversal needs explicit visited-set semantics, cancellation, bounds and a physical representation that does not repeatedly materialize the same nodes. Until tests and documentation say otherwise, do not assume property-path syntax is accepted.

Add custom SQL operators to MemCP

2026-08-28T10:13:54Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Add custom SQL operators to MemCP =

Applications can expose their own Scheme functions as scalar SQL functions. The SQL parsers look up function names in the shared <code>sql_builtins</code> registry and place the registered Scheme procedure directly into the generated query plan.

== Register a Scheme function for SQL ==

The smallest example adds one to its argument:

<pre>
(sql_builtins "ADD_1" (lambda (x) (+ x 1)))
</pre>

After the module containing this declaration has been imported, the function is available in SQL:

<pre>
SELECT ADD_1(4) AS result;
-- result: 5
</pre>

SQL function names are normalized to upper case before lookup, so registry keys should conventionally be uppercase. The function may still be written as <code>add_1(...)</code>, <code>Add_1(...)</code>, or <code>ADD_1(...)</code> in SQL.

The declaration must run after <code>lib/sql-parser.scm</code> or <code>lib/sql-builtins.scm</code> has created the shared registry. A custom application module loaded after <code>lib/main.scm</code> is a suitable place:

<pre>
/* apps/my-sql-functions.scm */
(define sql_add_1 (lambda (x)
(if (nil? x) nil (+ x 1))))

(sql_builtins "ADD_1" sql_add_1)
</pre>

Load the module when starting MemCP:

<pre>
./memcp --api-port=4321 --mysql-port=3307 \
lib/main.scm apps/my-sql-functions.scm
</pre>

Then test it through the HTTP frontend:

<pre>
curl --fail-with-body -u root:strong-password \
--data-binary 'SELECT ADD_1(4), ADD_1(NULL)' \
http://localhost:4321/sql/test
</pre>

== Parameters, NULL, and result types ==

The lambda parameter list defines the accepted arity. A three-argument function is registered in exactly the same way:

<pre>
(sql_builtins "CLAMP" (lambda (value lower upper)
(if (nil? value) nil
(max lower (min upper value)))))
</pre>

<pre>
SELECT CLAMP(12, 0, 10); -- 10
</pre>

A custom function is responsible for its observable SQL behavior:

* decide whether SQL <code>NULL</code> propagates, produces a boolean, or has another documented meaning;
* return values that the SQL result encoder and surrounding expression understand;
* avoid hidden mutable state when the function can run inside parallel shard scans;
* do not perform I/O or database writes from a function advertised as a pure scalar expression;
* validate strings, lists, numbers, JSON values, and other dynamic Scheme inputs explicitly.

The registry stores a Scheme procedure, not a complete SQL type declaration. Consequently, type and arity mistakes are normally detected when the generated plan is optimized or executed. Add successful and must-fail tests for NULLs, wrong arity, invalid types, boundary values, and both SQL syntax frontends where the function is intended to work.

== Use an existing Scheme builtin ==

An existing Scheme function can be exported without wrapping it:

<pre>
(sql_builtins "MY_UNIX_TIMESTAMP" unix_timestamp)
</pre>

Aliases in <code>lib/sql-builtins.scm</code> use this pattern extensively. A wrapper lambda is preferable when SQL needs different NULL handling, argument order, defaults, or result normalization.

== Implement the underlying builtin in Go ==

For hot functionality that cannot be expressed efficiently in Scheme, first declare a typed Scheme builtin in Go. The reduced pattern below follows the runtime's <code>Declaration</code> and <code>TypeDescriptor</code> API:

<pre>
DeclareInSection("Arithmetic / Logic", &Globalenv, &Declaration{
Name: "add_1",
Desc: "adds one to an integer and propagates nil",
Fn: func(args ...Scmer) Scmer {
if args[0].IsNil() {
return NewNil()
}
return NewInt(args[0].Int() + 1)
},
Type: &TypeDescriptor{
Params: []*TypeDescriptor{
{Kind: "int", ParamName: "value", ParamDesc: "value to increment"},
},
Return: &TypeDescriptor{Kind: "int"},
Const: true,
},
})
</pre>

Use <code>DeclareInSection</code> to place a function in an existing generated API chapter. A new chapter starts with <code>DeclareTitle</code> and ordinary <code>Declare</code> calls. A new initializer must also be called from the runtime initialization sequence; merely adding a Go function does not register it.

Set <code>Const</code> only when repeated calls with the same arguments are deterministic, side-effect-free, and safe to constant-fold. Describe every parameter and return value so <code>make docs</code> can generate a useful SCM reference. More advanced declarations can describe optional or variadic parameters, nested list/association types, ownership, optimizer hooks, and a native JIT emitter.

After the Scheme builtin exists, expose it to SQL separately:

<pre>
(sql_builtins "ADD_1" add_1)
</pre>

This separation is intentional: declaring a Scheme builtin makes it available to Scheme and generated plans, while registering it in <code>sql_builtins</code> gives the SQL parser a function name.

== Scalar functions versus new SQL syntax ==

<code>sql_builtins</code> is the right mechanism for ordinary calls such as <code>ADD_1(x)</code>. Adding an infix operator, aggregate syntax, table-producing function, clause, or construct that changes relational cardinality requires parser and planner work as well. Such a change must preserve the boundary between parser AST, logical decorrelation/join ordering, and physical lowering described in [[Query Planner and Physical Lowering]].

See [[Introduction to Scheme]], [[SCM Builtins]], [[Full SCM API documentation]], and [[Contributing]].

Transactions and Isolation

2026-08-28T10:13:53Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Transactions and Isolation =

Every SQL statement runs in an implicit transaction unless its session is already inside an explicit <code>BEGIN</code>/<code>COMMIT</code>/<code>ROLLBACK</code> transaction. This provides statement-level error rollback without requiring every client to manage a transaction for a single statement.

== Visibility and conflict handling ==

The default cursor-stability mode applies writes with undo information, provides statement atomicity, and rolls changes back on errors. MemCP also contains an ACID path with snapshot visibility and optimistic conflict detection at commit. Savepoints are used internally for nested trigger and error recovery.

Transaction state is tracked per shard. A read combines main rows, delta rows, insertion/deletion overlays, its transaction snapshot, and the active shard generation. Commit locks and publishes touched shards; rollback reverses staged or directly applied work. Nested shard work uses bounded fanout to avoid unbounded goroutines and lock cycles.

Applications should test their actual contention patterns. MemCP does not claim every MySQL isolation level, XA, distributed transactions, or every locking clause. An unsupported guarantee should be treated as unsupported rather than inferred from accepted syntax.

== Atomic write patterns ==

Prefer one atomic SQL statement where possible. For example, an upsert counter avoids a client-side read/modify/write race:

<pre>
INSERT INTO counters (name, value) VALUES ('jobs', 1)
ON DUPLICATE KEY UPDATE value = value + 1;
</pre>

Use an explicit transaction when several statements must succeed or fail as a unit. Keep it short: long transactions retain visibility state, increase conflict probability, and can delay maintenance.

== Durability is a separate axis ==

Isolation describes which concurrent changes a transaction observes; durability describes what survives a failure. For <code>safe</code> tables, WAL synchronization occurs at commit. <code>logged</code> writes WAL without fsync; <code>sloppy</code>, <code>memory</code>, and <code>cache</code> skip WAL durability. A transaction spanning tables inherits the guarantees and risks of every table ENGINE involved.

See [[Advanced SQL Tutorial]], [[Persistency and Performance Guarantees]], [[Triggers]], and [[Supported SQL]].

Storage Backends

2026-08-28T10:13:53Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Storage Backends =

MemCP can persist a database on the local filesystem, S3-compatible object storage, or optional Ceph/RADOS. ENGINE durability semantics still apply on top of the selected backend.

The local backend uses a directory below <code>-data</code>. For S3 or Ceph, create a JSON database configuration in the data directory. An S3 configuration names endpoint, region, bucket, prefix, credentials, and optional path-style access. Ceph names cluster, user, pool, prefix, and optional config file.

<pre>{
"backend": "s3",
"access_key_id": "...",
"secret_access_key": "...",
"region": "us-east-1",
"endpoint": "http://minio:9000",
"bucket": "memcp",
"prefix": "databases",
"force_path_style": true
}</pre>
Ceph is built with <code>make ceph</code> and requires <code>librados-dev</code>. Keep secrets outside version control and restrict config-file permissions. Test schema writes, column reload, WAL replay, listing/deletion, outage behavior, and backups against the actual service. Object storage latency affects cold reads, rebuilds, and WAL handling even though hot query execution is memory-oriented.

RecSets

2026-08-28T10:13:52Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= RecSets: compact, parallel record-set processing =

<blockquote>'''Verified against commit <code>c42e19eba</code> on 27 August 2026.''' RecSets are an internal physical execution mechanism. Applications normally use SQL and let the optimizer choose them.</blockquote>

A '''RecSet''' is a query-local subset of one MemCP table. Instead of materializing complete rows, it records the physical record IDs that currently qualify. The result remains table-shaped: <code>scan</code>, <code>scan_batch</code>, <code>scan_order</code>, <code>scan_order_multi</code>, and <code>scan_exists</code> can consume it as a source while retaining columnar reads, indexes, transaction visibility, and parallel shard execution.

This separation is valuable on large relations. A cheap predicate can identify a narrow working set once; expensive residual predicates, correlated access checks, joins, projections, ordering, and aggregation then operate only on those candidates. Millions of base rows need not become millions of Scheme lists or wide temporary rows.

== What a RecSet represents ==

A RecSet contains membership, not result rows:

* it belongs to exactly one base table and one transaction/visibility context;
* it has set semantics: no duplicates and no join multiplicity;
* it carries no result order;
* it is query-local and is neither persisted nor a stable public record-ID format;
* it can be an exact set of TRUE rows or a safe candidate superset followed by a residual predicate.

The essential implementation is deliberately small:

<pre>
type recSet struct {
tx *TxContext
table *table
shards []recSetShard
count int64
}

type recSetShard struct {
shard *storageShard
kind recSetRepresentation
universe uint32
data []uint32
used uint32
count int64
}
</pre>

Each shard owns its own record-ID universe. This mirrors MemCP's storage layout and makes construction and set algebra naturally parallel without sharing a global bitmap or exposing record IDs across shards.

== Adaptive representation ==

One fixed representation would waste memory for either sparse or dense sets. Every RecSet shard therefore chooses among three forms:

{| class="wikitable"
! Representation !! Layout !! Best fit !! Membership and combination
|-
| Ranges || Sorted <code>(base, count)</code> pairs || Long runs, full/empty shards, or a nearly full set with a few holes || Range lookup; linear range sweeps
|-
| Positive IDs || Sorted, deduplicated <code>uint32</code> record IDs || A small, scattered result || Sorted membership lookup; linear list merge/intersection
|-
| Bitmap || One bit per record-ID position || Dense or highly fragmented membership || Direct bit test and word-wise Boolean operations
|}

There is intentionally no separate “full”, “empty”, or negative representation. An empty shard is zero ranges; a full shard is one range; “everything except a few holes” is a small number of ranges. A fragmented set eventually becomes a bitmap.

For orientation, an uncompressed bitmap for a one-million-position shard needs approximately 125 kB. A positive-ID result uses four bytes per hit before slice overhead, and a range uses eight bytes per run. The builder chooses according to the observed shape rather than a SQL selectivity guess. Immutable search-index RecSets have an additional compressed form: their positive IDs and ranges can themselves use MemCP's packed integer storage, and their small interface can intersect a mutable query-local RecSet in place.

=== One-pass adaptive construction ===

The builder starts with ranges. More than three isolated singleton runs indicate sparse, non-clustered data and trigger conversion to a positive list. If the compact representation exceeds the bitmap budget, it escalates to a bitmap.

The filter predicate is evaluated '''exactly once per candidate record ID'''. A representation change copies already collected IDs or ranges; it never repeats the scan or replays Scheme predicates. This matters because a predicate may include decoded column values, JIT/interpreter work, search-index checks, or an expensive correlated condition. After construction, range and positive results are copied into right-sized slices so a small result does not keep the worst-case bitmap allocation alive.

Ranges are also split at the immutable-main/delta boundary. Consumers can then bulk-read a main-storage run without accidentally spanning into transaction deltas.

== Building a RecSet in the scan/filter path ==

<code>scan_recset</code> uses the normal MemCP scan machinery rather than walking every row blindly:

# extract equality, range, LIKE/search, computed, and RecSet boundaries from the filter;
# order useful boundaries and derive index limits;
# schedule eligible shards in parallel;
# prepare only the columns named by the filter and optimize its Scheme procedure;
# read index candidates in batches of up to 1,024 record IDs;
# apply transaction/deletion visibility and the residual predicate once;
# feed qualifying IDs to the adaptive shard builder.

This keeps late materialization intact. Creating <code>WHERE tenant_id = 7 AND state = 'open'</code> as a RecSet needs the filter columns, but it does not have to decode payload, JSON, or text columns that are only required by a later projection.

<pre>
(begin
(define tx ((context "session") "__memcp_tx"))
(define orders (table "shop" "orders"))
(define open_orders
(scan_recset tx orders
'("tenant_id" "state")
(lambda (tenant state)
(and (equal? tenant 7) (equal? state "open")))))
(recset_count open_orders))
</pre>

The low-level example illustrates the operator contract. SQL users should normally write the predicate in SQL and inspect the optimizer's decision with <code>EXPLAIN PHYSICAL</code>.

=== Narrowing an existing RecSet ===

Passing a RecSet as the input of <code>scan_recset</code> adds another condition without revisiting rows outside the existing membership:

<pre>
(define recent_open
(scan_recset tx open_orders
'("created_at")
(lambda (created_at) (>= created_at cutoff))))
</pre>

For a very sparse input, MemCP walks its IDs directly. For a broader input, it combines the exact RecSet boundary with other index/search boundaries before reading residual columns. This is the intended pattern for “cheap selective predicate first, expensive predicate second”, including ACL or search conditions with their own subscans.

When scanning a RecSet, consecutive visible main-storage IDs are coalesced into runs. Each required column is fetched for the whole run with <code>GetValueRange</code>; visibility remains row-specific, while column decoding becomes batched and cache-friendly. Delta rows use their transaction-aware delta path.

== Membership inside another scan ==

The pseudo-column <code>$recset_contains</code> supplies a closure bound to the current row. It tests one or more same-table RecSets without exposing physical record IDs:

<pre>
(scan tx orders
'("$recset_contains" "total")
(lambda (contains total)
(and (contains open_orders) (> total 1000)))
'("id")
(lambda (id) id)
(lambda (sum id) (+ sum id))
0)
</pre>

The closure caches the current RecSet and its shard entry, avoiding a repeated shard search for successive membership calls. If an exact RecSet boundary already proves the membership condition, the scan can provide an always-true closure instead of probing it again. Multiple <code>$recset_contains</code> parameters are supported when a filter needs independent sets.

== Set algebra, in parallel ==

<pre>
(define visible (recset_intersect (list tenant_rows active_rows)))
(define either (recset_union (list starred_rows recent_rows)))
(define allowed (recset_difference (list visible blocked_rows muted_rows)))
(define other_visible_rows (recset_not visible))
</pre>

Union, intersection, and difference require operands from the same table. Sorted range/list invariants allow linear two-pointer or N-way sweeps; bitmap pairs use word operations. Mixed representation pairs have specialized implementations instead of expanding everything into one common row list.

Each shard combination is independent and runs through MemCP's bounded fan-out scheduler. Results are sorted back into deterministic shard order after parallel completion. This lets Boolean work over millions of record positions use available cores while keeping synchronization out of the inner membership loop.

<code>recset_not</code> deserves special attention: it constructs the currently visible table universe and subtracts the input. It does not merely invert raw bits, because that could resurrect deleted rows or rows invisible to the transaction. At SQL level, a RecSet complement is also not automatically equivalent to <code>NOT predicate</code> when NULL/UNKNOWN is possible.

== Projecting membership through a join ==

<code>recset_project_join</code> reads distinct source keys from one RecSet and creates a RecSet over matching rows of another table. This is useful for EXISTS/IN carriers, ACL links, tags, and search-result relations:

<pre>
(define permitted_links
(scan_recset tx access_links
'("user_id")
(lambda (user_id) (equal? user_id current_user))))

(define permitted_documents
(recset_project_join tx permitted_links
'("document_id") documents '("id")))

(scan_order tx permitted_documents
'() (lambda () true)
'("created_at" "id") '(< <)
0 0 50
'("id" "title")
(lambda (id title) (list id title))
(lambda (rows row) (append rows (list row)))
'() false)
</pre>

Composite source/target keys are supported. At execution time the operator knows the actual number of distinct source keys and target rows per shard. It chooses between indexed point probes and a dense target scan. A single integral key can use an O(1) hash membership test during the dense scan; generic composite keys use sorted tuples and binary lookup.

The current cost constants were calibrated with production-shaped A/B fixtures. One recorded case projected about 80,000 integral keys through 1.84 million target rows in roughly 8 ms on the calibration machine, while another case with 105 keys and 22 indexed target shards favored point probes. These are decision-model observations, '''not portable latency guarantees'''; hardware, encodings, shard layout, visibility state, and surrounding query work matter.

For repeated scalar membership checks, <code>recset_key_index</code> builds an immutable lookup closure over one or more columns:

<pre>
(define contains_document
(recset_key_index tx permitted_documents '("id")))

(contains_document 12345)
</pre>

== Ordering, LIMIT, and adaptive batches ==

A RecSet itself is unordered. <code>scan_order</code> applies an index/order from the base table and intersects it with RecSet membership. Depending on cardinality, index span, and LIMIT, the execution kernel can:

* drive the base ordered index and test RecSet membership;
* translate sparse RecSet IDs into ordered index positions and sort those positions;
* iterate the RecSet directly when no useful ordering is required.

The crossover is calibrated by a benchmark with 800,000 rows, RecSet cardinalities from 16 to 720,000, bounded and unbounded scans, and both distributed and adversarial late hits. <code>EXPLAIN PHYSICAL</code> can report the adaptive RecSet boundary and runtime alternatives such as <code>ordered_inverse_recset</code> and <code>ordered_base_membership</code>.

For an ORDER BY/LIMIT query whose expensive acceptance condition lives on another relation, <code>scan_order_batch_accept</code> preserves order without evaluating the whole candidate domain. It draws an initial ordered RecSet of <code>OFFSET + LIMIT</code> candidates, asks a batch function to return an exact accepted subset, and doubles subsequent disjoint batches until enough rows are accepted or the source is exhausted. The accepted RecSet is used as a mask against the already ordered vector, so it is not rescanned in physical-ID order.

This is especially useful for ordered document/search/ACL queries: project a candidate batch to the access relation, filter it in parallel, project accepted membership back, and stop once the requested page is complete.

== How the optimizer uses RecSets ==

RecSets appear only during physical lowering. The logical planner first establishes joins, domains, SQL truth semantics, grouping, ordering, and multiplicity. The physical cost model can then compare:

{| class="wikitable"
! Choice !! Often attractive when
|-
| Direct indexed probe || The driving side is small and each lookup is selective.
|-
| RecSet carrier || A filtered domain is reused, projected, combined, or much narrower than the base relation.
|-
| Prepared key/group table || Keys or aggregate results need relational reuse and more than membership.
|-
| Dense fused scan || Most rows are visited once and building a reusable carrier would add work.
|-
| Ordered RecSet iterator || An existing membership domain must be consumed in base-table order or with a small LIMIT.
|}

Candidate RecSets do not permit the optimizer to drop correctness checks. Approximate LIKE/full-text boundaries and other safe supersets retain their residual SQL predicate. Nullable <code>IN</code>/<code>NOT IN</code> and general three-valued logic are handled before truth-only Boolean set identities are applied.

Use these views when investigating a plan:

<pre>
EXPLAIN IR SELECT ...;
EXPLAIN REORDER SELECT ...;
EXPLAIN PHYSICAL SELECT ...;
</pre>

<code>EXPLAIN PHYSICAL</code> exposes membership-carrier choices and runtime access alternatives for projected and ordered RecSets. A remaining roadmap item is richer tracing for why the same RecSet domain was built or reused and for the exact ordered-iterator mode chosen at runtime.

== Operational and correctness limits ==

* Do not retain a RecSet as application state or persist its physical IDs.
* Algebra operands must refer to the same table; projected joins deliberately return a new target-table RecSet.
* A RecSet is tied to its transaction/visibility context. Batch-accept callbacks must return a same-table, same-transaction subset.
* Scanning a RecSet for reads is supported; mutation callbacks such as <code>$update</code> are rejected on a RecSet source.
* Cancellation is checked before shard work is scheduled. Once entered, one shard's RecSet build runs atomically.
* Set cardinality is not SQL row cardinality after a one-to-many join, and membership does not retain duplicate keys.
* Ordered output needs <code>scan_order</code>; include a unique tie-breaker when stable pagination matters.

== Low-level operator map ==

{| class="wikitable"
! Operator !! Purpose
|-
| <code>scan_recset</code> || Build from a table or narrow an existing RecSet.
|-
| <code>recset_count</code> || Return stored membership cardinality.
|-
| <code>recset_union</code> / <code>recset_intersect</code> || Combine same-table memberships.
|-
| <code>recset_difference</code> / <code>recset_not</code> || Subtract sets or complement against visible rows.
|-
| <code>recset_project_join</code> || Project distinct keys into a target-table RecSet.
|-
| <code>recset_key_index</code> || Build an immutable scalar/composite-key membership closure.
|-
| <code>$recset_contains</code> || Test current-row membership inside a scan predicate.
|-
| <code>scan_order_batch_accept</code> || Filter progressively larger ordered candidate RecSets before LIMIT.
|}

The generated [[Storage]] reference is authoritative for exact signatures. For the surrounding execution architecture see [[Scan]], [[Query Planner and Physical Lowering]], [[Columnar Storage]], [[Data Auto Sharding and Auto Indexing]], and [[Parallel Computing]].

Query Planner and Physical Lowering

2026-08-28T10:13:52Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Query Planner and Physical Lowering =

<blockquote>'''Verified against commit <code>c42e19eba</code> on 27 August 2026.''' Planner details can change between releases.</blockquote>

MemCP compiles SQL into executable Scheme code. It first establishes the relational meaning of a query, decorrelates supported subqueries, and selects a join order. Only then does physical lowering choose scans, indexes, RecSets, caches, computed columns, and storage representations. Keeping those phases separate prevents a storage shortcut from silently changing SQL semantics.

== Planner pipeline ==

<pre>
SQL text
-> MySQL or PostgreSQL parser AST
-> normalize_sql_syntax
-> decorrelate_logical_query / untangle_query
-> predicate placement and join_reorder / optimize
-> build_queryplan
-> Scheme optimizer and optional native JIT
-> storage-engine scans
</pre>

{| class="wikitable"
! Phase !! Responsibility
|-
| Parser || Preserve SQL structure and produce neutral query terms.
|-
| Normalization || Remove frontend spelling differences and safe syntactic sugar while preserving three-valued logic.
|-
| Decorrelation || Replace dependent subqueries with explicit domains, stages, keys, and joins.
|-
| Logical optimization || Place predicates across proven-safe boundaries, estimate selectivity/cardinality, and choose the join tree.
|-
| Physical lowering || Select concrete scan sources and operators and emit Scheme code.
|-
| Scheme optimization/JIT || Optimize the functional program and compile supported hot procedures to native x86-64 code.
|}

Physical artifacts such as <code>scan</code>, RecSets, keytables, ORC columns, and temporary tables must not leak into logical planning. Conversely, physical lowering consumes the selected join tree; it must not flatten it and choose a second join order.

== Logical operator model ==

MemCP deliberately uses three broad logical shapes instead of splitting every clause into a long chain of tiny relational operators:

* <code>query-block</code> represents SELECT, JOIN, filtering, projection, ordering, LIMIT, and OFFSET;
* <code>group-stage</code> represents grouping, aggregates, correlated domains, EXISTS/IN/scalar helpers, HAVING, and window partitions;
* <code>union-block</code> represents <code>UNION</code> and <code>UNION ALL</code>.

A <code>stage-output</code> is a logical relation, not a physical table. This combined model lets the physical engine fuse filter, projection, and aggregation into one scan when their semantics and order requirements allow it.

== Subquery decorrelation ==

MemCP does not use “run the correlated subquery once per outer row” as a fallback. Supported correlated forms are transformed into relational stages; unsupported shapes fail explicitly.

The simple path collects equality classes, chooses safe representatives, pulls expressions across valid boundaries, and turns trivial dependent joins into ordinary joins. If dependencies remain, the planner constructs Neumann's '''Domain D''': the duplicate-free projection of the outer values actually read by the inner query. Domain keys pass through inner stages and join back to the outer result.

Decorrelation must retain:

* scalar-subquery cardinality, including an error when a single-row subquery returns a second row;
* SQL NULL behavior for <code>IN</code> and <code>NOT IN</code>;
* empty correlated aggregate groups, such as <code>COUNT = 0</code> versus a nullable aggregate;
* outer-join null extension;
* ordering, LIMIT, and window requirements.

== Join ordering ==

After decorrelation exposes the complete join graph, <code>join_reorder</code> uses relation cardinalities and predicate selectivities to select a tree. Exact DPHyp-style enumeration is available within a configurable budget; larger graphs use bounded strategies. Bushy trees are possible, and independent subtrees may execute in parallel.

<pre>
(settings "JoinReorderDPBudget")
(settings "JoinReorderDPBudget" 256)
</pre>

The physical engine may decide how to execute each selected join edge, but not which logical relation order the query means.

== Cost-based physical lowering ==

Physical lowering compares concrete ways to execute the already selected logical plan. Inputs include cardinality and distinct-count estimates, selectivity, scan and probe work, order compatibility, offset and LIMIT, available/adaptive indexes, group-cache state, expected reuse, build cost, memory, compilation cost, and collected telemetry.

{| class="wikitable"
! Physical choice !! Typical purpose
|-
| Fused <code>scan</code> || Unordered filtering, projection, and aggregation in one pass.
|-
| <code>scan_order</code> || Ordered access, top-k, offset/LIMIT ownership, or a bounded scalar probe.
|-
| <code>scan_order_multi</code> || Merge already ordered inputs, especially streamable <code>UNION ALL</code> branches.
|-
| <code>scan_exists</code> || Stop after proving that a matching row exists.
|-
| Direct nested scan || Cheap selective probe driven by bound join keys.
|-
| RecSet || Query-local exact or candidate set of record IDs.
|-
| Group keytable/cache || Reusable grouped key domain and computed aggregate columns.
|-
| FK-backed computed column || Reuse a lookup or aggregate shaped by a foreign key.
|-
| ORC || Reusable order-dependent computation, including window dependencies.
|-
| Query-local temporary relation || Relational barrier when streaming and reusable representations do not fit.
|}

Intermediate results remain storage-engine scan sources instead of becoming Scheme row lists. That preserves batch reads, indexing, statistics, range braking, late materialization, visibility, and bounded memory behavior.

== RecSets and residual predicates ==

A RecSet belongs to one base relation and visibility snapshot. It can contain the exact TRUE rows of a predicate or only a safe candidate superset. Candidate sets retain the original predicate as a residual filter. The storage engine supports sparse IDs and ranges plus union, intersection, complement, difference, and projection through join keys.

The representation is adaptive per shard (ranges, sorted positive IDs, or bitmap), and construction, algebra, and join projection can run shard-parallel without materializing complete rows. See [[RecSets]] for the data structure, scan/filter pipeline, ordered iterators, join projection, cost decisions, and low-level examples.

For truth-filtering contexts the planner can exploit <code>T(p OR q) = T(p) union T(q)</code> and <code>T(p AND q) = T(p) intersect T(q)</code>. SQL three-valued logic remains authoritative: complementing TRUE rows is not generally SQL <code>NOT</code>, and nullable <code>NOT IN</code> needs special handling.

== LIMIT, UNION, and windows ==

LIMIT belongs to a physical scan boundary, allowing scans to stop early instead of always materializing all matches. Ordered scans can use top-k thresholds and range braking when the next keys cannot improve the result.

Unordered <code>UNION ALL</code> can emit branches successively; <code>UNION</code> needs deduplication. Ordered streamable inputs can merge through <code>scan_order_multi</code>; incompatible orders materialize only the narrow relation required by the barrier.

A window expression does not automatically require a temporary table. If one order satisfies the base query and all partitions/orders, MemCP can fuse window work into that scan. Conflicting orders or shared order-dependent values can use a stage or ORC computed column.

== Plan cache and guarded specialization ==

The query-plan cache stores compiled Scheme formulas for normalized SQL shapes. A group cache is different: it is a reusable storage-engine relation. When parameters or statistics move the cost-model optimum, one cached SELECT can contain several guarded physical variants. A guard repeats cost comparisons; it does not scan data, build an index, or redo logical planning.

== Inspecting optimizer decisions ==

Both SQL frontends expose complementary views:

<pre>
EXPLAIN SELECT ...;
EXPLAIN IR SELECT ...;
EXPLAIN REORDER SELECT ...;
EXPLAIN PHYSICAL SELECT ...;
EXPLAIN COMPILE SELECT ...;
</pre>

* <code>EXPLAIN</code> shows the optimized executable Scheme plan;
* <code>EXPLAIN IR</code> shows logical operators before physical emission;
* <code>EXPLAIN REORDER</code> exposes join-order and selectivity information;
* <code>EXPLAIN PHYSICAL</code> summarizes concrete access paths and reusable structures;
* <code>EXPLAIN COMPILE</code> reports time spent parsing, planning, preparing, emitting, and optimizing.

<pre>
(settings "ExplainWidth" 80)
(settings "ScanDebugging" true)
</pre>

== Correctness boundaries ==

An unsupported planner shape should report a limitation. It must not silently discard residual predicates, turn an equijoin into a cross product, collapse SQL NULL semantics, replace scalar cardinality checks with <code>LIMIT 1</code>, materialize an unbounded relation as a Scheme list, or introduce per-outer-row correlated execution.

For practical query patterns see [[Advanced SQL Tutorial]]. Related internals are covered by [[RecSets]], [[Data Auto Sharding and Auto Indexing]], [[Temporary Computed Columns]], [[Columnar Storage]], [[Scan]], and [[Parallel Computing]].

JSON

2026-08-28T10:13:52Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= JSON and SQL/JSON =

MemCP supports native JSON values through a typed BSON-backed runtime representation. Both the MySQL and PostgreSQL syntax frontends expose constructors, path access, mutation, containment, aggregation, and relational expansion. JSON is useful for attributes whose shape genuinely varies; stable fields used for joins, constraints, or frequent filtering are often clearer as ordinary typed columns.

== Creating and reading JSON ==

MySQL-style constructors and paths include <code>JSON_OBJECT</code>, <code>JSON_ARRAY</code>, <code>JSON_EXTRACT</code>, <code>JSON_VALUE</code>, and the <code>-></code>/<code>->></code> operators.

<pre>
SELECT JSON_OBJECT('name', 'Ada', 'roles', JSON_ARRAY('admin', 'author'));
SELECT profile->>'$.name' AS name
FROM users
WHERE JSON_EXTRACT(profile, '$.active') = true;
</pre>

PostgreSQL syntax supports <code>json</code>/<code>jsonb</code> casts and operators such as <code>-></code>, <code>->></code>, <code>#></code>, <code>#>></code>, <code>@></code>, and <code><@</code>, together with PostgreSQL-style build and path functions.

== Updating and aggregating ==

Use <code>JSON_SET</code>, <code>JSON_INSERT</code>, <code>JSON_REPLACE</code>, <code>JSON_REMOVE</code>, array mutation, or merge functions to produce an updated document. JSON values are immutable expressions: an UPDATE assigns the returned value back to the column.

<pre>
UPDATE users
SET profile = JSON_SET(profile, '$.last_login', CURRENT_TIMESTAMP)
WHERE id = 42;

SELECT team_id, JSON_ARRAYAGG(name)
FROM users
GROUP BY team_id;
</pre>

Object aggregates, <code>JSON_TABLE</code>, PostgreSQL <code>json_array_elements</code>, and object-key expansion turn documents into relational rows or collect rows into documents. Their exact accepted syntax differs between the MySQL and PostgreSQL endpoints; test queries against the selected frontend.

== Indexing and compatibility ==

Frequently used path expressions can participate in computed-expression optimization and adaptive indexing. Keep the path expression stable and inspect the physical plan with <code>EXPLAIN PHYSICAL</code>; an accepted JSON predicate does not by itself guarantee an index.

JSON numbers, NULL, SQL NULL, missing paths, duplicate object keys, and scalar-versus-container results have compatibility-sensitive semantics. Validate them when migrating from MySQL or PostgreSQL. The executable regression suites <code>tests/sql/expressions/json-functions.yaml</code> and <code>postgresql-json-functions.yaml</code> are the most precise inventory for the current commit.

See [[Supported SQL]], [[Migration from MySQL and PostgreSQL]], [[SQL over REST]], and [[Data Auto Sharding and Auto Indexing]].

Main Page

2026-08-28T09:59:42Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= MemCP Database – Fast, Compressed SQL for OLTP and OLAP =

<div style="padding:2.5rem 2rem; margin:0 0 1.5rem; border-radius:14px; background:linear-gradient(135deg,#3d6208 0%,#64910c 52%,#76b512 100%); color:#fff; text-align:center;">
<div style="font-size:2.6rem; line-height:1.1; font-weight:700; margin-bottom:.7rem;">MemCP</div>
<div style="font-size:1.45rem; line-height:1.35; font-weight:600; margin:0 auto .9rem; max-width:56rem;">A fast, compressed, MySQL-protocol-compatible columnar database for modern OLTP and OLAP workloads</div>
<div style="font-size:1.05rem; line-height:1.6; margin:0 auto 1.4rem; max-width:54rem;">MemCP is an open-source SQL database written in Go. It combines persistent in-memory operation, adaptive column compression, parallel query execution and direct application APIs so operational data can be queried and analyzed without maintaining a separate analytical copy.</div>
<div style="margin-bottom:1.5rem;">Beta Open Source MySQL Protocol + HTTP APIs</div>
<div class="plainlinks">[[Install MemCP with Docker|Get started with Docker]] [[Supported SQL|Explore SQL support]] [https://github.com/launix-de/memcp View on GitHub]</div>
</div>

[[File:Memcp-Load.png|center|frameless|1000px|alt=MemCP database dashboard and workload overview]]

<div style="padding:.85rem 1rem; margin:1rem 0 2rem; border-left:5px solid #d99b00; background:#fff7d6; color:#332600;">
'''Development status: Beta.''' MemCP is under active development. Check [[Supported SQL]], [[Current Status and Open Issues]] and the durability requirements of your workload before migrating production data.
</div>

<div style="padding:1rem 1.15rem; margin:1rem 0 2rem; border-left:5px solid #76b512; background:#f5f8f0; color:#17202a;">
'''Your MySQL queries are too slow?''' Run the same representative workload on MemCP instead of assuming another index or a larger MySQL server is the only answer. Compare results, durability and authenticated end-to-end latency; if the migration gates pass, move the performance-critical workload to MemCP. [[MySQL is too slow|Start the MemCP performance evaluation →]]
</div>

<div style="padding:1rem 1.15rem; margin:1rem 0 2rem; border:1px solid #b7c99a; border-radius:9px; background:#f5f8f0; color:#17202a;">
'''Observed performance profile:''' MemCP has achieved '''up to 10× the performance of MariaDB/PostgreSQL''' in OLAP and search-oriented workflows, where RecSets and compressed column scans avoid wide row materialization. Isolated OLTP paths currently take about '''1.3–2.0× as long''', but in complete WordPress- and wiki-style page builds this has made '''no significant difference to overall page-loading time''' in the measured application workflows. Filtered-list queries over roughly one million documents, by contrast, have taken around '''30 seconds on PostgreSQL'''. These are workload observations, not universal guarantees; [[Performance Measurement|reproduce them on your data]].
</div>

== What is MemCP? ==

'''MemCP Database''' is a persistent, compressed, column-oriented SQL database for mixed OLTP and OLAP workloads. It speaks the MySQL client protocol, also exposes SQL over HTTP, and executes supported queries through a functional compiler and a parallel column-storage engine.

The name on this site refers to the database project. MemCP Database is '''not''' a Model Context Protocol (MCP) memory server and is unrelated to the C/C++ <code>memcpy()</code> memory-copy function. When writing about or linking to the project, the unambiguous name is '''MemCP Database'''.

== Problems MemCP solves ==

<div style="display:grid; grid-template-columns:repeat(auto-fit,minmax(240px,1fr)); gap:1rem; margin:1rem 0 2rem;">
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.1rem; font-weight:700; margin-bottom:.4rem;">Slow filtered lists on millions of rows</div>
Complex <code>WHERE</code>, joins, membership tests, <code>ORDER BY</code> and <code>LIMIT</code> can make a row store inspect or materialize far more data than the page returns. MemCP uses RecSets, late materialization, adaptive indexes and ordered braking to keep the working domain compact. [[MySQL is too slow|Evaluate the query]] · [[RecSets|How RecSets work]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.1rem; font-weight:700; margin-bottom:.4rem;">Slow GROUP BY, COUNT and dashboards</div>
Column scans read only referenced compressed values. Parallel shard-local aggregation, group caches and computed structures target repeated analytical queries over fresh operational data. [[Columnar Storage]] · [[Temporary Computed Columns]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.1rem; font-weight:700; margin-bottom:.4rem;">Slow database writes caused by fsync</div>
Durability is selectable per table. <code>safe</code> protects commits through power loss; <code>logged</code> has measured about 10× its write throughput when process-crash recovery is sufficient. [[Persistency and Performance Guarantees|Choose a write strategy]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.1rem; font-weight:700; margin-bottom:.4rem;">Database writes wear out an SD card</div>
For reconstructible edge data, <code>sloppy</code> avoids a continuous WAL and normally publishes one compressed generation every 15 minutes, reducing constant flash writes while making the loss window explicit. [[Hardware Requirements]] · [[Persistency and Performance Guarantees]]
</div>
</div>

== Choose your path ==

<div style="display:grid; grid-template-columns:repeat(auto-fit,minmax(220px,1fr)); gap:1rem; margin:1rem 0 2rem;">
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.15rem; font-weight:700; margin-bottom:.4rem;">Evaluate MemCP</div>
Understand the workload model, current status, hardware needs and differences from MySQL.

[[What is OLTP and OLAP|OLTP and OLAP]] · [[Comparison: MemCP vs. MySQL|Compare with MySQL]] · [[Hardware Requirements|Hardware requirements]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.15rem; font-weight:700; margin-bottom:.4rem;">Build an application</div>
Use the MySQL protocol, SQL over HTTP, RDF or application-specific endpoints inside MemCP.

[[Advanced SQL Tutorial|SQL tutorial]] · [[SQL over REST|SQL over HTTP]] · [[Database Tools compatibility with MemCP|Client tooling]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.15rem; font-weight:700; margin-bottom:.4rem;">Operate MemCP</div>
Plan deployment, migration, persistence, storage backends, settings and performance measurement.

[[Deployment]] · [[Persistency and Performance Guarantees|Durability]] · [[Migration from MySQL and PostgreSQL|Migration]]
</div>
<div style="padding:1rem 1.1rem; border:1px solid #b7c99a; border-radius:9px;">
<div style="font-size:1.15rem; font-weight:700; margin-bottom:.4rem;">Understand and contribute</div>
Explore the storage engine, optimizer, embedded Scheme runtime and project internals.

[[Query Planner and Physical Lowering|Query planner]] · [[RecSets]] · [[Columnar Storage]] · [[Contributing]]
</div>
</div>

== Quickstart ==

=== Run with Docker ===

<syntaxhighlight lang="bash">
docker run --name memcp \
-e ROOT_PASSWORD='choose-a-password' \
-p 4321:4321 -p 3307:3307 \
-v memcp-data:/data \
carli2/memcp:latest
</syntaxhighlight>

Then open <code>http://localhost:4321</code> and connect a MySQL application to <code>127.0.0.1</code> on port '''3307'''. The volume mounted at <code>/data</code> retains persistent table data across container replacement. See [[Install MemCP with Docker|the complete Docker guide]] before deploying it as a service.

=== Build from source ===

<syntaxhighlight lang="bash">
git clone https://github.com/launix-de/memcp
cd memcp
go mod download
make
./memcp --api-port=4321 --mysql-port=3307 lib/main.scm
</syntaxhighlight>

Connect with MySQL tooling:

<syntaxhighlight lang="bash">
mysql -h 127.0.0.1 -u root -p -P 3307
# Enter the password selected for this data directory.
</syntaxhighlight>

The development default for a fresh data directory is <code>admin</code>. Change it before exposing MemCP to another machine. MemCP can also be supervised with PM2:

<syntaxhighlight lang="bash">
pm2 start ./memcp --name memcp -- \
--no-repl -data ./data --api-port=4321 --mysql-port=3307 lib/main.scm
</syntaxhighlight>

Background deployments must use <code>--no-repl</code> so that closing standard input does not terminate the interactive console and stop the server. See [[Compile MemCP from Source|Build from Source]], [[Deployment]] and [[MemCP Console]].

== Key features ==

<div style="display:grid; grid-template-columns:repeat(auto-fit,minmax(260px,1fr)); gap:.8rem; margin:1rem 0;">
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''High performance''' Parallel, batch-oriented query execution is designed for multicore CPUs, compact working sets and fast persistent storage, serving both OLTP and OLAP workloads.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Columnar storage''' Data is stored by column for improved compression, a smaller memory footprint and fast analytical access.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Persistent in-memory operation''' MemCP is designed to keep active data in memory while offering configurable per-table durability and persistence backends.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Fast configurable writes''' Choose durability per table: <code>safe</code> protects committed data through power loss, while measured <code>logged</code> paths have reached about 10× its write throughput; <code>sloppy</code> batches reconstructible data into the normal 15-minute rebuild cycle.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Built-in APIs''' SQL over HTTP and in-database services can remove an extra middleware hop for suitable applications.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Adaptive compression''' Bit-packing, dictionary encoding and sequence compression reduce the bytes read for suitable data. Historical imports have reached reductions around 80% versus their MySQL/MariaDB representation; measure your own schema.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Simple deployment''' Start with Docker, PM2 or the native binary. The compact application has historically had an installation footprint of approximately 10 MB.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #76b512; background:#f5f8f0; color:#17202a;">'''Extensible frontends''' The Go storage engine and embedded Scheme environment support SQL, RDF, REST and custom application interfaces.</div>
<div style="padding:.9rem 1rem; border-top:4px solid #9ad32d; background:#f5f8f0; color:#17202a;">'''Cost-based query execution''' Logical decorrelation and join optimization are separated from cost-based physical selection of scans, indexes, RecSets, reusable caches and execution pipelines. [[Query Planner and Physical Lowering|How MemCP plans and lowers queries →]] · [[RecSets|How compact record sets process large domains →]]</div>
</div>

== Why MemCP? ==

Traditional relational databases were designed around spinning disks and comparatively small numbers of CPU cores. MemCP rethinks storage and query execution for modern multicore systems, large caches, fast storage and mixed workloads.

Typical use cases include:

* real-time dashboards and analytics;
* data-heavy SaaS platforms;
* embedded systems with limited resources;
* high-throughput OLTP/OLAP hybrids.

Whether MemCP is a good fit depends on the required SQL compatibility, durability, query mix, data size and operational environment. Review [[Supported SQL]], [[Persistency and Performance Guarantees]] and [[Performance Measurement]] rather than treating benchmark figures as universal guarantees.

== MemCP vs. MySQL ==

{| class="wikitable" style="width:100%;"
! Feature
! MySQL
! MemCP
|-
| Storage model
| Primarily row-based
| Column-based and compressed
|-
| Performance focus
| General-purpose relational workloads
| Parallel, in-memory-oriented execution for mixed operational and analytical workloads
|-
| In-memory capability
| Available through selected engines and caching
| Central design goal and default operating model
|-
| REST API integration
| Normally external
| Built in
|-
| Installation footprint
| Common server installations are approximately 150 MB or larger
| The native application has historically been approximately 10 MB
|-
| Open source
| ✅
| ✅
|}

MemCP provides MySQL protocol compatibility but does not claim to implement every MySQL feature. See [[Comparison: MemCP vs. MySQL]] for a detailed comparison and [[Database Tools compatibility with MemCP]] for client compatibility.

== Architecture overview ==

* '''Tables, schemas and columns''': Familiar SQL structures use a compressed columnar physical layout. See [[Databases, Tables and Columns]] and [[Columnar Storage]].
* '''Transaction model''': Delta and main storage support mixed OLTP and OLAP semantics. See [[Transactions and Isolation]] and [[Shards, RecordIDs, Main Storage, Delta Storage]].
* '''Query planning''': Logical optimization is separated from physical execution decisions. See [[Query Planner and Physical Lowering]].
* '''Persistence''': Per-table durability can use filesystem, S3 or Ceph/RADOS storage. See [[Persistency and Performance Guarantees]] and [[Storage Backends]].
* '''Operations''': The dashboard, metrics, process inspection and memory controls support day-to-day operation. See [[Dashboard and Operations]] and [[Memory Management and Eviction]].
* '''Scale-out roadmap''': MemCP is single-node today. A leaderless CRUSH/RADOS and MOESI-inspired cluster design is planned, but must not be treated as an available production feature. See [[Cluster Monitor]].
* '''Frontends''': MemCP provides multiple query and application interfaces:
** SQL through the MySQL wire protocol and SQL over HTTP;
** an RDF/graph query frontend;
** custom APIs through in-database web applications.

== Documentation ==

The following navigation remains on the start page so users and search engines can reach every major documentation area directly.

=== Introduction and evaluation ===

* [[What is OLTP and OLAP]]
* [[History of the MemCP project]]
* [[Hardware Requirements]]
* [[Persistency and Performance Guarantees]]
* [[Current Status and Open Issues]]
* [[MySQL is too slow|SQL performance problems MemCP solves]]
* [[Comparison: MemCP vs. MySQL]]

=== Getting started ===

* [[Install MemCP with Docker|Install with Docker]]
* [[With Singularity|Install with Singularity/Apptainer]]
* [[Compile MemCP from Source|Build from Source]]
* [[Contributing]]
* [[Introduction to Scheme]]
* [[Full SCM API documentation]]

=== Administration ===

* [[Deployment]]
* [[Migration from MySQL and PostgreSQL]]
* [[Settings]]
* [[Security and Authentication]]
* [[Dashboard and Operations]]
* [[Memory Management and Eviction]]
* [[Process Hibernation]]
* [[Performance Measurement]]
* [[MemCP Console]]

=== Frontends ===

==== SQL frontend ====

* [[Supported SQL]]
* [[Advanced SQL Tutorial]]
* [[JSON|JSON and SQL/JSON]]
* [[Triggers]]
* [[SQL over REST]]
* [[Database Tools compatibility with MemCP|Supported Tooling]]
* [[Query Planner and Physical Lowering|How SQL operators are implemented]]
* [[Add custom SQL operators to MemCP]]

==== RDF frontend ====

* [[Introduction to RDF]]
* [[Advanced Graph Querying]]
* [https://github.com/launix-de/rdfop RDF browser and templating example]

==== Custom frontends ====

* [[In-Database WebApps and REST Services]]
* [[MemCP for Microservices]]
* [[Websockets in MemCP]]

=== Persistence backends and storage ===

* [[File System]]
* [[Storage Backends|S3-compatible and Ceph/RADOS storage]]
* [[Cluster Monitor]]

=== Internals ===

==== How MemCP works ====

* [[Databases, Tables and Columns]]
* [[Shards, RecordIDs, Main Storage, Delta Storage]]
* [[Columnar Storage]]
* [[Transactions and Isolation]]
* [[Query Planner and Physical Lowering]]
* [[Full SCM API documentation]]

==== Scheme documentation ====

* [[SCM Builtins]]
* [[Arithmetic / Logic]]
* [[Strings]]
* [[Streams]]
* [[Lists]]
* [[Associative Lists / Dictionaries]]
* [[Date]]
* [[Vectors]]
* [[Parsers]]
* [[Sync]]
* [[IO]]
* [[Storage]]

==== Optimizations ====

* [[Query Planner and Physical Lowering]]
* [[In-Memory Compression, Columnar Compression Techniques]]
* [[Temporary Computed Columns]]
* [[Data Auto Sharding and Auto Indexing]]
* [[Parallel Computing]]

== Further reading ==

* [https://github.com/launix-de/memcp MemCP on GitHub]
* [https://www.vldb.org/pvldb/vol13/p2649-boncz.pdf VLDB Research Paper]
* [https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf LNI Proceedings Paper]
* [https://www.dcs.bbk.ac.uk/~dell/teaching/cc/paper/sigmod10/p135-malewicz.pdf Large Graph Algorithms]

Additional blog posts on design decisions, compression techniques and performance optimization are available on the [https://launix.de/launix/ Launix blog].

== Community ==

MemCP is an open-source project maintained by developers for developers. Contributions are welcome in the form of bug reports, feature requests and pull requests.

See [[Contributing]] and the [https://github.com/launix-de/memcp GitHub repository].

Auto Sharding

2026-08-28T09:59:41Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

#REDIRECT [[Data Auto Sharding and Auto Indexing]]

With Singularity

2026-08-28T09:59:41Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Run MemCP with Singularity/Apptainer =

Build the repository-provided image target:

<syntaxhighlight lang="bash">
git clone https://github.com/launix-de/memcp
cd memcp
make memcp.sif
apptainer run memcp.sif
</syntaxhighlight>

Bind a persistent host directory to the container data path and expose or forward the HTTP/MySQL ports according to the local Apptainer installation. Set a strong initial root password for a fresh data directory and keep the image, application modules, and data directory versions together during upgrades.

Singularity installations can use the corresponding <code>singularity</code> command. See [[Deployment]], [[Persistency and Performance Guarantees]], and [[Security and Authentication]].

What is OLTP and OLAP

2026-08-28T09:59:41Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= What is OLTP and OLAP? =

'''OLTP''' describes operational workloads with frequent inserts, updates, deletes, point lookups, constraints, and short transactions. '''OLAP''' describes analytical workloads that scan, group, join, and aggregate larger portions of a dataset. Real applications commonly mix both.

Row storage can favor whole-record access; column storage can reduce memory traffic when a query uses only a few attributes. Neither layout alone determines whether a system can provide transactions, durability, or good point lookup performance.

MemCP targets mixed workloads. Delta structures, indexes, constraints, and transaction machinery serve operational writes, while compressed columns, batch/parallel scans, and reusable computed relations serve analytical queries. Persistent cold columns may be evicted and reloaded, so workload behavior also depends on memory budget and storage backend.

== Typical query shapes ==

{| class="wikitable"
! Workload !! Typical operations !! Important properties
|-
| OLTP || create an order, update inventory, look up one account, enforce unique/foreign keys || short latency, concurrency, atomicity, durability, selective indexes
|-
| OLAP || revenue by month/region, cohort analysis, wide joins, windows, top-k reports || batch scans, compression, parallelism, grouping, ordering, memory bandwidth
|-
| Hybrid/HTAP || ingest events while dashboards aggregate the latest state || consistent visibility between writes and scans, predictable maintenance, no stale export pipeline
|}

== Combining both in MemCP ==

New writes land in delta state suited to change, while rebuilt main columns use an encoding chosen from their values. Scans combine both under transaction visibility. The planner can use selective indexes for point access and compressed batch pipelines for analytical work, and table ENGINE choice separates durability requirements from physical query layout.

That does not make every mixed workload free of trade-offs. High-contention writes, a hot set larger than RAM budget, long-running snapshots, frequent rebuilds, or analytical queries touching nearly every column can interfere with operational latency. Measure them concurrently, not as isolated best cases.

Whether one MemCP instance is suitable depends on isolation, durability, SQL compatibility, query mix, hot-set size, recovery needs, and measured performance. See [[Transactions and Isolation]], [[Persistency and Performance Guarantees]], and [[Performance Measurement]].

Websockets in MemCP

2026-08-28T09:59:40Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Websockets in MemCP =

An embedded HTTP handler can upgrade a request through the response object's WebSocket facility and receive messages through a callback. The <code>apps/minigame.scm</code> example in the repository demonstrates the current API and routing pattern.

WebSockets keep one bidirectional connection open for live dashboards, notifications, collaborative state, games, or change feeds. They are a delivery channel, not a durable message queue: reconnect, replay, acknowledgement, ordering, and missed-message semantics belong to the application.

== Server-side upgrade ==

<syntaxhighlight lang="scheme">
(set send ((res "websocket")
(lambda (message) (print "message: " message))
(lambda () (print "connection closed"))))
(send 1 "Hello from MemCP")
</syntaxhighlight>

The numeric first argument is the WebSocket frame opcode used by the current response API. Keep the sending function only for the connection lifetime.

A browser client can connect to the routed endpoint with the standard API:

<syntaxhighlight lang="javascript">
const socket = new WebSocket("wss://example.test/minigame/ws");
socket.addEventListener("open", () => socket.send(JSON.stringify({type: "hello"})));
socket.addEventListener("message", event => console.log(JSON.parse(event.data)));
</syntaxhighlight>

== Complete routed example ==

The repository's <code>apps/minigame.scm</code> shows the complete routing pattern: preserve the previous handler, serve an HTML/JavaScript client below one prefix, and upgrade only the WebSocket path.

<syntaxhighlight lang="scheme">
(define http_handler (begin
(set old_handler http_handler)
(lambda (req res) (begin
(match (req "path")
"/minigame/ws" (begin
(set send ((res "websocket")
(lambda (message) (begin
(print "message: " message)
(send 1 (concat "echo: " message))))))
(send 1 "Hello from MemCP"))
(regex "^/minigame/(.*)$" path asset) (begin
((res "header") "Content-Type" "text/plain")
((res "status") 200)
((res "print") "WebSocket client assets belong here"))
(old_handler req res))))))
</syntaxhighlight>

When this module is imported by <code>lib/main.scm</code>, the existing server uses the wrapped handler. A standalone application can call <code>serve</code> explicitly. Keep the receive callback and its connection-local <code>send</code> closure together; never store that closure after close.

Prefer JSON messages with an explicit type and version. Validate the decoded shape before reading fields, cap message size, and define how the client resubscribes or resumes after reconnect.

== Database-backed publish/subscribe ==

Prepare fixed queries during module startup, create a session per connection, and bind authenticated identity and subscription state there. A receive callback can update state atomically; query results can be emitted through <code>send</code>. On close, release subscriptions and other references so they do not keep sessions or sending functions alive.

For fan-out, use bounded per-client queues and a policy for slow consumers (drop/coalesce messages or disconnect). A database table can persist events for replay, but a <code>memory</code> or <code>cache</code> table cannot provide durable delivery after restart.

The earlier hardlife.io example used this structure at larger scale: prepare position-update, listener-membership and dirty-list queries once; authenticate a player handle with a message authentication code; keep the connection's player/location state in a session; mark affected listeners dirty after a position change; send only changed avatars; and remove listener state on disconnect. The complete historical listing coupled credentials, global <code>sloppy</code> defaults and application-specific tables, so reproducing it as a copy-and-paste server would be unsafe. The pipeline remains a useful design example.

Hosting the handler beside the database removes an application/database socket hop and repeated parsing for prepared shapes. That is a performance opportunity, not proof that every embedded WebSocket service is faster than a separate Node.js or Go service; benchmark the authenticated end-to-end path and account for the shared failure domain.

== Production considerations ==

Authenticate the upgrade, validate browser Origin, limit message and queue sizes, apply backpressure, and remove subscriptions when the connection closes. A slow client must not create an unbounded in-memory backlog. Long database operations should observe cancellation. Use TLS at a trusted proxy or transport layer and do not expose default credentials.

Storage ENGINE choice is independent of WebSocket delivery: <code>sloppy</code> can lose recent writes, and <code>memory</code>/<code>cache</code> are not durable. See [[In-Database WebApps and REST Services]] and [[Persistency and Performance Guarantees]].

Vectors

2026-08-28T09:59:40Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Vectors =


<div class="mw-message-box mw-message-box-notice">Generated from MemCP commit <code>c42e19eba</code> on 27 August 2026. See [[Full SCM API documentation]].</div>

The '''Vectors''' module treats numeric lists as vectors. Its current public primitive, <code>dot</code>, supports a normal dot product plus modes named <code>COSINE</code> and <code>EUCLIDEAN</code>. Vector functions are useful for scoring and mathematical expressions but do not by themselves provide storage, dimensional validation, or an approximate-nearest-neighbor index.

The implementation processes only positions present in both input lists. Callers must therefore validate equal dimensions when that is part of the data contract. In commit <code>c42e19eba</code>, <code>EUCLIDEAN</code> returns the square root of the accumulated pairwise products; this is '''not''' the standard Euclidean distance <code>sqrt(sum((a-b)^2))</code>. Do not use that mode as a distance metric without accounting for this compatibility behavior.

== dot ==

produced the dot product

'''Allowed number of parameters:''' 2–3


=== Parameters ===

* '''v1''' (<code>list</code>): vector1
* '''v2''' (<code>list</code>): vector2
* '''mode''' (<code>string</code>): DOT, COSINE, EUCLIDEAN, default is DOT ''(optional)''


=== Returns ===

* '''value''' (<code>number</code>)

Triggers

2026-08-28T09:59:40Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Triggers =

MemCP supports persistent BEFORE and AFTER triggers for INSERT, UPDATE, and DELETE. Trigger bodies can read <code>OLD</code> and <code>NEW</code>, assign permitted <code>NEW</code> values, run conditional BEGIN/END bodies, and execute supported DML including <code>INSERT … SELECT</code>.

Multiple triggers execute by registered priority. AFTER work may be configured as asynchronous, in which case its completion and failure are not identical to the originating statement's synchronous path. Trigger changes participate in transaction/savepoint recovery.

MemCP also uses hidden system triggers to enforce constraints and maintain computed columns, group caches, prejoins, and invalidation dependencies. [[Temporary Computed Columns]] explains how the storage engine analyzes generated Scheme computors and derives incremental, selective, or complete invalidation triggers automatically. User DROP/CASCADE behavior must not delete unrelated data; lifecycle callbacks are not general garbage collectors.

Test trigger persistence across restart, multi-row statements, NULL/default handling, errors, ordering, cross-table writes, and rollback. Recursive trigger graphs should be treated with particular care.

Temporary Computed Columns

2026-08-28T09:59:39Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Computed Columns, Group Caches, and ORC =

<blockquote>'''Verified against commit <code>c42e19eba</code> on 28 August 2026.''' These are optimizer-managed physical structures. Applications normally write SQL; MemCP analyzes the plan, creates suitable caches, and maintains their validity automatically.</blockquote>

MemCP reuses expensive expressions and relational stages without copying complete result rows into a generic temporary table. It can attach one computed value to an existing base row, keep a narrow table of grouping keys and aggregates, or maintain an order-dependent column. The surrounding base columns remain where they already are.

This matters for queries that join or filter a million rows, sort by one derived value, and finally return only 100 rows. A conventional wide temporary result may copy every projected column before <code>ORDER BY ... LIMIT</code>. MemCP instead tries to materialize only the value, key domain, [[RecSets|record set]], or ordering dependency needed by the next physical stage.

== The three reusable structures ==

{| class="wikitable"
! Structure !! Logical meaning !! Stored shape !! Typical use
|-
| Ordinary computed column || One derived value per live row of an existing table || A lazy <code>StorageComputeProxy</code> backed by compressed main values, sparse repaired values, and a validity mask || Repeated scalar expressions, lookup results, JSON paths, correlated scalar probes, computed ordering
|-
| Group cache/keytable || One row per distinct grouping/domain key plus computed aggregate columns || A narrow internal <code>ENGINE=cache</code> table with a unique key || Reused <code>GROUP BY</code>, correlated aggregates, prepared membership/group domains
|-
| ORC (ordered-reduce column) || One value whose computation depends on partition/order predecessors || An ordered computed proxy with per-row validity and dependency-range repair || Running totals, rank/row-number-like work, reusable window/order calculations
|}

The SQL plan cache is different: it stores compiled plan formulas, not computed rows. A query may reuse a compiled formula without having a warm group cache, or reuse a computed cache after a newly compiled query recognizes the same canonical definition.

== From SQL expression to canonical cache ==

MemCP does not name reusable structures after a query's disposable aliases. During logical planning and physical lowering it analyzes the expression and constructs a canonical identity from the data the structure represents:

* table aliases are rewritten to stable source roles, while two roles in a self-join remain distinct;
* column references include physical schema, relation, and column identity;
* grouping keys, source graph, filters, aggregate recipe, partition/order definition, and relevant bounds become part of the identity;
* SELECT aliases, formatting, and unrelated output columns do not create a second cache;
* stable structural hashes produce names such as internal <code>.grp:...</code>, <code>agg_...</code>, and <code>__orc_...</code> objects.

Consequently, two queries can share a computed aggregate even when their SQL aliases or final projections differ. A second aggregate over the same group domain can add another narrow computed column to the existing keytable instead of cloning the entire intermediate relation. Conversely, a different filter, join role, ordering recipe, or semantic dependency receives a different identity rather than incorrectly sharing values.

Preparation filters are deliberately absent from a computed column's logical identity. They only say which values are likely to be consumed soon and should be warmed eagerly. Every other live row still has a well-defined value that can be computed later.

== Automatic analysis of the generated Scheme code ==

The planner emits a <code>createcolumn</code> call containing the computation lambda and its direct input columns. This is not the end of dependency discovery. The storage engine walks the resulting Scheme AST/procedure to determine what the computation reads and how source changes can reach the cached value.

The analyzer:

# removes source-location wrappers and accepts both unresolved symbols and already-resolved builtin procedures;
# descends through lambdas and nested physical expressions;
# recognizes <code>scan</code>, <code>scan_order</code>, <code>scalar_scan</code>, and <code>scalar_scan_order</code> calls;
# also examines a scan's table expression, because a dynamic physical source can itself contain dependency-producing scans;
# resolves literal table handles or canonical table expressions to schema and relation;
# reads filter and map column lists from both quoted and constructed-list forms;
# records every source column used by the filter or mapper;
# inspects conjunctions of equality predicates and maps a source scan parameter back to the outer computed-column input it equals;
# follows source columns that are themselves computed and expands them transitively to their physical input, map, partition, and sort columns;
# extracts session-variable dependencies and creates session-specific cache variants where a value must not leak between users or sessions.

For example, a generated lookup conceptually shaped like this:

<syntaxhighlight lang="scheme">
(lambda (customer_id)
(scan tx (table "shop" "payments")
'("customer_id")
(lambda (payment_customer)
(equal? payment_customer (outer customer_id)))
'("amount")
(lambda (amount) amount)
+
0))
</syntaxhighlight>

tells the storage analyzer that <code>payments.customer_id</code> is the reverse lookup key for the target row's <code>customer_id</code>, and that <code>payments.amount</code> is relevant to the value. An UPDATE of an unrelated <code>payments.note</code> column therefore need not invalidate the cache.

The analysis is deliberately conservative. If a scan or reverse key relationship cannot be proven from the generated code, MemCP does not guess. It generates complete-column invalidation so the next read recomputes correct values. More analyzable code improves maintenance precision; opaque code must remain correct.

== Automatic generation of maintenance triggers ==

When the computed-column signature is installed or changes, <code>createcolumn</code> calls the storage engine's trigger generator. It registers hidden system triggers on every discovered source table. Registration is idempotent, so preparing the same canonical cache again updates/reuses its metadata instead of accumulating duplicate triggers.

{| class="wikitable"
! Analyzed shape !! Generated reaction to source INSERT/UPDATE/DELETE
|-
| Direct row-local inputs || The corresponding row value is marked stale or repaired through the computed proxy.
|-
| Lookup with proven equality key || Scan only target cache rows whose key equals the source row's <code>OLD</code> and/or <code>NEW</code> key, then invalidate that exact subset.
|-
| Additive aggregate (<code>+</code>, neutral zero, analyzable map without nested scans) || Apply the mapped delta with <code>$increment:<column></code>; UPDATE subtracts OLD and adds NEW when the group key stays stable.
|-
| Additive UPDATE whose group key changes || Invalidate safely because old and new keytable membership may differ.
|-
| COUNT-style constant-one helper || Prefer robust invalidation, because the value also controls empty/non-empty group visibility.
|-
| ORC with known partition and sort key || Invalidate the affected partition from the OLD/NEW sort position onward.
|-
| Opaque or unsupported dependency || Invalidate the complete computed column.
|}

The generated triggers run after INSERT, UPDATE, and DELETE. UPDATE triggers compare only columns proven relevant by the analysis and skip maintenance if none changed. They use the trigger row dictionaries <code>OLD</code> and <code>NEW</code> to find both sides of a key-changing update.

Selective invalidation is itself batched. The internal <code>$invalidate:<column></code> callback collects matching record IDs during the maintenance scan; invalidation is applied after scan locks are released. Incremental changes are similarly accumulated per proxy and record ID before <code>$increment:<column></code> updates cached values. This avoids nested lock acquisition in the inner scan loop.

System maintenance triggers are hidden from ordinary <code>SHOW TRIGGERS</code>; they are implementation-owned rather than user DDL. User triggers and the computed-cache trigger graph still share the storage engine's defined timing and locking machinery.

=== Dependency chains and invalidation waves ===

A cached column can depend on another computed cache. The storage engine registers <code>AfterInvalidate</code> edges in addition to source DML triggers. Invalidating the lower cache synchronously propagates to its dependants.

One invalidation wave carries a query/goroutine-local visited set keyed by target table and column. This makes repeated edges idempotent and prevents a malformed cycle from recursing forever without introducing a global invalidation lock. Selective invalidation is currently propagated conservatively at column level when a downstream reverse mapping cannot be preserved safely.

=== Lifecycle cleanup ===

The generator also owns cleanup:

* dropping an internal target cache removes the triggers it installed on source tables;
* dropping a source table can drop dependent dot-prefixed helper tables so a later table with the same name cannot inherit stale data;
* group-key maintenance reacts to source INSERT, UPDATE, and DELETE;
* dropping the base table or a grouping column removes its dependent keytable;
* trigger target leases pin a cache while maintenance is using it, preventing concurrent eviction from invalidating a live pointer.

These lifecycle triggers must only remove the helper objects they explicitly own. They are not general garbage collection and must never delete unrelated user data.

== Race-free initial construction ==

Installing triggers after filling a cache would leave a lost-update window: a source write between the snapshot and trigger registration would never invalidate the new value. <code>initialize_cache_table</code> closes that race.

It resolves and sorts all source tables into a deterministic lock order, blocks source mutations, registers maintenance, fills the canonical cache from a consistent source view, optionally finalizes/rebuilds it, and only then releases the locks. Concurrent attempts to initialize the same generation wait for the shared result rather than performing duplicate fills.

Internal keytables use <code>ENGINE=cache</code> and can store this initializer as a closed <code>oninit</code> procedure. After restart or eviction, the first idempotent creation guard rebuilds the empty generation before exposing it to consumers. See [[Persistency and Performance Guarantees]] for why cache data is reconstructible rather than durable.

== Lazy values, eager warming, and repair ==

An ordinary computed column is a complete logical column even when few physical values exist:

* <code>Compress</code> can eagerly calculate all rows when the plan expects broad consumption;
* <code>CompressFiltered</code> warms only rows selected by a preparation filter;
* reading a missing or invalid ordinary value calculates that row from its current inputs and stores the result;
* valid compressed main values use the normal bulk column-reader fast path;
* repeated <code>createcolumn</code> calls for the same signature preserve valid cached values and repair only missing/dirty state;
* a materially different semantic formula receives a different canonical identity; reissuing the same signature preserves its valid cache. ORC metadata changes explicitly invalidate the affected ordered proxies.

For broad selective invalidations, each proxy compares a short measured repair sample with its last full recomputation cost. If point repair is predicted to cost more, it marks the whole column dirty and lets the next consumer perform one coherent rebuild. This adapts to the actual computor rather than relying on one global row-count threshold.

The historical documentation said computations become parallel above 60,000 rows. That threshold is no longer the public execution contract. Current preparation fans work out over active shards, while planner cost, shard count, input size, and the maintenance/recompute path determine useful parallelism. Large independent shards can compute concurrently; a tiny table or a single ordered dependency cannot gain speed merely from crossing a fixed row number.

== GROUP BY through narrow computed relations ==

A reusable group cache conceptually follows four steps:

# create a canonical internal table containing a UNIQUE key over all grouping columns;
# populate the distinct grouping keys;
# add one computed column for each aggregate or derived ordering value;
# scan the narrow group relation and join/project base values only where the final result needs them.

<syntaxhighlight lang="sql">
CREATE TABLE t (a TEXT, b INT);
INSERT INTO t VALUES ('foo', 1), ('bar', 2), ('foo', 3);

SELECT a, SUM(b) AS total
FROM t
GROUP BY a;
-- bar | 2
-- foo | 4
</syntaxhighlight>

The keytable cleanup trigger inserts a missing key idempotently, removes a key after the last source row leaves the group, and handles OLD and NEW keys on UPDATE. It runs at priority 90, before aggregate invalidation at priority 100, so key membership is current when a value is repaired.

GROUP BY syntax does not force a cache. The physical lowerer compares a one-pass fused aggregation with keytable construction, expected probes/reuse, maintenance cost, memory, and available alternative carriers. Query-local or UNION-shaped groups may instead be filled in one batch, while reusable base-table groups can use storage-managed computed columns.

Different surrounding WHERE ranges can reuse the same row-local computed expression because the preparation predicate does not define the column. A group cache, however, includes the grouping domain and semantic source filter in its canonical identity: two genuinely different group populations must not share one aggregate value.

== Order-dependent computed columns (ORC) ==

An ORC is not pointwise. A running total at row 500 depends on preceding rows in its partition, so changing row 100 may invalidate a suffix rather than just row 100.

The ORC definition records sort columns/directions, the number of leading partition columns, map columns/function, reducer, and neutral value. Generated triggers:

* use equality bounds for the partition prefix;
* invalidate a range beginning at the changed OLD or NEW sort key;
* leave unrelated partitions valid;
* fall back to complete invalidation when a cross-table dependency cannot be mapped selectively;
* compare cumulative selective-invalidation cost with the last suffix/full recomputation and choose complete invalidation when that is cheaper.

On the next read, the ordered proxy repairs the dependency range under the table's ORC recomputation guard. Concurrent readers do not observe the internal “invalid” sentinel used by the reducer. If one ordered scan can compute a window directly, the planner may fuse it instead of creating an ORC at all.

== Cost, eviction, and observability ==

The physical lowerer compares:

* direct scalar/index probes;
* a fused one-pass scan or aggregate;
* a [[RecSets|RecSet]] carrier;
* a canonical computed column;
* an FK-backed/group cache;
* an ORC;
* a narrow temporary relation.

Inputs include build work, expected reuse, source and target cardinality, maintenance/invalidation work, memory, ordering, LIMIT, and available indexes. <code>EXPLAIN PHYSICAL</code> shows the selected physical family for supported decisions; it is better evidence than assuming repeated SQL text always builds a cache.

Temporary computed columns and keytables are accounted by the global memory manager. Access refreshes their lease/last-used state. Because they are reconstructible, the manager can evict them under pressure; a later query recreates or lazily repairs them. Base data remains authoritative throughout.

== Correctness boundaries ==

* A preparation filter changes warming, never the logical domain or NULL value of a computed column.
* A cached value must never outlive a source change without an invalidation/maintenance edge.
* Opaque code requires conservative invalidation; absence of a recognized equality is not evidence of independence.
* Session-dependent formulas require isolated variants and must not reuse one user's value for another session.
* ORC invalidation follows ordering dependencies and cannot be replaced by unrelated point repair.
* Internal trigger generation must be complete before source mutations are unblocked.
* Cache eviction and source-table DDL must remove their owned trigger graph without touching user data.

For exact low-level signatures see [[Storage]]. Related architecture is documented in [[Query Planner and Physical Lowering]], [[Triggers]], [[Columnar Storage]], [[Memory Management and Eviction]], [[RecSets]], and [[Parallel Computing]].

Sync

2026-08-28T09:59:39Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Sync =


<div class="mw-message-box mw-message-box-notice">Generated from MemCP commit <code>c42e19eba</code> on 27 August 2026. See [[Full SCM API documentation]].</div>

The '''Sync''' module provides explicit coordination for state that must cross functional scopes or concurrent tasks. It includes:

* thread-safe sessions and contextual values;
* promises and one-time publication;
* mutex and serialized execution helpers;
* shared computation and cache maps;
* timing, waiting, and lifecycle coordination.

Prefer ordinary immutable values when no sharing is required. Use these primitives to make ownership, failure, and publication explicit rather than relying on outer-scope mutation, which this Scheme dialect does not provide.

== newpromise ==

Creates a thread-safe promise that lets parallel work publish one result for other code to inspect. Use it for shared initialization, asynchronous results, or ensuring that only the first successful producer resolves a value. Read with (promise "value"), inspect completion with (promise "state"), resolve with (promise "value" value), resolve exactly once with (promise "once" value), and mark failure with (promise "fail" error).

'''Allowed number of parameters:''' 0–1


=== Parameters ===

* '''storage''' (<code>list<any></code>): optional existing two-item list used to hold the promise state; most callers omit this ''(optional)''
** '''slot''' (<code>any</code>): promise state or value slot


=== Returns ===

* '''promise''' (<code>func</code>): operation-based accessor for reading, resolving, or failing the promise
** '''Parameters'''
*** '''operation''' (<code>string</code>): one of: value, state, fail, once
*** '''value''' (<code>any</code>): value to store (for value/once/fail) ''(optional)''
*** '''message''' (<code>string</code>): optional error message used when once finds an already completed promise ''(optional)''
** '''Returns'''
*** '''result''' (<code>any</code>): stored value, state flag, or operation result

== newsession ==

Creates a thread-safe key-value session. Call it without arguments to list values, with a key to read, with a key and value to store, or with get_or_compute_scoped, a scope, a key, and a producer to share one concurrent computation.

'''Allowed number of parameters:''' 0–0


=== Parameters ===

''This function has no parameters.''


=== Returns ===

* '''session''' (<code>func</code>): session accessor accepting exactly zero, one, two, or four arguments
** '''Parameters'''
*** '''key_or_operation''' (<code>any</code>): key, or get_or_compute_scoped ''(optional)''
*** '''value_or_scope''' (<code>any</code>): value to store, or scope for get_or_compute_scoped ''(optional)''
*** '''scoped_key''' (<code>any</code>): cache key used by get_or_compute_scoped ''(optional)''
*** '''scoped_producer''' (<code>func</code>): producer used only by the four-argument get_or_compute_scoped form ''(optional)''
**** '''Returns'''
***** '''value''' (<code>any</code>): computed value cached for the scope and key
** '''Returns'''
*** '''result''' (<code>any</code>): value list, stored value, retrieved value, or shared computed value

== with_session ==

Executes a function with the given session installed in the execution context, so storage operations can access the session's transaction state.

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''session''' (<code>func</code>): the session to install
** '''Parameters'''
*** '''key''' (<code>any</code>) ''(optional)''
*** '''value''' (<code>any</code>) ''(optional)''
** '''Returns'''
*** '''value''' (<code>any</code>)
* '''fn''' (<code>func</code>): the function to execute
** '''Returns'''
*** '''value''' (<code>any</code>)


=== Returns ===

* '''value''' (<code>any</code>)

== context ==

Context helper function. Each context also contains a session. (context func args) creates a new context and runs func in that context, (context "session") reads the session variable, (context "check") will check the liveliness of the context and otherwise throw an error

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''args...''' (<code>any</code>): depends on the usage ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== sleep ==

sleeps the amount of seconds

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''duration''' (<code>number</code>): number of seconds to sleep


=== Returns ===

* '''value''' (<code>bool</code>)

== once ==

Creates a function wrapper that you can call multiple times but only gets executed once. The result value is cached and returned on a second call. You can add parameters to that resulting function that will be passed to the first run of the wrapped function.

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''f''' (<code>func</code>): function that produces the result value
** '''Parameters'''
*** '''argument''' (<code>any</code>) ''(variadic)''
** '''Returns'''
*** '''result''' (<code>any</code>)


=== Returns ===

* '''once_wrapper''' (<code>func</code>): calls the wrapped function once and returns its cached result thereafter
** '''Parameters'''
*** '''args''' (<code>any</code>): arguments forwarded to the wrapped function on first call ''(variadic)''
** '''Returns'''
*** '''result''' (<code>any</code>): result cached from the first call

== mutex ==

Creates a context-aware mutex. The return value serializes calls to parameterless functions and stops waiting when the current request is cancelled.

'''Allowed number of parameters:''' 0–0


=== Parameters ===

''This function has no parameters.''


=== Returns ===

* '''locked''' (<code>func</code>): executes one parameterless function while holding the mutex
** '''Parameters'''
*** '''fn''' (<code>func</code>): parameterless function to execute under the lock
**** '''Returns'''
***** '''result''' (<code>any</code>)
** '''Returns'''
*** '''result''' (<code>any</code>): result returned by the protected function

== numcpu ==

Returns the number of logical CPUs available for parallel execution

'''Allowed number of parameters:''' 0–0


=== Parameters ===

''This function has no parameters.''


=== Returns ===

* '''value''' (<code>number</code>)

== memstats ==

Returns memory statistics as a dict with keys: alloc, total_alloc, sys, heap_alloc, heap_sys (all in bytes)

'''Allowed number of parameters:''' 0–0


=== Parameters ===

''This function has no parameters.''


=== Returns ===

* '''value''' (<code>dict</code>)


== setTimeout ==

Schedules a callback to run after the given delay in milliseconds (fractional values allowed for sub-millisecond precision).

'''Allowed number of parameters:''' 2–10000


=== Parameters ===

* '''callback''' (<code>func</code>): function to execute once the timeout expires
** '''Parameters'''
*** '''args''' (<code>any</code>) ''(variadic)''
** '''Returns'''
*** '''value''' (<code>any</code>)
* '''milliseconds''' (<code>number</code>): milliseconds until execution
* '''args...''' (<code>any</code>): optional arguments forwarded to the callback ''(variadic)''


=== Returns ===

* '''value''' (<code>int</code>)


== clearTimeout ==

Cancels a timeout created with setTimeout.

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''id''' (<code>number</code>): identifier returned by setTimeout


=== Returns ===

* '''value''' (<code>bool</code>)

Supported SQL

2026-08-28T09:59:39Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Supported SQL =

<blockquote>Verified against development commit <code>c42e19eba</code> on 27 August 2026. MemCP is Beta and does not claim complete MySQL or PostgreSQL compatibility. Unsupported shapes should fail explicitly; test a required workload before migration.
</blockquote>
This page is a capability map for application evaluation. MemCP accepts a broad MySQL-oriented dialect through its MySQL protocol and HTTP endpoint, plus PostgreSQL-oriented syntax through a separate HTTP endpoint. Both compile into the same logical planner and storage runtime, but accepted surface syntax and returned metadata can differ by frontend.

Support is defined by tested semantics, not merely by whether the parser accepts a statement. Compatibility-only clauses may intentionally be no-ops, and uncommon combinations can remain unsupported even when their individual operators are available. Use this overview to select candidates, then verify every required query, constraint, transaction, prepared statement, and client behavior against the deployed release.

== Query language ==

* <code>SELECT</code>, aliases, <code>WHERE</code>, <code>DISTINCT</code>, <code>GROUP BY</code>, <code>HAVING</code>, <code>ORDER BY</code>, <code>LIMIT</code>, and <code>OFFSET</code>;
* <code>INNER</code>, <code>LEFT</code>, <code>RIGHT</code>, and <code>CROSS JOIN</code>;
* <code>UNION</code> and <code>UNION ALL</code>, including ordered/limited set results;
* derived tables and logical SQL views;
* scalar, <code>EXISTS</code>, <code>NOT EXISTS</code>, <code>IN</code>, and <code>NOT IN</code> subqueries, including many correlated and grouped forms;
* aggregates <code>COUNT</code>, <code>SUM</code>, <code>AVG</code>, <code>MIN</code>, <code>MAX</code>, and <code>GROUP_CONCAT</code>;
* window functions including <code>ROW_NUMBER</code>, <code>RANK</code>, <code>DENSE_RANK</code>, <code>LEAD</code>, <code>LAG</code>, <code>FIRST_VALUE</code>, and <code>LAST_VALUE</code> with partition/order clauses.


== Data modification and schema ==

* single- and multi-row <code>INSERT</code>, <code>INSERT IGNORE</code>, <code>INSERT … SELECT</code>, and MySQL or PostgreSQL upsert syntax;
* <code>UPDATE</code> and <code>DELETE</code>, including qualified and supported multi-table forms;
* <code>CREATE</code>, <code>ALTER</code>, <code>RENAME</code>, <code>TRUNCATE</code>, and <code>DROP</code> for databases and tables;
* <code>CREATE VIEW</code>, indexes, primary/unique keys, foreign keys, defaults, <code>AUTO_INCREMENT</code>, computed expressions, types, collations, and per-table ENGINE;
* persistent BEFORE/AFTER triggers for INSERT, UPDATE, and DELETE.

Foreign keys are enforced for the tested RESTRICT, CASCADE, and SET NULL paths. Some compatibility DDL is intentionally accepted as a no-op; for example, a client accepting <code>ADD FULLTEXT</code> does not imply a MySQL-compatible full-text index implementation.


== Transactions, security, and metadata ==

* <code>BEGIN</code>, <code>COMMIT</code>, and <code>ROLLBACK</code>;
* users, passwords, grants, revokes, and database access policies;
* <code>SHOW DATABASES</code>, tables, columns, indexes, create-table metadata, variables, engines, processlist, and selected <code>INFORMATION_SCHEMA</code> relations;
* prepared statements over the MySQL protocol;
* <code>KILL QUERY</code> and <code>KILL CONNECTION</code>.

See [[Transactions_and_Isolation|Transactions and Isolation]] and [[Security_and_Authentication|Security and Authentication]] for the operational contract.


== Expressions ==

MemCP implements SQL NULL/three-valued logic, CASE, COALESCE/NULLIF/IFNULL, arithmetic and bitwise operators, comparisons, BETWEEN, IN lists, LIKE/ILIKE, REGEXP/RLIKE, MATCH…AGAINST compatibility, casts, string functions, mathematical functions, hashes, date/time functions, intervals, and timezone conversion.

The exact parser and function catalog changes more often than this overview. Use the generated API reference and the SQL test taxonomy for detailed coverage.


== JSON ==

MemCP stores declared <code>JSON</code>/<code>JSONB</code> values in a native tagged BSON representation. The MySQL dialect implements JSON validation, type inspection, path extraction, constructors, aggregates, mutation functions, containment/search operations, <code>JSON_TABLE</code>, and the <code>-></code>/<code>->></code> operators. The PostgreSQL dialect provides its corresponding <code>json</code>/<code>jsonb</code> operators, constructors, aggregates, path functions, and set-returning expansion forms. Deterministic path expressions can be materialized and indexed through the ordinary computed-column/index machinery. See [[JSON]] for the detailed compatibility contract and deviations.


== Planner inspection ==

<syntaxhighlight lang="sql">EXPLAIN SELECT ...;
EXPLAIN IR SELECT ...;
EXPLAIN REORDER SELECT ...;
EXPLAIN PHYSICAL SELECT ...;
EXPLAIN COMPILE SELECT ...;</syntaxhighlight>
See [[Query_Planner_and_Physical_Lowering|Query Planner and Physical Lowering]].


== Known limitations ==

MemCP is not a byte-for-byte MySQL server implementation. Metadata details, administrative commands, uncommon syntax, advanced locking modes, XA, stored procedures, and untested SQL-standard edge cases may be absent. Consult the [https://github.com/launix-de/memcp/issues current issue tracker] and add a must-pass and must-fail test for every application-critical construct.

Strings

2026-08-28T09:59:38Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Strings =


<div class="mw-message-box mw-message-box-notice">Generated from MemCP commit <code>c42e19eba</code> on 27 August 2026. See [[Full SCM API documentation]].</div>

The '''Strings''' module provides text and binary-string processing for Scheme and SQL compatibility. It includes:

* validation, length, concatenation, slicing, padding, trimming, and case conversion;
* splitting, searching, replacement, wildcard/regular-expression matching, and simplification;
* locale-aware collation and natural ordering;
* URL, HTML, Base64, hexadecimal, JSON, BSON, and SQL-string conversion;
* hashes, checksums, random data, and SQL-dialect compatibility functions.

Indexing and length semantics can differ between bytes and Unicode characters; consult each function's parameter description rather than assuming one unit for every operation.


== string? ==

tells if the value is a string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>any</code>): value


=== Returns ===

* '''value''' (<code>bool</code>)

== concat ==

concatenates stringable values and returns a string

'''Allowed number of parameters:''' 1–10000


=== Parameters ===

* '''value''' (<code>any</code>): first value to concat
* '''more...''' (<code>any</code>): additional values to concat ''(variadic)''


=== Returns ===

* '''value''' (<code>string</code>)

== sql_concat ==

SQL CONCAT semantics: returns NULL if any argument is NULL

'''Allowed number of parameters:''' 1–10000


=== Parameters ===

* '''value''' (<code>any</code>): first value to concat
* '''more...''' (<code>any</code>): additional values to concat ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== substr ==

returns a substring (0-based index)

'''Allowed number of parameters:''' 2–3


=== Parameters ===

* '''value''' (<code>string</code>): string to cut
* '''start''' (<code>number</code>): first character index (0-based)
* '''len''' (<code>number</code>): optional length ''(optional)''


=== Returns ===

* '''value''' (<code>string</code>)

== sql_substr ==

SQL SUBSTR/SUBSTRING with 1-based index and bounds checking

'''Allowed number of parameters:''' 2–3


=== Parameters ===

* '''value''' (<code>string</code>): string to cut
* '''start''' (<code>number</code>): first character position (1-based)
* '''len''' (<code>number</code>): optional length ''(optional)''


=== Returns ===

* '''value''' (<code>string</code>)

== simplify ==

Converts numeric text to a number. Text beginning with { or [ becomes a native JSON value when it is valid JSON; other input remains a string.

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>any</code>): value to interpret as a number or JSON value


=== Returns ===

* '''value''' (<code>any</code>)

== strlen ==

returns the length of a string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>int</code>)

== strlike ==

matches the string against a wildcard pattern using SQL NULL semantics

'''Allowed number of parameters:''' 2–3


=== Parameters ===

* '''value''' (<code>string</code>): input string
* '''pattern''' (<code>string</code>): pattern with % and _ in them
* '''collation''' (<code>string</code>): collation in which to compare them ''(optional)''


=== Returns ===

* '''value''' (<code>bool</code>)

== strlike_cs ==

matches the string against a wildcard pattern case-sensitively using SQL NULL semantics

'''Allowed number of parameters:''' 2–3


=== Parameters ===

* '''value''' (<code>string</code>): input string
* '''pattern''' (<code>string</code>): pattern with % and _ in them
* '''collation''' (<code>string</code>): ignored (present for parser compatibility) ''(optional)''


=== Returns ===

* '''value''' (<code>bool</code>)


== toLower ==

turns a string into lower case

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)


== toUpper ==

turns a string into upper case

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)

== replace ==

replaces all occurances in a string with another string

'''Allowed number of parameters:''' 3–3


=== Parameters ===

* '''s''' (<code>string</code>): input string
* '''find''' (<code>string</code>): search string
* '''replace''' (<code>string</code>): replace string


=== Returns ===

* '''value''' (<code>string</code>)

== strtrim ==

trims whitespace from both ends of a string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)

== strltrim ==

trims whitespace from the left of a string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)

== strrtrim ==

trims whitespace from the right of a string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)

== sql_trim ==

SQL TRIM(): NULL-safe trim of whitespace from both ends

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)

== sql_ltrim ==

SQL LTRIM(): NULL-safe trim of whitespace from left

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)

== sql_rtrim ==

SQL RTRIM(): NULL-safe trim of whitespace from right

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)

== split ==

splits a string using a separator or space

'''Allowed number of parameters:''' 1–2


=== Parameters ===

* '''value''' (<code>string</code>): input string
* '''separator''' (<code>string</code>): (optional) parameter, defaults to " " ''(optional)''


=== Returns ===

* '''value''' (<code>list</code>)

== string_repeat ==

repeats a string n times

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''value''' (<code>string</code>): string to repeat
* '''count''' (<code>number</code>): number of repetitions


=== Returns ===

* '''value''' (<code>string</code>)

== collate ==

returns a canonical order relation for a collation and direction. MemCP allows natural sorting of numeric literals.

'''Allowed number of parameters:''' 1–2


=== Parameters ===

* '''collation''' (<code>string</code>): collation string of the form LANG or LANG_cs or LANG_ci where LANG is a BCP 47 code, for compatibility to MySQL, a CHARSET_ prefix is allowed and ignored as well as the aliases bin, danish, general, german1, german2, spanish and swedish are allowed for language codes
* '''reverse''' (<code>bool</code>): whether to reverse the order like in ORDER BY DESC ''(optional)''


=== Returns ===

* '''relation''' (<code>func</code>): compares two values using the selected collation and direction
** '''Parameters'''
*** '''a''' (<code>any</code>): left operand
*** '''b''' (<code>any</code>): right operand
** '''Returns'''
*** '''ordered''' (<code>bool</code>): whether a sorts before b

== htmlentities ==

escapes the string for use in HTML

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): input string


=== Returns ===

* '''value''' (<code>string</code>)

== urlencode ==

encodes a string according to URI coding schema

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): string to encode


=== Returns ===

* '''value''' (<code>string</code>)

== urldecode ==

decodes a string according to URI coding schema

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): string to decode


=== Returns ===

* '''value''' (<code>string</code>)

== json_encode ==

encodes a value in JSON, treats lists as lists

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>any</code>): value to encode


=== Returns ===

* '''value''' (<code>string</code>)

== json_quote ==

quotes a string as JSON

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_encode_assoc ==

encodes a value in JSON, treats lists as associative arrays

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>any</code>): value to encode


=== Returns ===

* '''value''' (<code>string</code>)

== json_decode ==

parses JSON into a map

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): string to decode


=== Returns ===

* '''value''' (<code>any</code>)

== json_decode_scmer ==

parses JSON produced by json_encode and preserves Scheme symbols and lists

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): Scmer JSON to decode


=== Returns ===

* '''value''' (<code>any</code>)

== base64_encode ==

encodes a string as Base64 (standard encoding)

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): binary string to encode


=== Returns ===

* '''value''' (<code>string</code>)

== base64_decode ==

decodes a Base64 string (standard encoding)

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): base64-encoded string


=== Returns ===

* '''value''' (<code>string</code>)

== sql_unescape ==

unescapes the inner part of a sql string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): string to decode


=== Returns ===

* '''value''' (<code>string</code>)

== bin2hex ==

turns binary data into hex with lowercase letters

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): string to encode


=== Returns ===

* '''value''' (<code>string</code>)


== bin2hex ==

turns binary data into hex with lowercase letters

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): string to encode


=== Returns ===

* '''value''' (<code>string</code>)

== hex2bin ==

decodes a hex string into binary data

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''value''' (<code>string</code>): hex string (even length)


=== Returns ===

* '''value''' (<code>string</code>)

== uuid ==

generates a new random UUID v4 string

'''Allowed number of parameters:''' 0–0


=== Parameters ===

''This function has no parameters.''


=== Returns ===

* '''value''' (<code>string</code>)


== randomBytes ==

returns a string with numBytes cryptographically secure random bytes

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''numBytes''' (<code>number</code>): number of random bytes


=== Returns ===

* '''value''' (<code>string</code>)

== regexp_replace ==

replaces matches of a regex pattern in a string

'''Allowed number of parameters:''' 3–3


=== Parameters ===

* '''str''' (<code>string</code>): input string
* '''pattern''' (<code>string</code>): regex pattern
* '''replacement''' (<code>string</code>): replacement string


=== Returns ===

* '''value''' (<code>string</code>)

== fnv_hash ==

computes a fast non-cryptographic 64-bit FNV-1a hash of a string, returns a 16-character hex string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''str''' (<code>string</code>): input string to hash


=== Returns ===

* '''value''' (<code>string</code>)

== stable_structural_hash ==

streams the string or serialized representation of a Scheme value into stable FNV-1a without constructing the complete representation

'''Allowed number of parameters:''' 1–2


=== Parameters ===

* '''value''' (<code>any</code>): value to hash
* '''serialize''' (<code>bool</code>): use the Scheme serializer instead of string rendering ''(optional)''


=== Returns ===

* '''value''' (<code>string</code>)

== sha1 ==

computes the SHA-1 digest of a string, returns a 40-character lowercase hex string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''str''' (<code>string</code>): input string to hash


=== Returns ===

* '''value''' (<code>string</code>)

== sha256 ==

computes the SHA-256 digest of a string, returns a 64-character lowercase hex string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''str''' (<code>string</code>): input string to hash


=== Returns ===

* '''value''' (<code>string</code>)

== regexp_test ==

tests if a string matches a regex pattern, returns true/false

'''Allowed number of parameters:''' 2–2


=== Parameters ===

* '''str''' (<code>string</code>): input string
* '''pattern''' (<code>string</code>): regex pattern


=== Returns ===

* '''value''' (<code>bool</code>)

== json_parse_bson ==

validates JSON and returns a BSON-backed value

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_array ==

creates a MySQL-compatible JSON array

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_object ==

creates a MySQL-compatible JSON object

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)


== json_quote ==

quotes a string as JSON

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_unquote ==

unquotes a JSON string

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_valid ==

tests whether a value contains valid JSON

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_type ==

returns the MySQL JSON type name

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_depth ==

returns maximum JSON document depth

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_length ==

returns JSON object or array length

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_extract ==

extracts one or more JSON paths

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_keys ==

returns object member names

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_contains_path ==

tests whether one or all JSON paths exist

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_contains ==

tests JSON containment

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_overlaps ==

tests whether JSON values overlap

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_member_of ==

tests whether a JSON scalar is a member of an array

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_pretty ==

pretty-prints JSON

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_storage_size ==

returns BSON payload size

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_storage_free ==

returns unused BSON storage space

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_schema_valid ==

validates a document against a MySQL Draft 4 JSON schema

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_schema_validation_report ==

reports MySQL Draft 4 JSON schema validation

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_set ==

sets or inserts JSON path values

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_insert ==

inserts missing JSON path values

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_replace ==

replaces existing JSON path values

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_remove ==

removes JSON path values

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_array_append ==

appends values to JSON arrays

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_array_insert ==

inserts values into JSON arrays

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_merge_patch ==

merges documents using RFC 7396 semantics

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_merge_preserve ==

merges documents while preserving values

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_search ==

searches JSON string values

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_value ==

extracts a scalar JSON value

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_to_json ==

implements PostgreSQL to_json and to_jsonb

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_row_to_json ==

implements PostgreSQL row_to_json

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_build_array ==

implements PostgreSQL JSON array builders

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_array_absent ==

implements SQL/JSON ARRAY ABSENT ON NULL

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_build_object ==

implements PostgreSQL JSON object builders

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_object ==

implements PostgreSQL json_object and jsonb_object array forms

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_serialize ==

implements PostgreSQL json_serialize

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_array_length ==

implements PostgreSQL json_array_length

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_extract_path ==

implements PostgreSQL json_extract_path

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_extract_path_text ==

implements PostgreSQL json_extract_path_text

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_set ==

implements PostgreSQL jsonb_set

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_set_lax ==

implements PostgreSQL jsonb_set_lax

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_insert ==

implements PostgreSQL jsonb_insert

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_strip_nulls ==

implements PostgreSQL json_strip_nulls

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_typeof ==

implements PostgreSQL json_typeof

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_populate_record_valid ==

validates PostgreSQL jsonb_populate_record input shape

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_concat ==

implements PostgreSQL jsonb concatenation

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_delete ==

implements PostgreSQL jsonb deletion

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_delete_path ==

implements PostgreSQL jsonb path deletion

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_subtract ==

dispatches PostgreSQL numeric and jsonb subtraction

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_exists ==

implements PostgreSQL jsonb top-level existence

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_path_exists ==

implements PostgreSQL jsonb_path_exists

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_path_match ==

implements PostgreSQL jsonb_path_match

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_path_query_array ==

implements PostgreSQL jsonb_path_query_array

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_jsonb_path_query_first ==

implements PostgreSQL jsonb_path_query_first

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_value ==

implements PostgreSQL SQL/JSON JSON_VALUE

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_get ==

implements PostgreSQL JSON object and array extraction

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_arrayagg_entry ==

wraps one JSON_ARRAYAGG input value

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_arrayagg_reduce ==

JSON_ARRAYAGG reducer

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_arrayagg_finalize ==

builds one BSON array from a collected JSON_ARRAYAGG value list

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_objectagg_entry ==

constructs a JSON_OBJECTAGG key/value entry

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== json_objectagg_reduce ==

JSON_OBJECTAGG reducer

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

== pg_json_table_rows ==

materializes a PostgreSQL JSON table function as rows

'''Allowed number of parameters:''' 0–10000


=== Parameters ===

* '''arguments''' (<code>any</code>) ''(variadic)''


=== Returns ===

* '''value''' (<code>any</code>)

Streams

2026-08-28T09:59:38Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

= Streams =


<div class="mw-message-box mw-message-box-notice">Generated from MemCP commit <code>c42e19eba</code> on 27 August 2026. See [[Full SCM API documentation]].</div>

The '''Streams''' module processes data incrementally instead of requiring one complete in-memory string. It includes string-backed streams plus gzip/xz compression and decompression adapters.

Compression functions produce compressed output; <code>zcat</code> and <code>xzcat</code> decode compressed input. Choose the direction explicitly when composing import and export pipelines, and close externally owned resources according to the caller's IO contract.


== streamString ==

creates a stream that contains a string

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''content''' (<code>string</code>): content to put into the stream


=== Returns ===

* '''value''' (<code>stream</code>)

== gzip ==

compresses a stream with gzip. Create streams with (stream filename)

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''stream''' (<code>stream</code>): input stream


=== Returns ===

* '''value''' (<code>stream</code>)

== xz ==

compresses a stream with xz. Create streams with (stream filename)

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''stream''' (<code>stream</code>): input stream


=== Returns ===

* '''value''' (<code>stream</code>)

== zcat ==

turns a compressed gzip stream into a stream of uncompressed data. Create streams with (stream filename)

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''stream''' (<code>stream</code>): input stream


=== Returns ===

* '''value''' (<code>stream</code>)

== xzcat ==

turns a compressed xz stream into a stream of uncompressed data. Create streams with (stream filename)

'''Allowed number of parameters:''' 1–1


=== Parameters ===

* '''stream''' (<code>stream</code>): input stream


=== Returns ===

* '''value''' (<code>stream</code>)

Storage

2026-08-28T09:59:37Z

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference