5 ms·

My saying has always been: be nice to the DB Don't use it anymore than you have to for your application. Other than network IO it's the slowest part of your s

My saying has always been: be nice to the DB Don't use it anymore than you have to for your application. Other than network IO it's the slowest part of your stack.

niels_bom 3y ago
Would you say it's slower than file IO too?
- alternatex 3y ago
  Kind of irrelevant since a DB provides some guarantees that a simple file does not by default.
  - samtheprogram 3y ago
    GP was responding to a comment comparing it to network IO in terms of bottlenecks in your application stack ...?
- Too 3y ago
  It’s not slow by itself. It’s a single point of bottleneck that will inevitably become slow as you cram everything into it.
  - dagss 3y ago
    ...but by trying to avoid the bottleneck and moving things to backend, you make things 10x worse resource wise for the DB. So it is not a easy tradeoff. Take any computation you can do in SQL like "select sum(..) ...". Should you do that in the database, or move each item over the network and sum them in the backend? Summing in the database uses a lot less resources FOR THE DB than the additional load the DB would get from "offloading" this to backend. More complex operations would typically also use 10x-100x less resources if you operate on sets and amortize the B-tree lookups over 1000 items. The answer is "it depends" and "understand what you are doing"; nothing about it is "inevitable". Trying to avoid computing in the DB is a nice way of thinking you maxed out the DB ...on 10% of what it should be capable of.
    - Too 3y ago
      Yes. Aggregations and search are often best done as close to the data as possible, in the DB. Rendering html, caching, parsing api responses, sending emails, background jobs: Nope. Basically, use the database for what it’s good at, no more.
- legohead 3y ago
  Well, it is file IO, plus processing on top. But it's not that simple, since if your data is small enough it can all be loaded into memory, allowing you to sidestep any file IO. But you still have the processing part...
cpursley 3y ago
Handling business logic in the database is often going to be an order of magnitude faster than the application layer of some of the popular language stacks (looking at you, Rails, Node, etc). It also will outlive whatever webstack of the day (and acquisition which of en requires a re-write of the application layer but keeps general database structure - been there done that).
- jgalentine007 3y ago
  Maybe faster... but I've met very few developers that are good DBAs (that understand procedures, cursors, permissions etc.) Database schema versioning / consistency is a whole other level of pain too.
  - dventimi 3y ago
    That sounds like a social problem, not a technical problem.

My saying has always been: be nice to the DB Don't use it anymore than you have to for your application. Other than network IO it's the slowest part of your stack.

niels_bom 3y ago

Would you say it's slower than file IO too?

alternatex 3y ago
Kind of irrelevant since a DB provides some guarantees that a simple file does not by default.
- samtheprogram 3y ago
  GP was responding to a comment comparing it to network IO in terms of bottlenecks in your application stack ...?
Too 3y ago
It’s not slow by itself. It’s a single point of bottleneck that will inevitably become slow as you cram everything into it.
- dagss 3y ago
  ...but by trying to avoid the bottleneck and moving things to backend, you make things 10x worse resource wise for the DB. So it is not a easy tradeoff. Take any computation you can do in SQL like "select sum(..) ...". Should you do that in the database, or move each item over the network and sum them in the backend? Summing in the database uses a lot less resources FOR THE DB than the additional load the DB would get from "offloading" this to backend. More complex operations would typically also use 10x-100x less resources if you operate on sets and amortize the B-tree lookups over 1000 items. The answer is "it depends" and "understand what you are doing"; nothing about it is "inevitable". Trying to avoid computing in the DB is a nice way of thinking you maxed out the DB ...on 10% of what it should be capable of.
  - Too 3y ago
    Yes. Aggregations and search are often best done as close to the data as possible, in the DB. Rendering html, caching, parsing api responses, sending emails, background jobs: Nope. Basically, use the database for what it’s good at, no more.
legohead 3y ago
Well, it is file IO, plus processing on top. But it's not that simple, since if your data is small enough it can all be loaded into memory, allowing you to sidestep any file IO. But you still have the processing part...

cpursley 3y ago

Handling business logic in the database is often going to be an order of magnitude faster than the application layer of some of the popular language stacks (looking at you, Rails, Node, etc). It also will outlive whatever webstack of the day (and acquisition which of en requires a re-write of the application layer but keeps general database structure - been there done that).

jgalentine007 3y ago
Maybe faster... but I've met very few developers that are good DBAs (that understand procedures, cursors, permissions etc.) Database schema versioning / consistency is a whole other level of pain too.
- dventimi 3y ago
  That sounds like a social problem, not a technical problem.