Two teams ship the same feature. One picks PostgreSQL and watches analytics queries crawl. The other picks a columnar warehouse and watches a simple checkout write fall over under load. Neither team chose a bad database. They chose a database built for the wrong workload.
In Part 1, we looked at why databases emerged and how data systems grew from flat files into sprawling cloud ecosystems. That history leaves one question unanswered: why are there so many different types of databases at all?
Why do some companies lean on PostgreSQL while others reach for MongoDB, Redis, Cassandra, Snowflake, or a vector database built for AI search? The answer comes down to one idea: not all data problems are the same. This post walks through the architectural decisions behind databases, how they work internally, and when each type earns its place.
TLDR
- Databases split along workload: OLTP serves fast transactions, OLAP serves heavy analytics.
- That split shows up physically as row storage (OLTP) versus column storage (OLAP).
- ACID gives you strict reliability, BASE trades it for availability and scale (the CAP theorem forces the choice).
- Real systems run several databases at once. That pattern is called polyglot persistence.
Workloads: OLTP vs OLAP#
Most database systems are tuned for one of two purposes, and the gap between them explains nearly every architecture decision downstream.
OLTP keeps the business running#
OLTP (Online Transaction Processing) systems handle fast, real-time operations: sending money, logging into an app, placing an order. Each operation is small, but it has to be quick and reliable every single time.
| Characteristic | Importance |
|---|---|
| Fast writes | High |
| Strong consistency | High |
| Reliability | Critical |
Common OLTP databases: PostgreSQL, MySQL, MongoDB.
OLAP makes sense of the business#
OLAP (Online Analytical Processing) systems exist for analysis, not transactions. Instead of processing one payment, they scan huge volumes of historical data to answer questions like "which product category generated the most revenue over the last three years?"
| Characteristic | Importance |
|---|---|
| Large-scale queries | Critical |
| Aggregations | High |
| Fast writes | Less important |
Common OLAP databases: Snowflake, BigQuery, ClickHouse.
The two paths handle a request very differently. An OLTP write touches a single record and returns; an OLAP query fans out across millions of rows before it answers.
Mental model
OLTP runs the business. OLAP understands the business.
Row storage vs column storage#
To see why OLTP and OLAP perform so differently, look at how each one physically lays data on disk.
Row-based storage powers OLTP#
Traditional databases store data row by row. This works well for transactional systems because an application usually wants a whole record at once, like fetching a complete user profile in a single read.
Column-based storage powers OLAP#
Analytical systems store data column by column. To calculate the average age of a million users, a columnar database scans only the Age column, which makes aggregations dramatically faster than reading every full row.
Reliability and tradeoffs: ACID vs BASE#
The ACID guarantee#
Critical systems like banking need absolute reliability. Relational databases offer it through ACID:
- Atomicity: transactions are all-or-nothing.
- Consistency: data rules are never violated.
- Isolation: concurrent users do not interfere with each other.
- Durability: saved data survives system crashes.
The BASE tradeoff#
Not every application needs strict consistency. For global, highly scalable apps like social media, eventual consistency is acceptable. That model is called BASE (Basically Available, Soft State, Eventually Consistent).
The CAP theorem
In distributed systems, you cannot perfectly guarantee Consistency, Availability, and Partition Tolerance at the same time. You choose between strict consistency (showing only verified data) and high availability (always responding, even if data is slightly stale). This tradeoff is a major reason NoSQL databases exist.
Types of databases#
Different databases exist because different problems exist. Each type below optimizes for a workload the others handle poorly.
Relational (SQL)
PostgreSQL, MySQL. Best for transactions, reliability, and structured relationships.
Document (NoSQL)
MongoDB. Stores flexible JSON-like documents. Best for rapid development and changing schemas.
Key-Value
Redis. Extremely simple and fast in-memory storage. Best for caching and user sessions.
Wide-Column
Cassandra. Dynamic columns designed for massive distributed write-heavy workloads (like IoT or event logs).
Vector (AI Era)
Pinecone, pgvector. Designed to store numerical embeddings for AI semantic search.
The reality: polyglot persistence#
Large systems rarely rely on a single database. A modern internet-scale architecture usually routes each kind of work to the store that handles it best.
Each store earns its slot by doing one job well:
- PostgreSQL holds core application state and transactions.
- Redis serves lightning-fast caching.
- Elasticsearch powers full-text search.
- Amazon S3 stores raw unstructured data.
- Snowflake runs business analytics.
- pgvector handles AI retrieval.
This pattern is called polyglot persistence: using the right database for the right problem. Database engineering is not about finding the single best database. It is about matching each system to the specific workload in front of you.
Key Takeaways#
- The OLTP versus OLAP split is the first decision, and it drives almost everything else about an architecture.
- Storage layout is the physical reason for that split: row storage favors single records, column storage favors aggregations.
- ACID buys you strict reliability; BASE trades consistency for availability and scale.
- The CAP theorem means a distributed system cannot have strict consistency and full availability during a partition.
- Mature systems run several specialized databases together rather than forcing one engine to do every job.
FAQ#
When should I choose OLTP over OLAP?
Choose OLTP when your app does many small, fast, reliable reads and writes, like payments, logins, or order processing. Reach for OLAP when you scan large historical datasets for reporting and aggregation.
Why does column storage make analytics faster?
Analytical queries usually touch a few columns across many rows. Column storage keeps each column together on disk, so the engine reads only the data it needs instead of loading every full row.
Does choosing BASE mean my data is unreliable?
No. BASE means the system reaches a consistent state eventually rather than instantly. It is a deliberate tradeoff for availability and scale, and it suits workloads where a brief delay in propagation is acceptable.
What does the CAP theorem actually force me to decide?
During a network partition, you cannot keep both strict consistency and full availability. You pick one: refuse stale reads to stay consistent, or keep serving and accept that some data may be slightly behind.
Is polyglot persistence overkill for a small project?
Often, yes. A single relational database handles most early-stage apps. Add specialized stores like Redis or a search engine only when a real workload outgrows what your primary database does well.
Where do vector databases fit in this picture?
Vector databases store embeddings so you can search by meaning rather than exact matches. They sit alongside your existing stores, powering AI features like semantic search and retrieval rather than replacing your transactional database.
