Detaspace MVCC: Time‑Travel Queries Without Lock Contention

The problem: analytics blocked by OLTP writes

Running long‑running analytical queries on a table that is constantly updated by transactional workloads often forces a choice: accept dirty reads, lock the table, or copy data to a separate warehouse. Each option adds latency, operational complexity, or both.

Thesis: Detaspace’s built‑in MVCC gives you a consistent snapshot automatically

Detaspace implements Multi‑Version Concurrency Control (MVCC) by storing an immutable row version at every commit. Readers see a stable snapshot identified by a transaction ID (tx_id) without acquiring locks on the live data. This lets analytical workloads run point‑in‑time queries while OLTP updates continue unhindered.

How MVCC works in Detaspace

• Version creation: On each UPDATE or DELETE, Detaspace writes a new row version and marks the previous version as historic. The current version is the one with the highest tx_id.
• Snapshot reads: A SELECT without a time‑travel clause reads the latest committed snapshot. Adding FOR SYSTEM_TIME AS OF 'timestamp' pins the query to the snapshot that existed at that moment.
• Retention policy: A configurable retention window (default 30 days) controls how long historic versions are kept. After the window expires, a background vacuum purges obsolete versions.

Worked example: creating a versioned table and travelling back in time

Run the following statements in a Detaspace SQL shell (or via the deta space sql CLI). You need CREATE TABLE, INSERT, UPDATE, and SELECT privileges on the target schema.

-- 1. Create a versioned table (MVCC is enabled by default)
CREATE TABLE orders (
  id INT PRIMARY KEY,
  amount DECIMAL(10,2)
);

-- 2. Insert the initial row
INSERT INTO orders VALUES (1, 100.00);

-- 3. Update the row – this creates a new version
UPDATE orders SET amount = 150.00 WHERE id = 1;

-- 4. Query the current state (should return 150.00)
SELECT * FROM orders WHERE id = 1;

-- 5. Time‑travel to a point before the update
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2026-10-01 12:00:00' WHERE id = 1;

Expected check: Step 5 should return amount = 100.00. If the timestamp is after the update, the result will reflect the newer version.

Verification: You can also inspect the version chain directly:

DETA SPACE SHOW VERSIONS orders;

This command lists each version’s tx_id, commit timestamp, and the column values, confirming that two distinct versions exist.

Trade‑offs and limitations

Aspect	Impact	Mitigation
Write overhead	~5‑10 % extra latency due to copy‑on‑write and version metadata.	Benchmark with pgbench‑style workloads; disable MVCC on tables that never need history.
Storage growth	Each update adds a full row version; tables can expand quickly.	Set a retention window that matches your audit requirements and schedule regular vacuum runs.
External tool compatibility	Some BI connectors may ignore the FOR SYSTEM_TIME clause and read the latest version.	Wrap time‑travel queries in views or materialized snapshots for downstream tools.
Aggressive purging risk	If the retention window is shorter than the oldest needed snapshot, historical queries fail.	Monitor DETA SPACE SHOW RETENTION and alert when purge age approaches query horizon.

Actionable closing: enable, verify, and monitor

1. Enable MVCC – it is on by default for new tables. For existing tables, run ALTER TABLE name SET MVCC = ON; (requires ALTER privilege).
2. Validate the version chain after the first few updates using DETA SPACE SHOW VERSIONS.
3. Set retention to the longest audit period you need, e.g., ALTER TABLE orders SET RETENTION = INTERVAL '90 days';.
4. Benchmark write latency with MVCC on vs. off to confirm the 5‑10 % overhead is acceptable for your SLA.
5. Add monitoring for vacuum lag and version count per table; a sudden spike indicates a runaway update pattern.

With these steps you get repeatable, lock‑free analytical reads while keeping the transactional path fast — exactly the balance most mixed‑workload teams need.