3 ms·
> I don't see this "technology craze" you mention, but I have seen things like parallel processing, bitmap indexes, selective indexes, query plans that use mult
by naasking 8y ago
> I don't see this "technology craze" you mention, but I have seen things like parallel processing, bitmap indexes, selective indexes, query plans that use multiple indexes, online index creation (no locking), support for complex data types like JSON or XML, partitioning, vastly improved resource usage (CPU+memory+disk) just to name a few.
Those all seem like pretty ad-hoc features, some of which were influenced by prevailing tech fashion trends at the time. For instance, the fact that you need to manually specify so many details about indices sounds like a failure to have a good theory around indexing which can be integrated into query plans. There are a lot of flaws like this in SQL stores as a whole.
- MaxBarraclough 8y agoHow are they 'ad-hoc'? With the exceptions of JSON and XML support (cf. the 'NoSQL' fad), they're all valuable enhancements to core functionalities, no? > the fact that you need to manually specify so many details about indices sounds like a failure to have a good theory around indexing which can be integrated into query plans I'm no SQL guru, but this smacks of a variant on the 'sufficiently smart compiler fallacy' to me. That the system can't always, uh, optimally optimise, isn't necessarily an indication that the system is fundamentally flawed. GCC permits inline assembly, but that doesn't mean GCC is a failure.
- Retra 8y agoIf GCC required inline assembly everywhere, it would definitely be a failure.
- MaxBarraclough 8y agoOf course. Given that this feature was added to the DBMS pretty late in the game, we can infer that it's only rarely worthwhile.
- naasking 8y agoSee my other comment [1] if you want further details as to SQL's limitations. [1] https://news.ycombinator.com/item?id=18147388 https://news.ycombinator.com/item?id=18147388
- ecnahc515 8y agoA lot of SQL databases can definitely adhoc determine if indices are necessary; it's just not the default because predictable performance and explicit indices tend to be preferred. Postgresql has a handful of plugins that can tell you if you had an indice; would it be used and you can see query plans before/after without actually having to add indices until you're comfortable. This same plugin could easily automate indice creation, but most people don't.
- tomnipotent 8y ago> For instance, the fact that you need to manually specify so many details about indices sounds like a failure to have a good theory around indexing Real life isn't about theory, it's about what works and doesn't work in production. These are not flaws, they're features. I don't need a bitmap index on my primary key, nor does it need to be selective because it will include every row. But having these features and the ability to customize based on the needs of my product is paramount. If you were not using an RDBMS to store your data, you'd still have these same problems and you'd still end up with optimized secondary indexes based on the lookup criteria.