3 ms·

Many scaling problems in databases are because the computation is not done in the database. Outside of exotic cases, good database engines are rarely computatio

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.

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.