6 ms·

This is such a great post! Soooo many databases have implemented some kind of “column store extension” where they change the on disk representation and declare

This is such a great post! Soooo many databases have implemented some kind of “column store extension” where they change the on disk representation and declare “look now we’re a hybrid database”. But the academic literature suggests that the bigger win for column stores may be in the execution layer. I love that Cockroach has taken the opposite of the usual approach and been so rigorous in measuring everything. After doing all this work, is it your opinion that (hypothetically) a columnar on-disk representation would be a bigger or smaller win than a fully-built-our vectorized execution engine?

manigandham 7y ago
> "data needs to be read from disk in its original row-oriented format before processing, which will account for the lion’s share of the query’s execution latency." IO is still a bottleneck without column-stores. The selectivity, compression and encoding alone can generate massive speedups because there's less data to process, and less to move through the execution pipeline. But batch/vectorized processing on row-stores is gaining adoption. MemSQL and SQL Server also use similar techniques.
rafiss 7y ago
Thanks! I do think that a columnar on-disk representation would likely help with large analytical queries. But I would imagine they would negatively affect performance of point transactions in an OLTP workload. Note that this vectorized engine we built will only be used on a given query if our SQL planner estimates that a large number of rows will be read.
asubiotto 7y ago
It's hard to say whether one would generally be a bigger gain than another without experimenting. We chose to focus on just execution because it was a relatively smaller project (with less side-effects) that still promised a large performance improvement. In the future, we might consider keeping data in columnar format on learner replicas to offer even better performance for users that would like to run OLAP-style queries on slightly stale data, but this would be a larger project.
jandrewrogers 7y ago
This is a complicated discussion because the relative performance is greatly impacted by design and implementation details. Broadly speaking, the execution engine and page representation designs are tightly coupled for performance optimization purposes. The kinds of optimizations outlined at the link will have a high return for most kinds of databases. The term "columnar" covers a diverse set of architectures with very different operational characteristics. For OLTP (like CockroachDB), using a classic DSM-style columnar representation is going to offer poor write performance no matter what you do with the execution engine. On the other hand, if you are using one of the newer vectorized page representations (VSM) that are popular for mixed workloads, which are quasi-columnar but not DSM (nor one of the intra-page DSM hybrids like PAX), the loss of write performance may be minimal versus a classic row store (NSM) but with much faster query processing (faster than DSM for some types of queries). The execution engine design you would attach to any of these models is pretty different. Note also that a practical limit on this kind of optimization is code complexity. While VSM-style on-disk representation and matching execution engine sounds like a nearly optimal hybrid of both NSM for write performance and DSM for query performance, an implementation that is general purpose and performs well across a diverse set of data models is massively more difficult and complex to build in practice so most database designers avoid it at all costs due to the engineering overhead. These tend to be more common when the set of supported data models are very limited at design time. It is a research area that still offers a lot of opportunity -- the literature mostly ignores parts of the productive design space that intrinsically have extremely high implementation complexity (too difficult to produce code in support of paper publication).

manigandham 7y ago

> "data needs to be read from disk in its original row-oriented format before processing, which will account for the lion’s share of the query’s execution latency." IO is still a bottleneck without column-stores. The selectivity, compression and encoding alone can generate massive speedups because there's less data to process, and less to move through the execution pipeline. But batch/vectorized processing on row-stores is gaining adoption. MemSQL and SQL Server also use similar techniques.

rafiss 7y ago

Thanks! I do think that a columnar on-disk representation would likely help with large analytical queries. But I would imagine they would negatively affect performance of point transactions in an OLTP workload. Note that this vectorized engine we built will only be used on a given query if our SQL planner estimates that a large number of rows will be read.

asubiotto 7y ago

It's hard to say whether one would generally be a bigger gain than another without experimenting. We chose to focus on just execution because it was a relatively smaller project (with less side-effects) that still promised a large performance improvement. In the future, we might consider keeping data in columnar format on learner replicas to offer even better performance for users that would like to run OLAP-style queries on slightly stale data, but this would be a larger project.

jandrewrogers 7y ago

This is a complicated discussion because the relative performance is greatly impacted by design and implementation details. Broadly speaking, the execution engine and page representation designs are tightly coupled for performance optimization purposes. The kinds of optimizations outlined at the link will have a high return for most kinds of databases. The term "columnar" covers a diverse set of architectures with very different operational characteristics. For OLTP (like CockroachDB), using a classic DSM-style columnar representation is going to offer poor write performance no matter what you do with the execution engine. On the other hand, if you are using one of the newer vectorized page representations (VSM) that are popular for mixed workloads, which are quasi-columnar but not DSM (nor one of the intra-page DSM hybrids like PAX), the loss of write performance may be minimal versus a classic row store (NSM) but with much faster query processing (faster than DSM for some types of queries). The execution engine design you would attach to any of these models is pretty different. Note also that a practical limit on this kind of optimization is code complexity. While VSM-style on-disk representation and matching execution engine sounds like a nearly optimal hybrid of both NSM for write performance and DSM for query performance, an implementation that is general purpose and performs well across a diverse set of data models is massively more difficult and complex to build in practice so most database designers avoid it at all costs due to the engineering overhead. These tend to be more common when the set of supported data models are very limited at design time. It is a research area that still offers a lot of opportunity -- the literature mostly ignores parts of the productive design space that intrinsically have extremely high implementation complexity (too difficult to produce code in support of paper publication).