TopolearnMost design trade-offs (normalise or not, which indexes, row or column store) depend on the workload.
Before choosing a schema shape or a database, ask what the workload looks like.
OLTP (online transaction processing) is what applications do: many small, concurrent reads and writes that touch a few rows by key — place an order, update a profile, fetch a user's cart. It needs low latency, strong correctness under concurrency, and normalised schemas with good indexes. PostgreSQL, MySQL and SQLite are OLTP databases at heart.
OLAP (online analytical processing) is reporting and analytics: fewer queries, each scanning millions of rows and aggregating a handful of columns — revenue per region per month. It favours column-oriented storage (only the needed columns are read, and they compress well), denormalised star schemas (a central fact table plus dimension tables), and batch loading. Warehouses such as BigQuery, Snowflake, ClickHouse or DuckDB are built for this.
Mixing them hurts both: a heavy report scanning an OLTP database competes with user traffic for I/O and can hold back cleanup of old row versions. The common architecture is to run the application on an OLTP database and copy data (via replication, change data capture or batch ETL) into an analytical store.
When someone says "the database is slow", find out which kind of query is slow — the fixes are different.
Example
CREATE TABLE orders (id bigint PRIMARY KEY, status text, region text,
total numeric, placed_at timestamptz);
-- OLTP: touch a few rows by key, many times per second
SELECT id, status FROM orders WHERE id = 48213;
UPDATE orders SET status = 'paid' WHERE id = 48213;
-- OLAP: scan and aggregate many rows, a few times per hour
SELECT date_trunc('month', placed_at) AS month,
region,
sum(total) AS revenue
FROM orders
WHERE placed_at >= '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;Remember
- OLTP: many small keyed reads/writes, normalised, row store
- OLAP: few big scans and aggregates, denormalised, column store
- Keep heavy analytics off the primary OLTP database