If you’re hiring devs for their experience with C# (which the app was written in) then you probably want to have the bulk of your business logic written in C#. Even the DBAs at my current company tend to advocate for using stored procedures only as a last resort for this reason.
That team eventually decided they needed to rewrite the whole app using a NoSQL database. I didn’t stick around long enough to see how that decision turned out.
But it's also a reality, that relational databases often become the main integration point in companies. In those environments it’s hard (next to impossible) and dangerous to use something like ORMs.
Often enough I don't "own the tables" and I don't "own the columns" for lack of a better word. The DBA only gives me SELECT and EXECUTE permissions on the database. How am I going to work here without Stored Procedures?
And while this sounds weird, these limited permissions are to protect me from accidentally writing to wrong columns. Wrong columns that could impact systems, I didn't even know about. Is it possible to write to the same columns with a Stored Procedure? Of course! But it's not as dangerous as giving an application fat UPDATE permissions.
By using SQL Views I can build a data model upon these tables for reading the data, and build a more consumable data model. And you mentioned C#: I can use EF Core to query these Views with LINQ. Stored Procedures are used to store data to multiple tables in a "legacy database" within a transaction.
This could also be done with EF Core, but I need to explicitly log all changes to the data. How should this be done without a Stored Procedure or Triggers? CDC doesn't help a lot here.
Perhaps, if the C# applications are the only ones accessing the database. But suppose multiple applications written in different languages (and for different purposes) need the database access? You can do this via stored procs or (better in my experience) by adding an intermediate server process which the applications use, via a publicly documented API, and which then talks to the the database which may provide stored proc access to the intermediate server.
Equally anecdotally:
An example is a server me and a couple of colleagues worked on at a big investment bank. The underlying databases were mostly proprietary from suppliers or written by other teams in the bank. And versioned (badly, eek!). We wrote the SQL queries/procs to access the data that the VB (now C# I guess) would not have been capable of (without enormous waste of effort), and then provided COM interfaces to our server that ran our queries. Mercifully, we didn't have to provide updates, but the same idea would have applied. This gives you more or less complete isolation between app and database.
Anyway, SPs can certainly fit into such an architecture, but might not be first (or second) choice.
I don't see how that is materially different from what the OP said, you're both advocating for a single server in front of the database. We can call it intermediate or not, but the only difference is whether the API is public, so it makes sense to keep business logic in the app code.
Without wishing this to sound like a personal attack, YUCK
A database that's accessed by multiple applications, regardless of the number of languages, is a really bad smell.
If I have a user db, and hundreds of other applications want to make use of the data in it, they come through MY application. I own the db, I decide what's in it, how the data is shaped, and when it changes.
Multiple applications accessing a single database is asking for a bureaucratic nightmare - "App A wants to change the first name field to UTF-8" - EVERY other user, of that database needs to be informed (best of luck figuring out what other apps are, and who owns them). If you are tempted to say "Those other apps should support UTF-8 as well" then that's the sound of someone that's never had legacy code in the environment.
For some reason stored procedures and automated testing seem to often not go together, maybe because they're often accompanied by an environment where DBAs and software developers are separate teams with separate sets of skills?
- Stored procedures are deployed separately from the code that calls them: one is a db migration, one is a k8s container deploy. And that binding isn’t strongly-typed, so there’s more thinking and double checking involved during deployment (and, heaven forbid, rollback).
- The SQL procedures duplicated some business logic and constants that are defined in some of our go core libraries. It felt good to remove that duplication.
- The procedures also had a few specific return codes. We needed to define some constants (like postgres itself does) that the go code could detect and translate into go errors. That’s more complexity.
- Maybe there’s a good SQL IDE out there, but I don’t have it, so I was writing these things in vscode with syntax highlighting. Plenty of errors that would get an immediate underline in go instead required 20 seconds to compile and run the test and interpret the error message.
- This lack of tooling, and the fact that I’m not great at SQL (I’m fairly good with queries, but not with stuff like variables and IF EXISTS that you only see in stored procedures), made it hard to use all the complexity-cutting techniques I’m used to applying in go and other languages. So the 100 line stored procedure stayed 100 lines, and everyone hated working on it.
Stored procedures are basically a cross-service API call, with all the complexities that entails. I won’t reach for them again unless there’s a technical problem that can’t be solved any other way. We don’t have a dedicated DBA so Conway’s Law doesn’t factor in.
(Aside: I think this is something PGlite helps with if your in Postgres land)
Then one java dev found a glaring sql injection hole. The whole app quickly reorganized around using it as an API for everything. Management was pleased speed went up and bugs went down, DBAs were pleased for less work, and Java devs were pleased they could understand what was going on in the app. Everybody happy I guess?
However the flip side of the coin in your case is somehow even worse, because people failed to identify the good in the RDBMS and only identified it all with evil, despite then storing their blobs in an RDBMS.
To me, the use of distributed transactions is the smell. If your solution to the problem of a multi-database model is a sticking plaster that can generate even worse problems (e.g. the deadlocks) then it just shows a poor approach to problem solving or allocating enough resource to development.
But for processing (and validating) various data loads and incoming data streams, by looking up matching values in existing reference tables, stored procedures can increase performance/efficiency and reduce complexity (of having to maintain connection pools and caching layers just for validating incoming record batches).
As far as the unwillingness to even evaluate stored procedures issue goes, I sometimes joke that maybe the DB vendors should start calling stored procedures as “database lambdas” instead…
Performing operations on data directly in the SQL provider is the peak of human enlightenment. It takes a lot of leadership or wisdom to push a modern team away from using crap like EF to process everything, but 100x slower.
In exactly 0% of cases of cycling records through code will you see a higher performance result than executing a single T-SQL or PL/SQL script against the provider.
The procedural SQL languages are Turing complete. SQL itself is as of recursive common table expressions. There's not any reasonable argument for not trying this if all the information is already in the SQL store.
Moving information is way more expensive than processing information that is already in place (cache). Your SQL server process can iterate on items in L1 millions of times before a packet makes it across the data center one time.
Now, give me ephemeral, per-connection procedures (call them unstored procedures for fun) that I can write in the language I want but that run on provider side, sure I’ll happily use them.
Perhaps I've been lucky or I haven't been observant enough, but I've never seen a company suffer financially because of inefficient code. Don't get me wrong, I still value good code for its own sake, but in my experience there is no correlation between that and profits.
> 1. Many critical APIs call would spend most of its CPU time inside the finite database servers. New APIs, or updating existing APIs meant you were hogging the DB/CPU time from every one else.
It's not that easy for a stored procedure to spend CPU on anything other than data processing. If you are going to do that data processing, then it's going to cost this amount of CPU regardless of whether it is initiate internally (through a stored procedure) or externally (by the client submitting queries). Furthermore, a frequently-used stored procedure's plan is likely already cached, so it doesn't have to be re-parsed and re-planned on each call. On the other hand, when the client submits the text of the query, that text has to at least be hashed, so it can be compared to the hashes in the query plan cache (even if the plan is already in the cache). So if anything, client-submitted queries will consume at least as much CPU as stored procedures, if not more.
> 2. API latency was at the mercy of the Query Planner. Any slight change in data statistics or business logic would cause the a different Query Plan to be picked, which drastically changed the execution time, and usually caused timeouts. A hash join suddenly became a full table scan across billions of rows without any warning.
This is also true for client-submitted queries. Basically the same techniques are useful for both stored procedures an client-submitted queries: query hints and plan guides, and above all: sound indexing. That being said, this is a big problem in practice and, I think, a fundamental problem in the design of SQL as "declarative" language where you are not supposed to concern yourself with the physical access path, even though it could make a big difference in performance. Fortunately SQL Server has mitigations for this problem (hints and plan guides), but not all DBMSes do (PostgreSQL still doesn't support hints natively, if I'm not mistaken).
The problems arise when processing data row-by-row using cursors or simular procedural approach, or with too much buisness logic in procedual form.
I guess the moral of the story is: use the right tool for the job instead of deciding on the “one true pattern”.
There were one or two outright failures (new code abandoned) but the more subtle ones were just efforts that took so long to deliver value that the whole market opportunity was lost.
In every single case it was possible to take the existing system and gradually morph it towards something better - but the desire for revolution prevented evolution and forced everyone to live with shit in the hope of a future that didn't arrive.
>Logically, that was… fine. In practice, writes into the same database which previously required 5 IO round trips, now required almost double the number of trips for the extra checkpointing operations...
So this sounds like trying to invent a form of optimistic locking, but it's definitely possible to do optimistic locking where no extra reads or writes are necessary unless there is a conflict. you have to write that value (whether timestamp or uuid) on every write, and then you do every write with a conditional "do this write as long as the lock value has not been changed."
But I guess the store they were using didn't allow even a conditional write like that? Although I'm not sure how they managed to make this "checkpoint" system work without that feature either... this seems a bit confusing, it seems like a more typical optimistic locking system should have been possible using whatever primitives the checkpointing system used? Maybe not?
Storing the checkpoints along with the mutation was for idempotency. If the checkpoint was in the document, that meant the mutation had succeeded and a retry should be no-op
Doesn't the KV provide idempotency on it's own -- so long as you're checking that no changes have happened between read and write, why wouldn't doing the same write twice produce an idempotent result? A change happenening between read and write seems the only reason that would be a problem.
But clearly it's complicated and we don't have the whole picture as to business needs. Definitely sounds awful.
Very nice to see the other side of this
I feel this is a very political phenomenon that is very poignant in democracy, both Argentina and the US have elected executives that promise to chainsaw most of the government bureocracy for cost and streamlining reasons.
There's a chapter of south park where the kids buy a roller coaster park and make it their own way with their own rules, and slowly they start to face the challenges of running the whole operation, they start by hiring a security guard, and then the security guard needs food so they hire some fast food chain and then the bathrooms and etcetera, by the end of the chapter the park is fully operational and as good as always.
I used to be very libertarian and revolutionary too when I was younger, then I tried to revolutionize a codebase, ended up breaking stuff, and when correcting I ended up with the traditional architecture.
I don't doubt that driving a plow over the traditions of the previous generation is necessary, but in my experience there is always more wisdom in what we throw away than what we innovate, so I guess I'm a conservative
I find a lot of programmers don't know how to write an array of floats to disk, if you forced me to choose between an ORM or No DB at all, I would choose no DB all day.
ORM feels like an abstraction on top of an abstraction. I don't trust that those who chose ORMs have studied and exhausted the possibilities of the two underlying layers, I feel more often than not they recourse to higher layers of technology without understanding the lower level.
But I may be wrong, maybe those who use ORMs know all about File systems and Posix and SQL itself.
It also reminds me of the people who ask chatgpt to write a prompt for them, it's as if you had a dishwasher and you were too lazy to load the dishes onto the dishwasher, so you buy a product that loads the dishes to the dishwasher.
I agree with that. However I feel that teams that choose not to use an ORM end up having one somehow reimplemeted by the seniors, and just used as you describe by the juniors.
I'd rather have the seniors master an existing ORM and spend their time elsewhere.
what does that have to do with it?
One of the things I appreciate about GraphQL is that it doesn’t even offer a way to ‘SELECT *’
You’re usually better off starting with a good standard ORM at the beginning.
Unless you're doing stock trades or black Friday sales, though, it can be pretty hard to pin down a specific inefficiency to a concrete measure of list income. Instead, people move on from products for general "we don't like it" vibes.
The most concrete measure, as someone else pointed out, is heavily inflated PAAS spend caused by poorly written code that requires beefier than necessary servers. In theory, moving faster means you're spending less money on developer salaries (the Ruby/rails mantra of old) but there's a distinct tipping point where you have to pony up and invest in performance improvements.
They made so much money but would then squander it on hopelessly inefficient designs that required an AWS spend that basically prevented them from ever financially scaling.
The worst is when the performance is so bad it starts to prevent onboarding new features or customers.
Well, how many companies that went bankrupt because of inefficient code have you looked at? ;) It's hard to see what no longer exists. See Survivorship Bias [1].
Also fun: "More than 10 years ago, Amazon found that every 100ms of latency cost them 1% in sales. In 2006, Google found an extra .5 seconds in search page generation time dropped traffic by 20%. A broker could lose $4 million in revenues per millisecond if their electronic trading platform is 5 milliseconds behind the competition." [2]
[1]: https://en.wikipedia.org/wiki/Survivorship_bias
[2]: https://www.gigaspaces.com/blog/amazon-found-every-100ms-of-...
But generally I would agree
2. Having the business logic (not just loading the data) inside the stored procs meant that a change in business logic that would normally only need to update application code, now invalidates the stored procedure's cached execution plan.
Interesting. Can you share more details about the "non-data" processing that was done? Were they doing heavy mathematical calculations and such?
> change in business logic that would normally only need to update application code, now invalidates the stored procedure's cached execution plan
As for plan cache invalidation - the most extreme case I saw was on the order of 5-10s. Basically, it depends on the size of that one stored procedure - not on all other stored procedures that may call it or be called by it. What was the actual time that they got?
The trick is to engage with the customer early, directly and often. If the customer isn't willing to pay more money for something, you should probably not do it.
I think it can be extremely good to rewrite small things - you always know how to do it better after you've done it once.
I often write code with full intention of replacing it, generally by writing it in such a way it's replacement is forced. That latter bit is pretty important because systems generate inertia, you need to have some hard forcing function baked into prevent it ossifying.
i.e my current project is to replace a pretty awful pile of Bash I wrote last year and replace it with a much more sophisticated and automated system in Golang. The new system is already in production and the old tools are being phased out.
Writing the original in Bash was how I basically ensured it would get rewritten and not continually upgraded/morphed into something grotesque. There was a ton of pushback, lots of people saying "why not Go! why not Python! This is an awful decision, 3k LOC of Bash is an unmaintainable nightmare!". Literally all of those people missed the point.
Building simple systems that you 100% will replace (not intend, that really isn't the same thing) is a really good pattern to find a real understanding of the solution space before building something gold plated/spaceship-like.
It generally means once you implement the follow up it very rarely becomes "legacy" software without horrible mismanagement, i.e bringing in new management that wants to rewrite things for the sake of rewriting them, usually in some FoTM nonsense.
As for writing code to be replaced - well I can understand doing it but generally I've been in companies where you never get a chance to go back to anything that doesn't seem to be critical to the next requirement from the business - and they always have more than you can do.
The big rewrites are almost a response to this - no improvement can be justified unless it enables so many new features that the business decides to sign off. But because it's such a big change it is also much much more risky.
i have found this to be true throughout my career. not that things cannot improve, they can always improve. but assuming the original work wasn't grossly incompetent, anything "new" tend to make the same [undocumented] mistakes
Not checked their situation much, but their current ideas were clearly not sustainable given repeated defaults. And general bad situation of population.
I mean that in a way I think it's more important to have a team that understands the problem domain fully than to have the code itself. Obviously the business might not think so but as a software process, the costly thing to do IMO is get a group of people to a point of common understanding where they can work productively.
When your developers didn't write the code they're unlikely to understand all the reasons why it is the way it is. How do you get them trained up to that level again? One way is a rewrite. The end result may be no better or even worse but now you have people who know the system, the business and they can look after it efficiently....until they all leave and you start again.
This is my devil's advocate answer to my own feeling that one should never rewrite.
50kloc made by chatgpt in 1 day of vibe coding =/= spending 3 months coding =/= npm install solution
Even if the code and product end up exactly the same, your capacity to improve the product and talk about the business are not.
I find similarities in physical supply chains and sourcing, while sand and cement may be fungible, you don't want to buy sand that was stolen from a beach, or cement made from an uneducated manufacturer that doesn't follow regulations. Even if the product is the same.
They don't have to. The procedural SQL commands can be source controlled along with the rest of the codebase. Transmitting the actual command text to the SQL server that does all the work is not the inefficient part.
[0] https://en.wikipedia.org/wiki/List_of_unit_testing_framework...
I still mostly use Django for PostgreSQL projects and the Django default test framework is great at running a temporary PostgreSQL (or MySQL or SQLite).
In the company I work for we use real PostgreSQL in unit tests — it's cheap to start one at the beginning of a suite, load the schema and go, and then shut it down and discard its file store.
I keep thinking of moving that file store to tmpfs when run on Linux, but it's nowhere near the top of the performance improvements for the test suite.
So: no more mocks or subsitute databases with their tiny inconsistencies.
Admittedly I’m only talking about selects, inserts, updates, views, etc. not stored procedures. But having worked in codebases with far too many stored procedures in the past, I think there might be a marginal improvement.
For what it’s worth, I fully agree that the main problem with using store procedures is testability and debugability.
Except that really was the original model back in the 90's. All "good" databases had an extensive roles and permissions system, and a point of pride was the number of ODBC connectors for different languages and environments.
You were supposed to have The Database, looked after and controlled by the hallowed DBAs, who had their own hardware budget, their own organization, and who controlled access to The Database, giving trusted applications access to it, but only after they had vetted the schema and queries that those dirty developers wanted to run against it. Trusted users could get SELECT access, but only to the tables they needed to run their custom reports and queries.
It was a whole ass thing that's fallen completely to the wayside as database software went from underpinning Larry's latest mega-yacht, to free as in beer, and we learned how to clone and shard the data instead.
If you squint at it wrong, it looks like some kind of bizarre and inhumane jobs program.
Meanwhile, transactions remain forgotten lore. Simply not achievable at our current tech level.
Read my files, fine. I always try to avoid breaking changes anyway. Just don't write them!!
you can imagine this:
```
var thing = KVStore.Get<MyThing>(...);
if (things.Checkpoints.Contains(myUuid) == false) {
thing.Counter += 1;
KVStore.Update(thing); ```
having an etag doesn't help you with retries, where we expect that `thing` could be mutated by another flow between your retries.
i may be missing something though?
when you retry and load the object, you get {count: 12, etag: 3}. how do you know if your previous attempt had successfully persisted or not, or if the updates to the object came from other processes/requests?
you're mixing up conflict handling vs. idempotency
This Simpson's clip summarizes it in a more poetic style
https://www.youtube.com/watch?v=2BT7_owW2sU
I've seen a common problem for auto-didacts is that, since the advanced and modern concepts outnumber the fundamentals, they often find themselves learning advanced concepts before the basics.
This is especially common in programming with stackoverflow or AIs where the devs look for the quickest and easiest to use solution, pushing the code and complexity beneath the rug under the dependency layer, so that their implementing code looks nice and clean.
It's hard to figure out as a begginer that the simplest and most basic solution are 10 lines of POSIX function calls, instead of three lines of "import solution" "setup solution" "use solution".
Imagine having a huge amount of applications all using the same database, which is, like I said, often enough the main integration point in a company. The applications you are maintaining are written in, let’s say Java, C#, Python and C++. They are too important, too large and a rewrite is way too risky.
How would you start to get this under your control? By moving common logic in a shared C++ DLL probably? Rewrite all those applications at once, in a heroic effort? I for one take the pragmatic approach and extract common business logic shared between all them into a Stored Procedure.
The Stored Procedures are invoked by all applications, so instead of maintaining 10 implementations, I maintain 1. Suddenly you have a common data access path! The 1 implementation will be drowned in a shitload integration tests, because this is what really matters.
Of course, a Stored Procedure doesn’t prevent you from shooting yourself in the foot! But in my experience, it’s way harder to shoot yourself in the foot using a Stored Procedure, that you can inspect the Query Planner for… than hunting down Monster-Queries for weeks, generated by a LINQ provider.
As for the INSERT, UPDATE and DELETE: With SPs, you’ll have fine-grained control, which columns are updated instead of needing to expose all properties in your application code and praying for an ORM to not accidentally update “columns I don’t own”, because of some convention applied by the ORM.
If you are in the lucky position of owning the database and don’t have to maintain legacy applications, I see resistance to Stored Procedures. Then go ahead and build an API all applications are going to use.
But if you don’t own the database and need to maintain dozens of mission-critical applications, Stored Procedures are a tool worth knowing.
You're coming at this from the idea that one user has full access to everything when that is likely the biggest security lapse you'll find in modern apps that use databases: a Web app that has full ddl and even dml access when it shouldn't have.
bytefish's comment seems to be linking stored procedures and writing to columns, which doesn't make sense because the scenario doesn't have any write permissions.
Anyway, okay, I get that I don't get it, and I get that it does sound terrible, agreed!
Imagine this: you issued a write, a few things can happen: 1. The callsite crashed, maybe due to an out of memory issue or whatever. You don't know if it succeeded
2. The database returned an acknowledgement, but then the callsite crashed before storing the acknowlwedgement for the next step.
> Unless the entire end to end operation is wrapped inside a giant transaction, no system in the world can give you the confirmation.
But how do people use, say, redis, without an external "checkpoint" system, how do you do, say, an INCR operation, and know if it succeeded or not?
Or are most uses of redis actually dangerous and subject to these error conditions, perhaps it's tolerable to risk that error for most redis use cases, but wasn't for the system under discussion? Most developers using redis definitely aren't using external "checkpointing" systems, or considering if they should be or not -- should they be?
But of course you've convinced me that the system under discussion would have been better off using an rdbms, something I never doubted.
Where our conversation is at, it's not about RDBMS vs. NoSQL.
On the Redis INCR operation example (or an equivalent operation in any database, even in an RDBMS). In short, you don't always have the guarantee to know the result of the operation. This is part of what the 99.9% means when services advertise their reliability. 99.9% of the time you'll get an acknowledgement back, but not 100%.
The question comes to, what happens if I miss an INCR, or if I accidentally double INCR when I retry? For some use cases, it's acceptable: like counting the number of views on a Youtube video. For others, say a financial system counting money or making a payment, that's not OK. That's where idempotency really matters (btw this is why many redis operations have the NX option).
Let's go completely off track for a little bit, say you're Strava. You let devices and users upload workouts to your service. What happens when a phone sent you the workout, then disconnected due to bad receptions? In this case, the phone doesn't know if the operation succeeded so it's forced to retry. How do we make sure the workout isn't created and counted twice on Strava? Well, each workout has a unique UUID generated by the device. Strava uses this UUID to dedup when it receives an upload. The UUID is the idempotency "checkpoint".
In this case, it doesn't matter what kind of database Strava is using. The idempotency problem is the same.