4 ms·

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 wan

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.

capableweb 6y ago
> 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.
jandrewrogers 6y ago
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.
- mattmanser 6y ago
  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.
- tuatoru 6y ago
  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.
- fendy3002 6y ago
  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.
marmaduke 6y ago
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.
taffer 6y ago
> 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.
- mattmanser 6y ago
  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.
  - magicalhippo 6y ago
    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.
  - somurzakov 6y ago
    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

capableweb 6y ago

> 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.

jandrewrogers 6y ago

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.

mattmanser 6y ago
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.
tuatoru 6y ago
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.
fendy3002 6y ago
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.

marmaduke 6y ago

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.

taffer 6y ago

> 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.

mattmanser 6y ago
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.
- magicalhippo 6y ago
  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.
- somurzakov 6y ago
  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