Databases and Analytical Storage

Almost every data system reads from and writes to a database. Before looking at a specific one, it helps to have a plain model of what a database stores and why analytical work tends to use a different kind of database than an application does.

What a database stores#

A database is an organized collection of data that a program can query and update. Inside a database, data is arranged into tables.

• A table holds data about one kind of thing, such as orders or customers.
• A row is one record in a table, such as a single order.
• A column is one field that every row has, such as amount or created_at.
• A schema is the definition of a table: its column names, the type of each column, and other rules.

A small analytics.orders table might look like this:

The schema is the part that stays fixed: four columns, with order_id and customer_id as integers, created_at as a timestamp, and amount as a number. Rows come and go; the schema describes what a valid row looks like.

Two kinds of workload#

Databases are usually tuned for one of two very different access patterns.

OLTP (online transaction processing) runs an application. It does many small operations: insert one order, read one customer, update one row. Each query touches few rows, runs constantly, and must be fast and correct for a single record.

OLAP (online analytical processing) answers questions about the data as a whole: total revenue per month, average order value per customer segment, the top products this week. Each query scans many rows, reads a few columns, and combines them into a summary.

OLTP query   reads a few rows, all columns   "give me order 101"
OLAP query   reads many rows, a few columns   "total amount per month for the last year"

The same data can support both, but a database optimized for one pattern is usually a poor fit for the other.

Row storage and column storage#

The difference comes down to how a table is laid out on disk.

A row store keeps all the values of a row next to each other. Reading one complete order is a single, small read. This suits OLTP.

A column store keeps all the values of one column next to each other. Reading amount for a million rows means reading one continuous block and skipping every other column entirely. Values in a column are also similar to each other, so they compress well. This suits OLAP.

Row store on disk   [101, 1, 2026-01-04, 1200][102, 1, 2026-01-04, 850][103, 2, ...]
Column store on disk   order_id:    [101, 102, 103, ...]   customer_id: [1, 1, 2, ...]   created_at:  [2026-01-04, 2026-01-04, 2026-01-05, ...]   amount:      [1200, 850, 640, ...]

An aggregation like sum(amount) over a year of data reads just the amount column in a column store, and reads every full row in a row store.

Why an analytical system uses a column store#

A data pipeline usually copies data out of the application database into a separate analytical database. That analytical store is where reports, dashboards, and models read from.

Using a dedicated analytical database has practical benefits:

• Large scans and aggregations run on storage built for them.
• Heavy analytical queries do not compete with the application for resources.
• The analytical layer can hold history and combine data from several sources as a single source of truth.

ClickHouse is a column-oriented database built for this role. The rest of this section uses it as the concrete example, but the ideas apply to other analytical stores as well.

ClickHouse and PostgreSQL do different jobs#

PostgreSQL is a capable general-purpose, row-oriented database. It is a strong choice for an application: single-row lookups, frequent small updates, foreign keys, and transactions.

ClickHouse is built for analytical reads. It is very fast at scanning and aggregating large tables, and deliberately weak at things an application needs: it has no enforced foreign keys, updates and deletes are heavy and asynchronous, and single-row lookups are not its strength.

A common architecture uses both: the application writes to PostgreSQL, and a pipeline loads that data into ClickHouse for analysis.

Common mistakes#

Running analytics on the application database#

Large aggregations on the production OLTP database slow down the application. Move analytical reads to a separate store.

Expecting OLTP guarantees from an analytical database#

Analytical databases usually relax constraints, uniqueness, and immediate updates to gain scan speed. Design the pipeline to produce correct data rather than relying on the database to enforce it.

Treating "database" as one thing#

"Which database" is really "which access pattern". Pick storage by how the data will be written and read, not by familiarity.

Quick reference#

See also#