[Shameless Plug] A summary of the evergrowing NoSQL and NewSQL market: https://medium.com/open-factory/nosql-newsql-a-smorgasboard-...
Comparing Database Types
131–140 of 176 posts
Re: Comparing Database Types
#132Earlier quoted context omitted.
I did a MOOC on relational algebra that made me much more productive in SQL and better appreciate the gravity of what RDBMS really offer. Understanding relational algebra helps demystify the magic or query planners and grok why they both add and reduce latency based on use cases.
Mind sharing the course? Sounds useful.
Re: Comparing Database Types
#133Earlier quoted context omitted.
That makes sense, they really are just relational databases optimized for certain tasks, with corresponding limitations e.g. they don't support arbitrary joins.
There's nothing intrinsic about not supporting joins, in a columnar store; it's just that you lose a huge amount of the linear scanning performance if you have to do joins for each value. Most columnar stores I've used (primarily Impala, SparkSQL and Clickhouse) all support joins, but they materialize one side of the join as an in-memory hash table, which limits the allowable size of the join, and is a cost multiplie…
Right, that’s sort of what I was getting at with the word “arbitrary”. Compared to a general purpose db the limitations on what can be joined are pretty severe, if only because of performance considerations.
Re: Comparing Database Types
#134What about time series databases that are fairly common nowadays ?
Time-series is more about a specific use-case about data that has a primary time component (like sensor metrics). You can store it in any database, although the common ones are usually some sort of key/value or relational with specific features for time-based queries. Hbase/Bigtable/DynamoDB/Cassandra are key/value. InfluxDB is key/value. Timescale is an extension to Postgres.
The big time TS databases (Sybase, KDB, Informix Datawarehouse) are column-based, not key value or traditional relational row-oriented. The ones you list are all lower-tier trying to shoehorn a time field on another model.
Re: Comparing Database Types
#135Earlier quoted context omitted.
Time-series is more about a specific use-case about data that has a primary time component (like sensor metrics). You can store it in any database, although the common ones are usually some sort of key/value or relational with specific features for time-based queries. Hbase/Bigtable/DynamoDB/Cassandra are key/value. InfluxDB is key/value. Timescale is an extension to Postgres.
The big time TS databases (Sybase, KDB, Informix Datawarehouse) are column-based, not key value or traditional relational row-oriented. The ones you list are all lower-tier trying to shoehorn a time field on another model.
Those are still relational databases, just with column-oriented/column-store tables. I don't see how the storage layer changes the database type. For example, MemSQL has both rowstores and columnstores. Postgres 12 has pluggable storage with column-store (zedstore).
Re: Comparing Database Types
#136The document completely overlooks Columnar databases, which are focused on analytics and are much faster than (most, not all) general-purpose DBMSes. See: https://en.wikipedia.org/wiki/Column-oriented_DBMS and https://www.slideshare.net/arangodb/introduction-to-column-o... or get: http://www.nowpublishers.com/article/Details/DBS-024 Examples: * MonetDB * SAP Hana * Actian Vector (formerly Vectorwise) * Oracle In-Memo…
Also Druid, HBase, Vertipaq (engine behind PowerBI), Redshift, Azure SQL DW, etc Columnar compression is a really interesting engineering problem
ClickHouse is another favourite
Re: Comparing Database Types
#137The document completely overlooks Columnar databases, which are focused on analytics and are much faster than (most, not all) general-purpose DBMSes. See: https://en.wikipedia.org/wiki/Column-oriented_DBMS and https://www.slideshare.net/arangodb/introduction-to-column-o... or get: http://www.nowpublishers.com/article/Details/DBS-024 Examples: * MonetDB * SAP Hana * Actian Vector (formerly Vectorwise) * Oracle In-Memo…
Also Druid, HBase, Vertipaq (engine behind PowerBI), Redshift, Azure SQL DW, etc Columnar compression is a really interesting engineering problem
memsql (which was in there for new sql).
Re: Comparing Database Types
#138Earlier quoted context omitted.
The big time TS databases (Sybase, KDB, Informix Datawarehouse) are column-based, not key value or traditional relational row-oriented. The ones you list are all lower-tier trying to shoehorn a time field on another model.
Those are still relational databases, just with column-oriented/column-store tables. I don't see how the storage layer changes the database type. For example, MemSQL has both rowstores and columnstores. Postgres 12 has pluggable storage with column-store (zedstore).
> I don't see how the storage layer changes the database type.
It does, because it leads to other types of optimization. LittleTable [0], for example, keeps adjacent data in time domain adjacent in disk. So querying large amount of data that are close to each other is efficient even on slow (spinning) disk. Vertica [1] does column compression which allows it to work with denormalized data (common in analytics workload) efficiently.
In an ideal world, you could have a storage layer sitting below a perfect abstraction; orthogonal to higher levels. In the real world, column-based and row-based are two completely different categories serving very different use-cases.
[0] https://meraki.cisco.com/lib/pdf/trust/lt-paper.pdf [1] http://vldb.org/pvldb/vol5/p1790_andrewlamb_vldb2012.pdf
Re: Comparing Database Types
#139What about time series databases that are fairly common nowadays ?
Time-series is more about a specific use-case about data that has a primary time component (like sensor metrics). You can store it in any database, although the common ones are usually some sort of key/value or relational with specific features for time-based queries. Hbase/Bigtable/DynamoDB/Cassandra are key/value. InfluxDB is key/value. Timescale is an extension to Postgres.
If you could store timeseries data "in any database", kdb wouldn't be a thing. Just go and ask a quant trader replace his kdb instance with postgres. (Be prepared to be laughed out of the room.)
Re: Comparing Database Types
#140The document completely overlooks Columnar databases, which are focused on analytics and are much faster than (most, not all) general-purpose DBMSes. See: https://en.wikipedia.org/wiki/Column-oriented_DBMS and https://www.slideshare.net/arangodb/introduction-to-column-o... or get: http://www.nowpublishers.com/article/Details/DBS-024 Examples: * MonetDB * SAP Hana * Actian Vector (formerly Vectorwise) * Oracle In-Memo…
They are much faster for analytical queries, not for transactional ones. If they were overall faster they would be the default relational databases.