Stored procedures often increase CPU load of the DB instances, deploys can be more challenging, and security is only better if one takes care how the procedure is authorized or delegated.
They can save some network round trips, and unify interfaces if there are a lot of different stacks sharing one data store. Though it's not universally better.
Stored procedures often increase CPU load of the DB instances
This is of course true if you're doing a bunch of computational work in your stored procedure (as opposed to just doing pure CRUD stuff) but I'm struggling to think of a real world situation where this might be the case.
Can you name some examples you've encountered? (I'm not doubting you, I'm just curious)
I did some complex expiration calculations in a stored function, it cut down on round trips and kept the app simpler. Though it did cause some modestly higher CPU load.
I've also seen MVs cause CPU spikes where there is a lot of SELECT massaging going on. Even without the MVs themselves certain functions like REGEXP stuff can impact performance. Language of the PL matters too, if you've got choices like Python.
Ah! Thanks. I'm not sure why my brain wasn't making the connection.
Materialized views are almost criminally underused. I feel most people don't know about or understand them. They can be a very effective "caching layer" for one thing. I have used them to great effect.
A lot of times I see people pulling things out of the database and caching them in Redis. When in fact a materialized view would accomplish the same thing with less effort.
> Materialized views are almost criminally underused. I feel most people don't know about or understand them. They can be a very effective "caching layer" for one thing. I have used them to great effect.
They are great if the frequency of changes isn't very high and the underlying tables are read-heavy and running it directly against the tables would be expensive (e.g. complex joins).
> A lot of times I see people pulling things out of the database and caching them in Redis. When in fact a materialized view would accomplish the same thing with less effort.
Typically Redis is used in medium to high volume of many smallish but repetitive queries where you want to store some key bits of information (not entire records at which point going to database might be simpler) that are sought often and you don't want to hit the database again and again for them - a bit different from the materialized view scenario.
They can save some network round trips, and unify interfaces if there are a lot of different stacks sharing one data store. Though it's not universally better.