Deductive Optimization of Relational Data Storage

TL;DR

This paper introduces a language for expressing diverse physical database layouts and uses deductive synthesis to optimize query execution, achieving competitive performance with state-of-the-art in-memory systems.

Contribution

It presents a novel language and compiler framework for optimizing relational data storage layouts beyond traditional methods.

Findings

Optimized queries perform competitively with leading in-memory database systems.

The language supports a wide range of physical data layouts.

Experimental results validate the effectiveness of the approach.

Abstract

Optimizing the physical data storage and retrieval of data are two key database management problems. In this paper, we propose a language that can express a wide range of physical database layouts, going well beyond the row- and column-based methods that are widely used in database management systems. We use deductive synthesis to turn a high-level relational representation of a database query into a highly optimized low-level implementation which operates on a specialized layout of the dataset. We build a compiler for this language and conduct experiments using a popular database benchmark, which shows that the performance of these specialized queries is competitive with a state-of-the-art in memory compiled database system.

Tables

Table 1. Table 1. Runtime of queries derived from TPC-H (ms). Memory use is the peak resident set size during a query (Mb). Size is the layout size (Mb).

	Hyper				Castor (Expert)			Castor (Optimizer)
Q#	Time3	Time2	Mem.	Size	Time2	Mem.	Size	Time2	Mem.	Size
1	19.00	0.12	15.2	17.8	0.04	2.3	0.1	0.03	2.2	0.1
2 0	5.00	10.52	238.0	206.6	0.71	10.4	41.8	0.08	2.9	102.8
3 0 1	17.00	22.30	877.1	966.8	4.73	18.3	81.0	4.98	18.4	81.0
4	8.00	0.04	17.0	17.8	¡0.01	2.4	0.2	¡0.01	2.2	0.2
5	11.00	3.87	26.0	24.1	¡0.01	2.5	1.2	0.04	3.0	3.8
6	12.00	11.75	900.5	858.8	5.69	7.2	31.0	—	—	—
7	12.00	0.01	16.3	17.8	¡0.01	2.3	¡0.1	0.34	2.5	0.2
8	7.00	17.35	529.5	375.4	7.33	15.4	67.1	0.02	2.5	29.4
9	31.00	40.60	1580.2	1550.8	54.08	358.6	365.0	241.00	317.6	323.0
10 0 1	17.00	¡0.01	116.8	112.2	7.82	34.5	33.1	38.10	227.2	230.6
11	8.00	16.07	89.5	68.2	0.15	2.6	5.3	—	—	—
12	8.00	0.08	15.4	17.8	¡0.01	2.3	0.2	0.24	3.8	148.3
14	4.00	¡0.01	16.0	17.8	¡0.01	2.2	¡0.1	¡0.01	2.3	¡0.1
15	13.00	¡0.01	14.7	17.8	¡0.01	2.2	0.2	—	—	—
16 1	38.00	3.19	32.7	35.7	1.01	5.8	3.9	—	—	—
17	11.00	7.43	1238.2	1224.7	0.04	5.0	50.8	¡0.01	2.4	1.6
18 0	42.00	12.47	388.7	295.7	39.18	73.9	73.5	—	—	—
19	28.00	0.02	19.5	17.8	0.10	2.4	0.2	—	—	—

Equations

$$\text{select}p.enter,c.enter\text{from}log\text{as}p,log\text{as}c\text{where}$$

$$p.enter<c.enter\wedge c.enter<c.exit\wedge p.id=\$pid\wedge c.id=\$cid$$

$$\text{select}(\{ente{r}_p,ente{r}_c\},\text{join}(ente{r}_p<ente{r}_c\wedge ente{r}_c<exi{t}_p,$$

$$\text{filter}(\$pid=i{d}_p,$$

$$\text{select}(\{id\mapsto i{d}_p,enter\mapsto ente{r}_p,exit\mapsto exi{t}_p\},log)),$$

$$\text{filter}(\$cid=i{d}_c,\text{select}(\{id\mapsto i{d}_c,enter\mapsto ente{r}_c\},log))))$$

$$\text{select}(\{ente{r}_p,ente{r}_c\},\text{filter}(\$pid=i{d}_p\wedge \$cid=i{d}_c,$$

$$\text{join}(ente{r}_p<ente{r}_c\wedge ente{r}_c<exi{t}_p,$$

$$\text{select}(\{id\mapsto i{d}_p,enter\mapsto ente{r}_p,exit\mapsto exi{t}_p\},log),$$

$$\text{select}(\{id\mapsto i{d}_c,enter\mapsto ente{r}_c\},log))))$$

$$\text{select}(\{ente{r}_p,ente{r}_c\},\text{filter}(i{d}_c=\$cid\wedge i{d}_p=\$pid,$$

$$\text{list}(\text{select}(\{id\mapsto i{d}_p,enter\mapsto ente{r}_p,exit\mapsto exi{t}_p\},log)\text{as}lp,$$

$${\text{tuple}}_{\text{cross}}([\text{scalar}(lp.i{d}_p),\text{scalar}(lp.ente{r}_p),$$

$$\text{list}(\text{filter}(lp.ente{r}_p<ente{r}_c\wedge ente{r}_c<lp.exi{t}_p,$$

$$\text{select}(\{id\mapsto i{d}_c,enter\mapsto ente{r}_c\},log))\text{as}lc,$$

$${\text{tuple}}_{\text{cross}}([\text{scalar}(lc.i{d}_c),\text{scalar}(lc.ente{r}_c)]))]))))$$

$$\text{select}(\{ente{r}_p,ente{r}_c\},\text{depjoin}(\text{hash-idx}(\text{select}(\{id\},log)\text{as}h,$$

$$\text{list}(\text{filter}(h.id=id\wedge enter>exit,log)\text{as}lh,$$

$${\text{tuple}}_{\text{cross}}([\text{scalar}(lh.enter\mapsto ente{r}_p),\text{scalar}(lh.exit)])),$$

$$\$pid)\text{as}p,$$

$$\text{filter}(id=\$cid,\text{order-ed-idx}(\text{select}(\{enter\},log)\text{as}o,$$

$$\text{list}(\text{filter}(enter=o.enter,log)\text{as}lo,$$

$${\text{tuple}}_{\text{cross}}([\text{scalar}(lo.id),\text{scalar}(lo.enter\mapsto ente{r}_c)])),$$

$$p.ente{r}_p,p.exit))))$$

$$x$$

$$v$$

$$e$$

$$e\ge e^{\prime }|e=e^{\prime }|\text{if}e\text{then}{e}_t\text{else}{e}_f|\text{exists}(q)|\text{first}(q)|\text{count}()$$

$$\text{sum}(e)|\text{min}(e)|\text{max}(e)|\text{avg}(e)$$

$$t$$

$$q$$

$$\text{group-by}(t,[x_1,\dots ,x_m],q)|\text{order-by}([e_1{o}_1,\dots ,e_m{o}_m],q)$$

$$\text{depjoin}(q\text{as}x,q^{\prime })|\text{scalar}(x\mapsto e)|{\text{tuple}}_{\tau }(t)|\text{list}(q_r\text{as}x,q)$$

$$\text{hash-idx}(q_k\text{as}x,q_v,t_k)|\text{order-ed-idx}(q_k\text{as}x,q_v,t_{lo},t_{hi})|\varnothing$$

$$Id=(Scope?,Name)Context=Tuple=\{Id\mapsto Value\}$$

$$Relation=[Tuple]$$

$$\sigma :Context\delta :Id\mapsto Relations:Idt:Tuplev:Valuer:Relation$$

$$\text{inference}\forall n.n\notin \sigma \vee n\notin {\sigma }^{\prime }\sigma \cup {\sigma }^{\prime }=\{n_i\mapsto v_i|\sigma [n_i]=v_i\vee {\sigma }^{\prime }[n_i]=v_i\}$$

$$\inference{t=\{n_{1}\mapsto e_{1},\dots,n_{m}\mapsto e_{m}\}&\forall i.\ \sigma,\delta|-e_{i}\Downarrow v_{i}}{\sigma,\delta|-t\Downarrow\{n_{1}\mapsto v_{1},\dots,n_{m}\mapsto v_{m}\}}$$

$$\text{inference}\delta [n]=r\sigma ,\delta |-\text{relation}(n)\Downarrow r$$

$$\begin{gathered} \text{inference}\sigma ,\delta |-q\Downarrow r_q \\ r=[t|t<-r_q\sigma \cup t,\delta |-e\Downarrow \text{true}]\sigma ,\delta |-\text{filter}(e,q)\Downarrow r \end{gathered}$$

$$\inference{\sigma,\delta|-q\Downarrow r&r^{\prime\prime}=\left[t^{\prime}~{}\Big{|}~{}{\begin{array}[]{c}t<-r,\ t^{\prime}<-r^{\prime}\quad\sigma\cup t_{s},\delta|-q^{\prime}\Downarrow r^{\prime}\\ t_{s}=\{s.f\mapsto v~{}|~{}(f\mapsto v)\in t\}\end{array}}\right]}{\sigma,\delta|-\textsf{depjoin}(q\ \textsf{as}\ s,q^{\prime})\Downarrow r^{\prime\prime}}$$

$$\text{inference}\sigma ,\delta |-e\Downarrow v\sigma ,\delta |-\text{scalar}(n\mapsto e)\Downarrow [\{n\mapsto v\}]$$

$$\text{inference}\sigma ,\delta |-\varnothing \Downarrow []$$

$$\text{inference}\sigma ,\delta |-\text{depjoin}(q_r\text{as}s,q)\Downarrow r\sigma ,\delta |-\text{list}(q_r\text{as}s,q)\Downarrow r$$

Full text

††conference: ACM SIGPLAN Conference on Programming Languages; January 01–03, 2018; New York, NY, USA††journalyear: 2018††doi: ††copyright: none††ccs: Software and its engineering General programming languages††ccs: Social and professional topics History of programming languages

1. Introduction

Traditional database systems are generic and powerful, but they are not well optimized for static databases. A static database is one where the data changes slowly or not at all and the queries are fixed. These two constraints introduce opportunities for aggressive optimization and specialization. This paper introduces Castor: a domain specific language and compiler for building static databases. Castor achieves high performance by combining query compilation techniques from state-of-the-art in-memory databases (Neumann, 2011) with a deductive synthesis approach for generating specialized data structures.

To better understand the scenarios that Castor supports, consider these two use cases. First, consider a company which maintains a web dashboard for displaying internal analytics from data that is aggregated nightly. The queries used to construct the dashboard cannot be precomputed directly, because they contain parameters like dates or customer IDs, but there are only a few query templates. Not all the data in the original database is needed, and some attributes are only used in aggregates. As another example, consider a company which is shipping a GPS device that contains an embedded map. The map data is infrequently updated, and the device queries it in only a few specific ways. The GPS manufacturer cares more about compactness and efficiency than about generality. As with the company building the dashboard, it is desirable to produce a system that is optimal for the particular dataset to be stored.

These two companies could use a traditional database system, but using a system designed to support arbitrary queries will miss important optimization opportunities. Alternatively, they could write a program using custom data structures. This will give them tight control over their data layout and query implementation but will be difficult to develop and expensive to maintain.

Castor is an attempt to capture some of the optimization opportunities of static databases and to address the needs of these two scenarios. As Figure 1 illustrates, the input to Castor is a dataset and a parameterized query that a client will want to invoke on the data. The user then interacts with Castor using high-level commands to generate an efficient implementation of an in-memory datastore specialized for the dataset and the parameterized query. The commands available in Castor give the programmer tight control over the exact organization of the data in memory, allowing the user to trade off memory usage against query performance without the risk of introducing bugs. Castor also uses code generation techniques from high-performance in-memory databases to produce the low-level implementations required for efficient execution. The result is a package of data and code that uses significantly less memory than the most efficient in-memory databases and for some queries can even surpass the performance of in-memory databases that already rely on aggressive code generation and optimization (Neumann, 2011).

1.1. Contributions

Castor is made possible by three major technical contributions: a new notation to jointly represent the layout of the data in memory and the queries that will be computed on it, a set of deductive optimization rules that generalize traditional query optimization rules to jointly optimize the query and the data layout, and a type-driven layout compiler to produce both a binary representation of the data from the high-level data representation and specialized machine code for accessing it.

Integrated Layout & Query Language

We define the layout algebra, which extends the relational algebra (Codd, 1970) with layout operators that describe the particular data items to be stored and the layout of that data in memory. The layout algebra is flexible and can express many layouts, including row stores and clustered indexes. It supports nesting layouts, which gives control over data locality and supports prejoining of data. Our use of a language which combines query and layout operators makes it possible to write deductive transformations that change both the runtime query behavior and the data layout.

Deductive Optimization Rules

Castor provides a set of equivalence preserving transformations which can change both the query and the data layout. The user can apply these transformations to deductively optimize their query without worrying about introducing bugs. Alternatively, they can use Castor’s optimizer, which automatically selects a sequence of transformations. Castor’s specialized notation turns transformations that would be complex and global in other database systems into local syntactic changes.

Type-driven Layout Compiler

Existing relational synthesis tools use standard library data structures and make extensive use of pointer based data structures that hurt locality (Loncaric et al., 2018, 2016; Hawkins et al., 2011). Castor uses a specializing layout compiler that takes the properties of the data into account when serializing it. Before generating the layout, Castor generates an abstraction called a layout type which guides the layout specialization. For example, if the layout is a row-store with fixed-size tuples, the layout compiler will not emit a length field for the tuples. Instead, this length will be compiled directly into the query. This specialization process creates very compact datasets and avoids expensive branches in generated code.

High Performance Query Compiler

Castor uses code generation techniques from the high performance in-memory database literature (Neumann, 2011; Shaikhha et al., 2016; Tahboub et al., 2018; Rompf and Amin, 2015). It eschews the traditional iterator based query execution model (Graefe, 1994) in favor of a code generation technique that produces simple, easily optimized low-level code. Castor directly generates LLVM IR and augments the generated IR with information from the layout type that allows LLVM to further optimize it.

Empirical Evaluation

We empirically evaluate Castor on a benchmark derived from TPC-H, a standard database benchmark (Council, 2008). We show that Castor is competitive with the state of the art in-memory compiled database system Hyper (Neumann, 2011) while using significantly less memory. We also show that Castor scales to larger queries than the leading data-structure synthesis tool Cozy (Loncaric et al., 2018).

1.2. Limitations

Castor constructs read-only databases. This design decision limits the appropriate use cases for Castor but it enables important optimizations. Castor takes advantage of the absence of updates to tightly pack data together, which improves locality. Castor also aggressively specializes the compiled query by including information about the layout, such as lengths of arrays and offsets of layout structures. Providing this information to the compiler improves the generated code.

Only one parameterized query can be optimized at once. This is a limitation, but it is not a serious one. A multi-query workload can be supported by replicating the dataset and optimizing each query separately. Castor removes any data which is not needed by the query and it produces compact layouts for the data that remains. This reduces the overhead of the replication.

2. Motivating Example

We now describe the operation of Castor on an application from the software engineering literature. DemoMatch is a tool which helps users understand complex APIs using software demonstrations (Yessenov et al., 2017). DemoMatch maintains a database of program traces—computed offline—which it queries to discover how to use an API. DemoMatch is a good fit for Castor. The data in question is largely static: computing new traces is an infrequent task. The data is automatically queried by the tool, so there is no need to support ad-hoc queries. Finally, query performance is important for DemoMatch to work as an interactive tool.

2.1. Background

DemoMatch stores program traces as ordered collections of events (e.g., function calls). Traces have an inherent tree structure: each event has an enter and an exit and nested events may occur between the enter and exit.

A critical query in the DemoMatch system finds nested calls to particular functions in a trace of program events:

[TABLE]

We refer to the caller as the parent function and the callee as the child function. Let $p$ and $c$ be the traces of events inside the parent and child function bodies respectively. The join predicate $p.enter<c.enter\land c.enter<p.exit$ selects calls to the child function from inside the parent function. The predicate p.id=\pid\land c.id=$cid$selects the pair of functions that we are interested in, where$$pid$and$$cid$ are parameters.

2.2. The Layout Algebra

Castor programs are written in a language called the layout algebra. The layout algebra is similar to the relational algebra, but as we will see shortly, it can represent the layout of data as well as the operation of queries. By design, it is more procedural than SQL, which is more akin to the relational calculus (Codd, 1971). For example, SQL leaves choices like join ordering to the query planner, whereas in the layout algebra join ordering is explicit.

In designing the layout algebra, we follow a well-worn path in deductive synthesis of creating a uniform representation that can capture all the refinement steps from a high-level program to a low-level one. Accordingly, the layout algebra can express programs which contain a mixture of high-level relational constructs and low-level layout constructs. At some point, a layout algebra program contains enough implementation information that the compiler can process it. We say that these programs are serializable (Sec. 3.3).

Here is the nested call query from Sec. 2.1 translated into the layout algebra:

[TABLE]

This program can be read as follows. filter takes a predicate as its first argument and a query as its second. It filters the query by the predicate. join takes a predicate as its first argument and queries as its second and third. The two queries are joined together using the predicate. select takes a list of expressions with optional names and a query, and selects the value of each expression for each tuple in the query, possibly renaming it.

The scoping rules for the layout algebra may look somewhat unusual, but they are intended to mimic the scoping conventions of SQL. In this query, the names $enter$, $exit$ and $id$ are field names in the $log$ relation. The select operators introduce new names for these fields, using the $\mapsto$ operator, so that $log$ can be joined with itself. We formalize the semantics of the layout algebra, including the scoping rules, in Sec. 3.2.

Note that at this point no layout is specified for $log$, so this is not a serializable program. Even though it is not serializable (and so cannot be compiled), this program still has well defined semantics. In Sections 2.4 to 2.6, we describe how layouts can be incrementally introduced by transforming the program until it is serializable.

2.3. Optimization Trade-offs

The nested call query is interesting because the data in question is fairly large—hundreds of thousands of rows—and keeping it fully in memory, or even better in cache, is a significant performance win. Therefore, minimizing the size of the data in memory should improve performance.

However, there is a fundamental trade-off between a more compact data representation and allowing for efficient access. Sometimes the two goals are aligned, but often they are not. For example, creating a hash index allows efficient access using a key, but introduces overhead in the form of a mapping between hash keys and values.

In the rest of this section we examine three layouts at different points in this trade-off space: a compact nested layout with no index structures (Figure 4(a)), a layout based on a single hash index (Figure 4(b)), and a layout based on a hash index and an ordered index (Figure 4(c)). A priori, none of these layouts is clearly superior. The hash based layout is the largest, but has the best lookup properties. The nested layout precomputes the join and uses nesting to reduce the result size, but is more expensive for lookups. The last layout must compute the join at runtime but it has indexes that will make that computation fast. The power of Castor is that it allows users to effectively explore different layout trade-offs by freeing them from the necessity to ensure the correctness of each candidate.

2.4. Nested Layout

Our first approach to optimizing the nested call query is to materialize the join, since joins are usually expensive, and to use nesting to reduce the size of the resulting layout.

The first step is to apply transformation rules (Sec. 4.2) to hoist and merge the filters. Now the join is in a term with no query parameters, so it can be evaluated at compile time:

[TABLE]

After applying two more rules—projection to eliminate unnecessary fields (Sec. 4.3) and join elimination (Sec. 4.6)—the result is the layout expression represented in code in Figure 3 and graphically in Figure 4(a). In this program we see our first layout operators: $\textsf{{list}}{}(\cdot,\cdot)$ and $\textsf{{tuple}}_{\textsf{cross}}([\dots])$.111As a point of notation, we separate layout operators from non-layout operators visually by bolding them. This is just to make the programs easier to read. The layout algebra extends the relational algebra with these operators, allowing us to write layout expressions, which describe how their arguments will be laid out in memory.

Figure 3 can be read as follows. The operator $\textsf{{list}}{}(log\ \textsf{as}\ l,q)$ creates a list with one element for every tuple in $log$. Each element in the list is laid out according to $q$. For example, the outermost list in Figure 3 creates a list from the $id$, $enter$ and $exit$ fields of the $log$ relation. Each row in the list is laid out as a $\textsf{{tuple}}_{\textsf{cross}}$222In this expression, cross specifies how the tuple will eventually be read. Layout operators evaluate to sequences, so a tuple needs to specify how these sequences should be combined. In this case, we take a cross product.. The first two elements in the tuple are the scalar representations of the $id_{p}$ and $enter_{p}$ fields, and the third element is a nested list. Note that the content of that inner list is filtered based on the value of $lp.enter_{p}$ and $lp.exit_{p}$, and each element laid out as just a pair of two scalars $id_{c}$ and $enter_{c}$.

The query is now serializable because it satisfies a set of rules described in Sec. 3.3. At a high-level, the rules require that we never use a relation without specifying its layout, a requirement that in this case is satisfied because all references to the original log relation appear in the first arguments of list operators.

Figure 4(a) shows the structure of the resulting layout. This layout is quite compact. It is smaller than the fully materialized join because of the nesting; the caller id and enter fields are only stored once for each matching callee record. When we benchmark this query, we find found that it performs reasonably well (11.5ms) and is fairly small (50Mb).

We can make this layout more compact by applying further transformations. For example, we know that $enter_{p}<enter_{c}<exit_{p}$. If we store $enter_{c}-enter_{p}$ instead of $enter_{c}$ we can save some space by using fewer bits. The ability to take advantage of this kind of knowledge about the structure of the data is an important feature of our approach.

2.5. Hash-index Layout

Now we optimize for lookup performance by fully materializing the join and creating a hash index. This layout will be larger than the nested layout but look ups into the hash index will be quick, which will make evaluating the equality predicates on $id$ fast. Figure 4(b) shows the structure of the resulting layout. When we evaluate the query, we find that it is much faster (0.4ms) but is larger than the nested query (60Mb).

2.6. Hash- and Ordered-index Layout

Finally, we investigate a layout which avoids the full join materialization, but still has enough indexing to be fast. We can see that the join condition is a range predicate, so we would like to use an index that supports efficient range queries to make that predicate efficient (Sec. 4.5). Then we can push the filters and introduce a hash table to select $id_{p}$. The resulting layout is shown in Figure 4(c). This layout will be larger than the original relation, but smaller than the other two layouts (9.8Mb), and it allows for much faster computation of the join and one of the filters (0.6ms).

This program introduces three new operators: ordered-idx, hash-idx and depjoin. ordered-idx creates indexes that support efficient range queries. Its first argument defines the set of keys and its second argument defines the values and their layout as a function of the keys. The remaining arguments are the upper and lower bounds to use when reading the index ($p.enter_{p}$ and $p.exit$ in this case). hash-idx is similar, but it creates efficient point indexes (in this query, \pid$ is the key).

[TABLE]

The more interesting operator is depjoin, which implements a dependent join. A dependent join is one where the right-hand-side of the join can refer to fields from the left-hand-side. In the depjoin operator, the left-hand-side is given a name (here it is $p$) that the right-hand-side can use to refer to its fields. One way to think about a dependent join is as a relational for loop: it evaluates the right-hand-side for each tuple in the left-hand-side, concatenating the results. Unlike the layout operators list, hash-idx and ordered-idx, depjoin executes entirely at runtime. It does not introduce any layout structure.

3. Language

In this section we describe the layout algebra in detail. The layout algebra starts with the relational algebra and extends it with layout operators. These layout operators have relational semantics, but they also have layout semantics which describes how to serialize them to data structures. The combination of relational and layout operators allows the layout algebra to express both a query and the data store that supports the execution of the query.

Programs in the layout algebra have three semantic interpretations:

1. (1)

The relational semantics describes the behavior of a layout algebra program at a high level. We define this semantics using a theory of ordered finite relations (Cheung et al., 2013). According to this semantics, A layout algebra program can be evaluated to a relation in a context containing relations and query parameters. 2. (2)

The layout semantics describes how the compiler creates a data file from a serializable layout algebra program. The layout semantics operates in a context which contains relations, but not query parameters. 3. (3)

The runtime semantics describes how the compiled query executes, reading the layout file and using the query parameters to produce the query output. The runtime semantics operates in a context which contains query parameters but not relations.

These three semantics are connected: the layout semantics and the runtime semantics combine to implement the relational semantics. The relational semantics serves as a specification. An interpreter written according to the relational semantics should execute layout algebra programs in the same way as our compiler. In this section, we discuss the relational semantics in detail. We leave the detailed discussion of the layout and runtime semantics to Appendix A and Appendix B.

3.1. Syntax

Figure 5 shows the syntax of the layout algebra. Note that the layout algebra can be divided into relational operators (select, filter, join, etc.) and layout operators (list, hash-idx, etc.). The layout algebra is a strict superset of the relational algebra. In fact, the layout operators have relational semantics in addition to byte-level data layout semantics (see Sec. 3.2.2).

3.2. Semantics

The semantics (Figure 6) operates on three kinds of values: scalars, tuples and relations. Scalars are values like integers, Booleans, and strings. Tuples are finite mappings from field names to scalar values. Relations are represented as finite, ordered sequences of tuples. $[~{}]$ stands for the empty relation, $:$ is the relation constructor, and $\mathbin{+\!\!+}$ denotes the concatenation of relations.

We use sequences instead of sets for two reasons. First, sequences are more like bag semantics than the set semantics of the original relational algebra. This choice brings the layout algebra more in line with the semantics of SQL, which is convenient for our implementation. Second, sequences allow us to represent query outputs which have an ordering.

In the semantic rules, $\sigma$ is an evaluation context; it maps names to scalar values. $\delta$ is a relational context; it maps names to relations. We separate the two contexts because the relational context $\delta$ is global and immutable; it consists of a universe of relations that exist when the query is executed (or compiled) which are contained in some other database system. The evaluation context $\sigma$ initially contains the query parameters, but some operators introduce new bindings in $\sigma$. $\cup$ denotes the binding of a tuple into an evaluation context. Read $\sigma\cup t$ as a new evaluation context that contains the fields in $t$ in addition to the names already in $\sigma$.

In the rules, $|-$ separates contexts and expressions and $\Downarrow$ separates expressions and results. Read $\sigma,\delta|-l\Downarrow s$ as “the layout $l$ evaluates to the relation $s$ in the context $\sigma,\delta$.”

We borrow the syntax of list comprehensions to describe the semantics of the layout algebra operators. For example, consider the list comprehension in the filter rule: $[t~{}|~{}t<-r_{q},(\sigma\cup t,\delta|-e\Downarrow\textsf{true})]$, which corresponds to the expression $\textsf{filter}(e,q)$. This list comprehension filters $r_{q}$ by the predicate $e$ where $r_{q}$ is the relation produced by $q$. $e$ is evaluated in a context $\sigma\cup t$ for each tuple $t$ in $r_{q}$.

Comprehensions that contain multiple $<-$, as in the join rule, should be read as a cross product.

3.2.1. Relational Operators

First, we describe the semantics of the relational operators: relation, filter, join, select, group-by, orderby, dedup, and depjoin. These operators are modeled after their equivalent SQL constructs. For brevity and because they are straightforward, we omit the rules for selection with aggregates, group-by, and order-by from Figure 6.

relation returns the contents of a relation in the relational context $\delta$. filter filters a relation by a predicate $e$. join takes the cross product of two relations and filters it using a predicate $e$.

select is used for projection, aggregation, and renaming fields. It takes a tuple expression $t$ and a relation $r$. If $t$ contains no aggregation operators, then a new tuple will be constructed according to $t$ for each tuple in $r$. If $t$ contains an aggregation operator (count, sum, min, max, avg), then select will aggregate the rows in $r$. If $t$ contains both aggregation and non-aggregation operators, then the non-aggregation operators will be evaluated on the last tuple in $r$.

group-by takes a list of expressions, a list of fields, and a relation. It groups the tuples in the relation by the values of the fields, then computes the aggregates in the expression list. order-by takes a list of expression-order pairs and a relation. It orders the tuples in the relation using the list of expressions to compute a key. dedup removes duplicate tuples.

Finally, depjoin denotes a dependent join, where the right-hand-side of the join can depend on values from the left-hand-side. It is similar to a for-each loop; $\textsf{depjoin}(q\ \textsf{as}\ n,\ q^{\prime})$333In this expression, $n$ is a scope, and it qualifies the names in $q$. Scopes are discussed in more detail in Sec. 3.2.3. can be read “evaluate $q^{\prime}$ for each tuple in $q$ and concatenate the results.” We use depjoin as a building block to define the semantics of the layout operators.

3.2.2. Layout Operators

We extend the relational algebra with layout operators that specify the layout of data in memory at a byte level. The nesting and ordering of the layout operators correspond to the nesting and ordering of the data structures that they represent. Nesting allows data that is accessed together to be stored together, increasing spatial locality. Note that layout operators can capture the results of executing common relational algebra operations such as joins or selections, allowing query processing to be performed at compile time. In addition, layout primitives can express common relational data storage patterns, such as row stores and clustered indexes.

Castor supports the following data structures:

Scalars: Scalars can be machine integers (up to 64 bits), strings, Booleans, and decimal fixed-point.

Tuples: Tuples are layouts that can contain layouts with different types. If a collection contains tuples, all the tuples must have the same number of elements and their elements must have compatible types. Tuples can be read either by taking the cross product or concatenating their sub-layouts.

Lists: Lists are variable-length layouts. Their contents must be of the same type.

Hash indexes: Hash indexes are mappings between scalar keys and layouts, stored as hash tables. Like lists, their keys must have the same type.

Ordered indexes: Ordered indexes are mappings between scalar keys and layouts, stored as ordered mappings.

Each data structure has a corresponding layout operator. The layout operators are the novel part of the layout algebra and their semantics are therefore non-standard. The relational semantics of the layout operators are in Figure 6. Although the layout operators can be used to construct complex, nested layouts, they evaluate to flat relations of tuples of scalars, just like the relational operators. The rules in Figure 6 only describe the relational behavior of the layout operators; they do not address the question of how data is laid out or how it is accessed. We discuss these aspects of the layout operators in Appendix A.

The simplest layout operators are scalar and tuple. The scalar represents a single scalar value. Evaluating a scalar operator produces a relation containing a single tuple. The tuple operator represents a fixed-size, heterogeneous list of layouts. When evaluated, each layout in the tuple produces a relation, which are combined either with a cross product or by concatenation.

Note that evaluating a tuple operator produces a relation not a tuple. Although these semantics are slightly surprising, there are two reasons why we chose this behavior. First, it is consistent with the other layout operators, all of which evaluate to relations. Second, tuples can contain other layouts (lists for example) which themselves evaluate to relations.

The remaining layout operators—list, hash-idx and ordered-idx—have a similar structure. We discuss the list operator in detail. Equation 8 specifies the behavior of list. Note that list is essentially an alias for depjoin. Like depjoin, list takes two arguments: $q_{r}$ and $q$. These two arguments should be interpreted as follows: $q_{r}$ describes the data in the list. Each element of the list has a corresponding tuple in $q_{r}$, so the length of the list is the same as the length of $q_{r}$. One can think of each tuple in $q_{r}$ as a kind of key that determines the contents of each list element. On the other hand, $q$ describes how each list element is laid out. $q$ will be evaluated separately for each tuple in $q_{r}$. It determines for each “key” in $q_{r}$, what the physical layout of each list element will be, as well as how that element must be read.

Returning to the query in Sec. 2.4, the inner list operator

[TABLE]

selects the tuples in $log$ where $enter_{c}$ is between $enter_{p}$ and $exit_{p}$, and creates a list of these tuples. The first argument describes the contents of the list and the second describes their layout. This program will generate a layout that has a list of tuples, structured as $[(id_{c1},enter_{c1}),\dots,(id_{cn},enter_{cn})]$.

hash-idx and ordered-idx are similar to list. They have a query $q_{k}$ that describes which keys are in the index and a query $q_{v}$ that describes the contents and layout of the values in the index. For example, in:

[TABLE]

the keys to the hash-index are the $id$ fields from the $log$ relation. For each of these fields, the index contains a list of corresponding $(enter,exit)$ pairs, stored in a tuple. When the hash-index is accessed, \pid$is used as the key. This program generates a layout of the form:${id\mapsto[(enter,exit),\dots],\dots}$, which is a hash-index with scalars for keys and lists of tuples for values.

3.2.3. Scopes & Name Binding

The scoping rules of the layout algebra are somewhat more complex than the relational algebra. There are two ways to bind a name in the layout algebra: by creating a relation or by using an operator which creates a scope.

All of the operators in the layout algebra return a relation. Some operators simply pass through the names in their parameter relations. Others, such as select and scalar can be used for renaming or for creating new fields.

Some operators, such as depjoin, create a scope. A scope is a tag which uniquely identifies the binding site of a name. For example, in $\textsf{depjoin}{}(q\ \textsf{as}\ s,q^{\prime})$, a field $f$ from $q$ is bound in $q^{\prime}$ as $s.f$. Scoped names with distinct scopes are distinct and scoped names are distinct from unscoped names. We add scopes to the layout algebra as a syntactically lightweight mechanism for renaming an entire relation. Renaming entire relations is necessary because shadowing is prohibited in the layout algebra. Prohibiting shadowing removes a major source of complexity when writing transformations. While we could use select for renaming, we opted to add scopes so that renaming at binding sites would be part of the language rather than a pervasive and verbose pattern.

There are still situations when renaming entire relations using select is necessary. For example, in a self-join one side of the join must be renamed.

3.3. Staging & Serializability

Another way to view the three semantic interpretations is from the point of view of multi-stage programming. A serializable layout algebra program can be evaluated in two stages: the layout is constructed in a compile-time stage, then the compiled query reads the layout and processes it in a run-time stage. However, while traditionally program staging is used to implement code specialization, in the layout algebra staging is used to implement data specialization. This difference in focus leads to different implementation challenges. In particular, the “unstaged” version of a layout algebra program is often large (tens to hundreds of megabytes). The layout algebra compiler must be carefully designed to handle this scale.

In Sec. 3.2.2, we explained how the layout operators execute in two stages: one stage at compile time and one stage at query runtime. Only a subset of layout algebra programs can be separated in this way. We say that programs which can be properly staged are serializable.

A program is serializable if and only if the names referred to in compile (resp. run) time contexts are bound in compile (resp. run) time contexts. An expression is in a compile-time context if it appears in the first argument to list, hash-idx, ordered-idx, or scalar. Otherwise, it is in a run-time context. We consider the relations in $\delta$ to be bound in a compile-time context and query parameters to be bound in a run-time context. The compiler uses a simple type system that tracks the stage of each name in the program to check for serializability.

Transforming a program into a serializable form is a key goal of our automatic optimizer (Sec. 6). Many of the rules that the optimizer applies can be seen as moving parts of the query between stages.

4. Transformations

In this section, we define semantics preserving transformation rules that optimize query and layout performance. These rules change the behavior of the program with respect to the layout and runtime semantics while preserving it with respect to the relational semantics. These rules subsume standard query optimizations because in addition to changing the structure of the query, they can also change the structure of the data that the query processes.

4.1. Notation

Transformations are written as inference rules. When writing inference rules, $e$ will refer to scalar expressions and $q$ will refer to layout algebra expressions. $E$ and $Q$ will refer to lists of expressions and layouts. In general, the names we use correspond to those used in the syntax description (Figure 5). If we need to refer to a piece of concrete syntax, it will be formatted as e.g., concat or x.

To avoid writing many trivial inductive rules, we define contexts. The definition is straightforward, so we leave it to the appendix (Figure 11). If $C$ is a context and $q$ is a layout algebra expression, then $C[q]$ is the expression obtained by substituting $q$ into the hole in $C$. In addition to contexts, we define two operators: $\xrightarrow{t}{}$ and $->$. $q\xrightarrow{t}{}q^{\prime}$ means that the layout algebra expression $q$ can be transformed into $q^{\prime}$ and $q->q^{\prime}$ means that $q$ can be transformed into $q^{\prime}$ in any context. The relationship between these two operators is:

[TABLE]

4.2. Relational Optimization

There is a broad class of query transformations that have been developed in the query optimization literature (Jarke and Koch, 1984; Chaudhuri, 1998). These transformations can generally be applied directly in Castor, at least to the relational operators. For example, commuting and reassociating joins, filter pushing and hoisting, and splitting and merging filter and join predicates are implemented in Castor. Although producing optimal relational algebra implementations of a query is explicitly a non-goal of Castor, these kinds of transformations are important for exposing layout optimizations.

4.3. Projection

Projection, or the removal of unnecessary fields from a query, is an important transformation because many queries only use a small number of fields; the most impactful layout specialization that can be performed for these queries is to remove unneeded fields.

First, we need to decide what fields are necessary. For a query $q$ in some context $C$, the necessary fields in $q$ are visible in the output of $C[q]$ or are referred to in $C$. Let $\textsc{schema}(\cdot)$ be a function from a layout $q$ to the set of field names in the output of $q$. Let $\textsc{names}(\cdot)$ be a function which returns the set of names in a context or layout expression. Let $\textsc{needed}(\cdot,\cdot)$ be a function from contexts $C$ and layouts $q$ to the set of necessary fields in the output of $q$:

[TABLE]

$\textsc{needed}(\cdot,\cdot)$ can be used to define transformations which remove unnecessary parts of a layout. For example, this rule removes unnecessary fields from tuples:

[TABLE]

There is a similar rule for select and groupby operators.

The projection rules differ from the others in this section because they refer to the context $C$. The other rules can be applied in any context. The context is important for the projection rules because without it, all the fields in a layout would be visible and therefore “necessary”. Referring to the context allows us to determine which fields are visible to the user.

4.4. Precomputation

A simple transformation that can improve query performance is to compute and store the values of parameter-free terms. This transformation is similar to partial evaluation. The following rule444Some of the rules make a distinction for parameter-free expressions, which do not contain query parameters. In these rules, parameter-free expressions are denoted as $\boxed{e}$. precomputes a static layout algebra expression:

[TABLE]

Hoisting static expressions out of predicates can also be very profitable:

[TABLE]

If the expression $e^{\prime}$ can be precomputed and stored instead of being recomputed for every invocation of the filter. Similar transformations can be applied to any operator that contains an expression. This rule is useful when the filter appears inside a layout operator. For example, in $\textsf{{list}}{}(q\ \textsf{as}\ x,\textsf{filter}(e,q^{\prime}))$, an expression $e^{\prime}$ can be hoisted out of the filter if it refers to the fields in $q$ but not if it refers to the fields in $q^{\prime}$.

In a similar vein, select operators can be partially precomputed. For example:

[TABLE]

After this transformation, the ordered index will contain partial sums which will be aggregated by the outer select. This rule is particularly useful when implementing grouping and filtering queries, because the filter can be replaced by an index and the aggregate applied to the contents of the index. A similar rule also applies to select and list. A simple version of this rule applies to hash-idx; in that case, the outer select is unnecessary.

This transformation is combined with group-by elimination (Sec. 4.5) in TPC-H query 1 to construct a layout that precomputes most of the aggregation.

4.5. Partitioning

Partitioning is a fundamental layout transformation that splits one layout into many layouts based on the value of a field or expression. A partition of a relation $r$ is defined by an expression $e$ over the fields in $r$. Tuples in $r$ are in the same partition if and only if evaluating $e$ over their fields gives the same value.

Let $\textsc{part}(\cdot,\cdot,\cdot)$ be a function which takes a layout $q$, a partition expression $e$, and a name $x$, and returns a query for the partition keys and a query for the partitions:

[TABLE]

In this definition, $q_{k}$ evaluates to the unique valuations of $e$ in $r$. These are the partition keys. Note that the expression $q_{v}$ contains a free scope $x$. We use $x.e$ to denote the expression $e$ with its names qualified by the scope $x$. Once $x.e$ is bound to a particular partition key, $q_{v}$ evaluates to a relation containing only tuples in that partition.

The partition function is used to define rules that create hash indexes and ordered indexes from filters:

[TABLE]

[TABLE]

Partitioning also leads immediately to a rule that eliminates $\textsf{group-by}{}(\cdot)$:

[TABLE]

There is a slight abuse of notation in this rule. $K$ is a list of expressions, so the filter in $q_{v}$ must have an equality check for each expression in $K$. This group-by elimination rule is used in many of the TPC-H queries which contain group-bys.

4.6. Join Elimination

Castor’s layout operators admit several options for join materialization. Since joins are often the most expensive operations in a relational query, choosing a good join materialization strategy is critical. Castor does not suggest a join strategy but it provides the necessary tools for an expert user.

Partitioning can be used to implement join materialization: a powerful transformation that can significantly reduce the computation required to run a query, at the cost of increasing the size of the data that the query runs on. Our layout language allows for several join materialization strategies.

For example, joins can be materialized as a list of pairs:

[TABLE]

Each pair in this layout contains the tuples that should join together from the left- and right-hand-sides of the join.

Joins can also be materialized as nested lists:

[TABLE]

This layout works well for one-to-many joins, because it only stores each row from the left hand side of the join once, regardless of the number of matching rows on the right hand side.

Or, joins can be materialized as a list and a hash table:

[TABLE]

This is similar to how a traditional database would implement a hash join, but in our case the hash table is precomputed. Using a hash table adds some overhead from the indirection and the hash function but avoids materializing the cross product if the join result is large.

If the join is many-to-many with an intermediate table, then either of the above one-to-many strategies can be applied.

4.7. Predicate Precomputation

In some queries, it is known in advance that a parameter will come from a restricted domain. If this parameter is used as part of a filter or join predicate, precomputing the result of running the predicate for the known parameter space can be profitable, particularly when the predicate is expensive to compute. Let $p$ be a query parameter and $D_{p}$ be the domain of values that $p$ can assume.

[TABLE]

This rule generates an expression $w_{i}$ for each instantiation of the predicate with a value from $D_{p}$. The $w_{i}$s are selected along with the original relation $r$. When we later create a layout for $r$, the $w_{i}$s will be stored alongside it. When the filter is executed, if the parameter $p$ is in $D_{p}$, the or will short-circuit and the original predicate will not run. However, this transformation is semantics preserving even if $D_{p}$ is underapproximate. If the query receives an unexpected parameter, then it executes the original predicate $e$. Note that in the revised predicate $e^{\prime}$, $p=D_{P}[i]$ can be computed once for each $i$, rather than once per invocation of the filter predicate.

We use this transformation on TPC-H queries 2 and 9 to eliminate expensive string comparisons.

4.8. Correctness

To show that the semantics that we have outlined in Sec. 3.2 are sufficient to prove the correctness of nontrivial transformations, we prove the correctness of the equality filter elimination rule (Sec. 4.5) in Appendix C. Although we do not prove the correctness of all of the rules, this example demonstrates that such proofs are possible.

In particular, since our notation mixes relational and layout constructs, even transformations that manipulate both the run- and compile-time behavior of the query are often local transformations, and are therefore simple to prove correct.

5. Compilation

The result of applying the transformation rules is a program in the layout algebra. This program is still quite declarative, so there is a significant abstraction gap to cross before the program can be executed efficiently. Compilation of layout algebra programs proceeds in three passes:

Type Inference. The type inference pass computes a layout type, which contains information about the ranges of values in the layout. For example, integers are abstracted using intervals, as are the numerators of fixed point numbers. Note that every element in collections like lists and indexes must be of the same type but tuples can contain elements of different types.

Serialization. The serialization pass generates a binary representation of the layout, using information from the layout type to specialize the layout to the data. Each of the layout operators has a binary serialization format which is intended to (1) take up minimal space and (2) minimize the use of pointers to preserve data locality.

Code generation. Query code is generated according to the compilation strategy described in (Tahboub et al., 2018). This is referred to as push-based, or data-centric query evaluation. We found that using this strategy instead of a traditional iterator model is critical for query performance. A syntax-directed lowering pass transforms each query and layout operator into an imperative intermediate representation, using the layout type to generate the layout reading code. This IR is then lowered to LLVM IR, optimized, and compiled into an executable that provides a command line interface to the query.

6. Optimization

Castor includes an automatic, cost guided optimizer for the layout algebra. Given a query written in the relational algebra fragment, the optimizer searches for a sequence of transformations that (1) makes the query serializable (Sec. 3.3) and (2) minimizes the cost of executing the query. The optimizer consists of two components: a transformation scheduling language and a cost model for the layout algebra.

Scheduling. The space of transformation sequences is far too large for an exhaustive search, so we write a schedule that only considers a subset of the full space of transformations. We use a small domain specific language to construct this schedule. This language is inspired by (Visser, 2005) and provides combinators for sequencing, fix-points, and context selection. The schedule captures some of the domain knowledge that we have about how to optimize query layouts.

The optimizer schedule has four phases: join nest elimination, hash-index introduction, ordered-index introduction, and precomputation. These phases are not the entirety of the optimizer but they give a rough picture of its behavior.

The join nest elimination phase looks for unparameterized join nests and replaces them with layouts. As discussed in Sec. 4.6, there are several ways to eliminate a join operator. The right choice depends on whether the join is one-to-one or one-to-many. To eliminate a join nest, the optimizer performs an exhaustive search using the join elimination rules and uses the cost model to choose the least expensive candidate.

The hash- and ordered-index introduction phases attempt to replace filter operators with indexes. When replacing a filter operator with an index, the most important choice to make is where in the query to place the filter. This choice determines which part of the layout the index will partition. The optimizer makes this choice by first hoisting all of the candidate filters as far as possible. It then pushes the filters, introducing an index at each position. The cost model is used to select the best candidate.

Finally the precomputation phase selects parts of the query that can be computed and stored. Other transformation rules are interleaved with these phases.

The output of the optimizer is a sequence of transformation rules that lower the input query to a layout, minimizing the cost of executing the resulting query. A pleasant feature of the optimizer is that because it simply schedules transformation rules, it is semantics preserving if all of the rules are. This means that all schedules are equally correct—they differ only in the quality of their optimization.

Cost Model. The staged nature of the layout algebra makes evaluating the cost of a query complicated. We use the layout type (Sec. 5) to estimate the cost of evaluating a query. The layout type tells us the size of the collections in the layout, and we use simple models of the costs of the runtime query operators to estimate the cost of the entire query. Computing the layout type is expensive, so we use a sample of the database for cost modeling during optimization.

7. Evaluation

We compare Castor with three other systems: Hyper (Neumann, 2011), Cozy (Loncaric et al., 2018), and Chestnut (Yan and Cheung, 2019) (see Sec. 8).

Hyper is an in-memory column-store which has a state-of-the art query compiler. It implements compilation techniques (e.g. vectorization) that are well outside the scope of this paper. We compare against Hyper in two modes: with the original TPC-H data and with a transformed version of the data and query that mimics the layout used by Castor. We compare against vanilla Hyper to show that layout specialization is a powerful optimization that can compensate for the many low-level compiler optimizations in Hyper. We compare against Hyper with transformed data to show that the specialization techniques that Castor uses are beneficial in other systems.

In the comparison with Hyper, the Castor results are split into two categories: expert generated queries and optimizer generated queries. In both cases we start with a direct translation of the SQL implementation of each query into the layout algebra. For the expert queries we hand-selected a sequence of transformations that generates an efficient, serializable version of the query. For the optimized queries, the optimizer (Sec. 6) searches over the space of transformation sequences, using its cost model to evaluate candidates. In some cases, the optimizer fails to find a serializable candidate, so there is no bar in the plot.

Cozy is a state-of-the-art program synthesis tool that generates specialized data structures from relational queries. Chestnut is a tool for synthesizing specialized data structures for object queries. We run both on TPC-H and compare with Castor.

7.1. TPC-H Analytics Benchmark

TPC-H is a standard database benchmark, focusing on analytics queries. It consists of a data generator, 22 query templates, and a query generator which instantiates the templates. The queries in TPC-H are inherently parametric, and their parameters come from the domains defined by the query generator. To build our benchmark, we took the query templates from TPC-H and encoded them as Castor programs. It is important that the queries be parametric. Specializing non-parametric queries is uninteresting; a non-parametric query can be evaluated and the result stored.

TPC-H is a general purpose benchmark, so it exercises a variety of SQL primitives. We chose not to implement all of these primitives in Castor, not because they would be prohibitively difficult, but because they are not directly related to the layout specialization problem. In particular, Castor does not support executing order-by, group-by, join, or dedup operators at runtime555These operators can be processed into the compiled form of the query., and it does not support limit clauses at all. Some of these operators can be replaced by layout specialization, but others cannot. We implemented the first 19 queries in TPC-H. Of these queries, we dropped query 13 because it contains an outer join and removed runtime ordering and limit clauses from five other queries.

7.2. Results

When evaluating the TPC-H queries, we used the 1Gb scale factor. We ran our benchmarks on an Intel® Xeon® E5–2470 with 100Gb of memory.

Runtime. Figure 7 shows the speedup over baseline Hyper. The query runtime numbers in Table 1 show that the layouts and query code generated by Castor from expert queries are faster or significantly faster than Hyper for 15 out of 18 queries. In the cases where Castor is slower than Hyper, no query is more than 2x slower.

If Hyper is given specialized views and indexes, then its performance is on par with Castor. However, constructing and maintaining these views takes effort, and Hyper cannot assist the user in creating a collection of views which maintains the semantics of the original query.

Layout Size. We recorded the size of the layouts that Castor produced and compared them to the size of Hyper’s specialized views and indexes. Figure 7 shows that Castor’s specialized layouts are much smaller than Hyper’s, even if Hyper uses the same kind of data removal and indexing optimizations.

The layouts were generally small—less than 10Mb for 10 out of 18 queries and less than 100Mb for all but one query. The original data set, the output of the TPC-H data generator, is 1.1Gb. The size difference between Castor’s layouts and the original data supports the hypothesis that queries, even parameterized queries, rely on fairly small subsets of the whole database, making layout specialization a profitable optimization.

Memory Use. We also measured the peak memory use of the query process for Hyper and for Castor. Hyper consistently uses approximately the same amount of memory as the layout size. In some cases it uses more, presumably because it has large runtime dependencies like LLVM. In contrast, Figure 7 shows that Castor’s peak memory use is significantly lower than Hyper for all expert queries.

Cozy**.** We transformed our input queries into Cozy’s specification format and ran Cozy with a 6 hour timeout. In this configuration, we found that Cozy was unable to make significant improvements on all but two of the TPC-H queries. In Q4, Cozy precomputed one of the joins and a filter. In Q17, Cozy added an index. We ran both of these queries and found that despite the optimization Q4 was slower than baseline Hyper at 5.4s and Q17 was too slow to run on the entire TPC-H dataset. Although Cozy is effective at synthesizing data structures from small relational specifications, the size of the TPC-H queries causes a significant slowdown in its solver-based verification step.

Chestnut**.** We attempted to use Chestnut to optimize four of the TPC-H queries, but we were unable to build and run the generated code. Manually examining the selected layouts for Q1 and Q3–6, we find that Chestnut uses projection and indexes in many of the same places that Castor does, but misses some optimizations that Castor can take advantage of, such as aggregate precomputation (Sec. 4.4).

7.3. Optimizer

The performance of the automatic optimizer is mixed. In some cases, it generates queries that perform similarly or better than the expertly tuned queries. In others, it fails to find a transformation sequence that generates a serializable query, it generates a serializable query that uses a layout that is too large to compile in a reasonable amount of time, or it generates a query which performs poorly.

In most of the cases where the optimizer fails to find a solution, it fails because the cost model provided an inaccurate estimate of the performance of the candidate. In the cost model, we only consider the time required to run the query, not the size of the layout. This avoids a complex multi-variable optimization problem, but it means that the optimizer sometimes generates layouts that are too large to be compiled efficiently.

Despite the mixed performance of the optimizer, it provides a good starting point for an expert implementation, and it demonstrates that automatically optimizing programs in the layout algebra is feasible.

7.4. Summary

We showed that Castor produces artifacts that are competitive in performance and in size with a state-of-the-art in-memory database. These results show that database compilation is a compelling technique for improving query performance on static and slowly changing datasets.

8. Related Work

Deductive Synthesis. There is a long line of work that uses deductive synthesis and program transformation rules to optimize programs (Blaine et al., 1998; Puschel et al., 2005), to generate data structure implementations (Delaware et al., 2015), and to build performance DSLs (Ragan-Kelley et al., 2013; Sujeeth et al., 2014). Castor is a part of this line of work: it is a performance DSL which uses deduction rules to generate and optimize layouts. However its focus on particular data sets and on deduction rules to optimize data in addition to programs separates it from previous work.

Data Representation Synthesis. The layout optimization problem is similar to the problem of synthesizing a data structure that corresponds to a relational specification (Hawkins et al., 2010, 2011; Loncaric et al., 2016, 2018; Sujeeth et al., 2014). Castor considers a restricted version of the data structure synthesis problem where the query and the dataset are known to the compiler, which allows Castor to use optimizations which would not be safe if the data was not known. The best data structure synthesis tool—Cozy—uses an SMT solver to verify candidates, which does not scale to the TPC-H queries. Castor’s use of deduction rules avoids this costly verification step.

Database Storage. Traditional databases are mostly row-based. Column-based database systems (e.g., Hyper (Neumann, 2011), MonetDB (Boncz and Kersten, 1999) and C-Store (Stonebraker et al., 2005)) are popular for OLAP applications, outperforming row-based approaches by orders of magnitude. However, the existing work on database storage generally considers specific storage optimizations (e.g., (Ailamaki et al., 2001)), rather than languages for expressing diverse storage options. In this vein is RodentStore (Cudre-Mauroux et al., 2009), which proposed a language to express rich types of storage layouts and showed that different layouts could benefit different applications. However, a compiler was never developed to create the layouts from this language; the paper demonstrated its point by implementing each layout by hand. Also related is Chestnut: a tool for generating specialized layouts for object queries (Yan and Cheung, 2019). Chestnut has separate layout and query languages and synthesizes a query after choosing the layout. This limits its ability to use transformations that change the data, like predicate precomputation (Sec. 4.7).

There have also been studies of physical layouts for other types of data, such as for scientific data (Stonebraker, 2012), and geo-spatial data (Gutiérrez and Baumann, 2007). Although not directly comparable, we hope that Castor can be extended to support those data types.

Materialized View and Index Selection. The layouts that Castor generates are similar to materialized views, in that they store query results. Castor also generates layouts which contain indexes. Several problems related to the use of materialized views and indexes have been studied (see (Halevy, 2001) for a survey): (1) the view storage problem that decides which views need to be materialized (Chirkova and Genesereth, 2000), (2) the view selection problem that selects view(s) that can answer a given query, (3) the query rewriting problem that rewrites the given query based on the selected view(s) (Pottinger and Levy, 2000), (4) the index selection problem that selects an appropriate set of indexes for a query (Stonebraker, 1974; Gupta et al., 1997; Bruno and Chaudhuri, 2005; Talebi et al., 2008). However, materialized views are restricted to being flat relations. The layout space that Castor supports is much richer than that supported by materialized views and indexes. In addition, the view selection literature has not previously considered the problem of generating execution plans for chosen views and indexes.

Query Compilation. Castor uses techniques from the query compilation literature (Tahboub et al., 2018; Shaikhha et al., 2016; Klonatos et al., 2014). It extends these techniques by using information about the layout to specialize its queries.

9. Conclusion

We have presented Castor, a domain specific language for expressing a wide variety of physical database designs, and a compiler for this language. We have evaluated it empirically and shown that it is competitive with the state-of-the-art in memory database systems.

Appendix A Layout Semantics

In this section we discuss the layout semantics, which specifies how a layout algebra program may be serialized to a binary format.

Each of the layout operators has a serialization format that is designed to be as compact as possible. These are as follows:

• •

Integers are stored using the minimum number of bytes, from 1 to 8 bytes.

• •

Booleans are stored as single bytes.

• •

Fixed point numbers are normalized to a fixed scale, and stored as integers.

• •

Tuples are stored as the concatenation of the layouts they contain, prefixed by a length.

• •

Lists are stored as a length followed by the concatenation of their elements. They can be efficiently scanned through, but not accessed randomly by index.

• •

Hash indexes are implemented using minimal perfect hashes (Botelho et al., 2007; Davi de Castro Reis et al., 2011). The hash values are stored as in a list, but during serialization a lookup table is generated using the CMPH library and stored before the values. Using perfect hashing allows the hash indexes to have load factors up to 99%.

• •

Ordered indexes are similar to hash indexes in that they store a lookup table in addition to storing the values. In the case of the ordered index keys are stored sorted and the correct range is found by binary search.

Serialization proceeds in two passes. First, we compute a layout type according to the rules in Figure 8. This type is an abstraction of the layout; we use it to specialize the layout to the data that it stores. For example, we use an interval abstraction to represent integer scalars as well as lengths of collections like lists. We use these intervals to choose the number of bytes to use for these integers when serializing the layout. In some cases we are able to use this abstraction to avoid storing anything at all. For example, if we know that the length field of a tuple is always the same, we can avoid storing that field and instead bake it into the layout reading code. This is a surprisingly important optimization; for a tuple containing two integers, a naive implementation would spend a third of the tuple’s bytes just to store the length field.

After computing the layout type, we serialize the layout according to the rules in Appendix A.