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

PostgreSQL example of self-contained stored procedures

sivers.org

Post reply on HN
Print view View original

61–70 of 81 posts

Re: PostgreSQL example of self-contained stored procedures

#61

Post by mistahenry »

tashian wrote: post #45

One unmentioned reason this is powerful: It avoids concurrency bugs and inconsistencies introduced by write skew, in cases where the application layer (eg. Ruby/Python) makes business logic decisions based on data that has become stale. These are hardest to detect and avoid in the case where business logic depends on no rows matching (count(*) == 0), eg. meeting room scheduling systems, because there is nothing for a…

Can’t you just handle these sorts of consistency issues with uniqueness, foreign key, and check constraints?

Re: PostgreSQL example of self-contained stored procedures

#62

Post by cryptonector »

ben509 wrote: post #39
detaro wrote: post #25

Earlier quoted context omitted.

Why not put them in version control, and have your deployment process pull them out of the repo and apply them to the database?

It's a bit more complicated than just "upload all my stored procedures" in practice. I think we need a version control / deployment system that is designed for databases to make this work. We want it to regard the database as the source of truth, and understand that while you can blow away a development database, you can't do that to prod. We want it to be able to track which clients are using which stored procedures…

I don't get it. You have a source file. It has your store procedures and what not. You deploy it. You have the server startup load it. Done.

What's the problem?

Well, you might complain that some schema changes are hard to make this way, and that's true, but for stored procedures it's straightforward, and even for other things you can manage and make the update atomic (though it'd be nicer to have better SQL language support for schema changes).

As with everything, you need to mind backwards compatibility. This is true for C, C++, Rust, Java, and SQL/PlPgSQL. There's nothing special about this case.

Re: PostgreSQL example of self-contained stored procedures

#63

Post by dariojcravero »

paulddraper wrote: post #22
cpursley wrote: post #19

Earlier quoted context omitted.

I prefer https://hasura.io Instead of JS, it's written in Haskell (end user does not write haskell). It's incredibly performant and has hooks for database events. Another similar tool is http://postgrest.org/en/v6.0/

Hadn't heard that before. Why do you prefer it? Performance? Features? Ease-of-use?

Automated and consistent GraphQL APIs for all CRUD operations and a simple extension layer (through events or remote schemas) are our reasons to work with Hasura. It's fantastic. The performance is very good too because of how they optimise queries.

Re: PostgreSQL example of self-contained stored procedures

#64

Post by dragonwriter »

tashian wrote: post #45

One unmentioned reason this is powerful: It avoids concurrency bugs and inconsistencies introduced by write skew, in cases where the application layer (eg. Ruby/Python) makes business logic decisions based on data that has become stale. These are hardest to detect and avoid in the case where business logic depends on no rows matching (count(*) == 0), eg. meeting room scheduling systems, because there is nothing for a…

> These are hardest to detect and avoid in the case where business logic depends on no rows matching (count(*) == 0), eg. meeting room scheduling systems, because there is nothing for a transaction to lock!

The predicate lock held by a read transaction at the serializable transaction level will, in fact, prevent this. If you need something that secured it between DB transactions because you need a longer-running business transaction but need to avoid long-running DB transactions, you can place a hold with a pending record in a reservations table and appropriate exclusion constraint without a stored proc.

> Stored procedures can avoid this entirely, even under the weaker transaction isolation levels, by keeping all the logic in the DB and running it atomically.

Isn't that just manually replicating, on an ad hoc basis, the machinery the server already has for implementing serializable isolation level? There may be times when doing this by hand is justified for performance or other reasons, but in general doing special-case replication of features for which the DB server had general solutions is a pretty poor use of development time.

Re: PostgreSQL example of self-contained stored procedures

#65

Post by ramraj07 »

Merad wrote: post #53
tashian wrote: post #45

One unmentioned reason this is powerful: It avoids concurrency bugs and inconsistencies introduced by write skew, in cases where the application layer (eg. Ruby/Python) makes business logic decisions based on data that has become stale. These are hardest to detect and avoid in the case where business logic depends on no rows matching (count(*) == 0), eg. meeting room scheduling systems, because there is nothing for a…

Ive worked on several apps that tried this approach and each of them was a mess. I’ve seen two fundamental problems with building your app on top of stored procs: * Tooling around sql is generally inferior to what’s available for . I’ve yet to see a company with effective automated tests around their database... it’s far more common to have _no_ tests around the database. Even if you’re the unicorn that does have all…

This has always struck me as odd, how can _any_ competent developer suck at SQL especially after spending maybe a week or two trying to write logic in it? As "languages" go it can't get any simpler, you're just directly forced to think about data in a model that's extremely close to the data itself. Seems to me that a dev that can't think well in SQL with minimal training is not a good dev at all, and this could actually be a nice filter.

Re: PostgreSQL example of self-contained stored procedures

#66

Post by ramraj07 »

pmontra wrote: post #23
BubRoss wrote: post #18

Earlier quoted context omitted.

How is that not how databases are typically used?

Returning JSON to a client is relatively recent. The standard for web apps was to get data from the db and generate HTML on the server. A JSON layer would be a useless overhead. Furthermore about everybody knows how to use an ORM. Not many people can write store procedures. Maybe the question is why is that so. I heard "I know [language], I don't know SQL, so I write queries in [language] " so many times even from pe…

Do you think it's an acceptable excuse for a dev to say they don't know SQL?

Re: PostgreSQL example of self-contained stored procedures

#67

Post by bfung »

This is an old pattern (like early 2000s MS SQL app development) and works wonderfully for the majority of applications.

It does become complex and awkward in two case, 1) variable option selection and 2) at a certain "scale" / quantity of inserts/deletes.

Variable option selection

Take for example a search interface of dependent items and item options, like cars. The query for car options for different car models generally returns a variable list of options. Further filtering results based on the variable list of options, when the list of options is all in one table, is most efficient with an "in clause". Trying to represent the "in clause" using functions/stored procedures is a little awkward as there is list construction and deconstruction involved, and sql is not great at list/string parsing.

Number of inserts/deletes

At some point, a successful app will reach a point where it's more efficient to batch insert & delete -- a couple hundred rows. This goes back again to using "in clauses' and the stored procedure approach makes things a bit awkward, as then the store procedure will need to argument parse and check.

Not saying what's done here is bad - it's actually really good for many, many reasons. I'd actually recommend the approach and avoid what I've described above unless it makes the desired functionality hard and complex.

Re: PostgreSQL example of self-contained stored procedures

#68

Post by dragonwriter »

thijsvandien wrote: post #17
llimllib wrote: post #3

I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. I wrote apps which consisted mostly of oracle stored procedures when I first got out of college, and it was a pretty awful experience, but it was also at a place where the development knowledge was minimal. I've since been getting closer and closer to writing postgres in this way. My current app (which I ar…

Every once in a while I encounter somebody who heavily promotes that way of working. There are a couple of arguments I've heard in favor of it. Often it's a mix of performance, ultimate data integrity, and "simplicity". I just don't buy it. If performance is so critical, whatever you gain by moving everything to the database is lost by the fact that databases are harder to scale. You may be able to get by with a sing…

> If performance is so critical, whatever you gain by moving everything to the database is lost by the fact that databases are harder to scale.

Performance and arbitrary scaling aren't problems that always occur together. Not every app with performance concerns is a mass audience consumer app.

Re: PostgreSQL example of self-contained stored procedures

#70

Post by segmondy »

ris wrote: post #59
segmondy wrote: post #33

Earlier quoted context omitted.

Tell us about your scaling problem. Do you really have a scaling problem?

Ok, where does this go when it needs to grow beyond a single machine? Is it time to set up replication? Partitioning?

How many orgs do you know that need to grow beyond a single machine let alone an indie developer? This post was from an indie developer not some big corp engineering blog. I don't know what you mean by partitioning, I'm assuming sharding and not partition/splitting of tables, because this approach is not mutually exclusive to that. MySQL and PostgreSQL can scale up very vertical if designed well. 99.99% or even more never need to scale horizontally. Nevertheless, this can be scaled horizontally carefully a bit. You grow your replicas. Your main DB will be your transactional DB. All procedures that are doing inserts/updates/deletes and select will call that DB. All non transactional SELECTS will call the read only replicas and treat those as your analytical. You can easily have something like one main DB, 3-5 read only DB. Add caching, Add queues and you can have a system that can serve 1+ million users daily.
Post reply on HN