4 ms·

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

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.
dagss 7y ago
We write most code in backend (go), but we DEFINITELY spin up a new, real SQL database in for every test in our automated test suite. ("CREATE DATABASE" is rather cheap; use a fresh docker DB instance for each suite run, or run it permanently while developing. 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).
- marcus_holmes 7y ago
  agree on the mocking the database - I wrote an entire test suite based on mocking the database responses, and then found a ton of live problems caused by the database - backend interaction (things like structs not aligning with the actual return records from functions, input format for arrays of ids being different from the mockup to the actual driver, and hstores). 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.
dragonwriter 7y ago
> 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.

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.

dagss 7y ago

We write most code in backend (go), but we DEFINITELY spin up a new, real SQL database in for every test in our automated test suite. ("CREATE DATABASE" is rather cheap; use a fresh docker DB instance for each suite run, or run it permanently while developing. 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).

marcus_holmes 7y ago
agree on the mocking the database - I wrote an entire test suite based on mocking the database responses, and then found a ton of live problems caused by the database - backend interaction (things like structs not aligning with the actual return records from functions, input format for arrays of ids being different from the mockup to the actual driver, and hstores). 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.

dragonwriter 7y ago

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