6 ms·

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 s

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 architected) sticks close to postgres and uses many of its features like custom types, enums and json, but we don't rely on it for stored procedures at all. Instead, all application logic lives in the go API app. I've been considering moving some things directly into the database, but I'm nervous about it because the app runs well and I like the data/logic division. Also go seems easier to learn for new developers than SQL, and it's a lot easier to deploy a new app server than it is to reconfigure a database server.

tyingq 7y ago
It's "cheating" a bit, but Postgrest is interesting in this space: https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest
- llimllib 7y ago
  yes! I've seen that and I'm considering writing some sort of similar postgres -> protobuf layer so that I can automatically generate my grpc structs from postgres enums. Right now a pain point for us is that we have a generated database layer, a generated protobuf layer, and we have to do lots of fiddly manual database -> grpc struct translations. Too easy to get off-by-one errors (because you usually need a null enum element in protobuf that you don't need in postgres, particularly)
- krueger71 7y ago
  Yes definitely. I was involved in a project where we used Postgrest on top of a layered database. Postgrest served an api-schema consisting only of views where some had instead-of triggers needed for updates and inserts of data. Some functions and procedures were also present in the api-schema. The tables were kept in a separate data-schema and considered a private implementation detail in the same way one would do it with a more traditional architecture. During development we used tools like https://sqitch.org/ https://sqitch.org/ and https://pgtap.org/ https://pgtap.org/ to do schema-evolution and unit-testing. The reason for these architectural choices was that we had a need to connect database-only clients such as https://qgis.org/ https://qgis.org/ as well as web-front ends and wanted to route all requests and updates through the same logic. I think it worked out quite well. We were able to do quite heavy https://postgis.net/ https://postgis.net/ lifting in the views with good performance. Of course the tooling feels rather primitive during editing, but the round-trip and deployment is fast. Unit tests run faster than you might expect even with rollbacks and loading of test data between each test.
moltar 7y ago
You may find Graphile interesting https://www.graphile.org/ https://www.graphile.org/
- cpursley 7y ago
  I prefer https://hasura.io 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/ http://postgrest.org/en/v6.0/
  - paulddraper 7y ago
    Hadn't heard that before. Why do you prefer it? Performance? Features? Ease-of-use?
    - dariojcravero 7y ago
      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.
msvalkon 7y ago
I have worked in a team which maintained and improved a legacy piece of software generating a few hundred million euros of yearly revenue. Not technically a website but a critical backend piece of a very popular one. The application was written in Java and PostgreSQL, which leveraged stored procedures heavily. This was a _major pain_ for us. While the separation of concerns was sometimes quite easy to understand between the business logic in the Java code and the business logic in the sprocs, there were times when it was an impossible, tangled mess. Debugging this thing was _hard_ and every deployment was fragile and hard to deal with. There are of course many reasons as to why this particular piece of software evolved as it did. I can only say that if you plan to move any parts of the business logic to stored procedures, make sure that you have a good reason to do so and clear architectural patterns and rules to communicate and follow.
Avamander 7y ago
> I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. I personally didn't develop but I've talked to people who did. The Estonian national web portal `eesti.ee` intially ran on stored procedures (and technically survived the DDoS of 2007 but was taken down "just in case" :S), later some Java garbage layer was added which significantly worsened the performance and made further development significantly harder.
thijsvandien 7y ago
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 single instance a little longer, but when eventually you don't anymore, you've now worked yourself in a corner. It's an 80/20 game anyway, so just use an occasional stored procedure when it's really worth it. For data integrity, I've always found databases not expressive enough. Sanity checks, okay, but you can't capture a non-trivial domain in some custom data types, check constraints and foreign keys. Even if you introduce stored procedures that will be responsible for keeping everything proper, you need to go crazy with permissions to block circumventing those. Might as well build your own API then. (I do find it difficult where to draw the line when it comes to what to enforce in the database still.) "But you don't need another API! Just use the database as one!" Then I ask how they do testing, and the answer basically is: "We don't make mistakes or we find out about them (in production) soon enough." That pretty much ends the discussion for me. Surely there must be ways to devise a basic testing framework for stored procedures, but why bother? I don't want to spin up a database just to test some logic. Never mind testing, what about refactoring? From a theoretical point of view, I can see the potential, but practically...? The tooling isn't there, and perhaps that's for a reason. Maybe databases are just not supposed to be used that way. Despite the extra functionality they offer, I treat them mostly as a data store. What am I missing? I would love to be proven wrong.
- patrec 7y ago
  That scaling dbs is hard is so perf argument makes no sense is a questionable claim. Almost nothing needs to scale horizontally-but almost all software is so slow that users notice. I agree with you that there is a big practical tooling problem.
- chatmasta 7y ago
  The stored procedure can operate on foreign schemas, so could be scaled that way. Then you would have a bunch of lightweight PG engines with no data in them making calls to the upstream. In practice though, the logic your application uses is going to generate the same queries anyway. So I’m skeptical that using stored procedures increases the workload of the database. If you need to scale your database, you can do it with any standard replication strategy, and just make sure to bring the functions to the replica too. If you package them in extensions, this is easy. To be clear, you’re not answering web requests from the database. There is still an application in between; it’s just much thinner, or can be some abstraction like postgrest or postgraphile. You scale that too.
sgk284 7y ago
Salesforce is built (or at least was ~7 years ago) heavily on stored procedures. Some projects, exclusively so. Working there as a software engineer introduced me to a lot of dysfunctional things (as is true of all big tech cos) but pushing as much into the database as possible is one thing that worked beautifully and I fell in love with it. Since leaving Salesforce, I've embraced that pattern in three other projects and I've never regretted it. It turns out that the database is the best place to work with data. It's verbose but has primitives that your application server simply can't have. And it's beautiful how querying data vs reading a variable have the same amount of cognitive overhead. That is, the lines between what's in memory and what's coming from disk start to disappear. You just work with data, insert it when you're ready, and don't think about it again. And the performance gains of not having to serialize data back and forth across the wire unnecessarily is huge. I'd also be remiss if I didn't point out that if you are a fan of pub/sub patterns then triggers should be your favorite feature to use. They're just transactional pub/sub.
TheTaytay 7y ago
> I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. YNAB's web app (YouNeedABudget.com) is architected in a similar manner, in that a lot of logic is performed in Postgres Functions. Rails is the API layer, (which lets Rails do authentication, authorization, and a number of other things that Rails does well), but many of the performance-sensitive calculations and sync algorithm are implemented as Postgres functions. The SQL is version controlled of course, and the functions are DROPped/Redeclared when they change, so other than being written in PL/pgSQL the development experience is similar to any other deployed web app. We chose to do it not because we expect the wrapping language to change from Rails, but because our data is very relational, our calculation and sync logic is more "easily" and accurately expressed in PL/pgSQL, and we don't have to pay a performance penalty to bring a lot of data across the wire to Ruby, run logic on it, and then ship a lot of data back to the database. I'm not advocating this as an approach for most web apps, but it's well worked for us. It's important to get your tooling right from the get-go so that the development experience is a good one. > I've been considering moving some things directly into the database, but I'm nervous about it because the app runs well and I like the data/logic division. Also go seems easier to learn for new developers than SQL, and it's a lot easier to deploy a new app server than it is to reconfigure a database server. Those are really valid concerns! It's difficult to find developers who are comfortable with _advanced_ SQL, so I believe that is especially true for new developers as you point out. I think it's easier to reason about higher level languages like Go than it is to perform advanced logic in a declarative language like SQL, but at least PL/PGSQL makes that easier with constructs like loops, "if" statements, etc. Although I don't regret the decision in this case, I would have to be presented with a _very_ similar problem to architect a future web app the same way. Otherwise, I'd stick with a language/framework the team is more comfortable with.
AmericanChopper 7y ago
This used to be the standard architecture for “enterprise” apps. I’ve worked on lots of Java/Oracle apps like this, and there’s some pretty good reasons this isn’t widely used anymore. It makes your DB more of a bottleneck, which is the hardest part of your architecture to scale. You can’t use SQL for all of your business logic, so you end up fragmenting it across two codebases, which sucks, especially at scale. Databases are optimised for reading, writing and comparing large sets of data, and a typical app will have business logic that simply isn’t performant in a DB. This architecture is basically guaranteed to scale into performance bottlenecks and enormous amounts of complexity. On the other hand, I don’t really buy the “most engineers aren’t familiar with SQL” line of reasoning. Maybe that’s true, but SQL is incredibly simple, I think any engineer could pick it up quite easily, and that what you learn from doing so will just make you a better engineer in general.
eternalban 7y ago
If you have competent or better domain modeling skills, the database route is optimal. In general, Object layer domain modeling (typical in industry) is mainly addressing skill gaps in domain modeling, relational dbs, and SQL. Of course, if you are writing something like Microsoft Office, where there will be pervasive reuse of object layer components, an Object Domain Model will be a better fit - it makes sense to define the system fundamentally at the object/component level. In my experience in the industry, the rise of object layer modeling was due to multiple issues, some noted above and also including organizational factors such as the traditional DBA/Dev divide, licensing, etc. So a pure technical approach to this question is, imo, non-optimal. It is ultimatley a business decision.

tyingq 7y ago

It's "cheating" a bit, but Postgrest is interesting in this space: https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest

llimllib 7y ago
yes! I've seen that and I'm considering writing some sort of similar postgres -> protobuf layer so that I can automatically generate my grpc structs from postgres enums. Right now a pain point for us is that we have a generated database layer, a generated protobuf layer, and we have to do lots of fiddly manual database -> grpc struct translations. Too easy to get off-by-one errors (because you usually need a null enum element in protobuf that you don't need in postgres, particularly)
krueger71 7y ago
Yes definitely. I was involved in a project where we used Postgrest on top of a layered database. Postgrest served an api-schema consisting only of views where some had instead-of triggers needed for updates and inserts of data. Some functions and procedures were also present in the api-schema. The tables were kept in a separate data-schema and considered a private implementation detail in the same way one would do it with a more traditional architecture. During development we used tools like https://sqitch.org/ https://sqitch.org/ and https://pgtap.org/ https://pgtap.org/ to do schema-evolution and unit-testing. The reason for these architectural choices was that we had a need to connect database-only clients such as https://qgis.org/ https://qgis.org/ as well as web-front ends and wanted to route all requests and updates through the same logic. I think it worked out quite well. We were able to do quite heavy https://postgis.net/ https://postgis.net/ lifting in the views with good performance. Of course the tooling feels rather primitive during editing, but the round-trip and deployment is fast. Unit tests run faster than you might expect even with rollbacks and loading of test data between each test.

moltar 7y ago

You may find Graphile interesting https://www.graphile.org/ https://www.graphile.org/

cpursley 7y ago
I prefer https://hasura.io 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/ http://postgrest.org/en/v6.0/
- paulddraper 7y ago
  Hadn't heard that before. Why do you prefer it? Performance? Features? Ease-of-use?
  - dariojcravero 7y ago
    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.

msvalkon 7y ago

I have worked in a team which maintained and improved a legacy piece of software generating a few hundred million euros of yearly revenue. Not technically a website but a critical backend piece of a very popular one. The application was written in Java and PostgreSQL, which leveraged stored procedures heavily. This was a _major pain_ for us. While the separation of concerns was sometimes quite easy to understand between the business logic in the Java code and the business logic in the sprocs, there were times when it was an impossible, tangled mess. Debugging this thing was _hard_ and every deployment was fragile and hard to deal with. There are of course many reasons as to why this particular piece of software evolved as it did. I can only say that if you plan to move any parts of the business logic to stored procedures, make sure that you have a good reason to do so and clear architectural patterns and rules to communicate and follow.

Avamander 7y ago

> I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. I personally didn't develop but I've talked to people who did. The Estonian national web portal `eesti.ee` intially ran on stored procedures (and technically survived the DDoS of 2007 but was taken down "just in case" :S), later some Java garbage layer was added which significantly worsened the performance and made further development significantly harder.

thijsvandien 7y ago

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 single instance a little longer, but when eventually you don't anymore, you've now worked yourself in a corner. It's an 80/20 game anyway, so just use an occasional stored procedure when it's really worth it. For data integrity, I've always found databases not expressive enough. Sanity checks, okay, but you can't capture a non-trivial domain in some custom data types, check constraints and foreign keys. Even if you introduce stored procedures that will be responsible for keeping everything proper, you need to go crazy with permissions to block circumventing those. Might as well build your own API then. (I do find it difficult where to draw the line when it comes to what to enforce in the database still.) "But you don't need another API! Just use the database as one!" Then I ask how they do testing, and the answer basically is: "We don't make mistakes or we find out about them (in production) soon enough." That pretty much ends the discussion for me. Surely there must be ways to devise a basic testing framework for stored procedures, but why bother? I don't want to spin up a database just to test some logic. Never mind testing, what about refactoring? From a theoretical point of view, I can see the potential, but practically...? The tooling isn't there, and perhaps that's for a reason. Maybe databases are just not supposed to be used that way. Despite the extra functionality they offer, I treat them mostly as a data store. What am I missing? I would love to be proven wrong.

patrec 7y ago
That scaling dbs is hard is so perf argument makes no sense is a questionable claim. Almost nothing needs to scale horizontally-but almost all software is so slow that users notice. I agree with you that there is a big practical tooling problem.
chatmasta 7y ago
The stored procedure can operate on foreign schemas, so could be scaled that way. Then you would have a bunch of lightweight PG engines with no data in them making calls to the upstream. In practice though, the logic your application uses is going to generate the same queries anyway. So I’m skeptical that using stored procedures increases the workload of the database. If you need to scale your database, you can do it with any standard replication strategy, and just make sure to bring the functions to the replica too. If you package them in extensions, this is easy. To be clear, you’re not answering web requests from the database. There is still an application in between; it’s just much thinner, or can be some abstraction like postgrest or postgraphile. You scale that too.

sgk284 7y ago

Salesforce is built (or at least was ~7 years ago) heavily on stored procedures. Some projects, exclusively so. Working there as a software engineer introduced me to a lot of dysfunctional things (as is true of all big tech cos) but pushing as much into the database as possible is one thing that worked beautifully and I fell in love with it. Since leaving Salesforce, I've embraced that pattern in three other projects and I've never regretted it. It turns out that the database is the best place to work with data. It's verbose but has primitives that your application server simply can't have. And it's beautiful how querying data vs reading a variable have the same amount of cognitive overhead. That is, the lines between what's in memory and what's coming from disk start to disappear. You just work with data, insert it when you're ready, and don't think about it again. And the performance gains of not having to serialize data back and forth across the wire unnecessarily is huge. I'd also be remiss if I didn't point out that if you are a fan of pub/sub patterns then triggers should be your favorite feature to use. They're just transactional pub/sub.

TheTaytay 7y ago

> I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. YNAB's web app (YouNeedABudget.com) is architected in a similar manner, in that a lot of logic is performed in Postgres Functions. Rails is the API layer, (which lets Rails do authentication, authorization, and a number of other things that Rails does well), but many of the performance-sensitive calculations and sync algorithm are implemented as Postgres functions. The SQL is version controlled of course, and the functions are DROPped/Redeclared when they change, so other than being written in PL/pgSQL the development experience is similar to any other deployed web app. We chose to do it not because we expect the wrapping language to change from Rails, but because our data is very relational, our calculation and sync logic is more "easily" and accurately expressed in PL/pgSQL, and we don't have to pay a performance penalty to bring a lot of data across the wire to Ruby, run logic on it, and then ship a lot of data back to the database. I'm not advocating this as an approach for most web apps, but it's well worked for us. It's important to get your tooling right from the get-go so that the development experience is a good one. > I've been considering moving some things directly into the database, but I'm nervous about it because the app runs well and I like the data/logic division. Also go seems easier to learn for new developers than SQL, and it's a lot easier to deploy a new app server than it is to reconfigure a database server. Those are really valid concerns! It's difficult to find developers who are comfortable with _advanced_ SQL, so I believe that is especially true for new developers as you point out. I think it's easier to reason about higher level languages like Go than it is to perform advanced logic in a declarative language like SQL, but at least PL/PGSQL makes that easier with constructs like loops, "if" statements, etc. Although I don't regret the decision in this case, I would have to be presented with a _very_ similar problem to architect a future web app the same way. Otherwise, I'd stick with a language/framework the team is more comfortable with.

AmericanChopper 7y ago

This used to be the standard architecture for “enterprise” apps. I’ve worked on lots of Java/Oracle apps like this, and there’s some pretty good reasons this isn’t widely used anymore. It makes your DB more of a bottleneck, which is the hardest part of your architecture to scale. You can’t use SQL for all of your business logic, so you end up fragmenting it across two codebases, which sucks, especially at scale. Databases are optimised for reading, writing and comparing large sets of data, and a typical app will have business logic that simply isn’t performant in a DB. This architecture is basically guaranteed to scale into performance bottlenecks and enormous amounts of complexity. On the other hand, I don’t really buy the “most engineers aren’t familiar with SQL” line of reasoning. Maybe that’s true, but SQL is incredibly simple, I think any engineer could pick it up quite easily, and that what you learn from doing so will just make you a better engineer in general.

eternalban 7y ago

If you have competent or better domain modeling skills, the database route is optimal. In general, Object layer domain modeling (typical in industry) is mainly addressing skill gaps in domain modeling, relational dbs, and SQL. Of course, if you are writing something like Microsoft Office, where there will be pervasive reuse of object layer components, an Object Domain Model will be a better fit - it makes sense to define the system fundamentally at the object/component level. In my experience in the industry, the rise of object layer modeling was due to multiple issues, some noted above and also including organizational factors such as the traditional DBA/Dev divide, licensing, etc. So a pure technical approach to this question is, imo, non-optimal. It is ultimatley a business decision.