5 ms·

I think a good DBA ought to be able to better than an automatic process possibly could. The database can't know whether an index ought to be unique, you have to

I think a good DBA ought to be able to better than an automatic process possibly could. The database can't know whether an index ought to be unique, you have to understand the data model to know that. You might want to make an index multi-column even if you aren't using all the columns the first time you make a query, but again you need a human-level understanding of the data model to do that. And if you already have indexes that work reasonably well, you can't expect an automatic optimizer to realize it could do even better by replacing existing indices. That being said, an automatic indexer could probably be "good enough" in many cases.

gpderetta 4y ago
If a database is expected to be good enough to optimize query plans by default, wouldn't you expect it to be also good enough to optimize layout/indexing?
- falcolas 4y ago
  They're often terrible at optimizing query plans, especially when there's no index, since the index provides data about the data (cardinality, for example) into the query optimizer.
- nikita 4y ago
  Traditionally query optimizers are focused on producing good and not disastrous plans. Not necessarily the best plan. This is the role of a query optimizer: do decently well and don't royally screw up. What makes optimizers to screw up is cardinality misestimations which leads to the wrong join order or wrong join types (nested loop join thinking that a subresult in a query plan is 3 rows where in reality it's 1M). Optimizing physical layout presents new challenges: - it takes time to build those layouts and consumes resources. nothing like adding a load to a database that's already struggling to keep up with the load :) - you don't know what you are going to break With all that said that's the future and the industry has to figure it out.
- Beltalowda 4y ago
  The problem is that indexes aren't free; an index can easily take up gigabytes of disk space, if not more. There are also performance considerations with respect to insert performance. What a "good" index is depends on the use case; for example for a webapp a 0.25 second query that's run on every pageload is extremely slow, and creating an index for that is almost certainly a good trade-off. On the hand, it's usually fine if some analytical query that only your finance people need once a month runs for a minute; in that case, an index is probably a poor trade-off. Or: in my app one query takes about ~5 seconds for a rarely used feature; I could add an index to make it faster, but it would also eat up ~25G of disk space (last I checked, probably more now). The very small number of people using it can wait an extra few seconds. It's not too hard to automatically create indexes; I'd say it's almost easy. But these kind of judgements based on use case isn't something the database can do, and you're likely to end up with dozens of semi-useless indexes eating up your disk space and insert performance. This is probably one of those cases where automating things will lead to problems and confusion; it's probably better to spend time on tools to gain insight in what the database is doing, so a human can come along and do something that makes sense.
  - iSnow 4y ago
    >for example for a webapp a 0.25 second query that's run on every pageload is extremely slow, and creating an index for that is almost certainly a good trade-off. On the hand, it's usually fine if some analytical query that only your finance people need once a month runs for a minute; in that case, an index is probably a poor trade-off. But that should be automatable. If the DB sees frequent queries, it would create indexes, if those queries cease, it would drop them. If queries are infrequent, it would not create an index.
    - beachy 4y ago
      Would not want to be responsible for a database where some AI was vigorously creating and dropping indexes behind the scenes, just as I was trying to debug performance issues.
  - maest 4y ago
    > But these kind of judgements based on use case isn't something the database can do, and you're likely to end up with dozens of semi-useless indexes eating up your disk space and insert performance That is an empirical question and another comment points to this working just fine: https://news.ycombinator.com/item?id=31991469 https://news.ycombinator.com/item?id=31991469 Even ignoring that, creating indexes is a matter of tradeoffs - it's not the index creation that's difficult but the decision on whether the tradeoff is worth it. Seeing this, possible issues arising from automatic index creation can be mitigated by allowing the admin to set parameters that dictate where the tradeoff should be made (as a naive example: "I'm okay with using 10Gb of storage if it results in a 20% improvement for P95 queries on this table"). This is not an unreasonable idea and it sounds like it would greatly improve UX for devs working on median-sized DBs (people needing FAANG scale can manually tweak their DBs). Worst case, you can have a whitelist approach to automatic indexes, where the admin is shown index suggestions which require manual approval.
msbarnett 4y ago
> I think a good DBA ought to be able to better than an automatic process possibly could Absolutely, although in modern orgs I'd expect this to be the domain of backend engineers rather than "a DBA", a mythical creature I've never actually seen in real life.
- kristiandupont 4y ago
  Oh they exist alright, I have met them. They are bearded old men that rule the kingdom of Oracle in a dusty room that smells of stale coffee. If you ask them if you may please have a table they respond angrily "NO, you may NOT have a table!"
  - _eht 4y ago
    This made me spit out my stale coffee.
  - smrtinsert 4y ago
    I sometimes think this was the actual use case being solved by the nosql movement.
    - ruined 4y ago
      all new technology solves a problem, sometimes it's a social problem
    - Shorel 4y ago
      And you would be right. Grumpy old men complaining no one in the company is able to write good enough queries. We got rid of them when we migrated from Oracle to AWS Aurora.
  - partdavid 4y ago
    I was an Oracle DBA for 11 years. The joke is: Q: What is the difference between a DBA and a terrorist? A: You can negotiate with a terrorist. After being a DBA, I moved into security, so I could tell people NO with even less consideration and more cruelty.
nikita 4y ago
Time an again AI systems outperform humans. This maybe true today but not tomorrow
Daishiman 4y ago
It depends also on what you want to prioritize. Indices add insertion overheads. You can have general rules for indices but ultimately it comes down to what operations are time-critical.
- Someone 4y ago
  Indices also can be extremely large. For example, a SELECT foo, bar, baz FROM quux WHERE foo = ?; will be fastest with an index on foo that also stores bar and baz (once the index entry is found, the engine won’t need to do a disk seek to the row data). If that table is large and you run SELECT foo FROM quux WHERE foo = ?; (Edited after zimpenfish’s remark about a copy-paste error) more frequently than the former query, just creating an index on foo alone may be the better choice. Whether it does depend on lots and lots of factors such as query distribution (is it worth it to make the monthly report twice as fast at the cost of running many other queries 1% slower?), disk speed (if you move a database to SSD you should revisit all indexing choices), and disk space. With advanced SQL engines, there’s way more choice. Maybe, it’s best to create an index on the first n character of a string field, or an index that only works for equality searches, not for phrases such as foo < 3, etc, or an index on only the data from last month (can be done in some databases with partitioned tables)
  - zimpenfish 4y ago
    > SELECT foo, bar, baz FROM quux WHERE foo = ?; > SELECT foo, bar, baz FROM quux WHERE foo = ?; Possibly a typo in the second query since the text indicates it should be different from the first?
igouy 4y ago
"Peloton is a relational database management system that is designed for autonomous operation. The system’s integrated planning component not only optimizes the system for the current workload, but also predicts future workload trends before they occur so that the system can prepare itself accordingly. It also enables new optimizations that are not possible today because the complexity of managing these systems has surpassed the abilities of human experts." https://pelotondb.io/ https://pelotondb.io/

gpderetta 4y ago

If a database is expected to be good enough to optimize query plans by default, wouldn't you expect it to be also good enough to optimize layout/indexing?

falcolas 4y ago
They're often terrible at optimizing query plans, especially when there's no index, since the index provides data about the data (cardinality, for example) into the query optimizer.
nikita 4y ago
Traditionally query optimizers are focused on producing good and not disastrous plans. Not necessarily the best plan. This is the role of a query optimizer: do decently well and don't royally screw up. What makes optimizers to screw up is cardinality misestimations which leads to the wrong join order or wrong join types (nested loop join thinking that a subresult in a query plan is 3 rows where in reality it's 1M). Optimizing physical layout presents new challenges: - it takes time to build those layouts and consumes resources. nothing like adding a load to a database that's already struggling to keep up with the load :) - you don't know what you are going to break With all that said that's the future and the industry has to figure it out.
Beltalowda 4y ago
The problem is that indexes aren't free; an index can easily take up gigabytes of disk space, if not more. There are also performance considerations with respect to insert performance. What a "good" index is depends on the use case; for example for a webapp a 0.25 second query that's run on every pageload is extremely slow, and creating an index for that is almost certainly a good trade-off. On the hand, it's usually fine if some analytical query that only your finance people need once a month runs for a minute; in that case, an index is probably a poor trade-off. Or: in my app one query takes about ~5 seconds for a rarely used feature; I could add an index to make it faster, but it would also eat up ~25G of disk space (last I checked, probably more now). The very small number of people using it can wait an extra few seconds. It's not too hard to automatically create indexes; I'd say it's almost easy. But these kind of judgements based on use case isn't something the database can do, and you're likely to end up with dozens of semi-useless indexes eating up your disk space and insert performance. This is probably one of those cases where automating things will lead to problems and confusion; it's probably better to spend time on tools to gain insight in what the database is doing, so a human can come along and do something that makes sense.
- iSnow 4y ago
  >for example for a webapp a 0.25 second query that's run on every pageload is extremely slow, and creating an index for that is almost certainly a good trade-off. On the hand, it's usually fine if some analytical query that only your finance people need once a month runs for a minute; in that case, an index is probably a poor trade-off. But that should be automatable. If the DB sees frequent queries, it would create indexes, if those queries cease, it would drop them. If queries are infrequent, it would not create an index.
  - beachy 4y ago
    Would not want to be responsible for a database where some AI was vigorously creating and dropping indexes behind the scenes, just as I was trying to debug performance issues.
- maest 4y ago
  > But these kind of judgements based on use case isn't something the database can do, and you're likely to end up with dozens of semi-useless indexes eating up your disk space and insert performance That is an empirical question and another comment points to this working just fine: https://news.ycombinator.com/item?id=31991469 https://news.ycombinator.com/item?id=31991469 Even ignoring that, creating indexes is a matter of tradeoffs - it's not the index creation that's difficult but the decision on whether the tradeoff is worth it. Seeing this, possible issues arising from automatic index creation can be mitigated by allowing the admin to set parameters that dictate where the tradeoff should be made (as a naive example: "I'm okay with using 10Gb of storage if it results in a 20% improvement for P95 queries on this table"). This is not an unreasonable idea and it sounds like it would greatly improve UX for devs working on median-sized DBs (people needing FAANG scale can manually tweak their DBs). Worst case, you can have a whitelist approach to automatic indexes, where the admin is shown index suggestions which require manual approval.

msbarnett 4y ago

> I think a good DBA ought to be able to better than an automatic process possibly could Absolutely, although in modern orgs I'd expect this to be the domain of backend engineers rather than "a DBA", a mythical creature I've never actually seen in real life.

kristiandupont 4y ago
Oh they exist alright, I have met them. They are bearded old men that rule the kingdom of Oracle in a dusty room that smells of stale coffee. If you ask them if you may please have a table they respond angrily "NO, you may NOT have a table!"
- _eht 4y ago
  This made me spit out my stale coffee.
- smrtinsert 4y ago
  I sometimes think this was the actual use case being solved by the nosql movement.
  - ruined 4y ago
    all new technology solves a problem, sometimes it's a social problem
  - Shorel 4y ago
    And you would be right. Grumpy old men complaining no one in the company is able to write good enough queries. We got rid of them when we migrated from Oracle to AWS Aurora.
- partdavid 4y ago
  I was an Oracle DBA for 11 years. The joke is: Q: What is the difference between a DBA and a terrorist? A: You can negotiate with a terrorist. After being a DBA, I moved into security, so I could tell people NO with even less consideration and more cruelty.

nikita 4y ago

Time an again AI systems outperform humans. This maybe true today but not tomorrow

Daishiman 4y ago

It depends also on what you want to prioritize. Indices add insertion overheads. You can have general rules for indices but ultimately it comes down to what operations are time-critical.

Someone 4y ago
Indices also can be extremely large. For example, a SELECT foo, bar, baz FROM quux WHERE foo = ?; will be fastest with an index on foo that also stores bar and baz (once the index entry is found, the engine won’t need to do a disk seek to the row data). If that table is large and you run SELECT foo FROM quux WHERE foo = ?; (Edited after zimpenfish’s remark about a copy-paste error) more frequently than the former query, just creating an index on foo alone may be the better choice. Whether it does depend on lots and lots of factors such as query distribution (is it worth it to make the monthly report twice as fast at the cost of running many other queries 1% slower?), disk speed (if you move a database to SSD you should revisit all indexing choices), and disk space. With advanced SQL engines, there’s way more choice. Maybe, it’s best to create an index on the first n character of a string field, or an index that only works for equality searches, not for phrases such as foo < 3, etc, or an index on only the data from last month (can be done in some databases with partitioned tables)
- zimpenfish 4y ago
  > SELECT foo, bar, baz FROM quux WHERE foo = ?; > SELECT foo, bar, baz FROM quux WHERE foo = ?; Possibly a typo in the second query since the text indicates it should be different from the first?

igouy 4y ago

"Peloton is a relational database management system that is designed for autonomous operation. The system’s integrated planning component not only optimizes the system for the current workload, but also predicts future workload trends before they occur so that the system can prepare itself accordingly. It also enables new optimizations that are not possible today because the complexity of managing these systems has surpassed the abilities of human experts." https://pelotondb.io/ https://pelotondb.io/