Persistency and Performance Guarantees

Persistency and Performance Guarantees

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

MemCP lets every table choose its own balance between durability and write latency with ENGINE=<mode>. The default is safe. 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

Engine	Rows stored in	Write path	Survives process crash	Survives kernel crash/power loss	May be evicted	Typical use
safe (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
logged	Compressed column files plus RAM/delta state	WAL without fsync; measured write paths have reached about 10× safe 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
sloppy	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
memory	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
cache	RAM only; reconstructible definition/oninit 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:

• cache and memory provide the shortest RAM-only write path but no row persistence;
• sloppy and logged avoid a synchronous durability fence, but accept different loss windows;
• safe 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 SUM, COUNT, and the partial state behind AVG 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

ENGINE=safe

safe 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 safe 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.

ENGINE=logged

logged keeps the WAL but deliberately omits fsync:

• 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 safe to logged has reached roughly 10× the write throughput. The mechanism is straightforward: safe must wait for a durable storage fence at commit, whereas logged 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 safe 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 safe merely because the WAL file exists.

ENGINE=sloppy

sloppy 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; sloppy 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.

ENGINE=memory

memory 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 oninit 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.

ENGINE=cache

cache is also RAM-only, but more disposable than memory:

• row contents have no WAL or persistent column files;
• schema/reconstructible definition and a closed oninit 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 safe tables, synchronization happens at commit. Consequently:

<syntaxhighlight lang="sql"> -- 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; </syntaxhighlight>

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

<syntaxhighlight lang="sql"> 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; </syntaxhighlight>

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

Changing ENGINE safely

Irreversible data-safety boundary: changing a persisted table (safe, logged, or sloppy) to memory or cache 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.

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

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

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 DROP TABLE and an explicit persisted-to-memory/cache transition.

Failure scenarios in plain language

Event	safe	logged	sloppy	memory	cache
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. safe protects committed writes against failures; it does not undo an accidental DROP TABLE, 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 sloppy;
• 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.