> This smells like stored procedures. You can’t unit test it. You can’t version it
Say what? Stored procedures are awesome when used correctly.
Versioning is straightforward. You stick any sort of monotonically increasing id at the end of the name. Whenever you need a breaking change, you bump the id. You also leave the old version with the old id, retiring it only after it’s no longer used. You do need a real story for DB upgrades for this to work well. If your story is that someone on the team executes some random SQL migration as root, you’re gonna have a bad time.
You can unit test stored procedures in exactly the same way you could test any other SQL. You have to spin up a DB to do it. But if you can’t test your stored procedures, you’re admitting you have no way to test your SQL which is your real problem.
> Business logic in the database, (hidden brain problem)
Ok? How much you shove into your stored procedures is up to you. In my experience the real alternative to stored procedures is not zero business logic in the DB. It’s SQL code sprinkled throughout the codebase, where it’s harder to test, poorly versioned, and poorly encapsulated. And also often needlessly slow.
> harder to isolate noisy workloads
Dunno what this means
> no observability
Maybe some truth here. It is more work to inspect issues in SQL than most programming languages.
> scaling pressure lands solely in Postgres, lack of IO, especially API calls.
If stored procedures are causing IO problems and scaling issues then you are using them wrong.
Stored procedures often drastically reduce IO when used correctly and thereby improve scalability.
The road to eternal burning hell is paved with stored procedures. My experiences (!!) make it so i will never be convinced on the risk:reward being worth it.
I was referring to network I/O. But disk I/O should be at least as good for a stored procedure and often better. It’s classic “bring the computation to the data”. Putting the computation into the DB means that use of disk caching (up to and including CPU caching) is maximized.
Pulling the data out of the DB to do computation in a higher layer cannot be more efficient in terms of any I/O unless your stored procedure is just poorly written. It might be a win if your DB is compute bound, though.
Say what? Stored procedures are awesome when used correctly.
Versioning is straightforward. You stick any sort of monotonically increasing id at the end of the name. Whenever you need a breaking change, you bump the id. You also leave the old version with the old id, retiring it only after it’s no longer used. You do need a real story for DB upgrades for this to work well. If your story is that someone on the team executes some random SQL migration as root, you’re gonna have a bad time.
You can unit test stored procedures in exactly the same way you could test any other SQL. You have to spin up a DB to do it. But if you can’t test your stored procedures, you’re admitting you have no way to test your SQL which is your real problem.
> Business logic in the database, (hidden brain problem)
Ok? How much you shove into your stored procedures is up to you. In my experience the real alternative to stored procedures is not zero business logic in the DB. It’s SQL code sprinkled throughout the codebase, where it’s harder to test, poorly versioned, and poorly encapsulated. And also often needlessly slow.
> harder to isolate noisy workloads
Dunno what this means
> no observability
Maybe some truth here. It is more work to inspect issues in SQL than most programming languages.
> scaling pressure lands solely in Postgres, lack of IO, especially API calls.
If stored procedures are causing IO problems and scaling issues then you are using them wrong.
Stored procedures often drastically reduce IO when used correctly and thereby improve scalability.