Hacker News Reader

Comment by kevincox | Hacker News Reader

The primary issue I have seen with stored procedures is how you update them. I would be curious how they manage that.

Generally when releasing new code you want to do a gradual release so that a bad release is mitigated. It would be possible by creating multiple functions during the migration and somehow dispatching between them in PostgREST but I would be interested to see what they do.

The other obvious concern is scaling which was only briefly mentioned. In general the database is the hardest component of a stack to scale, and if you start doing it do more of the computation you are just adding more load. Not to mention that you may have trouble scaling CPU+RAM+Disk separately with them all being on a single machine.

View on HN

Many scaling problems in databases are because the computation is not done in the database. Outside of exotic cases, good database engines are rarely computation-bound in a competently designed system. By pushing computation outside of the database, it tends to increase the consumption of database resources that actually are bottlenecks, typically effective bandwidth (both memory and I/O).

As a heuristic, moving computation to the data is almost always much more scalable and performant than moving the data to the computation. The root cause of most scalability issues is excessive and unnecessary data motion.

That's not been my experience at all. Quite the opposite.

I actually can't remember a single time in 15 years where I've ever seen that. Maybe it's happened, but I don't remember, but I can easily recount tons of poorly performing SQL queries though. Sub-selects, too many joins, missing indexes, dead-locks, missing foreign keys...

I'm not saying it wouldn't cause a problem, I'm saying I've never, ever seen production code where someone dumped tons of data out and then processed it.

I have seen people try and make extremely complex ORM calls that were fixed by hand-coded SQL, but that's a different problem.

Profound comment. Should be top of mind whenever programmers think they need a cache.

We've just seen a lower-level example of this principle in action, with Apple's M1 processor and its Unified Memory Architecture.

That's only works for sql optimized case / queries, aggregation with indexed keys is one of them. However there are cases where application level is more optimized, such as cases where more complex data type is required, or array processing. Furthermore sql logic cannot be easily encapsulated and reuseable, which leaves you with many duplication without proper planning.

> The primary issue I have seen with stored procedures is how you update them. I would be curious how they manage that.

1. You put your stored procedures in git.

2. You write tests for your stored procedures and have them run as part of your CI.

3. You put your stored procedures in separate schema(s) and deploy them by dropping and recreating the schema(s). You never log into the server and change things by hand.

Wouldn't that lose all the cached query plans every deploy? Could be wrong, bit rusty on that stuff now, been a while since I worked on something where we had to worry about that.

We have a db schema update tool. We generate an XML file representing the schema which is committed to source control.

Then when new version is being deployed, the tool compares the database with the XML, and generates SQL to alter the database.

For stored procs etc it compares the text of them, so those that weren't changed are ignored.

It's a simple homebrew tool but it gets the job done.

rebuilding query plan takes less than 50ms and is routinely done by the engine itself without you ever realizing it.

what you probably wanted to mention is table statistics - but they are cleared only if you truncate your table, but then again - once you populate your table - the engine will recalc statistics by itself.

overall RDBMS does a lot of stuff behind the scenes for you, and you should take advantage of it, and instead think about more important atuff - business logic, data modeling, schema evolution, etc

> Generally when releasing new code you want to do a gradual release so that a bad release is mitigated

Make each stored procedure not depend on any other procedure. If you change one procedure, make sure that every existing procedure can work with both the current and the future version. Once compatible, start upgrading procedures one by one. Add in monitoring and automatic checking of the values during deployment, and you have yourself a system that can safely roll out changes slowly.

I'm not advising anyone to do this, it sounds horribly inefficient to me, but it's probably possible with the right tooling.

I think read replicas scale easily, so if it’s views and such then there is no problem. If you have to compute stuff during writes, then it’s hard, but probably solved by normalizing the data.

Reply on news.ycombinator.com