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

Implementing State Machines in PostgreSQL

felixge.de

Post reply on HN
Print view View original

31–40 of 90 posts

Re: Implementing State Machines in PostgreSQL

#31

Post by edoceo »

mercer wrote: post #19
jsjohnst wrote: post #9

Earlier quoted context omitted.

I hear the argument about switching storage layers a lot, and I don't completely disagree with it, but in my 20+ years writing code, I've never found a case where switching storage engines didn't cause a massive rewrite even when the storage layer was used in an agnostic way. I'm sure there are examples where it has worked, but saying it as if it's a maxim just feels wrong to me.

Could you give some specifics as to what kind of stuff typically needs refactoring if you switch storage layers?

Stored Procedures, any use of the RDBMS SQL extensions (eg: BETWEEN)

Re: Implementing State Machines in PostgreSQL

#32

Post by bluejekyll »

felixge wrote: post #17
bluejekyll wrote: post #2

Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres. If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else.…

Author here. > If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. Yeah, but I don't consider this a bad thing. IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database. > If ever you want to scale this, such that you wan…

> IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database.

Very likely true. But when you get locked into Oracle and it makes it nearly impossible to move because of the features your relying on, has a big effect on shaping your perspective on this.

> The application I'm using this in has > 1 billion rows and we frequently re-compute the state of all our entities across the entire data set in batches after we make changes to our logic.

That is very impressive. Mind sharing the rate of change across all of those rows?

Also, I'm curious. Is this all that DB instance does? Or is it responsible for other data as well?

Re: Implementing State Machines in PostgreSQL

#33

Post by cwyers »

bluejekyll wrote: post #32
felixge wrote: post #17

Earlier quoted context omitted.

Author here. > If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. Yeah, but I don't consider this a bad thing. IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database. > If ever you want to scale this, such that you wan…

> IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database. Very likely true. But when you get locked into Oracle and it makes it nearly impossible to move because of the features your relying on, has a big effect on shaping your perspective on this. > The application I'm using this in has > 1 billion rows and we…

The only thing worse than paying through the nose for Oracle is paying through the nose for Oracle and treating it like a really expensive version of MySQL/Postgres. If you're paying for it, might as well get some value for your money and use those features.

Re: Implementing State Machines in PostgreSQL

#34

Post by felixge »

bluejekyll wrote: post #32
felixge wrote: post #17

Earlier quoted context omitted.

Author here. > If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. Yeah, but I don't consider this a bad thing. IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database. > If ever you want to scale this, such that you wan…

> IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database. Very likely true. But when you get locked into Oracle and it makes it nearly impossible to move because of the features your relying on, has a big effect on shaping your perspective on this. > The application I'm using this in has > 1 billion rows and we…

> But when you get locked into Oracle and it makes it nearly impossible to move because of the features your relying on, has a big effect on shaping your perspective on this.

I hear you. Picking a database is major decision and shouldn't be made lightly. That being said, I hear a lot of companies are successfully migrating from Oracle to Postgres these days. And in fact, this application was actually migrated from Couchbase to Postgres, but that's a story for another day perhaps :).

> That is very impressive. Mind sharing the rate of change across all of those rows?

Nowadays its about 5 million inserts / day with peak rates of 150 inserts / sec. Not too crazy, but it adds up over time :).

> Also, I'm curious. Is this all that DB instance does? Or is it responsible for other data as well?

It does a few other things, but this is the main workload.

Re: Implementing State Machines in PostgreSQL

#35

Post by _jal »

pilif wrote: post #6
bluejekyll wrote: post #2

Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres. If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else.…

> If ever you decide you want to migrate away from Postgres[…] it's one of the things to keep in mind. Depending on your application, migrating away from postgres will be more or less painful. If you're already deeply invested in postgres features, this is just one more problem to solve. > If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewher…

> If the database blows up on any attempts to store invalid data, I'm protected from having to ask these questions.

I'm always surprised when people fight using constraints. During dev, I see them as a godsend for spotting problems - I don't know how many bugs having self-enforcing data structures has caught.

I will say that I have, under protest, turned them off in production once for performance. A particular heavily-used flow was annoying due to a large number of FK checks on an intermediate step. But never during development, and given the number of FK-violation errors I've seen in production code, preferably not even then.

Fixing code is almost always so much easier than fixing data.

Re: Implementing State Machines in PostgreSQL

#36

Post by felixge »

Author here: If the article above has you excited, come and join my team at Apple. We're hiring Go and PostgreSQL developers in Shanghai, China right now. Relocation is possible, just send me an e-mail to find out more about this role.

My e-mail is in my profile.

Re: Implementing State Machines in PostgreSQL

#37

Post by oelmekki »

bluejekyll wrote: post #2

Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres. If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else.…

Actually, I'm under the impression it's way more common to migrate language or framework than data storage. I externalize most of my business logic to postgres for that reason (plus, it's incredibly performant, especially since you don't keep requesting connections from connection pool for each single part of the computation). My backend app handles request sanitizing, providing endpoints, etc. All data processing is made in the database. Love it.

Re: Implementing State Machines in PostgreSQL

#38

Post by _jal »

devy wrote: post #5
ericfrederich wrote: post #4

Earlier quoted context omitted.

My thoughts exactly. I don't do a ton of DB programming, but I've only ever written one thing in a non-agnostic way. We got a requirement that users wanted to copy an entire "project" which was the top level of a hierarchy. I wrote an Oracle routine to do the deep copy. I did it because I imagined what the PL/SQL would look like (clean) vs. what the Java code would look like (considering Hello World in Java is ugly,…

Mine too! Having logic embedded into SQL functions seems to be an anti-pattern to me (it's harder to maintain and harder to do release management). While it's great that Postgres can do this (btw, I love Postgres), I suspect there aren't many people will use this feature in production environment.

I used to think this, but have relaxed that view over time.

For constraints, validity checking, things like adding/updating timestamps, and other things that are about data integrity, about the only time I don't do that in the DB is when outside information is involved such that it can't be. Otherwise, to the extent possible, I want the datastore to only accept valid data. This goes to the notion that fixing code is easier than fixing data, so the store can not only defend itself, but also help catch bugs.

There are also times when dealing with huge amounts of data that doing whatever you're doing in an SP is the only way to get decent performance. Dragging enormous tables over the network to process is sometimes really wasteful. If you're tuning indexes and whatnot against that model, you're already changing the DB, and doing so at a level that is implicit rather than explicit, and in ways that can change out from under you (if the statistics change).

Re: Implementing State Machines in PostgreSQL

#39

Post by mozumder »

ysleepy wrote: post #12

Cool trick. Should be an enum instead of text though. Also the transition table might be an actual SQL table as well instead of switch-cases in a function. - That way it remains a bit more declarative and you can do some meta-queries.

> Also the transition table might be an actual SQL table as well instead of switch-cases in a function.

That would likely add an unnecessary read and slow things down just a little bit.

Re: Implementing State Machines in PostgreSQL

#40

Post by aeorgnoieang »

bluejekyll wrote: post #26
jjawssd wrote: post #7

Earlier quoted context omitted.

This implies that a need to migrate away from Postgres will arise. For most companies it is very likely that such a need will never materialize. Meanwhile Postgres keeps getting better and better. https://wiki.postgresql.org/wiki/New_in_postgres_10

It's not just about migration. There are deployment issues as well. I like to think of each layer in a tiered system as having differing deployment requirements and time tables. Front-end systems will be very frequent, backend systems possibly less so, though not necessarily, and then DBs ideally infrequent and they generally take longer. At a minimum, keeping them decoupled is freeing for patching bugs and releasing…

There are good deployment tools for databases, e.g. ones for which all of the database 'code' objects (or just all of the objects period) are maintained in source control and updates are either automatic or scripted.

Of course updating a database is much harder than overwriting executable or library files, but a lot of the objects in a database should be safely updatable by simply dropping and recreating them.

The tricky changes are of course things like, e.g. splitting one column into two or merging two columns in two tables into one. But those changes are even harder to do the 'dumber' your database is, i.e. the less logic there is in it that enforces a certain level of quality in its data.

Post reply on HN