7 ms·

> I always liked this way of working, but I also wonder why it never really took off. In today's new fangled world, a lot of developers don't use a lot of the

• dalyons 2y ago
  the approach in the article is for analytics / DW type use cases, not OTLP. So a dig about kids these days not understanding constraints is not particularly relevant.
• Arch-TK 2y ago
  I've heard people call the old approach "outdated" and even "wrong" before. It's honestly the dumbest situation, where people eagerly use extremely complex databases as if they were indexed KV stores. Completely ignoring about 97% of the features in the process. What's especially funny is that half the time a basic KV store would perform better given all the nonsense layered on top. And then there's this whole mentality of "we can't interface with this database unless we insert a translation layer which converts between relationships between sets of tuples and a graph structure". It's like people have unearthed an ancient technology and have no idea how it's intended to be used.
  • dalyons 2y ago
    Stored procs, triggers and suchlike actually are outdated and mostly wrong. Have you considered that people moved away from them for good reasons, not just ignorance? Hard to source control, impossible to debug, written in weird/bad languages, massive blast radius, don’t scale. Usability matters! I have written more TSQL than I would care to admit, and it’s a hell you couldn’t pay me enough to go back to. The “consistency must be enforced at all costs” just turned out not to be true. Worked at many places at moderate scale since my old dba days that don’t use fks. It just doesn’t actually matter, I can’t recall any serious bugs or outages related to dangling rows. Plus, you end up having multiple databases anyway for scale and organizational reasons so fks are useless in that environment anyway. On the other hand, I’m all for indexes and complex querying. not just k-v.
    • leoqa 2y ago
      I think you touched on the real reason: we separate services now and they all have different databases. We shifted up to the application layer not because of technical reasons but social reasons.
      • dalyons 2y ago
        Your timing is off, we as an industry ditched stored procs and friends before (micro)services became a trend. For the reasons I said. I’m not sure why it’s so hard to accept that we stopped using them because they were bad, not for some other gotcha reason (social, ignorance, fads, etc)
        • btreecat 2y ago
          Maybe because claiming they are bad with out explaining why, giving explicit examples, or really digging into the nuance of the more than one feature mentioned makes the claim feel unsubstantiated.
          • dalyons 2y ago
            Pretty sure I did say why. “ Hard to source control, impossible to debug, written in weird/bad languages, massive blast radius, don’t scale. Usability matters! ” and I forgot “generates non obvious non local changes” for triggers.
            • ako 2y ago
              What is so hard to source control with stored procedures, I’ve writing them in text files for over 30years, version controlled in rcs, cvs, svn and git. No problem, it’s just the deployment automation that works a bit different. Also, oracle plsql is way better than ms t-sql, it mostly feels like pascal/modula2 with embedded sql statements.
              • dalyons 2y ago
  • jacob019 2y ago
    Yep, that's me. I use sqlite as a key value store. Usually just two columns per table, a key and value, that I map to a python dict, and pickle the value if I need something more complex.
    • actionfromafar 2y ago
      Sqlite is obscenely performant as Key Value store, though.
• vosper 2y ago
  > In today's new fangled world, a lot of developers don't use a lot of the great stuff that RDBMS can provide - stored procedures, SQL constraints, even indexes. The modern mindset seems to be that that stuff belongs in the code layer, above the database. Implementing constraints in application code is certainly a lot easier (and easier to test) than in the database. What I want is a database which far stronger constraint capabilities than eg MySQL or Postgres provide, so that my application-level constraints can live in the database where they belong, without compromising ease of development and maintenance.
  • zie 2y ago
    In PostgreSQL you can write pretty much any constraint you wish: https://www.postgresql.org/docs/current/ddl-constraints.html https://www.postgresql.org/docs/current/ddl-constraints.html
  • mbreese 2y ago
    > Implementing constraints in application code is certainly a lot easier I can’t imagine doing this. You’re basically saying that you will only ever have one program writing to your database.
    • tomrod 2y ago
      That's the idea of microservices.
    • vineyardmike 2y ago
      This is, for many, considered a best practice. The idea being that a single service/codebase is controlling interaction with the database. Any read/writes should go through that service's APIs. Basically a "CreateFoo(a, b, c)" API is a better way to enforce how data is written/read vs every service writing their own queries.
      • mkleczek 2y ago
        It incurs huge costs though: 1. Costs of API maintenance - rigid APIs need constant change. To mitigate that people create APIs like GraphQL which is a reimplementation of SQL (ie. a query language). 2. Costs of data integration - (micro)systems owning databases need data synchronisation and duplication. What's worse - most of the times data synchronisation bypasses service APIs (using tools like CDC etc.) so all efforts to decouple services via APIs are moot. A single data platform (a database) with well governed structure enforced by a DBMS is a compelling alternative to microservices in this context.
• whizzter 2y ago
  People do still use indexes and the basic FK/UQ constraints because they're add value even when using ORM mappers,etc. I don't think that the ideas of changing databases are that prevalent any more (apart from perhaps unit-testing but since db's can often be dockerized that's also solved now). People don't hate stored-procedures (or other custom SQL constraints/triggers) per-se, but more the amount of headache they bring when it comes to debugging and keeping in sync with version control.
  • jasonkester 2y ago
    .sql files go into version control just as well as .java files. And because your build will be blowing all your stored procedures away and rebuilding them as part of the build, you'll know at compile time if Junior Dev Jimmy dropped a null constraint or renamed something but didn't fix one of the procs. That's way easier to debug than if Jimmy also had his query living as a string somewhere in your backend code. Honestly, if this stuff is hard, it's because you've made it hard. I can only assume most people griping about stored procedures don't have a /sql folder living next to their /python or /java folder in source control.
    • dalyons 2y ago
      Or… you just keep all your business logic in /java and call it a day. It’s stored procs that are making things harder.
    • whizzter 2y ago
      Keeping SQL updates as part of the build does sound intriguing but does it work flexibly, not something for a live DB i hope (so when is it updated?) and assumes local copies for each DB. What build system has good integration for doing these kinds of updates out of the box or does it require customization?
• banku_brougham 2y ago
  Stored procedures are awful. There, I said it. Its storing transform code in an inscrutible way, hiding pipeline components from downstream users. The rest of your alarms and dq has to be extendend to look into these... Oh an PL/SQL requires a huge context switch from whatever you were doing.
  • dalyons 2y ago
    It’s not a controversial take. They are awful, and thus haven't been used for new development for at least a decade, going on two.
    • mvc 2y ago
      I generate my pl/pgsql using Lisp. Which is another technology that has been dying for decades. The money it generates seems to work just as well as the money I earned writing js.
  • xarope 2y ago
    I'm a bit surprised about the flak that stored procs have. Treat them just like any other, hopefully well documented, API, and they work perfectly fine. There are pros and cons, just like whether you decide to use react or htmx, python or golang, erlang or lisp, row based database vs column based vs kv, etc. On the other hand, yes, going back to the days of poorly written and documented oracle pl/sql stored procs, yes, I shudder too, but then again, that can also be said of a number of public APIs that have been hacked/had side effects exposed.
• galkk 2y ago
  IMHO stored procedures are evil and they shouldn’t have place in modern development. They are old solution for client server world.
  • mbreese 2y ago
    Sometimes you need to have atomic updates for a function. Stored procedures aren’t inherently evil, but are a hold over from when DB admins were a thing and they didn’t trust app developers. Don’t worry, just wait another 5-10 years, they’ll be back in vogue again.
  • ako 2y ago
    I don’t get this mentality, they’re just code that lives closed to your data, and can do bulk processing on data in your database way more efficiently than anything living in the application layer.
    • FpUser 2y ago
      >"can do bulk processing on data in your database way more efficiently than anything living in the application layer." Not necessarily. All depends on particulars. Very complex processing of reasonably sized chunk of data especially when can be done in multiple threads / distributed can be way more efficient. I've head very real case: consulting company was hired by TELCO to write the code that will take content of the database and create one huge file containing invoices to clients. Said file would then be sent to a print house that can print the actual invoices, and mail those to clients. They tried to do it with stored procedures and had failed miserably - it would run for a couple of days give or take and then crash. I was called for help. Created 2 executables. On is a manager and the other is multithreaded calculator that did all the math. Calculator was put on 300 client care workstations as a service. Dispatcher would do initial query from accounts table for current bill cycle, split account id's into few arrays and send those arrays to calculators. Calculators would suck needed data in bulk from a database, do all the calculations and send the results back to dispatcher for merging into final file. TELCO people were shocked when they realized that they can have print file in less than 2 hours.
  • 2y ago
• victor106 2y ago
  > Sometimes it's justified as keeping the database vanilla, so that it can be swapped out. I understand if it's a prototype, I have seen developers of even mature products follow this mentality that they want the DB to be portable. It's insane. The reality is that rarely happens and when it does its mostly cause your business is growing. But not knowing (or caring to know) the features your DB offers you and not exploiting it is just laziness.
• LunaSea 2y ago
  The reason a lot of more advanced database features aren't used is simply because a lot (most?) web developers don't know them and have a very light understanding of databases in general.