AlloyDB for PostgreSQL under the hood: Columnar engine

> I wonder if there are performance tradeoffs sometimes leading to slower than vanilla postgres?

Speculation: alloydb probably has both a row store and a column store and some fancy logic to decide which one to use. That's usually what HTAP means.

In very broad strokes, columnar data stores struggle if you select many columns.

edit: column stores existed before c-store, but c-store did some very nifty stuff around integrating compression awareness into the query executor

If you want to be technical, C-Store was the real innovator...and those people refined and commercialized it as Vertica.

Given they specify analytical workloads, I'm betting a lot of it comes from column storage.

Row-based vs columnar-based storage. Latter is more suitable (read much faster) for OLAP workloads.

- The storage engine architecture is obsolete and highly suboptimal. It has no way to accommodate modern high-density or high-bandwidth storage, both of which are the norm now, nor is it possible to implement any of the (very successful) I/O schedule optimization concepts that have emerged in recent years. The net effect is a gross underutilization of the capabilities of modern storage hardware.

- The internal page layout and table architecture in Postgres is a textbook classic but not appropriate for most workloads on modern hardware. Modern page engines need great performance across diverse categories of workloads and data models -- we use databases for a lot more than accounting systems these days -- while also being amenable to aggressive use of hardware optimization like SIMD. Thinking in terms of "row-oriented" and "column-oriented" is a false dichotomy; optimized page engines commonly have many elements of both but are not identifiable as pure expressions of either nor even a simple hybrid (e.g. PAX layouts). This is one of the easiest changes to backport into old database architectures.

- Indexing in some modern systems have been completely reimagined to great effect. Tables are effectively index-organized with no secondary indexing structures but without loss of high query selectivity across diverse columns; there is a separation of the concerns regarding fast search and key constraint enforcement; major increases in index write throughput concurrent with major decreases in storage and memory footprint (no B-tree bloat). For small tables, this optimization has no effect and may even be a mild pessimization depending on the workload. As tables become larger the performance starts to diverge greatly, becoming multiple orders of magnitude faster. The Postgres architecture can't be modified to support this.

If you built a database engine that was approximately state-of-the-art in these three areas, I would expect a 100x difference in performance relative to Postgres for many large data model workloads using server hardware like an AWS i3en.24xlarge. Obviously building such a database would be a hell of a lot of work, it isn't something you can reasonably do for fun on nights and weekends.