OLTP vs OLAP: Why Your App Database Isn't Built for Analytics

Introduction Most engineers start their journey with PostgreSQL or a similar relational database. These systems handle everyday tasks exceptionally well: storing users, orders, and settings. But when the business starts asking questions - "How many orders did we have last quarter?", "Which products are bought together?" - performance problems emerge. The issue isn't that PostgreSQL is bad. It was designed for atomic transaction processing (Online Transaction Processing), while analytics is a different type of workload (Online Analytical Processing). In this article, I'll explore the precise differences between these two task types and why analytics requires specialized systems like ClickHouse. What is OLTP - Databases for Applications When a customer places an order in an online store, the application must perform several actions simultaneously: record the order, decrease the quantity of goods in inventory, and create a payment record. All these actions must happen together -either everything or nothing. If payment is processed but the product isn't deducted from stock, that's a problem. That's why the primary task of systems designed to handle such events is the ability to reliably record and quickly find specific records. This is exactly what OLTP systems (Online Transaction Processing - real-time transaction processing) are built for. What Does an OLTP Query Look Like? A typical query in an OLTP system accesses a specific record through a unique identifier or an exact filter: SELECT addr1, addr2, street, town, price FROM properties WHERE postcode = 'BB1 6NP'; Such a query returns a few rows and executes in milliseconds. The database uses an index - like an index in a book - to jump directly to the needed record without scanning the entire table. Characteristics of OLTP Systems These characteristics are common across all OLTP systems. | Characteristic | OLTP Value | |----|----| | Rows read per query | Tens to hundreds | | Queries per second | Thousands to tens of thousands | | Operation types | Key-based reads, writes, updates, deletes | | Response time | Milliseconds | | Data guarantees | ACID: all or nothing, data always correct | When OLTP Is the Right Choice Online store: order checkout, shopping cart management, payment processing Banking application: transfers, points accrual, transaction history CRM system: customer profiles, manager tasks, communication history Any scenario where data needs to be quickly found by a specific ID or condition What is OLAP - Databases for Analytics Now consider a different query: "What is the revenue by city for last year?" This question isn't about a specific record - it requires reviewing all orders for a year, grouping them by city, and calculating totals. This is an entirely different type of workload. OLAP systems (Online Analytical Processing - analytical data processing) are designed precisely for such tasks: they process massive volumes of data and quickly return aggregated results. What an OLAP Query Looks Like The same real estate dataset, but now an analytical query: SELECT town, count() as count_of_deals, round(avg(price)) as average_price FROM properties WHERE date >= '2026-01-01' GROUP BY town ORDER BY average_price DESC LIMIT 20; This query can process tens of millions of rows and return just 20 results. In PostgreSQL , it would take minutes; in ClickHouse - seconds or fractions of a second. The reason comes down to how data is physically stored on disk. PostgreSQL stores data row by row - to calculate avg(price) , it must read every column of every row in the table, even columns you don't need. Analytical databases like ClickHouse use a different approach called columnar storage : each column is stored separately, so a query over price reads only the price column and skips everything else entirely. This is the core architectural reason the same query runs in seconds instead of minutes. I'll examine columnar storage in detail in a separate article. OLAP System Characteristics These characteristics are common across all OLAP systems. | Characteristic | OLAP Value | |----|----| | Rows read per query | Millions to billions | | Operation types | Reads with aggregation (count, sum, avg), GROUP BY | | Data volume | Terabytes to petabytes | | Response time | Seconds (in modern systems - fractions of a second) | | Example systems | ClickHouse, BigQuery, Snowflake | Types of Analytical Tasks Analytical queries look very different, but most of them can be reduced to several recurring patterns. Understanding these patterns helps you design tables properly and write efficient queries. Aggregations Aggregations are the most common type of analytical queries. The idea: take many rows and collapse them into a single result using a function. Key aggregation functions: count() - how many rows match the condition sum(column) - sum of values avg(column) - average value min() / max() - minimum and maximum value uniq(column) - count of distinct values (e.g., unique users) Typical questions solved by aggregations: How many unique users made a purchase this month? What is the average order value by country? Which product sells the best? Aggregations are the foundation of any BI dashboard. Almost every widget on a dashboard is the result of some aggregation. Time Series Time Series are data points tied to time: user events, sales by day, application metrics. This is one of the most common data types in analytics. Typical questions: How did revenue change day by day? When are users most active during the day? Is there seasonality in purchases? Analytical databases excel at working with time series: data is physically stored sorted by time, which allows fast reading of the required date range without scanning the entire table. Time Series and Aggregations are the foundation of most dashboards and reports. Funnels and Cohorts More complex patterns typical of product analytics. A funnel shows how many users completed each step in a sequence - for example, visited the site → added a product to the cart → completed the purchase. A funnel helps identify at which step the most users drop off. Cohort analysis groups users by the date of their first action and tracks their behavior over time. For example: all users who signed up in January, how many returned after one month, after two? OLTP vs OLAP: The Fundamental Difference Both types of systems use SQL, which can be misleading: it seems like one system can do everything. In practice, they are optimized for opposite scenarios. | Question | OLTP | OLAP | |----|----|----| | Purpose? | Store and quickly find a specific record | Calculate and compare large volumes of data | | Rows per query? | Tens to hundreds | Millions to billions | | Type of operations? | INSERT, UPDATE, DELETE, SELECT by ID | SELECT with GROUP BY, aggregations | | Who uses? | Application, end user | Analyst, BI instrument, dashboard | | What data? | Current state right now | History over a period: day, month, year | | Volume? | Gigabytes to terabytes | Terabytes to petabytes | A simple analogy: OLTP is a cashier in a store who quickly processes one receipt. OLAP is an accountant who at the end of the month analyzes all receipts and calculates totals. PostgreSQL vs ClickHouse: A Simple Comparison Many teams already use PostgreSQL and ask: why do we need ClickHouse if Postgres can also do GROUP BY and aggregations? Short answer: use PostgreSQL to store your application data and ClickHouse for analytics on that data. They are not competitors, but partners. | Task | PostgreSQL | ClickHouse | |----|----|----| | Application data storage | Excellent | Not designed for this | | Transactions (all or nothing) | Yes, fully | Limited | | Queries on billions of rows | Slow (minutes) | Fast (seconds) | | Analytics and aggregations | Works, but slowly | Primary purpose | | Data compression | Basic | Efficient, up to 10x | | Scaling for large volumes | Difficult | Designed for this | Summary OLTP systems (PostgreSQL) are built for fast work with specific records: find, store, update, with reliability guarantees OLAP systems (ClickHouse) are built for analyzing large volumes of data: count, group, compare over a period Main patterns of analytical tasks: aggregations, time series, funnels, and cohort analysis PostgreSQL and ClickHouse complement each other: one stores application data, the other handles analytics \