4 ms·

I keep saying this and it's still applicable. What's really needed is "Dynamic Relational" ( https://www.reddit.com/r/Database/comments/qw1erd/are_the_nosql_dat

by tabtab 4y ago

I keep saying this and it's still applicable. What's really needed is "Dynamic Relational" ( https://www.reddit.com/r/Database/comments/qw1erd/are_the_nosql_databases_larger_than_the_sql/hl1gbc3/ https://www.reddit.com/r/Database/comments/qw1erd/are_the_no... ) D.R. allows ad-hoc "schemas" (or equivalent) yet keeps most of the RDMBS idioms to reduce the learning curve for those already familiar with RDBMS, including most of SQL. Other database categories reinvent everything just to get dynamism, or to mostly get dynamism. That's not the shortest path to the goal. It's more logical just to tweak what's needed for dynamism and leave the rest alone (RDBMS-like).

jandrewrogers 4y ago
There are deep adverse performance implications for what you are suggesting. The result of trying to dynamically mix and match that much arbitrary structure would likely combine the worst of both worlds. You can essentially do this today with databases like PostgreSQL but there are good reasons no one does. (What you are describing appears to be a thin wrapper on what would conventionally be called a graph database.) If there is an "obvious" improvement to database capabilities that seems to be mysteriously absent from all competent implementations, one should consider the hypothesis that it would make databases strictly worse across many dimensions people care about. One thing that can be said about the history of database software is that it has tended to exhaustively explore the known phase space of possible implementations. Novelty in database implementations is usually predicated on a material computer science advance at the architectural level; any rearrangement of existing parts and ideas has usually already been tried multiple times. There are many good ideas for databases that no one implements because we don't know how to make them fast enough. People care greatly about database performance and scalability. You can implement almost any database feature you can imagine if you don't care about performance and scalability, you just won't have any users.
- cmrdporcupine 4y ago
  Ok all that said, this is what I've heard before, but what is the actual technical limitation behind "simply" providing alternate columnar storage&index implementations for tables optimized for OLAP but sharing the surrounding query parser, query execution framework, and potentially even query planner that is used elsewhere for OLTP? Like... Vertica is a fork of Postgres. I'm curious why they chose to fork and implement column-oriented storage and indexes etc. rather than simply add them as options to stock Postgres. Obviously joins across the two different worlds would be highly problematic, and perhaps query execution, data materialization, query planning etc. could look significantly different. But the potential advantage to the end user seems high, if moving from "transactional" to "analytical" workloads is a matter of moving data from one table-type to another, within the same underlying database system. Again, I know there are reasons why this approach has not been successful. I'm curious what they are.
  - convolvatron 4y ago
    most of it i think is just focus. its alot of work to pull together an OLAP and an OLTP database and they are very different workloads. the one place where you do start to get into fundamental issues is consistency. the planner can certainly identify read-only transactions and statically remove some conflicts. but whatever scheme you are using (locks, mvcc, optimistic) is going to struggle the more concurrent overlapping transactions there are. since OLAP transactions are very long lived and touch alot of things - they are pretty hostile co-residents with the OLTP traffic.
  - jandrewrogers 4y ago
    The original OLTP/OLAP dichotomy was based on architectural tradeoffs required for spinning disk and the way indexes worked. Especially at the time Vertica was created, the OLTP-ness of PostgreSQL was essentially hardcoded into the architecture. Vertica made a lot of changes, e.g. to storage behavior, to support their use case that would have significantly impacted OLTP performance. A complicated query that mixes NSM and DSM tables would be unreasonably complex since the query building blocks are different depending on the type of storage. This trade off doesn't really need to exist in a SQL database today on modern hardware with its extremely high storage bandwidth. There are other models that can satisfy both OLTP and OLAP use cases satisfactorily in a single coherent system with modern internals. The SQL database market is extremely conservative, so even if you built it no one would adopt it for a decade.
    - heisjustsosmart 4y ago
      "There are other models that can satisfy both OLTP and OLAP use cases satisfactorily in a single coherent system with modern internals" ok, which are these.

by tabtab 4y ago

jandrewrogers 4y ago

There are deep adverse performance implications for what you are suggesting. The result of trying to dynamically mix and match that much arbitrary structure would likely combine the worst of both worlds. You can essentially do this today with databases like PostgreSQL but there are good reasons no one does. (What you are describing appears to be a thin wrapper on what would conventionally be called a graph database.) If there is an "obvious" improvement to database capabilities that seems to be mysteriously absent from all competent implementations, one should consider the hypothesis that it would make databases strictly worse across many dimensions people care about. One thing that can be said about the history of database software is that it has tended to exhaustively explore the known phase space of possible implementations. Novelty in database implementations is usually predicated on a material computer science advance at the architectural level; any rearrangement of existing parts and ideas has usually already been tried multiple times. There are many good ideas for databases that no one implements because we don't know how to make them fast enough. People care greatly about database performance and scalability. You can implement almost any database feature you can imagine if you don't care about performance and scalability, you just won't have any users.

cmrdporcupine 4y ago
Ok all that said, this is what I've heard before, but what is the actual technical limitation behind "simply" providing alternate columnar storage&index implementations for tables optimized for OLAP but sharing the surrounding query parser, query execution framework, and potentially even query planner that is used elsewhere for OLTP? Like... Vertica is a fork of Postgres. I'm curious why they chose to fork and implement column-oriented storage and indexes etc. rather than simply add them as options to stock Postgres. Obviously joins across the two different worlds would be highly problematic, and perhaps query execution, data materialization, query planning etc. could look significantly different. But the potential advantage to the end user seems high, if moving from "transactional" to "analytical" workloads is a matter of moving data from one table-type to another, within the same underlying database system. Again, I know there are reasons why this approach has not been successful. I'm curious what they are.
- convolvatron 4y ago
  most of it i think is just focus. its alot of work to pull together an OLAP and an OLTP database and they are very different workloads. the one place where you do start to get into fundamental issues is consistency. the planner can certainly identify read-only transactions and statically remove some conflicts. but whatever scheme you are using (locks, mvcc, optimistic) is going to struggle the more concurrent overlapping transactions there are. since OLAP transactions are very long lived and touch alot of things - they are pretty hostile co-residents with the OLTP traffic.
- jandrewrogers 4y ago
  The original OLTP/OLAP dichotomy was based on architectural tradeoffs required for spinning disk and the way indexes worked. Especially at the time Vertica was created, the OLTP-ness of PostgreSQL was essentially hardcoded into the architecture. Vertica made a lot of changes, e.g. to storage behavior, to support their use case that would have significantly impacted OLTP performance. A complicated query that mixes NSM and DSM tables would be unreasonably complex since the query building blocks are different depending on the type of storage. This trade off doesn't really need to exist in a SQL database today on modern hardware with its extremely high storage bandwidth. There are other models that can satisfy both OLTP and OLAP use cases satisfactorily in a single coherent system with modern internals. The SQL database market is extremely conservative, so even if you built it no one would adopt it for a decade.
  - heisjustsosmart 4y ago
    "There are other models that can satisfy both OLTP and OLAP use cases satisfactorily in a single coherent system with modern internals" ok, which are these.