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.

INSERT ONE LOGICAL ROW

date column     needs a new entry
product column  needs a new entry
quantity column needs a new entry

Every column must agree on row position.

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.

NEW WRITE
-> in-memory sorted structure
-> accumulate recent changes
-> merge with disk columns
-> 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.

QUERY

historical column files on disk
+ recent in-memory changes
--------------------------------
current logical result

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.

VIRTUAL VIEW
saved query definition
-> run underlying query when read

MATERIALIZED VIEW
saved query result
-> read precomputed data

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.

RAW DATA CHANGES
-> materialized result must change

More write work
for
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.

                 Product A    Product B
Jan 1              total        total
Jan 2              total        total

cell = prepared aggregate
for one date-product combination

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.

RAW EVENTS
-> flexible questions
-> expensive scans

DATA CUBE
-> prepared questions
-> 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.

OLTP
few records, key lookups, small writes

OLAP
large scans, selected columns, aggregates

Choose storage around access pattern.

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