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.
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.
Mocking away DB is both an inefficient way to work and lets the tests cover less. I will never write software without running tests against a real DB again.
Our setup totally would let you test anything in stored procedures (although we do not use them much).
Now I have a test database, which I create from a version-controlled schema.sql, and each test creates its own fixtures in the test db. Works way better.
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.
Performance and arbitrary scaling aren't problems that always occur together. Not every app with performance concerns is a mass audience consumer app.
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.
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.
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.
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/
Why do you prefer it? Performance? Features? Ease-of-use?
The reason for these architectural choices was that we had a need to connect database-only clients such as 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/ 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.
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)
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.
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.
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.