> Is there any good reason to use stored procedures in 2026?
more unhinged HN energy disconnected from reality
more unhinged HN energy disconnected from reality
Timeline
Post
Remote status
Context
7
@feld you need to demonstrate serious competence and professionalism before you are entrusted with that power.
@feld anyway the argument against stored procedures was, one, you're probably doing it wrong and two, now you're locked into one vendor's system for life. that said I have implemented them before when I needed both the querying and custom aggregation
@sun@shitposter.world @feld@friedcheese.us how often you're transitioning between DB vendors anyways?
(also, since writing them is litteraly part of my job, the best use cases I'd say are either small ones for ensuring nore complex consistency terms of the database, or large ones for bulk data transformation and saving.
@ignaloidas @feld I have presided over at least ten different projects to migrate a company away from Oracle to PostgreSQL. In a previous job I left in 2010, almost everything was written using PL/SQL in Oracle. They are still using Oracle because it was impossible to rewrite them.
I am very against using stored procedures unless you can can bring a strong case for why your usage doesn't get us swamped for life on one platform.
I am very against using stored procedures unless you can can bring a strong case for why your usage doesn't get us swamped for life on one platform.
@sun@shitposter.world @feld@friedcheese.us ok, I understand it with oracle because of it's toxic licensing
But like, I've rewritten a whole bunch of stored procedures - e.g. from postgres to mssql and then back to postgres after some changes while it lived in mssql. Was fine.
And yes, if you put all of it in stored procedures, you're gonna have a bad time migrating (guess why oracle wants you to use them so much), but as I said, you should have either small ones for consistency, or large ones for large transformations/analytics. The first case is easy to rewrite because small, the second case is a pain either way because the alternative is just having all of the sql in some script or whatever and you're gonna have to rewrite it either way.
@ignaloidas @sun I once worked with a company that had 20 million lines of PL/SQL for Oracle
@feld@friedcheese.us @sun@shitposter.world ok thats bonkers and stupid
Both because it's on oracle, and because SQL is kinda ass for such code volume
Replies
2
@ignaloidas @feld you can serve entire pl/sql php-style pages directly out of the database. it is an abomination. it's deprecated now at least.
@sun @ignaloidas that's exactly what they built. A ridiculous super customizable CMS where the HTML, js, and CSS was dynamically being generated out of the database too. Thanks to Apache's mod_ora or whatever that made this possible.