Why faster analytics comes with less write flexibility

Fast analytical reads make ordinary writes awkward

Column storage, compression, and sort order are excellent for the large, read-heavy scans that happen in a warehouse. The same layout becomes difficult to change one row at a time.

A sorted compressed table cannot update a value in place in the way a B-tree can update a disk page. Inserting one logical row in the middle may require rewriting every column file, because all columns must preserve the same row positions.

flowchart TD
  A(["Insert one logical row"]) --> B[/Date column: new entry/]
  A --> C[/Product column: new entry/]
  A --> D[/Quantity column: new entry/]
  B --> E[["All columns agree on row position"]]
  C --> E
  D --> E

Column stores can borrow the LSM write path

The solution looks familiar from log-structured storage engines. Recent writes first enter an in-memory sorted structure. Once enough writes accumulate, the system merges them with the immutable column files on disk and writes new files in bulk.

The in-memory component may be row-oriented or column-oriented. What matters is that it absorbs small writes cheaply while the expensive rewrite happens later as a batch. Vertica follows this broad approach.

flowchart TD
  A[/New write/] --> B(["In-memory sorted structure"])
  B --> C["Accumulate recent changes"]
  C --> D[("Disk columns")]
  D --> E[/Write new column files in bulk/]

Queries see one current dataset

A query must examine both older column data on disk and recent writes still in memory. The engine combines them before returning the result.

The query optimizer hides this physical split. To an analyst, inserts, updates, and deletes appear in later queries immediately, even before the system has rewritten the main column files.

flowchart LR
  A[("Historical column files on disk")] --> C(["Current logical result"])
  B(["Recent in-memory changes"]) --> C

A materialized view stores an answer

Warehouse queries often repeat the same aggregates, such as counts, sums, averages, minima, or maxima. Recomputing the same expensive aggregate from raw events every time can be wasteful.

A virtual view stores a query definition. When read, the database expands and runs that underlying query. A materialized view stores a real copy of the query result on disk, so a later query can read the prepared result instead.

flowchart TD
  subgraph Virtual view
    A["Saved query definition"] --> B[/Run underlying query when read/]
  end
  subgraph Materialized view
    C[("Saved query result")] --> D[/Read precomputed data/]
  end

Precomputation shifts work from reads to writes

A materialized view is a denormalized copy. When raw data changes, the database must update that copy as well. This makes writes more expensive, which is why materialized views are less attractive in transactional workloads.

In a read-heavy warehouse, the trade can be worthwhile if many people repeatedly ask for the same aggregate. It is still a case-by-case choice because the extra maintenance may not always save enough query time.

flowchart LR
  A[/Raw data changes/] --> B[("Materialized result must change")]
  B --> C["More write work"]
  C --> D(["Less repeated read work"])

Data cubes precompute aggregates across dimensions

A data cube, also called an OLAP cube, is a common special case of a materialized view. With date and product as dimensions, each cell holds an aggregate for one date-product combination. The system can also store summaries by product regardless of date, or by date regardless of product.

Real fact tables may have dimensions for date, product, store, promotion, and customer. The same principle applies even when the cube is too many-dimensional to picture easily: each combination has a prepared aggregate that can be summarized along one or more dimensions.

flowchart TD
  A(["Date-product combination"]) --> B[["Prepared aggregate cell"]]
  B --> C["Jan 1, Product A"]
  B --> D["Jan 1, Product B"]
  B --> E["Jan 2, Product A"]
  B --> F["Jan 2, Product B"]

Cubes are fast because they give up flexibility

If a warehouse has already computed yesterday's sales by store, that question can be answered by reading totals instead of scanning millions of sales events. The answer is fast because the expensive work happened earlier.

A cube can only answer questions built into its dimensions and aggregates. If price is not represented, it cannot calculate the share of sales from items above a chosen price. Warehouses therefore keep raw events for flexible analysis and add cubes only for repeated, predictable questions.

flowchart LR
  A[("Raw events")] --> B["Flexible questions"]
  A --> C["Expensive scans"]
  D[["Data cube"]] --> E["Prepared questions"]
  D --> F["Fast answers"]

My Chapter 3 storage model

This chapter changed how I think about database storage. There is no universally fast layout. A storage engine makes particular reads and writes cheap, then makes other operations pay the cost.

OLTP handles many user-facing requests that touch a few records, so indexes and disk seeks matter. OLAP runs fewer but much broader queries, so compact encoding, disk bandwidth, and column-oriented scans matter more. The storage model should follow the workload.

flowchart TD
  A{"Choose storage around access pattern"}
  A --> B(["OLTP: few records, key lookups, small writes"])
  A --> C(["OLAP: large scans, selected columns, aggregates"])

These are my personal learning notes from Designing Data-Intensive Applications by Martin Kleppmann.