Turning the database inside-out (2015)

https://martin.kleppmann.com/2015/11/05/database-inside-out-at-oredev.html

by andection • 1 year ago

192 95 1 year ago

martin.kleppmann.com

jackeyzhang• 1 year ago

Awesome，very useful

esafak• 1 year ago

(2015)

swyx• 1 year ago

https://news.ycombinator.com/item?id=42813049

dagss8• 1 year ago

We did this style on top of plain MSSQL. Each event would have a SQL table which is the primary storage. Then we have workers that listens to new data in tables and updates projections we needed. (Sometimes DB triggers but mostly async workers.)

The main issue is "listening to new data in a SQL table". I wrote this code to achieve it in MSSQL (perhaps it is somehow built into postgres?): https://github.com/vippsas/mssql-changefeed

In my experience this approach is beautiful; as Martins says, our backend code is mostly stateless and functional these days, why have mutable objects in the DB? And the approach is extremely useful for dry-running business logic etc

But we didn't like the prospect of adopting Kafka wholesale. Having all the data in a SQL DB is extremely convenient for debugging, and since we already used SQL it was a smaller change that was done first where it made most sense and then spread out.

It would be great with more DB features targeting this style. Explicit partition event tables (kafka-in-SQL), and writing a projection simply as a SQL query which is inverted into an async trigger by the DB would be awesome. (MSSQL has indexed views, but it cannot be done online...)

Materialize is the DB I know about in this territory.

hans_castorp• 1 year ago

perhaps it is somehow built into postgres?

Postgres has a built-in listen/notify mechanism. The problem with that is, that it doesn't guarantee delivery and if no process is listening, notifications will be lost.

Most solutions that need something like that use "logical decoding" these days. That's the built-in change data capture exposed as a public API as part of the logical replication.

dagss8• 1 year ago

Yes, listen/notify is something very different. We would often write new projections that consumes events from years back and until today.

You want sequence numbers that indicate the event's position in a partitioned log.

Something like "int identity" except that the int is assigned during commit, so that you have guarantee that if you see IDs 5 and 7, then 6 will never show up, so that each consumer can store a cursor of its progress of consuming the table which is safe against inserts.

I was hoping to do it using CDC, but Microsoft SQL has a minimum 1 minute delay on CDC which destroys any live data usecase. Perhaps postgres allows listening to the replication log with lower latency?

fzeindl• 1 year ago

I have implemented something similar (updating projection from background and serving the projection automatically via REST) and wrote a high-Level article about it https://www.fabianzeindl.com/posts/the-api-database-architec...

mkleczek• 1 year ago

I use the architecture you described as the go-to architecture for most of so called "business" applications (with PostgreSQL as dbms though).

The missing pieces are:

- incremental materialized view maintenance

- (bi)temporal primary and foreign keys (upcoming in Pg18)

Lately I found out about DBSP and Feldera which looks very promising as it is based on sound theory: https://github.com/feldera/feldera?tab=readme-ov-file#-theor...

dagss8• 1 year ago

Great link, thanks!

What do you do postgres for consuming old events and smoothly transitioning to consuming new events? Anything like an event ID allocated at commit time that is usable? As I talk about in sibling comment..

agumonkey6• 1 year ago

I would love to know if other people in the industry (beside hickey/datomic) use the immutable log/stream + integrators. From my small experience in enterprise app: auditability and time travelling are always bolted on good old sql tables/snapshots after the fact and the pain is already baked in.

reubenmorais3• 1 year ago

LinkedIn supposedly: https://engineering.linkedin.com/distributed-systems/log-wha...

mike_hearn2• 1 year ago

Depends which industry. If you look at a lot of non-tech industry then they'll use a commercial DB with all those features in place already, rather than hacking up their own data layer. A few years ago I spent some time in the enterprise finance space, and learned some unfashionable tech you don't see talked about on Hacker News much. It left me with a new appreciation for what goes on there. A staggering amount of time spent in tech startups is spent on solving and resolving problems that you can buy off the shelf solutions for and have been able to for a long time.

After all, this talk is now 10 years old but appears to be describing features that have been around for much longer. Take your average bank - it will have a bunch of Oracle databases in it. Those already have every feature discussed in this thread and in the talk:

• Incremental materialized view maintenance (with automatic query rewrite to use it, so users don't have to know it exists).

• Exposing logical commit logs as an API, with tooling (e.g. GoldenGate, LogMiner, query change notifications).

• Time travelling SELECT (... AS OF).

• Lots of audit features.

• Integrated transactional and scalable MQ (no need for Kafka).

My experience was that faced with a data processing problem, enterprise devs will tend to just read the user guide for their corporation's database, or ask for advice from a graybeard who already did so. They go write some SQL or an Excel plugin or something old school, ship it, close the ticket, go home. Then a few years later you look at HN and find there's a whole startup trying to sell the same feature.

082349872349872• 1 year ago

thoughts from 1992 (Gray+Reuter): https://news.ycombinator.com/item?id=42829878

bubbleRefuge• 1 year ago

In large scale business intergration platforms/apps, you have operational systems like SAP and and Oracle Service Cloud generate/stream raw or business events which are published to message brokers in topics ( orders, incidents, suppliers, logistics, etc). There the data is published , validated, transformed (filtered, routed, formatted, enriched, aggregated, etc) into other downstream topics which can be used to egress to other apps or enterprise data stores/data lakes. Data governance apps control who has access. Elastic search or Splunk for data lineage and debugging. you also have sbservability systems sandwiched in there as well.

dakiol• 1 year ago

I always thought that the most flexible approach was:

- good old mutable relational tables

- a separate db to store immutable events (could be the same kind of db you use for business transactions or something fancy like big query)

I feel like mixing both into one has more disadvantages

dagss8• 1 year ago

This is of course the more common approach, I was aware that our approach is not usual which is why I posted.

You don't list the disadvantages so cannot respond to that. But I really like the code resulting from flipping it around. It just fits how I think and I now feel "unsafe" messing around with code that mutates state directly.

The programming style of those mutable relational tables is a bit like mutable objects in Java -- eventually people moved to more stateless code and immutable objects. The same shift doesn't have to happen in the storage layer, but it is what the OP (and I) argue for.

I really enjoy having the tools around to replay any object from history as part of the online backend. For instance, consider if there was a bug so that customer input was buggy in some timeframe. Instead of writing a job that looks through the history of each customer and tries to figure out if you should mutate the data, then roll out that ad hoc mutation while holding your breath -- you can add some rules when fetching the relevant events on lookup-time and change how they reflect the state; i.e. a change that is only read only and only changes deployed code and not data, and roll back by only rolling back the service not by reverting a data change.

EvanAnderson• 1 year ago

The main issue is "listening to new data in a SQL table".

You may want to take a look at Service Broker[0]. It's the idiomatic messaging and queuing bit of SQL Server. It's a bit of an obscure feature and has a bit of a steep learning curve. If I were trying to implement what you're doing it would be the tool I'd reach for.

[0] https://learn.microsoft.com/en-us/sql/database-engine/config...

oa3352• 1 year ago

“ The main issue is "listening to new data in a SQL table". I wrote this code to achieve it in MSSQL (perhaps it is somehow built into postgres?): https://github.com/vippsas”

Postgres uses “publications” for this purpose. Clients can subscribe to a publication that gets updates to a given table.

joshlemer• 1 year ago

The thing I always get stuck on with these techniques is, how do you handle transactions which perform validations/enforce invariants on data when you’re just writing writes to a log and computing materialized views down the line? How can you do essentially, an “add item to shopping cart” if for example, users can only have max 10 items and so you need to validate that there aren’t already 10 items in the cart?

cwalv• 1 year ago

You write the 'add item' event regardless, and when building the 'cart' view you handle the limit.

philbo• 1 year ago

Alternatively "invalid cart" could itself become an event.

RedShift1• 1 year ago

Sounds like an easy way to run out of storage space

dagss8• 1 year ago

Well, assume the non-overdraftable bank account example instead then, what do you do then?

eyads2• 1 year ago

add_item is not an event, rather a command/ request that is yet to be validated. item_added is the event = a fact that was 'allowed to happen' by the system.

Keeping commands in a persistent store is a matter of choice but not necessary. I've seen people doing command sourcing and calling it event sourcing.

nejsjsjsbsb• 1 year ago

You don't use it for that sort of thing.

But if you did you'd need an aggregatable (commutative) rule.

Like you can't aggregate P99 metrics. (To see why, it is similar to why you can't aggregate P50. You can't because a median of a bunch of medians is not the total median)

So you measure number of requests of latency < 100ms and number of requests. Both of these aggregate nicely. Divide one by the other. Now you get Pxx for 100ms. So if your P99 target was 100ms you set your 100ms target to 99%.

Anyway you'd need something like this for your shopping cart. It is probably doable as a top 10 (and anything else gets abandoned). Top 10 is aggregatable. You just need an order. Could be added to cart time or price.

adamcharnock• 1 year ago

This all sounds to me very close to the event-sourcing/CQRS/DDD area of thinking. In which case you look at it in two parts:

- Event firing: Here is where you fire an event saying that the thing has happened (i.e. item_added_to_cart, not add_item_to_cart). Crucially, this event states the thing has happened. This isn't a request, it is a past-tense statement of fact, which is oddly important. It is therefore at this point where you must do the validation.

- Event handing: Here you receive information about an event that has already happened. You don't get to argue about it, it has happened. So you either have to handle it, or accept you have an incomplete view of reality. So perhaps you have to accept that the cart can have more than 10 items in some circumstances, in which case you prompt the use to correct the problem before checking out.

In fact, this is typically how it goes with this kind of eventual-consistency. First fire the event that is as valid as possible. Then when handing an 'invalid' event accept that its just got to happen (cart has 11 items now), then prompt the user fix it (if there is one).

Not sure how helpful this is here, but thought it a useful perspective.

mrkeen7• 1 year ago

This kind of thing only works if your whole universe belongs to your database.

Transactions and conditional-updates work smoothly if it's your customer browsing your shop in your database - up to a point.

But I usually end up with partner integrations where those techniques don't work. For instance, partners will just tell you true facts about what happened - a customer quit, or a product was removed from the catalogue. Your system can't reject these just because your Db can't or won't accept that state change.

anonzzzies• 1 year ago

I was thinking about this as well as these streaming things become more popular. Would you write add-to-cart events and those trigger add-cart events; the latter containing an valid field which will become false after the 10th add-cart. So after that you remove-from-cart which triggers add-cart which then becomes valid again < 11 items? And transactions similarly roll back by running the inverse of what happened after the transaction started. I'm just thinking out loud. I understand you probably wouldn't use this for that, but let's have some fun shall we?

eyads2• 1 year ago

TLDR; You evaluate your preconditions and invariants before the event is published, using the current state of the aggregate.

Here's how that looks like in a DDD world:

* An aggregate is responsible for encapsulating business rules and emitting events

* An aggregate is responsible for maintaining the validity of its own state (ensuring invariants are valid)

* When a command/request is received, the aggregate first rehydrates its current state by replaying all previous events

* The aggregate then validates the command against its business rules using the current state

* Only if validation passes does the aggregate emit the new event

* If validation fails, the command is rejected (e.g., throws CartMaxLimitReached error)

Example flow: Command "AddItemToCart" arrives

> System loads CartAggregate by replaying all its events
> CartAggregate checks its invariants (current items count < 10)
> If valid: emits "ItemAddedToCart" event. If invalid: throws CartMaxLimitReached error

dagss8• 1 year ago

All systems I have worked on like this has some concept of a version number for each entity / aggregate.

So you get that the account_balance was 100$ on version 10 of the account, and write an event that deducts 10$ on version 11.

If another writer did the same at exact same time, they would write an event to deduct 100$ at version 11. There will be a conflict and only one version will win.

This is exactly like any optimistic concurrency control also without event as the primary storage.

Didn't check if the system linked to supports this, I guess it might not? But this primitive seems quite crucial to me.

swiftcoder• 1 year ago

I assume that shopping cart limit is a made-up example, but I'm curious what preconditions are you actually enforcing in the real world via DB transaction rollback?

williamdclt2• 1 year ago

You're not "just" writing to a log. You always need a view of the state of the world to take decisions that enforce invariants (in this case, the "world" is the cart, the "decision" is whether an item can be added).

What you'd do is, when you receive the "addToCart" command, construct the current state of the cart by reading the log stream (`reduce` it into an in-memory object), which has enough data to decide what to do with the command (eg throw some sort of validation exception). Plus some concurrency control to make sure you don't add multiple items concurrently.

For reading data, you could just read the log stream to construct the projection (which doesn't need to be the same structure as the object you use for writes) in-memory, it's a completely reasonable thing to do.

So at the core, the only thing you persist is a log stream and every write/read model only exists in-memory. Anything else is an optimization.

DDD calls this "view of the world" an "aggregate". Reading the log stream and constructing your aggregate from it is usually fast (log streams shouldn't be very long), if it's not fast enough there's caching techniques (aka snapshots).

Similarly, if reducing the log stream into read models is too slow, you can cache these read models (updated asynchronously as new events are written), this is just an optimization. This comes at the cost of eventual consistency though.

Joker_vD3• 1 year ago

Databases are global, shared, mutable state. [...] However, most self-respecting developers have got rid of mutable global variables in their code long ago. So why do we tolerate databases as they are?

Because the world itself is a global, shared, mutable state (which, incidentally, is also a single source of truth) and databases were invented to mirror it (well, relevant parts of it) 1-to-1, or close to it. This style of "we use the database as a proxy of the physical world itself" is still pretty common, see e.g. the example with the shopping cart somewhere else in these comments.

reubenmorais3• 1 year ago

Seeing the world as mutable is a matter of perspective, if you explicitly model time as a dimension it can instead be seen as a sequence of transitions from immutable state to immutable state, an accumulation of events over time, which fits the log abstraction perfectly.

hahn-kev• 1 year ago

Except lots of applications need to be able to forget things, similar to how things can be destroyed in the real world

Swizec• 1 year ago

if you explicitly model time as a dimension it can instead be seen as a sequence of transitions from immutable state to immutable state, an accumulation of events over time, which fits the log abstraction perfectly

I worked with a feature that used this approach once. It even made sense for the feature (an immutable history log of patient chart data). It was absolute hell to work with. Querying current state, which was 99% of the usecases, was cumbersome and extremely slow.

Turns out doctors rarely care about any of that immutable history. They just wanna know what’s up right now. In their ideal world, you’d re-answer all the same questions 30 seconds before walking in the door.

Turns out a combination mutable table of current state + derived immutable log/snapshot/audit table works much better for most things.

crabbone3• 1 year ago

perfectly

That's a joke, right? If you tried to live in this world, as a human being, not in some abstract database-building sense, you'd be completely lost in the first second of your existence, and would probably die in minutes because your body parts would "forget" how to function properly.

We make sense of the world because we have "object permanence", which requires the concept of larger things made up of components, with multiple possible configurations. This object identity is what allows to understand that you can do things like "type on a keyboard" (a keypress that changes the physical configuration of the keyboard doesn't destroy it, it's still a keyboard, just slightly different, but functionally equivalent to the one you had a moment ago).

If all you can do is transitions, you don't know upon transitioning if you still have a keyboard you are typing on or not. This also means that if you want to be able to function somehow in this world, then after each transition, you'd have to reassess all properties of all interesting aspects of the world just to make sure they are still there.

Only in very restricted number of cases can you use append-only log as a useful model. And you'd be bending over backwards with this approach when modeling trivial things, like a realtor business or an online book store.

draw_down• 1 year ago

Just get rid of side effects, man! (Turns out side effects are the whole reason we write code, whoops)

mwgalloway2• 1 year ago

The "world" can just as easily be conceived of as being composed of discrete immutable facts rather than global mutable state. Either way, I kind of don't think that unfalsifiable ontological premises should be an important factor in system design.

Joker_vD3• 1 year ago

While the world can tentatively be conceived to be like that, it is not nearly "just as easily". Lots of those "immutable facts" can't be realistically discovered: e.g. good luck recovering "this blackboard had a drawing of a cat and a bird until five minutes ago when it was cleared with a wet sponge" if you weren't there in time. The approach with mutable state fosters this destructible nature of many things upon you and makes you cope with it, somehow.

fungiblecog• 1 year ago

Datomic

agumonkey6• 1 year ago

My first thought. And then I realized that the talk is from 2015, so I wonder if there was cross pollination between people at the time (datomic was released in 2012 according to wikipedia, so it's plausible it predates Martin's ideas but I don't know)

https://news.ycombinator.com/item?id=20937215

amelius• 1 year ago

The concept is even older than that.

guskel2• 1 year ago

Yeah, like 15th century old.

mgaunard• 1 year ago

Stopped reading at the word "Kafka".

chikere2322• 1 year ago

fair

chikere2322• 1 year ago

Isn't this essentially how a modern transactional database works anyway? All mutations end up in the Write Ahead Log (WAL) and you can replicate or back up that to be able to recover the state at a point in time?

isbvhodnvemrwvn• 1 year ago

One difficult to replicate thing is visibility rules and rollbacks, with postgres you can abort and your changes are hidden, no such luxuries worth this architecture unless you make it very complex with partial states, drafts or something similar.

mrkeen7• 1 year ago

Two points:

* Technically the data is probably there, but I really don't think you want back-up ops invoked by your REST call to /getUserHistory/. Is it even possible to mix old data and new data within the same SQL expression?

* The DB is still a god object at the centre of your system. It doesn't give you consistency across partner systems and end users. If a partner sends the event CustomerBanned(2025-02-04, 1234) and you try to translate it into CRUD with 'UPDATE Customer SET Banned=True WHERE id=1234' it could fail (or worse - be rejected by an invariant for "data integrity" reasons) and then it's gone. If you just blindly write the event, then you always know that fact about customer 1234 in any future query.

zbentley2• 1 year ago

If you just blindly write the event, then you always know that fact about customer 1234 in any future query.

Unless the write times out or the DB is down for maintenance when the event arrives.

Sure, you could block acknowledgement of the event until the event log receives it, but can your DB handle synchronous write volume from however many people are out there sending events? If your RPC servers listening for events are unavailable, do you trust event senders to retry when they're back?

Down that road lies "let's put every event in a fast message bus with higher insert volume and availability than the database, and feed that into the DB asynchronously", hence Kafka and friends.

timacles2• 1 year ago

Thats how the database works internally, thats not how the interface to the database works.

Apps dont know about the WAL log, and the database has internal logic for processing the WAL. Things like transactions, rollback etc...

Xenoamorphous• 1 year ago

Does any one have some resources where a real, practical example is implemented? Because I can only find fairly theoretical resources but not real world examples.

Say, like how some simple CMS would work with a datastore like this. What does the event to update the headline of an article look like? How are integrity constraints enforced, e.g. an article can't reference an author that doesn't exist? Things like that.

briankelly• 1 year ago

Similar enough is double entry bookkeeping and generally I think event sourcing is more common for fraud detection, log analysis for security, etc. - use cases where how the application got in its state is as important as the state.

sriku• 1 year ago

https://materialize.com/ provides another approach, based on "timely dataflow" (https://timelydataflow.github.io/timely-dataflow/) - originated at MS.

belter2• 1 year ago

This is Aurora...

https://pages.cs.wisc.edu/~yxy/cs764-f20/papers/aurora-sigmo...

mrkeen7• 1 year ago

This is the technique that developers used to build Aurora, not Aurora the end-product.

Customers writing code against Aurora are still doing plain ol' destructive CRUD mutations "now".

Event-sourcing is write-ahead-logging is CQRS is journaling-file-systems is Git-reflog is persistent-data-structures is copy-on-write. It's all good stuff and is decades old.

belter2• 1 year ago

This is the technique that developers used to build Aurora, not Aurora the end-product.

It's what I meant. But I am behaving like an LLM and economizing on tokens... :-)

anacrolix• 1 year ago

Isn't this what you get with Datomic?

crabbone3• 1 year ago

So why do we tolerate databases as they are?

Because they reflect the way we understand the world? We understand that things are made of smaller things, and that sometimes the smaller things making up larger things may change, while the larger thing stays the same? The idea that as soon as one component changes the whole thing needs to be discarded and rebuilt from ground up is insane and creates a lot of problems. It's absolutely not worth it to try to redefine the way we deal with the world to get the benefits of stateless code.

Making database stateless is making it worthless. The world has a state, and if you want a useful program, it needs to accept this "unfortunate" aspect of the world. The alternative is the world where as soon as you finish drinking your coffee, your cup, your table, your kitchen, your credit card history, your grandparents and all planets in the solar system disappear, and have to be built fresh. But you wouldn't know about it, because your memory of how the world used to be would disappear too.

agentultra• 1 year ago

This is also known as event sourcing [0] and is a common pattern used inside of databases, in git, in lots of popular software.

I don't generally recommend it for every application as the tooling is not as well integrated as it is in an RDBMS and the data model doesn't fit every use-case.

However, if you have a system that needs to know "when" something happened in an on-going process, it can be a very handy architecture... although with data-retention laws it can get tricky quickly (among other reasons).

[0] https://martinfowler.com/eaaDev/EventSourcing.html

arialdomartini2• 1 year ago

I saw other times Git being related to event sourcing, but the argument is wrong.

Most of the VCSs before Git (RCS, CVS, SVN) used to store deltas and to rebuild the state reapplying them.

The very reason why Git took them by the storm is exactly because, on the contrary, Git does not store deltas but snapshots. Each commit is not there collection of the occurred chances but a complete snapshot of the whole project. Git is very efficient in reusing the blob objects to save space, but it’s still a whole snapshot. The occurred changes are not stored, and they are calculated on demand.

The very opposite of event sourcing, where it’s the state to be calculated and the occurred changes / events to be stored.

Git is really the demonstration that for code versioning state sourcing is way more efficient than event sourcing.

jdkoeck• 1 year ago

Git is still event sourced, it’s just there is only one kind of event (a commit), and its payload is the whole state ¯\_(ツ)_/¯

kragen3• 1 year ago

It seems like event sourcing keeps gaining mindshare.

alecco2• 1 year ago

Event sourcing is a PITA, from experience.

Something like Datomic makes a lot more sense:

https://vvvvalvalval.github.io/posts/2018-11-12-datomic-even...

kragen3• 1 year ago

Thanks! Does the article accurately describe the PITA in your experience? Because it seems to say that it's separable from the core architectural principle of event sourcing.

dang• 1 year ago

Turning the database inside out (2014) [video] - https://news.ycombinator.com/item?id=41664271 - Sept 2024 (1 comment)

Turning the database inside-out with Apache Samza (2015) - https://news.ycombinator.com/item?id=13581096 - Feb 2017 (30 comments)

Turning the database inside-out with Apache Samza - https://news.ycombinator.com/item?id=9145197 - March 2015 (64 comments)

Turning the database inside-out (2015)

Related Stories