Beyond SQL: choosing the right data store
Postgres can do far more than most teams use it for, and a second database brings a second set of backups, failures and skills to learn. This lesson helps you tell when Postgres is enough, when another kind of store really earns its place, and how to move data into analytics without slowing down the store tills.
By the end you can
- Explain OLTP and OLAP, and why one database struggles to do both
- Name the main families of data stores and the one job each does best
- Use JSONB, full-text search and materialized views in Postgres before reaching for another product
- Choose between batch ETL and change data capture for feeding a warehouse
- Walk through a short decision guide for a new storage need
The everyday picture
Walk through a Mangrove Market store. Fish and meat go in the freezer. Milk and yoghurt go in the chiller. Rice, oil and soap sit on open shelves where customers can reach them. Extra cartons wait in the backstore. Cash goes in the cash box, and at night into the safe.
Nobody stores rice in the freezer "to keep everything in one place", and nobody builds a separate building for each kind of biscuit. Each storage place exists because it does one job much better than the others, and each one costs money and staff time to run.
Data stores work the same way. Postgres is the big, well-organised shelf that can hold almost everything. A few special needs deserve their own freezer. The skill is knowing which needs those are.
OLTP and OLAP: two different jobs
OLTP is the everyday work of an app: record this sale, reserve this stock, show this order. Each query touches a few rows, finds them through an index, and must finish in milliseconds. Thousands run at once. Postgres, organised by rows, is built for this.
OLAP is analysis: "revenue by category, by division, for each week of the last two years". One query scans hundreds of millions of rows but reads only 3 or 4 of their 20 columns, then groups and sums them. There are few such queries, but each one is huge.
Running both on one primary hurts both. A large report fills the cache with old sales, uses CPU and disk for minutes, and makes POS writes slower. And a row store is slow at OLAP, because it reads every column of every row even when the query needs three. Columnar warehouses such as ClickHouse, BigQuery, Snowflake or Amazon Redshift store each column separately and compressed. A query that needs 3 columns reads only those 3, often many times faster.
So the common shape is: Postgres for the app, a copy of the data in an analytics store for reports, and a pipe between them.
Quick check
Which of these is an OLAP query?
OLAP means few, huge queries that scan and group many rows, and the weekly revenue report scans hundreds of millions of rows for a few columns. The other three touch a handful of rows by key and must finish in milliseconds: classic OLTP.