Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Turning the database inside-out (2015)

martin.kleppmann.com

Post reply on HN
Print view View original

11–20 of 97 posts

Re: Turning the database inside-out (2015)

#11

Post by philbo »

cwalv wrote: post #9
joshlemer wrote: post #7

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?

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

Alternatively "invalid cart" could itself become an event.

Re: Turning the database inside-out (2015)

#12

Post by dagss »

hans_castorp wrote: post #6
dagss wrote: post #5

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/ms…

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

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?

Re: Turning the database inside-out (2015)

#13

Post by mkleczek »

dagss wrote: post #5

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/ms…

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

Re: Turning the database inside-out (2015)

#14

Post by andyferris »

dagss wrote: post #12
hans_castorp wrote: post #6

Earlier quoted context omitted.

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

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 curs…

> Perhaps postgres allows listening to the replication log with lower latency?

Yes, I think that's what the "logical decoding" referred to. Postgres can emit a "logical" version of the WAL (something with a stable spec writtten down so that other services can stream and decode it). My understanding was that "logical replication" was designed for low latency situations like creating read replicas.

I haven't heard of the logical log being preserved for "years back" but that's an interesting case...

Re: Turning the database inside-out (2015)

#15

Post by adamcharnock »

joshlemer wrote: post #7

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?

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.

Re: Turning the database inside-out (2015)

#16

Post by mrkeen »

joshlemer wrote: post #7

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?

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.

Re: Turning the database inside-out (2015)

#17

Post by dagss »

mkleczek wrote: post #13
dagss wrote: post #5

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/ms…

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-…

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

Re: Turning the database inside-out (2015)

#18

Post by dagss »

andyferris wrote: post #14
dagss wrote: post #12

Earlier quoted context omitted.

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 curs…

> Perhaps postgres allows listening to the replication log with lower latency? Yes, I think that's what the "logical decoding" referred to. Postgres can emit a "logical" version of the WAL (something with a stable spec writtten down so that other services can stream and decode it). My understanding was that "logical replication" was designed for low latency situations like creating read replicas. I haven't heard of t…

That is OK, guess I would write a job to listen to the logical WAL and use it to do an update that writes an event sequence number.

Re: Turning the database inside-out (2015)

#19

Post by anonzzzies »

joshlemer wrote: post #7

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?

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?

Re: Turning the database inside-out (2015)

#20

Post by RedShift1 »

cwalv wrote: post #9
joshlemer wrote: post #7

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?

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

Sounds like an easy way to run out of storage space
Post reply on HN