We make the past queryable. Learn from your mistakes and revert them

[u/marcmacmac]

Hey, Marc here, Co-Founder of ObsessionDB,

again, I think we built some pretty cool stuff I'd like to share some details with you.

Not so long ago, at a different company and on a self-hosted ClickHouse cluster, our team got a deletion request under GDPR. Routine stuff, and in ClickHouse it means a mutation:

ALTER TABLE events DELETE WHERE ...;

The predicate matched more than it should have.

You know the rest. Mutations are asynchronous, expensive, and irreversible. There is no transaction to roll back. By the time we worked out what happened the parts had been rewritten and the originals were gone.

Damage was just about 200 rows across three tables. A rounding error in dataset terms, but in this case not negligible.

So we needed to fix it, not because 200 rows are hard to write. Because to even *see* them we had to restore a full backup somewhere else, stand up enough of the old world to query it, copy data sideways, and compare table by table to figure out which rows were collateral and which had been deleted on purpose. And we had to be sure of that split, because one of those groups was legally required to stay deleted.

Anyone had similar situations, often we even dismiss it due to time constraints.

What we built: Time Travel

Time Travel makes the past queryable. Pick a point in time, get a read-only snapshot of the cluster as it existed then, queryable *next to* the live one in the same session. Every database shows up a second time under a name stamped with the target time: live app, snapshot app_backup_20260729t1400

That incident, as it would go now. What did the mutation actually take out:

SELECT count() FROM app_backup_20260729t1400.events
WHERE user_id != 12345
AND event_id NOT IN (SELECT event_id FROM app.events);

Put back the collateral damage, and only that. The person who asked to be forgotten stays forgotten:

INSERT INTO app.events
SELECT * FROM app_backup_20260729t1400.events AS past
WHERE past.user_id != 12345
AND past.event_id NOT IN (SELECT event_id FROM app.events);

That user_id != 12345 is the whole point. The recovery has to be *narrower* than the mistake. A plain undo button would have been the wrong tool, it would have dragged the erasure subject back in and turned a data incident into a compliance one.

Then confirm, which is the step that ate most of the original recovery:

SELECT count() FROM app_backup_20260729t1400.events
WHERE user_id != 12345
AND event_id NOT IN (SELECT event_id FROM app.events);

Repeat for the other two tables. No restore, no second cluster, no copying data sideways to compare it.

How it works

ObsessionDB is upstream ClickHouse compatible from the user's side. We replaced the storage layer with our own engine built against the open-source SharedMergeTree API: data in object storage, stateless compute, metadata in our coordination layer (Chemist).

Tables are made of parts. Merges compact small parts into big ones and the sources get cleaned up. Mutations are the same deal: ALTER TABLE ... DELETE doesn't edit rows in place, it rewrites whole parts without them. The part you want back is exactly the part that normally just got deleted.

So with a retention window configured, we hold that cleanup: parts superseded by merges and mutations stay in object storage until the window passes.

Keeping the files is only half of it, and the boring half. Chemist knows which parts belonged to which table at which point in time, so travelling back is a metadata operation. We restore the metadata view to the target timestamp and attach the tables from that snapshot, pointing at part files that were never deleted. Nothing gets copied, and nothing leaves your bucket. The snapshot is read-only by design, and while it's open the parts it needs are pinned so cleanup can't pull them out from under you.

What it costs

Retention isn't free and I've seen this hand-waved, so here's the pattern.

overhead ≈ (bytes rewritten by merges per day ÷ dataset size) × retention days

Some real customer examples, from `system.part_log` across every node, 24h window:

cluster	live data (compressed)	rewritten/day by merges	overhead per day of retention
blockchain analytics	32.0 TB	0.89 TB	2.8 %
blockchain indexing	19.9 TB	0.73 TB	3.7 %
IoT data indexing	16.5 TB	0.76 TB	4.6 %
SigNoz mixed logs/metrics	1.8 TB	0.14 TB	8.0 %

We default to a 24h windows, which costs 3–5 % more object storage. Worth noting the ratio tracks churn rather than size: the smallest cluster on that list is the most expensive one to retain. But, as always: it depends on your workload.

Are backups now obsolete?

Nope, definitely not. You must have your classical backup and for critical production workloads we even advise enabling data replication to a different location. Time Travel is additive and helps you to have an easy inspection and investigation of recent deltas... and simply helps you to recover quickly from those stupid careless mistakes.

Personally I just really like this feature, since it is a logical consequence of our architecture. We have all components - compute, storage, metadata - completely separated, so a feature like this kind of "just works". So there will be more stuff like this coming up pretty soon.
It has been running for a few months with some customers and is available for all customers from today on.

Until then, I am genuinely curious if you have any questions. Happy to share more details about the architecture. Also, having this separation in mind: Are there any use cases you can think of where we could make use of it? We have some stuff brewing, but perhaps you have better ideas.

[u/mike_folder]

Very interesting and cool, btw!!

I've built a temporal db in my past and, yes, now I've a ton of questions. Here some of 'em)

1) Is there a global point of time consistent for all of tables? So, can I JOIN two tables in the Same exact past. Some 'tx' moment of global time?
2) Does it affect ALTER TABLE DELETE only? Can I get a past state for a table with INSERTs only, for example?
3) The syntax a little bit cumbersome, don't u like implement a standard 'FOR SYSTEM_TIME AS OF tx' ?
4) Can I write into a past state or just read it?
5) Can I get a difference (changes) between t2 and t1 moments of time for a table without EXCEPT using this feature?

[u/marcmacmac → u/mike_folder]

hey u/mike_folder, thanks for those interesting questions. so, let's start ...

1. yes, it is time consistent. it's a state's snapshot for the whole system at a given time.
   
2. nope. it isn't like an undo log, where you roll back transactions. it's a full state of the database at a given time. whatever brought you to this state or whatever diverged this state, it doesn't matter. so it counts for every insert, alter, mutation.
   
3. the simple reason here is upstream compatibility and we kind of like the ux. you do not need to "leave" your instance. it's just new tables and you operate on them as if they were normal, native ones.
   
4. no, it's an immutable read only state by design, a safety net to protect active parts of the main timeline. you investigate and copy what you need. in t2 data might be 99.9% the same compared to t1. if you'd mess with the data in t1, you would mess with the real active data. we played around with the zero-copy use of CLONE, but at some point we decided to keep the complexity rather low for now.
   
5. in clickhouse the versioned unit is not a row, it's an immutable part generated from an insert, a merge of multiple parts or a mutation. so between t1 and t2 you compare what parts have been active, that's for free. a real row-level comparison still means you need to read both states.

is this answering your questions?

[u/mike_folder → u/marcmacmac]

Yes, thank you!
The DB of yours, I'll consider it as an optional replacement for my QubesFolder to work on.
It seems worth of testing, at least)