4 ms·
I'm sure the database could, but it doesn't mean the database should. Indexes come at the cost of extra disk space, slower inserts, and slower updates. In some
by elric 3y ago
I'm sure the database could, but it doesn't mean the database should. Indexes come at the cost of extra disk space, slower inserts, and slower updates. In some cases, some slower queries might be an acceptable tradeoff. In other cases, maybe not. It depends.
- kiwicopple 3y agothis is our posture for this extension on the supabase platform. we could automate the creation of the indexes using the Index Advisor, but we feel it would be better to expose the possible indexes to the user and let them choose
- gneray 3y agothis is the way ^^
- dmurray 3y agoYou could tell it "you have a budget of X GB for disk space, choose the indexes that best optimize the queries given the budget cap." Not perfect, because some queries may be more time-critical than others. You could even annotate every query (INSERT and UPDATE as well as SELECT) with the dollar amount per execution you're willing to pay to make it 100ms faster, or accept to make it 100ms slower. Then let it know the marginal dollar cost of adding index storage, throw this all into a constraint solver and add the indexes which are compatible with your pricing.
- d0100 3y agoAre the trade-offs measurable? If they are the database could just undo the index... Not just indexing, but table partitions, materialized views, keeping things in-memory...
- remus 3y ago> Are the trade-offs measurable? Yes, but you need the context about what is the correct tradeoff for your use case. If you've got a service that depends on fast writes then adding latency via extra indices for improved read speed may not be an acceptable trade off. It depends on your application though.