4 ms·
Modern column-oriented databases are rather incredible at what they do, and I'm surprised by how little they are used or known about. Redshift, BigQuery, Snowfl
by manigandham 8y ago
Modern column-oriented databases are rather incredible at what they do, and I'm surprised by how little they are used or known about. Redshift, BigQuery, Snowflake, Azure SQL DW, SQL Server, MonetDB, Vertica, Greenplum, MemSQL, kdb+, Clickhouse, even Druid are all column-stores that can do sub-second queries on massive amounts of data.
I also want to note that time-series databases are basically obsolete at this point because time-series data is very well handled by these OLAP column-stores. Create a table with time as a primary or sort key and you'll get fast queries with full SQL and joins.
- beagle3 8y agoKdb+ is a time series database; and in my experience, for this work it is much, much faster than vertica, sql server and Monet.
- halayli 8y agomapd beats it and iirc so does clickhouse.
- arnon 8y agoThere's no "This database beats that other database". There are a variety of use-cases, query profiles, workloads. There is no one-database-fits-all.
- coldtea 8y agoThere are however worse than others in all aspects.
- halayli 8y agoYes there is, and that's why tpc benchmarks exist. I didn't say there's a one-database-fits-all. But comparing kdb+ to mapd and clickhouse is a very reasonable comparison.
- arnon 8y agoTPC focuses on very specific scenarios. That's why there's more than one. There are also a variety of scale factors. A database that performs well on TPC-H SF 100 will not necessarily perform well on TPC-H SF 10000. One that performs well on TPC-H may be completely useless at TPC-DS. So, no.
- manigandham 8y agoAs listed in my comment, kdb+ is actually just a column-store, and like all column-stores it handles time-series really well. There's nothing stopping you from putting other data in there that doesnt have a time component at all, but it's designed to primarily work in the fintech space so has special performance optimizations and features for working with time and money.
- alexhutcheson 8y ago> Create a table with time as a primary or sort key and you'll get fast queries with full SQL and joins. Be careful with this. You can easily end up with pathologically bad performance, resource usage, contention etc. if most of your requests end up being directed to the same workers or shards in a distributed system. Partitioning by time can cause this because most queries tend to be for recent time windows. Time can be a decent primary key withing a shard, but is normally a really bad choice for any key that's used to partition data between shards. Make sure you understand how your system partitions data so you don't fall into this. Apologies if that came across as pedantic - you probably already know this, but hopefully some other readers can be saved from learning this through painful experience.
- deleted 8y ago[deleted]
- georgewfraser 8y agoColumn stores typically don’t use the primary key for anything except metadata. I think you’re thinking of distributed row stores that use the primary key to shard. In a column store, you would apply the sort/cluster key to your time column, which would partition the table within nodes, not across nodes. This will speed up queries that specify a narrow time range, and will not cause any of the pathological behavior you describe. Some column stores allow you to chhose a distribution key, which specifies how data is distributed across nodes. It can be used to optimize joins but it’s dangerous—it can cause exactly the problem you describe.
- alexhutcheson 8y agoGood clarification, thanks. I'm not familiar with the exact partitioning schemes of the various systems.
- manigandham 8y agoRight, if the primary key is being used to distribute data as a table then make time a secondary key for better distribution across partitions and nodes. Usually there's a difference between table primary/sort keys and distribution/shard keys. I should've made that more clear.
- jandrewrogers 8y agoMost column-oriented database architectures have a giant weakness that limits their utility, which was briefly mentioned at the end of the article: they offer poor performance for high-throughput mixed workloads, especially if there is a real-time component to it. Getting data into the system fast enough is a real problem. As more analytics become operational in nature, being able to scale online mixed workloads becomes the key feature. In practice, these still use vector-ish internal organization but the techniques differ considerably from many of the ones in the article, since online write performance is critical. Scalable online ingestion performance is a killer feature for many analytics systems but few database kernels are designed for it (I specialize in high-throughput mixed workload kernel design). Also, time-series database implemented as you suggest will work in some cases, and better than some might expect if you are clever, with the caveat that it has significant weaknesses and scalability limitations. Many real-world time-series data models and workloads would run poorly on a database organized this way. Sorting or sharding on time in the data organization is usually a mistake in my opinion. Some high-performance time-series databases don't use time as a sort/shard key at all, instead leveraging the approximate partial order of the underlying data sources directly. This often offers orders of magnitude better write performance with little or no loss of query performance in practice and it generalizes better. This is all a very complex but fantastically interesting technical design area. I firmly believe that scalable high-throughput mixed-workload analytical databases are possible, but they largely don't exist. Yet.
- manigandham 8y agoThey all have pretty fast ingest, and some can stream in data continuously at millions of rows/sec. MemSQL and SQL Server already support the HTAP combined use case with constant updates to both row and columnstore tables in the database while running large OLAP queries, so I'm not sure what you mean by the databases not existing yet - are you talking about something else? Yes, time as a secondary sort key works great. We have trillion row time-series tables with sub-second query times.
- jandrewrogers 8y agoI believe we are talking different scales, neither MemSQL nor SQL Server are designed for scale-out high-throughput mixed workloads for many practical definitions of "scale-out" and "high-throughput". First, online insertion (parsing, processing, indexing, disk storage) of millions of records/second should be per node, basically saturating the ingress path of each node's 10/25-GbE interface, and scale-out additively. You'll need the aggregate insertion throughput of several nodes for many live sensor data sources, never mind trying to load several petabytes of historical data in a reasonable amount of time. Second, by implication, the workload is far too large for an in-memory database -- those online indexes you are dynamically constructing need to be on disk and distributed. Indexing several columns through disk storage at wire speed under these parameters is not something either MemSQL or SQL Server (or any database really) is designed to do, which was my point. Being in-memory often has few implications for query performance in well-designed parallel database kernels; on modern hardware, storage I/O is rarely the throughput bottleneck for mixed workloads if you are doing everything else optimally. The core problem is that online disk-backed secondary index construction in a distributed environment that can use the full bandwidth of the switch fabric isn't feasible. A modern storage engine and scheduler will get you the raw throughput in most cases but that is a separate question from how do you support low-latency queries at the same time. Like I alluded to with time-series databases, the way you avoid the low throughput of index construction is by not constructing indexes -- other, sometimes exotic, selectivity mechanism exist that have orders of magnitude lower construction cost than a classic index while providing relatively efficient search. Even if those search mechanisms are slightly less selective than a traditional index, you make up for it with the intrinsically higher throughput of those mechanisms. re: time-series, I don't doubt that you can do sub-second queries on a time-series table with a trillion rows -- I've done it with tens of trillions of rows in a single table. Time-series is one of the easiest data models to scale if you have a modicum of cleverness. The challenge is ensuring this query performance while inserting tens of millions of records per second into the index at the same time and/or you need to index more dimensions than a timestamp.