We haven't implemented disk spilling for aggregations yet. It's different from the algorithms discussed in this article in that the amount of memory used is proportional to the number of aggregation groups (buckets), rather than the number of input tuples. This makes it less likely that aggregations would need to spill to disk and requires a slightly specialized solution to figure out how to serialize intermediate aggregation results.
Sorting is a good idea. The in-memory aggregator could keep track of the aggregation columns of each bucket, sort the buckets and input that has not yet been processed and then perform an ordered aggregation on the sorted input using the already-computed intermediate result as a starting point for the group's aggregation result.
Another option is to partition the buckets and input, which would subdivide the aggregation and avoid a sort.
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.
CockroachDB does target OLTP workloads. Note that the vectorized SQL engine covered in this blog post is execution-only (and used only for queries that operate on many rows). The storage layer remains row-oriented so the write performance is not affected. Rows are columnarized before processing by the execution engine.
