3 ms·

The append only constraint is super nice until it's not. Developing a way to manage this as the dataset scales is challenging. Replaying a 100gb log after a cra

The append only constraint is super nice until it's not. Developing a way to manage this as the dataset scales is challenging. Replaying a 100gb log after a crash could become a problem. I've built entire product prototypes around something like this, but you always reach a point where it has a bug while you were way up in business logic land and so it feels like being ripped right down to hell. It's no longer fun after the first few cases of that experience.

derefr 3y ago
I think you're speaking here about 1. queue-based event-store systems, e.g. combining a compacting Kafka topic with a CQRS reducer agent to serve as a store-of-record + snapshot-state representation respectively; 2. where you've likely restructured what were fundamentally CRUD-esque operations into CQRS commands and events, to hold in the store, just so that they can be folded back down to CRUD updates to a snapshot living in an RDBMS by the reducer? I do agree that this kind of system can get really messy/painful once you have a lot of data. But I'm thinking more about: 3. "the Dynamo/Hadoop model, in the small": a single-process client-server row-store, but where a table is made of immutable, read-only 64MB chunks, with the newest chunk being an "open" buffer that becomes "closed" as soon as it's filled up; and where these chunks are of a known file-format such that you could directly compose them outside the DBMS in parallel and push them to the DBMS as chunks to be ingested "whole" (i.e. "mounted" as micro-partitions of the table); 4. where the business-domain's types are already fundamentally immutable datatypes, that don't need any "projecting" into a CQRS representation; i.e. where the thing the DB exists to store (and query on!) is the immutable business data, not some latest-computed-state reduction over it, so there's no need to ever play a log into a CQRS reducer, let alone replay that log. I know that, for example, Clickhouse's MergeTree engine is at its core akin to the kind of system I'm describing in 3 above — but because Clickhouse is designed as a general-purpose RDBMS (and so still offers an API that includes UPDATE and DELETE) rather than being purpose-built for use-case 4 above, it needs to do a whole bunch of stuff on top of that constrained chunked-immutable storage primitive, to virtualize those updates/deletes pre-merge, and to present MVCC transactions. Same with, for another example, CouchDB: an immutable-data-store core, with a ton of logic on top to allow the user to pretend they can update/delete. If you imagine a version of Clickhouse or CouchDB that was solely focused on delivering use-case 4 above, then you could strip away all the "extra stuff." For use-case 4, the "64MB immutable-once-full micro-partitions" paradigm is literally all that's needed to losslessly convey all domain state; and so a storage engine akin to the one described in 3 is all you need to support it. (If you're wondering, the business domain I'm working in, where this pertains, is: analytical querying of blockchain data. All the "CQRS events" [blockchain blocks and their transactions] come from third parties, and are all guaranteed-immutable upon insert, if not guaranteed-canonical. [But canonicity can be tracked as a log of chain tip changes, like a git commit log.] If you don't care about blockchains, though, domains with similar needs for immutable append-only analytical stores include financial forensic accounting, and structured API audit-logging as state for rule engines inside Intrusion Detection Systems.)
- lichtenberger 3y ago
  It's fundamentally how SirixDB approaches this (basically also storing checksums) as also written in another reply :-) Every commit directly syncs the binary data to the durable storage (currently a file) and incrementally adds data. Furthermore, it stores optionally the changes (type of change/ctx node/updatePosition... in JSON files). For instance, lately I've implemented a simple copy mechanism based on this. Copy a given revision and optionally apply all changes with intermediate commits to also copy the full history up to the most recent revision). However, the main idea is to use the change tracking also for diff visualizations... maybe even stream these via web sockets. A production ready system BTW may be Datomic. And it also reminds me of this paper: https://dl.acm.org/doi/abs/10.5555/3275366.3284969 https://dl.acm.org/doi/abs/10.5555/3275366.3284969
dotancohen 3y ago
So what did you learn? What were the bugs? GP is referring exactly to your experience - what would this database look like if you kept refining it? I could see this as being very useful for e.g. security system logging, banking and other temporal transactions, even for VCS.
- bob1029 3y ago
  I learned that single writer principle is maybe the most important thing if you care about performance. If I kept going, we'd have a perfect time traveling database of everything that ever happened in the enterprise. I was even proposing a hashing technique that would provide cryptographic guarantees that our log has not been tampered with, given a pre-shared seed signature. This would be shared with all of our customers and placed in their vaults. At any point, they would be permitted to audit all activity we have logged and recompute our hashes themselves. Really the vision was make B2B consulting with banks and other financial institutions as transparent as possible. Performance was pretty bananas too. Easily outstripped both sql server and SQLite in testing for our hot path by more than 10x. We weren't doing SQL command processing though. Very application-specific access patterns. LMAX Disruptor, or it's equivalent abstraction, will be at the heart of any database I ever attempt to write again.

derefr 3y ago

I think you're speaking here about 1. queue-based event-store systems, e.g. combining a compacting Kafka topic with a CQRS reducer agent to serve as a store-of-record + snapshot-state representation respectively; 2. where you've likely restructured what were fundamentally CRUD-esque operations into CQRS commands and events, to hold in the store, just so that they can be folded back down to CRUD updates to a snapshot living in an RDBMS by the reducer? I do agree that this kind of system can get really messy/painful once you have a lot of data. But I'm thinking more about: 3. "the Dynamo/Hadoop model, in the small": a single-process client-server row-store, but where a table is made of immutable, read-only 64MB chunks, with the newest chunk being an "open" buffer that becomes "closed" as soon as it's filled up; and where these chunks are of a known file-format such that you could directly compose them outside the DBMS in parallel and push them to the DBMS as chunks to be ingested "whole" (i.e. "mounted" as micro-partitions of the table); 4. where the business-domain's types are already fundamentally immutable datatypes, that don't need any "projecting" into a CQRS representation; i.e. where the thing the DB exists to store (and query on!) is the immutable business data, not some latest-computed-state reduction over it, so there's no need to ever play a log into a CQRS reducer, let alone replay that log. I know that, for example, Clickhouse's MergeTree engine is at its core akin to the kind of system I'm describing in 3 above — but because Clickhouse is designed as a general-purpose RDBMS (and so still offers an API that includes UPDATE and DELETE) rather than being purpose-built for use-case 4 above, it needs to do a whole bunch of stuff on top of that constrained chunked-immutable storage primitive, to virtualize those updates/deletes pre-merge, and to present MVCC transactions. Same with, for another example, CouchDB: an immutable-data-store core, with a ton of logic on top to allow the user to pretend they can update/delete. If you imagine a version of Clickhouse or CouchDB that was solely focused on delivering use-case 4 above, then you could strip away all the "extra stuff." For use-case 4, the "64MB immutable-once-full micro-partitions" paradigm is literally all that's needed to losslessly convey all domain state; and so a storage engine akin to the one described in 3 is all you need to support it. (If you're wondering, the business domain I'm working in, where this pertains, is: analytical querying of blockchain data. All the "CQRS events" [blockchain blocks and their transactions] come from third parties, and are all guaranteed-immutable upon insert, if not guaranteed-canonical. [But canonicity can be tracked as a log of chain tip changes, like a git commit log.] If you don't care about blockchains, though, domains with similar needs for immutable append-only analytical stores include financial forensic accounting, and structured API audit-logging as state for rule engines inside Intrusion Detection Systems.)

lichtenberger 3y ago
It's fundamentally how SirixDB approaches this (basically also storing checksums) as also written in another reply :-) Every commit directly syncs the binary data to the durable storage (currently a file) and incrementally adds data. Furthermore, it stores optionally the changes (type of change/ctx node/updatePosition... in JSON files). For instance, lately I've implemented a simple copy mechanism based on this. Copy a given revision and optionally apply all changes with intermediate commits to also copy the full history up to the most recent revision). However, the main idea is to use the change tracking also for diff visualizations... maybe even stream these via web sockets. A production ready system BTW may be Datomic. And it also reminds me of this paper: https://dl.acm.org/doi/abs/10.5555/3275366.3284969 https://dl.acm.org/doi/abs/10.5555/3275366.3284969

dotancohen 3y ago

So what did you learn? What were the bugs? GP is referring exactly to your experience - what would this database look like if you kept refining it? I could see this as being very useful for e.g. security system logging, banking and other temporal transactions, even for VCS.

bob1029 3y ago
I learned that single writer principle is maybe the most important thing if you care about performance. If I kept going, we'd have a perfect time traveling database of everything that ever happened in the enterprise. I was even proposing a hashing technique that would provide cryptographic guarantees that our log has not been tampered with, given a pre-shared seed signature. This would be shared with all of our customers and placed in their vaults. At any point, they would be permitted to audit all activity we have logged and recompute our hashes themselves. Really the vision was make B2B consulting with banks and other financial institutions as transparent as possible. Performance was pretty bananas too. Easily outstripped both sql server and SQLite in testing for our hot path by more than 10x. We weren't doing SQL command processing though. Very application-specific access patterns. LMAX Disruptor, or it's equivalent abstraction, will be at the heart of any database I ever attempt to write again.