3 ms·

As someone who does DB optimization for a living - there is a tradeoff, I've seen far too many places where db instances run with default settings out of the bo

As someone who does DB optimization for a living - there is a tradeoff, I've seen far too many places where db instances run with default settings out of the box, queries missing basic indexes, no one even cared to run an explain analyze. I've seen far too experienced developers with no basic understanding of how relational databases operate. And it's gotten worse over the years - as they can keep throwing hardware at the problem. Performance optimization has become a lost art. This is as much a problem of negligence as much as it is of education. I won't even touch on proper design, which requires someone with significant database expertise from the get-go ( which most startups may not have).

dkjaudyeqooe 3y ago
> Performance optimization has become a lost art. It's really not very hard, basically add indexes wherever lookups are done but don't go overboard, especially with tables that see a lot of inserts. The DB will tell you what it's doing if you ask, it's all easily investigated. If your joins resist optimisation your data structure is probably off. > I won't even touch on proper design, which requires someone with significant database expertise from the get-go In more extreme cases this is true, but a few basic principles and an understanding of databases will get you 95% of the way there and is quite intuitive. Something as basic as "don't copy data around, each individual entity exists in one place, and use its key to refer to it otherwise" will help enormously.
- alex7734 3y ago
  > It's really not very hard, basically add indexes It's really not but you'd be surprised how many people don't know the basics of how indexes work. I've had people tell me that their queries couldn't be optimized further because "they had already added an index for every column of the table". It doesn't help that SQL, by design, hides the actual algorithm doing the data access from the users while simultaneously relying on them to add indexes to achieve performance, which is in my humble opinion the worst mistake of SQL.
  - corytheboyd 3y ago
    I love to ask this very simple question in interviews: if a database index makes queries faster, why not add an index for every column on a table? I want them to say “the heck are you talking about, because that’s not how anything works?”… it does trip some people up though that just legitimately don’t understand what’s happening.
  - ako 3y ago
    That is not a SQL mistake, but an implementation choice. Most modern databases can determine where indexes should be added, and add these automatically. E.g.: https://www.oracle.com/news/connect/oracle-database-automatic-indexing.html https://www.oracle.com/news/connect/oracle-database-automati...
    - akoboldfrying 3y ago
      This is news to me. Can any other DBs do this? (I only skimmed -- far too much "Joan explains that" to signal ratio.)
      - ako 3y ago
        SQL server: https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/a... Interesting read on a project implementing this for Postgres: https://pganalyze.com/blog/automatic-indexing-system-postgres-pganalyze-indexing-engine https://pganalyze.com/blog/automatic-indexing-system-postgre...
    - sharadov 3y ago
      Not for postgres or mysql.
- sgarland 3y ago
  > The DB will tell you what it's doing if you ask, it's all easily investigated. It will tell you _what_ it's doing, but not _why_ it's doing it. Unraveling the mysteries of the query planner is a dark art that is still firmly in the realm of graybeards.
  - sharadov 3y ago
    Exactly and that's where it gets hairy and you need deep expertise.
- xarope 3y ago
  As a former DBA and optimization expert, the more appropriate response is NOT "add more indexes", but rather to profile and analyze your query plans first, then make decisions off those; e.g. a long running query might be causing deadlocks, another query can be optimize with different joins or ctes, etc. Adding indexes can increase I/O time (more writes), increase DB file sizes (more stuff to store) etc, which are not inconsequential. Sometimes it's also as simple as moving indexes to a tablespace stored on an SSD.
  - ndriscoll 3y ago
    Rounding out the end of 2023 where a 15 TB pcie gen 4 drive is $1800, it's hard for me to accept the fact that SSD storage for databases (or at least the indexes) isn't just a given.
jjav 3y ago
> I've seen far too many places where db instances run with default settings out of the box, queries missing basic indexes, no one even cared to run an explain analyze. Indeed! One place I worked at, there was something like 10x (forget exactly) gains to be had simply by editing the MySQL config to match the workload. Seems like nobody bothers anymore. There is so much money to be saved by reducing excess operating costs.
- winrid 3y ago
  Yep. Also, this is what I specialize in if anyone wants a senior engineer that knows operating systems and databases! ^_^

dkjaudyeqooe 3y ago

> Performance optimization has become a lost art. It's really not very hard, basically add indexes wherever lookups are done but don't go overboard, especially with tables that see a lot of inserts. The DB will tell you what it's doing if you ask, it's all easily investigated. If your joins resist optimisation your data structure is probably off. > I won't even touch on proper design, which requires someone with significant database expertise from the get-go In more extreme cases this is true, but a few basic principles and an understanding of databases will get you 95% of the way there and is quite intuitive. Something as basic as "don't copy data around, each individual entity exists in one place, and use its key to refer to it otherwise" will help enormously.

alex7734 3y ago
> It's really not very hard, basically add indexes It's really not but you'd be surprised how many people don't know the basics of how indexes work. I've had people tell me that their queries couldn't be optimized further because "they had already added an index for every column of the table". It doesn't help that SQL, by design, hides the actual algorithm doing the data access from the users while simultaneously relying on them to add indexes to achieve performance, which is in my humble opinion the worst mistake of SQL.
- corytheboyd 3y ago
  I love to ask this very simple question in interviews: if a database index makes queries faster, why not add an index for every column on a table? I want them to say “the heck are you talking about, because that’s not how anything works?”… it does trip some people up though that just legitimately don’t understand what’s happening.
- ako 3y ago
  That is not a SQL mistake, but an implementation choice. Most modern databases can determine where indexes should be added, and add these automatically. E.g.: https://www.oracle.com/news/connect/oracle-database-automatic-indexing.html https://www.oracle.com/news/connect/oracle-database-automati...
  - akoboldfrying 3y ago
    This is news to me. Can any other DBs do this? (I only skimmed -- far too much "Joan explains that" to signal ratio.)
    - ako 3y ago
      SQL server: https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/a... Interesting read on a project implementing this for Postgres: https://pganalyze.com/blog/automatic-indexing-system-postgres-pganalyze-indexing-engine https://pganalyze.com/blog/automatic-indexing-system-postgre...
  - sharadov 3y ago
    Not for postgres or mysql.
sgarland 3y ago
> The DB will tell you what it's doing if you ask, it's all easily investigated. It will tell you _what_ it's doing, but not _why_ it's doing it. Unraveling the mysteries of the query planner is a dark art that is still firmly in the realm of graybeards.
- sharadov 3y ago
  Exactly and that's where it gets hairy and you need deep expertise.
xarope 3y ago
As a former DBA and optimization expert, the more appropriate response is NOT "add more indexes", but rather to profile and analyze your query plans first, then make decisions off those; e.g. a long running query might be causing deadlocks, another query can be optimize with different joins or ctes, etc. Adding indexes can increase I/O time (more writes), increase DB file sizes (more stuff to store) etc, which are not inconsequential. Sometimes it's also as simple as moving indexes to a tablespace stored on an SSD.
- ndriscoll 3y ago
  Rounding out the end of 2023 where a 15 TB pcie gen 4 drive is $1800, it's hard for me to accept the fact that SSD storage for databases (or at least the indexes) isn't just a given.

jjav 3y ago

> I've seen far too many places where db instances run with default settings out of the box, queries missing basic indexes, no one even cared to run an explain analyze. Indeed! One place I worked at, there was something like 10x (forget exactly) gains to be had simply by editing the MySQL config to match the workload. Seems like nobody bothers anymore. There is so much money to be saved by reducing excess operating costs.

winrid 3y ago
Yep. Also, this is what I specialize in if anyone wants a senior engineer that knows operating systems and databases! ^_^