3 ms·

Excellent article. As promised, the author brings a historical perspective that's typically lacking in this debate (at least for someone like me who has casuall

Excellent article. As promised, the author brings a historical perspective that's typically lacking in this debate (at least for someone like me who has casually followed it on HN). Not to stray too far off topic, but this article raised a question that I've had since starting software development in earnest... Why do we have to choose between heavily "normalized" relational databases that structure all data & thus allow more arbitrary queries, OR un-structured databases that are fast and flexible but often slow to query in complex ways? Why can't there be a hybrid "smart" database that dynamically generates indexes (to use the term loosely) based on how it's queried, in order to speed up similar queries in the future? With some kind of additional weight given to the most frequent queries. Granted, it would need some time to "warm up" (not unlike a tracing JIT compiler?), and the implementation might be fairly complex, but other than that, I can't think of any downsides...?

lukaseder 13y ago
You might enjoy PostgreSQL, then. It supports a variety of "weakly" structured data types, such as JSON, hashmaps (key/value types, also called hstore in PostgreSQL). Many databases also support XML data types. And of course, you can always store unstructured data in CLOBs and BLOBs
- sowhatquestion 13y ago
  Actually, you read my mind! I'm currently working on a Rails project that uses PostgreSQL with hstore for semi-structured data such as user information, configuration, etc. Haven't gotten too far into it yet, but it looks promising. :) What I was trying to imagine in my post, though, was some hypothetical DB where all this would be abstracted away--i.e., it would expose a NoSQL-like interface for all data, while working behind the scenes to provide SQL-like speed for frequent queries. Hence the analogy that it would be the "tracing JIT" of databases.
  - mtdewcmu 13y ago
    I've been thinking along similar lines, I think. One problem I see with data in RDBMSs is that data gets transformed in the process of importing it to match the schema, and this transformation loses information, i.e. it's hard to recover the original meaning and context, because it's been contorted to fit the schema. Schemas tend to need to evolve, and so you'd like not to be stuck with the assumptions of schema 1.0 forever. Data isn't that big these days in relation to the speed of computers, so you could keep the data in its original form and leave open the ability to "reimport" to fit the current schema as needed. If you're building a website, for instance, then your web app is going to be doing a limited number of queries over and over. If you start from the queries, you can infer things about how the data should be organized, e.g. which indexes are needed. In fact, if you took a query and pre-generated all the possible results of that query and saved them, that's basically an index, the one that fits that query. The inputs to this hypothetical database would be the original unprocessed data, the set of queries you need, and mappings to transform the unprocessed data into queryable form, i.e. the equivalent of a schema. The inputs apart from the data proper would also be considered as data.
- lafar6502 13y ago
  Yes, but usually you don't want to use these 'weakly structured' BLOBS if you have an alternative. Relational dbs have some weak points but structured data storage is not one of them. I can't imagine a database without a fixed schema, it always exists even if you don't have to declare it upfront.
a8da6b0c91d 13y ago
It's probably possible to incorporate very smart query engines in purely relational database systems that would obviate many of the complaints about relational databases. But SQL does not really implement the relational model and makes that infeasible. See "The Third Manifesto" and co.
smilliken 13y ago
PostgreSQL is the closest to what you're looking for. It's really fast; if it doesn't meet your requirements you're probably already aware that you need a specialized solution. Besides explicit indexing, it also analyzes your data and maintains stats that inform the query planner. As your tables grow and your schema evolves, it will change its query plans accordingly. It can be quite smart. (A few examples: it maintains histograms of common values, null percentages, correlation between physical ordering and logical ordering or rows, and much more). Of course, you also have a lot of flexibility to denormalize and get the best of both worlds.
MehdiEG 13y ago
AFAIK RavenDB, which describes itself as a "2nd generation document database", does exactly what you describe. Whenever you run a query that's missing an index, it will automatically create a temporary "dynamic index" to service it. If it finds that this index is used a lot, it will automatically promote it to a permanent index. I haven't tried it yet however so can't comment on how well it works. But yes, generally speaking, I'm also surprised to see that in 2013 we're creating our indexes manually. While there are of course applications where you really want to ensure that all the right indexes are created ahead of time, for most applications having indexes created automatically based on query patterns would seem like a much better solution.
- lukaseder 13y ago
  I agree that automatic creation of indexes looks like a reasonable thing to do for a database like Oracle. Oracle could gather statistics and auto-create indexes where it seems fit, i.e. where there is a lot of querying and little writing going on. In a way, this is what caches can do. Besides that, Oracle is already very good at giving you statistics and tuning hints to help you assess where you could add an index: http://stackoverflow.com/a/2937047/521799 http://stackoverflow.com/a/2937047/521799 DB2 and SQL Server probably have similar tools. As far as I know, they all don't go as far as automatically creating or dropping any indexes.
- pepijndevos 13y ago
  Googles appengine datastore will suggest indexes, but it's rather bad at it IMO.
sehugg 13y ago
Well, Oracle has its SQL query tuning tools... but in a production system you often don't want too much magical optimization leading to erratic behavior/performance problems. Oracle also has a "plan stability" feature for this very reason.
jandrewrogers 13y ago
The major downside is that it is an inherently non-scalable behavior for a database engine. A few databases do what you suggest but they are limited in the amount of data they can organize in this way without performance becoming problematic. Since data is becoming quite large rather quickly, it is not a sensible architecture choice these days. There are several reasons why you would not want to design a large-scale database this way. It implies an enormous amount of extra data motion which is arguably the major killer of performance in parallel databases; it is the same reason Hadoop is so slow and inefficient for analytic queries. Additionally, secondary indexing structures and similar types of de-normalization offer poor performance in distributed environments due to the implied consistency and coordination problems; it will severely degrade insert/update/delete operation performance as the data structures grow. Most database applications also value a high-degree of predictability of performance under load, which this definitely will not allow. These tradeoffs are unacceptable for many (most?) applications. In short, while you could design a database that does what you suggest, it would only be useful at tiny scales or for read-only databases where consistent performance characteristics are not a requirement.
- rusabd 13y ago
  >The major downside is that it is an inherently non-scalable behavior for a database engine well, that is true for the whole SQL, but certainly not true for some subset of SQL which is rather big. And how much is "quite large"? We have client who claims that 160GB is big (he is running cluster of 8 machines now, poor bastard)
  - jandrewrogers 13y ago
    Actually, it is not true for the whole SQL. It is just true for implementations that require secondary indexing or extensive denormalization. This is essentially the way you would implement SQL if you were copying 1990s database kernel design but it is not the only way. Unfortunately, almost everyone still implements database engines this way because that is the way they have traditionally been designed; designing a genuinely new database kernel from scratch is not for the faint of heart. I normally design around databases in the tens of terabytes to tens of petabytes range, it is not an unusual scale. A database engine designed as the parent outlined would start to exhibit unacceptable performance characteristics at single-digit terabyte scales. A terabyte is tiny; I can buy servers with that much memory. Complex multi-attribute selection and joins are efficiently parallelizable but most databases do not implement algorithms and data structures that allow this to be realized.
softwaredoug 13y ago
Hey thanks for liking the article (I'm the author) It seems maybe some of the NewSQL stuff is doing this. You use "SQL" but the data is still organized by some kind of hierarchy (like in F5). I'm trying to get into Google's Dremel paper to learn more about how they do it. In general, though, it seems if there's any chance something might be normalized, but behind the scenes maybe its actually denormalized a bit, then folks try to layer on some kind of SQL-likeness to the database.
Spooky23 13y ago
Fundamentally, the two problems (max throughput for transactions vs. query flexibility) are different. So while you may build a jack of all trades system that can do it all, but if you keep growing, eventually you'll need to make tuning decisions that negatively impact queries or throughput. The solution in most cases is to maintain transaction and query optimized databases separately.

lukaseder 13y ago

You might enjoy PostgreSQL, then. It supports a variety of "weakly" structured data types, such as JSON, hashmaps (key/value types, also called hstore in PostgreSQL). Many databases also support XML data types. And of course, you can always store unstructured data in CLOBs and BLOBs

sowhatquestion 13y ago
Actually, you read my mind! I'm currently working on a Rails project that uses PostgreSQL with hstore for semi-structured data such as user information, configuration, etc. Haven't gotten too far into it yet, but it looks promising. :) What I was trying to imagine in my post, though, was some hypothetical DB where all this would be abstracted away--i.e., it would expose a NoSQL-like interface for all data, while working behind the scenes to provide SQL-like speed for frequent queries. Hence the analogy that it would be the "tracing JIT" of databases.
- mtdewcmu 13y ago
  I've been thinking along similar lines, I think. One problem I see with data in RDBMSs is that data gets transformed in the process of importing it to match the schema, and this transformation loses information, i.e. it's hard to recover the original meaning and context, because it's been contorted to fit the schema. Schemas tend to need to evolve, and so you'd like not to be stuck with the assumptions of schema 1.0 forever. Data isn't that big these days in relation to the speed of computers, so you could keep the data in its original form and leave open the ability to "reimport" to fit the current schema as needed. If you're building a website, for instance, then your web app is going to be doing a limited number of queries over and over. If you start from the queries, you can infer things about how the data should be organized, e.g. which indexes are needed. In fact, if you took a query and pre-generated all the possible results of that query and saved them, that's basically an index, the one that fits that query. The inputs to this hypothetical database would be the original unprocessed data, the set of queries you need, and mappings to transform the unprocessed data into queryable form, i.e. the equivalent of a schema. The inputs apart from the data proper would also be considered as data.
lafar6502 13y ago
Yes, but usually you don't want to use these 'weakly structured' BLOBS if you have an alternative. Relational dbs have some weak points but structured data storage is not one of them. I can't imagine a database without a fixed schema, it always exists even if you don't have to declare it upfront.

a8da6b0c91d 13y ago

It's probably possible to incorporate very smart query engines in purely relational database systems that would obviate many of the complaints about relational databases. But SQL does not really implement the relational model and makes that infeasible. See "The Third Manifesto" and co.

smilliken 13y ago

PostgreSQL is the closest to what you're looking for. It's really fast; if it doesn't meet your requirements you're probably already aware that you need a specialized solution. Besides explicit indexing, it also analyzes your data and maintains stats that inform the query planner. As your tables grow and your schema evolves, it will change its query plans accordingly. It can be quite smart. (A few examples: it maintains histograms of common values, null percentages, correlation between physical ordering and logical ordering or rows, and much more). Of course, you also have a lot of flexibility to denormalize and get the best of both worlds.

MehdiEG 13y ago

AFAIK RavenDB, which describes itself as a "2nd generation document database", does exactly what you describe. Whenever you run a query that's missing an index, it will automatically create a temporary "dynamic index" to service it. If it finds that this index is used a lot, it will automatically promote it to a permanent index. I haven't tried it yet however so can't comment on how well it works. But yes, generally speaking, I'm also surprised to see that in 2013 we're creating our indexes manually. While there are of course applications where you really want to ensure that all the right indexes are created ahead of time, for most applications having indexes created automatically based on query patterns would seem like a much better solution.

lukaseder 13y ago
I agree that automatic creation of indexes looks like a reasonable thing to do for a database like Oracle. Oracle could gather statistics and auto-create indexes where it seems fit, i.e. where there is a lot of querying and little writing going on. In a way, this is what caches can do. Besides that, Oracle is already very good at giving you statistics and tuning hints to help you assess where you could add an index: http://stackoverflow.com/a/2937047/521799 http://stackoverflow.com/a/2937047/521799 DB2 and SQL Server probably have similar tools. As far as I know, they all don't go as far as automatically creating or dropping any indexes.
pepijndevos 13y ago
Googles appengine datastore will suggest indexes, but it's rather bad at it IMO.

sehugg 13y ago

Well, Oracle has its SQL query tuning tools... but in a production system you often don't want too much magical optimization leading to erratic behavior/performance problems. Oracle also has a "plan stability" feature for this very reason.

jandrewrogers 13y ago

The major downside is that it is an inherently non-scalable behavior for a database engine. A few databases do what you suggest but they are limited in the amount of data they can organize in this way without performance becoming problematic. Since data is becoming quite large rather quickly, it is not a sensible architecture choice these days. There are several reasons why you would not want to design a large-scale database this way. It implies an enormous amount of extra data motion which is arguably the major killer of performance in parallel databases; it is the same reason Hadoop is so slow and inefficient for analytic queries. Additionally, secondary indexing structures and similar types of de-normalization offer poor performance in distributed environments due to the implied consistency and coordination problems; it will severely degrade insert/update/delete operation performance as the data structures grow. Most database applications also value a high-degree of predictability of performance under load, which this definitely will not allow. These tradeoffs are unacceptable for many (most?) applications. In short, while you could design a database that does what you suggest, it would only be useful at tiny scales or for read-only databases where consistent performance characteristics are not a requirement.

rusabd 13y ago
>The major downside is that it is an inherently non-scalable behavior for a database engine well, that is true for the whole SQL, but certainly not true for some subset of SQL which is rather big. And how much is "quite large"? We have client who claims that 160GB is big (he is running cluster of 8 machines now, poor bastard)
- jandrewrogers 13y ago
  Actually, it is not true for the whole SQL. It is just true for implementations that require secondary indexing or extensive denormalization. This is essentially the way you would implement SQL if you were copying 1990s database kernel design but it is not the only way. Unfortunately, almost everyone still implements database engines this way because that is the way they have traditionally been designed; designing a genuinely new database kernel from scratch is not for the faint of heart. I normally design around databases in the tens of terabytes to tens of petabytes range, it is not an unusual scale. A database engine designed as the parent outlined would start to exhibit unacceptable performance characteristics at single-digit terabyte scales. A terabyte is tiny; I can buy servers with that much memory. Complex multi-attribute selection and joins are efficiently parallelizable but most databases do not implement algorithms and data structures that allow this to be realized.

softwaredoug 13y ago

Hey thanks for liking the article (I'm the author) It seems maybe some of the NewSQL stuff is doing this. You use "SQL" but the data is still organized by some kind of hierarchy (like in F5). I'm trying to get into Google's Dremel paper to learn more about how they do it. In general, though, it seems if there's any chance something might be normalized, but behind the scenes maybe its actually denormalized a bit, then folks try to layer on some kind of SQL-likeness to the database.

Spooky23 13y ago

Fundamentally, the two problems (max throughput for transactions vs. query flexibility) are different. So while you may build a jack of all trades system that can do it all, but if you keep growing, eventually you'll need to make tuning decisions that negatively impact queries or throughput. The solution in most cases is to maintain transaction and query optimized databases separately.