← Back to the section

"How much revenue did each store bring in over January?" — a harmless-looking question that can knock over your application's live database. The catch isn't the size of the data: this is a query from another world. Queries against data split into two classes built oppositely — what's good for one is agony for the other.

one orders table, two queries, two layouts on disk by rows: the whole row is one chunk by columns: each column is its own file id id customer customer status status amount amount 41 41 Ann Ann NEW NEW 120 120 42 42 Bob Bob PAID PAID 340 340 43 43 Cat Cat PAID PAID 90 90 44 44 Dan Dan NEW NEW 75 75 OLTP: WHERE id = 42 — one row found by index, answered in milliseconds OLAP: sum(amount) — a row store lifts all 16 cells to get one column the same total on the column layout reads 4 cells of one file — and they compress

A row layout wins when you need the whole row by key and loses when you need one column out of every row: the cells you did not ask for are lifted off disk anyway. A column layout reads exactly the file that was asked for, and the similar values inside that file compress well.

Two ways of reaching for data

OLTP (online transaction processing) is the world of applications: someone placed an order, opened a profile, updated a cart. Each query touches a few rows found by key: found via an index, read or changed, answered in milliseconds. The data here is the state of the world right now. This is what B-trees and the whole discipline of transactions are tuned for.

OLAP (online analytical processing) is the world of analytics. The question comes from an analyst: "how many more bananas did we sell during the promotion than usual?". Such a query runs over millions of rows but takes two or three columns from each and rolls them into a single total — a sum, a count, an average. The data here is the history of events over years, and the bottleneck is different: not "how fast can we find the row" but "how many bytes per second we push through a scan".

You can see the difference in the queries. The application's point query:

live example

SELECT id, status, total_amount FROM orders WHERE id = 42;
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Free week →

And the analyst's rollup:

live example

SELECT date_trunc('month', created_at) AS month,
       count(*) AS orders,
       sum(total_amount) AS revenue
FROM orders
WHERE status = 'PAID'
GROUP BY 1
ORDER BY 1;
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Free week →

Same language, different needs: the first wants an index on the key, the second scan speed. So storing data for the two pays off differently.

The data warehouse and ETL

Letting analysts into the application's live database is bad on both sides: heavy scans eat resources away from user transactions, and at peak hour that shows. A company also has many OLTP systems — the website, the warehouse, delivery, the CRM — and one query can't ask them all.

So analytics is moved to a separate database — a data warehouse: a read-only copy of data from all the OLTP systems in one place. It is filled by an ETL process — three steps: extract (pull from the sources), transform (reshape for analytics), load (into the warehouse). It runs either as periodic dumps or as a continuous stream of events.

A small company doesn't need a warehouse: its data fits in an ordinary PostgreSQL. The warehouse shows up when OLTP systems are many and history has grown to terabytes.

The star schema: facts and dimensions

In the application world schemas differ for every task; analytical warehouses are almost all built the same way — as a star schema.

At the center of the star is the fact table: one row per event ("a customer bought such-and-such a product at such-and-such a moment"), hundreds of columns, billions of rows. Around it are the dimension tables: product, store, customer, date, promotion. The fact references them by foreign keys, and the dimensions answer "who, what, where, when and why" about each event.

Even the date is its own dimension: a row per calendar day with flags like "holiday/weekday", otherwise you can't ask "how do weekend sales compare to weekdays". The variant where dimensions are split into sub-tables is called a "snowflake", but the flatter star usually wins.

Columnar storage: why analytics flies

Ordinary OLTP databases store data by rows — a whole row sits on disk as one chunk, which is perfect for "read the whole order". But an analytical query against a hundred-column table touches three, and a row store still lifts whole rows off disk.

Columnar storage flips the layout: each column lives in its own separate file, and a row is reconstructed by position — the fifth value in each file belongs to the same fifth row. A query reads only the columns it needs, already a manyfold win. Then a second, stronger effect kicks in: values within one column resemble each other (the "product" column has billions of rows but maybe ten thousand distinct values), so columns compress beautifully — tenfold, and a hundredfold along a sorted column, because equal values line up next to each other. Filters turn into fast bitwise operations, and the scan runs up against the CPU cache, not the disk.

The same arithmetic in a small program: one table in two layouts, summing a single column.

live example

import java.util.List;

public class ColumnarScan {
    record Order(int id, String customer, String status, int amount) {}

    public static void main(String[] args) {
        List<Order> rows = List.of(
                new Order(41, "Ann", "NEW", 120),
                new Order(42, "Bob", "PAID", 340),
                new Order(43, "Cat", "PAID", 90),
                new Order(44, "Dan", "NEW", 75));

        int rowCells = 0;
        int rowSum = 0;
        for (Order order : rows) {
            rowCells += 4;
            rowSum += order.amount();
        }

        int[] amountFile = {120, 340, 90, 75};
        int colCells = 0;
        int colSum = 0;
        for (int value : amountFile) {
            colCells++;
            colSum += value;
        }

        System.out.println("row layout:    sum " + rowSum + ", cells lifted " + rowCells);
        System.out.println("column layout: sum " + colSum + ", cells lifted " + colCells);
    }
}
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Free week →

Same total, 16 cells lifted against 4 — on a table of four rows.

This is how ClickHouse, Vertica, Redshift and Parquet work. And it's why ClickHouse is not a replacement for PostgreSQL but a tool from a different world. The price for read speed is expensive writes: you can't insert a row "into the middle" of compressed sorted columns, so writes are taken in batches — a batch lands as a separate chunk and chunks are merged in the background later, as in the LSM approach.

Materialized summaries

If a thousand queries a day compute SUM(net_price) along the same axes, scanning everything each time is wasteful. The answer is computed ahead of time and kept in a materialized view — a table holding the query's result (in PostgreSQL that's a materialized view). The extreme form of the idea is an OLAP cube: a grid of totals over combinations of dimensions (date × product × store), where "revenue for yesterday" is a single cell read.

The price is flexibility: in a cube you can't ask for something that isn't among its dimensions (say, "the share of sales under 100 rubles", if price isn't a dimension). That's why warehouses keep the raw events and hold summaries on top — as an accelerator for frequent queries.

Where this applies

The fork shows up earlier than you'd think: the first "sales by day" summary is already an analytical query. While the data is small, a PostgreSQL read replica and materialized views are enough; once scans have become heavy and frequent, you set up a columnar store and a stream of events into it.

Where beginners stumble:

  • Running analytics against the live database. One heavy scan at peak hour, and the response time of user queries slides into the tail.
  • Building one "universal" schema for everything. A normalized OLTP schema is awkward for the analyst, and a star for the application: two views of the same data, and that's fine.
  • Using ClickHouse for point reads ("find an order by id"): on that query a columnar store loses cleanly to PostgreSQL — and the other way round.
  • Hiding all answers in cubes. A summary with no raw events is a dead end: a new question needs a dimension the cube doesn't have.

In short

  • OLTP: a few rows by key, an answer in milliseconds. OLAP: millions of rows, two or three columns from each, one total on the way out.
  • Analytics is moved off the live database into a data warehouse, filled by ETL: extract, transform, load.
  • A warehouse is built as a star: the fact table at the center, dimensions (product, store, date) around it.
  • A row layout lifts the whole row, a columnar one reads only the files asked for, and those compress tenfold.
  • The price of columnar storage is expensive point writes and reads: ClickHouse does not replace PostgreSQL.
  • Cubes and materialized views speed up frequent questions, but a warehouse always keeps the raw events — otherwise a new question has nothing to run against.