7 ms·

Wrong ways to use the databases, when the pendulum swung too far

103 points by luuio 1y ago

kragen 1y ago
Not Dan Luu, is it? The site seems to leave that intentionally ambiguous.
- Tevo 1y ago
  Seems to be this[1] guy, which you can find out by skulking around an older snapshot of his website from the Wayback Machine, where he displayed his real name within the footer on every page, and went by a slightly different username up to seemingly as late as April this year. Maybe a rather poor attempt at dissociating his real identity from the website somehow? (on second thought, he still links to the webpage from his GitHub profile, so that wouldn't make much sense, I think. Hmm.) [1]: https://github.com/luuhq https://github.com/luuhq
- luuio 1y ago
  Not Dan Luu. And to the other comment, not intentionally hiding my identity. Just that for most of my writing, who I am is irrelevant.
  - kragen 1y ago
    I sometimes wonder what it would be like to be named something like Madonna Tucci or Madonna Bianchi and be born in the late 60s, slowly coming to the realization, as you grew up, that your name would never be fully yours again; that, if you were to write a book or perform in a play as just "Madonna", half the audience would assume you were that damned blonde from New York.
deleted 1y ago
[deleted]
deleted 1y ago
[deleted]
cl0ckt0wer 1y ago
Does the author really call joins "joints"? I thought it was maybe a spelling thing but then they make a Snoop joke...
- jrochkind1 1y ago
  Yeah, I wondered what was going on there. Maybe just a joke or error, but definitely makes one wonder how much experience the author has with sql to compare/contrast the approaches to a more standard approach.
- nemothekid 1y ago
  The author was implying that in between writing the business logic, they were smoking a lot of weed.
- deleted 1y ago
  [deleted]
- cjfd 1y ago
  Never try to smoke an inner joint.
gxs 1y ago
This was a good read, especially for me considering I’m always having to remind people that SQL has been around since the 80s and has been optimized for a number of tasks over a very long time, tasks that should live in the database vs in code Very nice to see the other side of this
jrochkind1 1y ago
> The checkpoint system worked like this: every time we needed to perform a write, we would generate a UUID, store this UUID into a “checkpoint” for the current flow.... >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?
- luuio 1y ago
  The KV store had etag support for conditional writes. Etags are only useful to make sure the data didn't change underneath between your read and your write. 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
  - jrochkind1 1y ago
    Hm, I misunderstood, I thought the checkpoint system was also concurrency control to make sure nobody else had changed it from underneath you between read and write, since you had to read and write the whole (mega) document even though you only wanted to change a part (sub-document). 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.
Quarrelsome 1y ago
Religion and engineering do not make good bedfellows. I got into a pointless argument with someone on LinkedIn who was trashing on ORMs, strawmanning them by stating they would all pull the entire data into memory to just perform a count (some ORMs are much more sophisticated than this). It was some sort of weird religious argument because the chap thought every single piece of data processing should be written in a stored procedure (much like your first example). 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.
- gfody 1y ago
  ORMs are basically the enemy of rdbms, by encouraging antipatterns and undermining language features they have easily done more harm than good. of course reasonable people can be trusted to use them reasonably but if you're arguing about it on the internet..
  - Quarrelsome 1y ago
    ye I think the guy I was arguing with was mostly a DBA so I get his perspective, but its just religious to be wholly on one or the other side of the argument.
    - winrid 1y ago
      Even as someone who is a DBA I say ORMs are great for most simple queries.
- scuff3d 1y ago
  I work for a company that's still obsessed with micro services. "Religious" is a pretty good way to describe it. Drives me absolutely nuts, especially when no one seems to understand what micro services are. I've seen at least half a dozen teams that have just created a distributed monolith and called it a micro service because they're using Docker and Kubernetes.
marc_abonce 1y ago
> The company is still standing and seems to be doing well financially, so I guess things turned out well enough, or maybe some of the technical decisions started trending more reasonable. 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.
- spockz 1y ago
  IME, the suffering of bad performing code is mostly secondary. It increases compute costs. Mostly because requiring more beefy VMs than strictly required which is still benign and possibly more cost-efficient than spending more engineering effort. Sometimes because of the lack of performance now more scaling and orchestration is required which comes additional complexity and therefore compute and staffing cost. This is rare to get noticed and fixed due to organisational momentum. The worst is when the performance is so bad it starts to prevent onboarding new features or customers.
  - sumanthvepa 1y ago
    The real cost is an opportunity cost. It doesn't show up in the financials. Your ability to react quickly to new business opportunities is hurt. Most CEOs and boards don't notice it, until it's too late.
- wongarsu 1y ago
  I bet Atlassian could make even more money with Jira if it wasn't this slow. They are not struggling as it is, but it's bad enough that it is costing them customers But generally I would agree
  - hnaccount_rng 1y ago
    Do you have any source for the "they are loosing customers"? I always thought, that they consciously decided to have that shit of an interface because no one relevant to them (i.e. purchasing departments) cares?
branko_d 1y ago
I never quite understood these kinds of arguments, event though they seem to be repeated a lot: > 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).
- luuio 1y ago
  1. Beyond just querying, the stored proc spent a lot of time processing data. As in, looping through cursors, making business logic decisions, calculating things, etc. 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.
  - 1y ago
freetime2 1y ago
Anecdotally, the worst codebase I ever worked on made heavy use of stored procedures. Over the years people couldn’t be bothered or were afraid to update them - which really was the root of the problem. This led to all kinds of crazy patterns in the application code like calling things in a loop where a bulk operation was needed. Or stringing together several stored procedure calls to get the desired outcome, when really a refactor was need to combine or decompose them as needs evolved. 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.
- simonw 1y ago
  I'm convinced that the solution to stored procedures that people are afraid to update is automated tests. Write unit tests for them just like you would any other complex logic - the tests can evolve over time into a detailed spec for how they should work, and give you the ability to refactor or improve them with confidence later on. 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?
  - samwillis 1y ago
    I think a lot of the problems come from the fact testing stored procedures ventures into e2e testing land. You have to stand up infra in order to test them. There's not really been a simple way to unit test stored procedures as part of your application codes testing framework. (Aside: I think this is something PGlite helps with if your in Postgres land)
    - dottedmag 1y ago
      PostgreSQL server is a single process that starts in under 100ms on a developer's laptop. 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.
TZubiri 1y ago
"They wanted everything to be written into simple primitive Key-Value stores for their new design." 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
- luuio 1y ago
  > there is always more wisdom in what we throw away than what we innovate 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
  - TZubiri 1y ago
    An interesting mathematical caveat is that regardless of whether the majority of the work thrown out is correct, throwing the baby with the bathwater may still be optimal, as it might be the only way to affect change. It's not like decreasing the rate of change would decrease the amount of false positives discarded.
- matkoniecz 1y ago
  Note that Argentine definitely needed some change in course given their situation. Not checked their situation much, but their current ideas were clearly not sustainable given repeated defaults. And general bad situation of population.
bazoom42 1y ago
Stored procedures are actually great when used for bulk set-oriented data operations. Much simpler and more efficient than passing large datasets back and forth between servers. 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”.
- jajko 1y ago
  The problem I've seen, very few people from Java / C# / other languages actually know plsql or ever bothered to learn it. Its not that hard, but I guess not a cool enough tech to have on resume? When it could massively help with speed of certain use cases, in ways no external code could ever dream of achieving.
tanelpoder 1y ago
The reluctance of using stored procedures where they’d be valuable is also a skill + will issue. I do get the non-database-developer view that if 98% of your app is gonna be written in some other language anyway, why complicate your app layers by adding a bit of PL/SQL code + testing infra here and there. 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…
- bob1029 1y ago
  > 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 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.
  - slooonz 1y ago
    Operational concerns trumps raw performances most of the time. Stored procedures live in a different CI/CD environment, with a different testing framework (if there’s even one), on a different deployment lifecycle, using a different language than my main code. It is also essentially an un-pinnable dependency. Too much pain for the gain. 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.
t43562 1y ago
So far in my career, such as it is, I have been on lots of rewrite projects and not one of them was a good idea. 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.
- deleted 1y ago
  [deleted]
- jpgvm 1y ago
  It really depends on the origin of the system to be replaced. If it was never designed to be replaceable you are usually in for a bad time. Similarly if the drive to replace it comes from new folk that simply don't understand the original system you are probably completely doomed. 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.
  - t43562 1y ago
zupa-hu 1y ago
Is it just me? No conclusion, no takeaway, nothing.
m_ransing 1y ago
Nowdays we can have json APIs in relational databases (at least in Oracle and SQLServer). These APIs allows reading and writing of single value in nested level too. I think at least check-points could have been avoided.

103 points by luuio 1y ago

kragen 1y ago

Not Dan Luu, is it? The site seems to leave that intentionally ambiguous.

Tevo 1y ago
Seems to be this[1] guy, which you can find out by skulking around an older snapshot of his website from the Wayback Machine, where he displayed his real name within the footer on every page, and went by a slightly different username up to seemingly as late as April this year. Maybe a rather poor attempt at dissociating his real identity from the website somehow? (on second thought, he still links to the webpage from his GitHub profile, so that wouldn't make much sense, I think. Hmm.) [1]: https://github.com/luuhq https://github.com/luuhq
luuio 1y ago
Not Dan Luu. And to the other comment, not intentionally hiding my identity. Just that for most of my writing, who I am is irrelevant.
- kragen 1y ago
  I sometimes wonder what it would be like to be named something like Madonna Tucci or Madonna Bianchi and be born in the late 60s, slowly coming to the realization, as you grew up, that your name would never be fully yours again; that, if you were to write a book or perform in a play as just "Madonna", half the audience would assume you were that damned blonde from New York.

deleted 1y ago

[deleted]

deleted 1y ago

[deleted]

cl0ckt0wer 1y ago

Does the author really call joins "joints"? I thought it was maybe a spelling thing but then they make a Snoop joke...

jrochkind1 1y ago
Yeah, I wondered what was going on there. Maybe just a joke or error, but definitely makes one wonder how much experience the author has with sql to compare/contrast the approaches to a more standard approach.
nemothekid 1y ago
The author was implying that in between writing the business logic, they were smoking a lot of weed.
deleted 1y ago
[deleted]
cjfd 1y ago
Never try to smoke an inner joint.

gxs 1y ago

This was a good read, especially for me considering I’m always having to remind people that SQL has been around since the 80s and has been optimized for a number of tasks over a very long time, tasks that should live in the database vs in code Very nice to see the other side of this

jrochkind1 1y ago

> The checkpoint system worked like this: every time we needed to perform a write, we would generate a UUID, store this UUID into a “checkpoint” for the current flow.... >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?

luuio 1y ago
The KV store had etag support for conditional writes. Etags are only useful to make sure the data didn't change underneath between your read and your write. 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
- jrochkind1 1y ago
  Hm, I misunderstood, I thought the checkpoint system was also concurrency control to make sure nobody else had changed it from underneath you between read and write, since you had to read and write the whole (mega) document even though you only wanted to change a part (sub-document). 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.

Quarrelsome 1y ago

Religion and engineering do not make good bedfellows. I got into a pointless argument with someone on LinkedIn who was trashing on ORMs, strawmanning them by stating they would all pull the entire data into memory to just perform a count (some ORMs are much more sophisticated than this). It was some sort of weird religious argument because the chap thought every single piece of data processing should be written in a stored procedure (much like your first example). 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.

gfody 1y ago
ORMs are basically the enemy of rdbms, by encouraging antipatterns and undermining language features they have easily done more harm than good. of course reasonable people can be trusted to use them reasonably but if you're arguing about it on the internet..
- Quarrelsome 1y ago
  ye I think the guy I was arguing with was mostly a DBA so I get his perspective, but its just religious to be wholly on one or the other side of the argument.
  - winrid 1y ago
    Even as someone who is a DBA I say ORMs are great for most simple queries.
scuff3d 1y ago
I work for a company that's still obsessed with micro services. "Religious" is a pretty good way to describe it. Drives me absolutely nuts, especially when no one seems to understand what micro services are. I've seen at least half a dozen teams that have just created a distributed monolith and called it a micro service because they're using Docker and Kubernetes.

marc_abonce 1y ago

> The company is still standing and seems to be doing well financially, so I guess things turned out well enough, or maybe some of the technical decisions started trending more reasonable. 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.

spockz 1y ago
IME, the suffering of bad performing code is mostly secondary. It increases compute costs. Mostly because requiring more beefy VMs than strictly required which is still benign and possibly more cost-efficient than spending more engineering effort. Sometimes because of the lack of performance now more scaling and orchestration is required which comes additional complexity and therefore compute and staffing cost. This is rare to get noticed and fixed due to organisational momentum. The worst is when the performance is so bad it starts to prevent onboarding new features or customers.
- sumanthvepa 1y ago
  The real cost is an opportunity cost. It doesn't show up in the financials. Your ability to react quickly to new business opportunities is hurt. Most CEOs and boards don't notice it, until it's too late.
wongarsu 1y ago
I bet Atlassian could make even more money with Jira if it wasn't this slow. They are not struggling as it is, but it's bad enough that it is costing them customers But generally I would agree
- hnaccount_rng 1y ago
  Do you have any source for the "they are loosing customers"? I always thought, that they consciously decided to have that shit of an interface because no one relevant to them (i.e. purchasing departments) cares?

branko_d 1y ago

I never quite understood these kinds of arguments, event though they seem to be repeated a lot: > 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).

luuio 1y ago
1. Beyond just querying, the stored proc spent a lot of time processing data. As in, looping through cursors, making business logic decisions, calculating things, etc. 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.
- 1y ago

freetime2 1y ago

Anecdotally, the worst codebase I ever worked on made heavy use of stored procedures. Over the years people couldn’t be bothered or were afraid to update them - which really was the root of the problem. This led to all kinds of crazy patterns in the application code like calling things in a loop where a bulk operation was needed. Or stringing together several stored procedure calls to get the desired outcome, when really a refactor was need to combine or decompose them as needs evolved. 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.

simonw 1y ago
I'm convinced that the solution to stored procedures that people are afraid to update is automated tests. Write unit tests for them just like you would any other complex logic - the tests can evolve over time into a detailed spec for how they should work, and give you the ability to refactor or improve them with confidence later on. 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?
- samwillis 1y ago
  I think a lot of the problems come from the fact testing stored procedures ventures into e2e testing land. You have to stand up infra in order to test them. There's not really been a simple way to unit test stored procedures as part of your application codes testing framework. (Aside: I think this is something PGlite helps with if your in Postgres land)
  - dottedmag 1y ago
    PostgreSQL server is a single process that starts in under 100ms on a developer's laptop. 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.

TZubiri 1y ago

"They wanted everything to be written into simple primitive Key-Value stores for their new design." 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

luuio 1y ago
> there is always more wisdom in what we throw away than what we innovate 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
- TZubiri 1y ago
  An interesting mathematical caveat is that regardless of whether the majority of the work thrown out is correct, throwing the baby with the bathwater may still be optimal, as it might be the only way to affect change. It's not like decreasing the rate of change would decrease the amount of false positives discarded.
matkoniecz 1y ago
Note that Argentine definitely needed some change in course given their situation. Not checked their situation much, but their current ideas were clearly not sustainable given repeated defaults. And general bad situation of population.

bazoom42 1y ago

Stored procedures are actually great when used for bulk set-oriented data operations. Much simpler and more efficient than passing large datasets back and forth between servers. 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”.

jajko 1y ago
The problem I've seen, very few people from Java / C# / other languages actually know plsql or ever bothered to learn it. Its not that hard, but I guess not a cool enough tech to have on resume? When it could massively help with speed of certain use cases, in ways no external code could ever dream of achieving.

tanelpoder 1y ago

The reluctance of using stored procedures where they’d be valuable is also a skill + will issue. I do get the non-database-developer view that if 98% of your app is gonna be written in some other language anyway, why complicate your app layers by adding a bit of PL/SQL code + testing infra here and there. 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…

bob1029 1y ago
> 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 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.
- slooonz 1y ago
  Operational concerns trumps raw performances most of the time. Stored procedures live in a different CI/CD environment, with a different testing framework (if there’s even one), on a different deployment lifecycle, using a different language than my main code. It is also essentially an un-pinnable dependency. Too much pain for the gain. 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.

t43562 1y ago

So far in my career, such as it is, I have been on lots of rewrite projects and not one of them was a good idea. 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.

deleted 1y ago
[deleted]
jpgvm 1y ago
It really depends on the origin of the system to be replaced. If it was never designed to be replaceable you are usually in for a bad time. Similarly if the drive to replace it comes from new folk that simply don't understand the original system you are probably completely doomed. 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.
- t43562 1y ago

zupa-hu 1y ago

Is it just me? No conclusion, no takeaway, nothing.

m_ransing 1y ago

Nowdays we can have json APIs in relational databases (at least in Oracle and SQLServer). These APIs allows reading and writing of single value in nested level too. I think at least check-points could have been avoided.