Columnar and time series databases

In short: A columnar database stores each column separately so reports over millions of rows read fast, and a time series database stores timestamped measurements such as sensor readings.

Problem

The report wants two columns and reads seven

The table sales from the previous lesson has seven columns: order, day, city, category, product, quantity and amount. The report on revenue by month wants two of them, day and amount.

An ordinary database stores the data by rows: everything about one sale is together on disk. To read two columns, it reads the whole rows and throws away the other five. With a billion sales that is hundreds of gigabytes, of which a little over a quarter is needed.

SQLdatabase: shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS rows, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT category) AS categories FROM sales;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS rows, COUNT(DISTINCT city) AS cities, COUNT(DISTINCT category) AS categories FROM sales;

▶ RunRestore the queryFresh database

The idea

Every column on its own

A columnar database (ClickHouse, BigQuery, Snowflake, DuckDB) stores every column in a separate file. The revenue report opens only the files of day and amount. And a column holds the same kind of value next to each other (only dates, only cities), so it compresses much better than a row in which they are mixed.

The simplest compression is a run: instead of "Sofia, Sofia, Sofia…" you write "Sofia × 50". The whole column city, sorted by city, is seven runs:

SQLdatabase: shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT city, COUNT(*) AS repeated
FROM sales
GROUP BY city
ORDER BY city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT city, COUNT(*) AS repeated FROM sales GROUP BY city ORDER BY city;

▶ RunRestore the queryFresh database

It works only if the equal values are next to each other. Here is how many runs the column has when the rows are in the order of the orders, and how many when they are sorted by city:

SQLdatabase: shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS runs
FROM (
  SELECT city, LAG(city, 1, '-') OVER (ORDER BY order_id, product) AS before
  FROM sales
) AS t
WHERE before IS NOT city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS runs FROM ( SELECT city, LAG(city, 1, '-') OVER (ORDER BY order_id, product) AS before FROM sales ) AS t WHERE before IS NOT city;

▶ RunRestore the queryFresh database

SQLdatabase: shop

CREATE TABLE sales AS
SELECT o.id AS order_id, o.ordered_at AS day, c.city,
       cat.name AS category, p.name AS product, oi.quantity,
       ROUND(oi.quantity * oi.unit_price, 2) AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COUNT(*) AS runs
FROM (
  SELECT city, LAG(city, 1, '-') OVER (ORDER BY city, order_id, product) AS before
  FROM sales
) AS t
WHERE before IS NOT city;

CREATE TABLE sales AS SELECT o.id AS order_id, o.ordered_at AS day, c.city, cat.name AS category, p.name AS product, oi.quantity, ROUND(oi.quantity * oi.unit_price, 2) AS amount FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled'; SELECT COUNT(*) AS runs FROM ( SELECT city, LAG(city, 1, '-') OVER (ORDER BY city, order_id, product) AS before FROM sales ) AS t WHERE before IS NOT city;

▶ RunRestore the queryFresh database

Out of 128 values 39 runs are left if the rows are not sorted, and 7 if they are. That is why in ClickHouse every table is made with ORDER BY. The order of the columns in it is the sort order on disk.

More precisely, if you are curious

LAG(city, 1, '-') gives the city of the previous row (and '-' for the first row), and every row in which the city changes starts a new run. IS NOT instead of <> is because NULL <> NULL is not true, while NULL IS NOT NULL is a clear "no". Real columnar databases also use other kinds of compression: a dictionary (the cities become the numbers 1–7), differences between neighbouring numbers, and a general algorithm on top (LZ4, ZSTD). Compression of 10 times is not rare.

The idea

When columnar, when not

A columnar database is excellent for reports: few columns, many rows, sums and groups. It is bad for the till. One new order means a write into seven separate files, and an UPDATE of one row often means rewriting a whole chunk of the column. That is why columnar databases like to write in big batches (thousands of rows at once) and rarely change. This is the OLTP and OLAP split from the previous lesson, only on disk.

Problem

The dairy fridge

The fridge with the dairy products has a sensor that records the temperature every 15 minutes. Above 8 °C the cheese and the milk go off quickly, and the shop has to know when it happened. Such data is a time series: values ordered by time, which are only added and almost never changed. Two days of readings:

SQLdatabase: shop

SELECT * FROM fridge ORDER BY at LIMIT 5;

SELECT * FROM fridge ORDER BY at LIMIT 5;

▶ RunRestore the queryFresh database

SQLdatabase: shop

SELECT COUNT(*) AS readings, MIN(at) AS first, MAX(at) AS last FROM fridge;

SELECT COUNT(*) AS readings, MIN(at) AS first, MAX(at) AS last FROM fridge;

▶ RunRestore the queryFresh database

Two days of 96 readings should be 192, but they are 184. Eight are missing, and you will see where.

The idea

Downsampling to hours

Nobody looks at 35,000 points on a chart for a year. The readings are grouped by hour (or day) with a few numbers for each: the average, the lowest and the highest. This is downsampling. The hour is the first 13 characters of the time:

SQLdatabase: shop

SELECT substr(at, 1, 13) || ':00' AS hour,
       ROUND(AVG(temp), 1) AS avg_temp,
       MAX(temp) AS max_temp
FROM fridge
WHERE at >= '2026-10-24 12:00' AND at < '2026-10-24 18:00'
GROUP BY hour
ORDER BY hour;

SELECT substr(at, 1, 13) || ':00' AS hour, ROUND(AVG(temp), 1) AS avg_temp, MAX(temp) AS max_temp FROM fridge WHERE at >= '2026-10-24 12:00' AND at < '2026-10-24 18:00' GROUP BY hour ORDER BY hour;

▶ RunRestore the queryFresh database

The average for 14:00 is 8.9 °C, and the highest is 9.7 °C. The door stood open.

Try it

When is the alarm

Show the hours in which at least one reading is above 8 °C (columns hour and max_temp, the highest temperature in the hour), in the order of the hour.

SQLdatabase: shop

SELECT substr(at, 1, 13) || ':00' AS hour FROM fridge LIMIT 5;

SELECT substr(at, 1, 13) || ':00' AS hour FROM fridge LIMIT 5;

Check▶ Run

The idea

A moving average

Sensors sometimes give a single wrong value, and the fridge is opened for seconds by every customer. So the alarm often looks not at one reading but at the average of the last four (the last hour). It moves with every new row: a window function with ROWS BETWEEN 3 PRECEDING AND CURRENT ROW:

SQLdatabase: shop

SELECT *
FROM (
  SELECT at, temp,
         ROUND(AVG(temp) OVER (ORDER BY at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2) AS last_hour
  FROM fridge
) AS t
WHERE at BETWEEN '2026-10-24 13:30' AND '2026-10-24 15:15'
ORDER BY at;

SELECT * FROM ( SELECT at, temp, ROUND(AVG(temp) OVER (ORDER BY at ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2) AS last_hour FROM fridge ) AS t WHERE at BETWEEN '2026-10-24 13:30' AND '2026-10-24 15:15' ORDER BY at;

▶ RunRestore the queryFresh database

The average goes above 8 °C only at 14:45, when the door has been open for an hour. One random high reading would raise it by about 1 °C, but not above 8.

The window function is in the inner query, and the period is outside. WHERE runs before the window: if the condition for the period is in the same query, the average for 13:30 does not see 12:45, 13:00 and 13:15 and is from one reading only.

Try it

The gap

The sensor measures every 15 minutes. Find where it lost the connection: the last reading before the gap and the first one after it (columns last_before and first_after).

SQLdatabase: shop

SELECT at, LAG(at) OVER (ORDER BY at) AS before FROM fridge LIMIT 5;

SELECT at, LAG(at) OVER (ORDER BY at) AS before FROM fridge LIMIT 5;

Check▶ Run

The idea

A retention period

The raw readings grow by 35,000 rows a year for one sensor, and the shop has hundreds. The usual thing is a table by hour, kept for a long time, and raw readings kept briefly (here for one day). The hourly table is made as in the data warehouse, with CREATE TABLE … AS SELECT:

SQLdatabase: shop

CREATE TABLE fridge_hourly AS
SELECT substr(at, 1, 13) || ':00' AS hour,
       ROUND(AVG(temp), 2) AS avg_temp, MIN(temp) AS min_temp, MAX(temp) AS max_temp,
       COUNT(*) AS readings
FROM fridge
GROUP BY hour;

SELECT * FROM fridge_hourly WHERE hour BETWEEN '2026-10-25 02:00' AND '2026-10-25 05:00';

CREATE TABLE fridge_hourly AS SELECT substr(at, 1, 13) || ':00' AS hour, ROUND(AVG(temp), 2) AS avg_temp, MIN(temp) AS min_temp, MAX(temp) AS max_temp, COUNT(*) AS readings FROM fridge GROUP BY hour; SELECT * FROM fridge_hourly WHERE hour BETWEEN '2026-10-25 02:00' AND '2026-10-25 05:00';

▶ RunRestore the queryFresh database

The hours 03:00 and 04:00 are missing in it too: there is not a single reading from them. The chart has to show an empty space, not join 02:00 and 05:00 with a straight line as if everything was fine.

Exercise

Cleaning up and a report

1. The hourly table is already made.

Delete the raw readings from before 25 October 2026. The table fridge_hourly stays whole.

SQLdatabase: shop

SELECT COUNT(*) FROM fridge;

SELECT COUNT(*) FROM fridge;

Check▶ Run

2. A report for the day.

For every day: the lowest, the highest and the average temperature (rounded to 0.01) and the number of readings (columns day, min_temp, max_temp, avg_temp, readings), in the order of the day.

SQLdatabase: shop

SELECT substr(at, 1, 10) AS day FROM fridge LIMIT 3;

SELECT substr(at, 1, 10) AS day FROM fridge LIMIT 3;

Check▶ Run

The idea

Databases for time series

There are also separate databases for time series: TimescaleDB (an extension of PostgreSQL that splits the table by time on its own and downsamples to hours on its own), InfluxDB and Prometheus (for server metrics). They all do the same as in the lesson: sort by time, compress the columns, keep downsampled tables and delete raw data after a period. The columnar ClickHouse and DuckDB handle them well too.

More precisely, if you are curious

In TimescaleDB an hourly table that updates itself is CREATE MATERIALIZED VIEW … WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', at) …, and the retention period is SELECT add_retention_policy('fridge', INTERVAL '1 day'). SQLite has nothing like it: the hourly table and the deleting are run on a schedule by the program.

Test

Check yourself

1. A report sums the column amount in a table with 7 columns and 100 million rows. How much of the data does a columnar database read?

Only the first rowOnly the column amount (and the columns in WHERE)All 7 columns

2. Why do columnar databases sort the rows (ORDER BY in a ClickHouse CREATE TABLE)?

Equal values end up next to each other and the column shrinks to a few runsTo look niceBecause they do not work without it

3. The sensor measures every 15 minutes, and the chart for a year has to be fast. What is usually done?

They measure less oftenThe chart reads all the readingsA table by hour (or day) with AVG, MIN and MAX is kept, and the raw readings are deleted after a period

4. The moving average for the last hour is in a subquery, and the WHERE for the period is outside. Why is it not in the same query?

There is no differenceBecause SQLite does not allow itWHERE runs before the window function, and the first rows of the period would lose their past hour

Check

Start the lesson over

Related lessons

• Builds on: CREATE TABLE: a new table · Natural keys and denormalization · Indexes · OLTP and OLAP
• Continues in: How to choose a database
• Tasks on this topic: Kinds of databases