June 2019 Summaries

CockroachDB utilizes a tool called SQLsmith for randomized SQL testing to automate the discovery of bugs that are difficult to identify through manual testing. Inspired by the C compiler tester Csmith, SQLsmith generates type and column-aware SQL queries, effectively testing the execution logic of CockroachDB and finding over 40 bugs that previous tools missed. Initially, CockroachDB's random testing began with Go fuzzing, which evolved into a more sophisticated SQL-aware fuzzer that generates valid SQL tokens, though issues with semantic checking persisted. SQLsmith, initially tailored for Postgres, was adapted into Go to integrate better with CockroachDB's existing systems and has since uncovered various bugs, including panics and logic errors. While SQLsmith's primary role is to expose panics rather than correctness issues, it excels at identifying unexpected interactions within SQL features. Future work includes refining SQLsmith to compare CockroachDB and Postgres outputs and expanding its application to other database layers, ultimately increasing confidence in deploying new features or refactoring existing ones.

CockroachDB's implementation of query plan caching aims to optimize query performance by reducing planning times while maintaining the quality of execution plans. Since the 2.1 release, a cost-based optimizer has been in place, performing more work and thus requiring longer planning times, which led to the development of caching strategies. The planning process involves several stages, such as parsing, semantic analysis, normalization, exploration, and execution building, each contributing significantly to planning time. CockroachDB uses different methods for issuing queries, including the simple and extended protocols, with the latter being more secure but less efficient due to its multi-step process. The caching work focused initially on the prepare-once method, which is the most efficient, and later extended to other methods. Despite challenges like the inability to reuse certain stages due to changes in database schema or the presence of placeholders, the introduction of caching has resulted in significant planning time reductions, especially for frequently executed queries. Future improvements may involve optimizing the reuse of plans with different values, enhancing user control over query plans, and potentially parameterizing queries to further streamline the process.

The blog post by George Utsin discusses the vectorization of the merge join operator in CockroachDB, a SQL database designed for global business applications. The traditional merge join algorithm, which efficiently processes sorted data, often underperforms due to the overhead of its integration into a database system. To address this, the vectorized approach processes data a column at a time, reducing the need for repeated type checks and conversions, thereby boosting efficiency. CockroachDB's vectorized merge join operator incorporates probing and building phases to handle data one column at a time, optimizing performance and supporting various join types and data types. The vectorized merge joiner has demonstrated a significant performance improvement, offering up to a 20x increase in speed for certain queries compared to the traditional row-by-row approach. This development moves CockroachDB closer to a production-ready vectorized execution engine, freeing up CPU resources for other queries.

CockroachDB, originally an open source software, is shifting its licensing model to address challenges from major tech companies offering its product as a service. The company is adopting the Business Source License (BSL) to prevent commercial use of CockroachDB as a database-as-a-service (DBaaS) without a license, while allowing free use for other purposes. This license will initially restrict commercial DBaaS offerings for three years post-release, after which the code will revert to the Apache 2.0 open source license. The decision was made in response to competitors like Amazon, who have utilized open source software to create their own services, and aims to balance open source principles with business needs. CockroachDB's new license model will apply from version 19.2 onwards, with enterprise features continuing under a separate Cockroach Community License (CCL). Despite not being entirely open source during the three-year restriction period, the approach is intended to support an open core while ensuring business viability.