Category 1 — OLTP Engines (PostgreSQL, MySQL, SQL Server, Oracle)

Row-oriented storage, normalized schemas, strong transactional guarantees (ACID), fast single-row reads/writes — the database behind an application. PostgreSQL is the closest thing to a modern default; Oracle's PL/SQL (Part 11) is still common in banking, telecom, and government systems.

Pros:

• ACID transactions guarantee correctness under concurrent writes — two people can't double-book the same seat, and a payment charge + inventory decrement either both happen or neither does.
• Fast single-row lookups/writes via primary-key indexes — fetching or updating one specific row stays fast even as the table grows.
• Referential integrity (foreign keys, constraints — Part 1) is enforced by the engine itself, not left to application code to get right every time.
• Decades of mature tooling, drivers, and operational knowledge — nothing here is exotic to hire for or operate.

Cons:

• Row-oriented storage makes "scan and aggregate 500M rows" slow and I/O-heavy — the wrong physical shape for wide analytical queries (Part 9).
• Vertical scaling has a real ceiling — a single write-heavy instance eventually needs read replicas or sharding, genuine engineering effort, not a config flag.
• Not built for ad hoc queries touching many columns across huge historical tables — that's a warehouse's job (Category 2).

Real scenario — why it wins: an e-commerce checkout. Each order needs its payment charge and inventory decrement to succeed or fail together (a transaction), thousands of shoppers may write concurrently, and each individual query touches a handful of rows (this one order, this one customer). PostgreSQL wins here because ACID guarantees plus fast single-row access is exactly the job description — a columnar warehouse, optimized for scanning many rows at once, would be both slower and needlessly expensive for this write-heavy, single-row access pattern.

Category 2 — Cloud Analytical Warehouses (Snowflake, BigQuery, Databricks, Fabric, Redshift)

Columnar storage, elastic/serverless compute, mostly-separated storage and compute (Redshift is the architectural exception — Part 12). This is what a BI tool or a scheduled dbt pipeline (Part 10) actually queries.

Pros:

• Columnar storage + elastic compute scan and aggregate billions of rows fast, without anyone provisioning or managing hardware.
• Storage/compute mostly separated (Redshift excepted) means query power scales independently of how much data is stored — and on BigQuery/Snowflake, you largely pay only when actually querying.
• Native integration with the analytics ecosystem — BI tools, dbt (Part 10), orchestration — built for that workflow, not bolted onto it.

Cons:

• Poor fit for high-frequency, low-latency single-row writes — an OLTP-style checkout flow here would be slow and often needlessly costly.
• Real cost risk: a badly written query can scan far more data (and money) than expected, with no query-plan visibility unless you check before running it (Part 9's cost note).
• Some proprietary SQL extensions don't port cleanly between platforms (Snowflake Scripting, BigQuery-specific functions — Part 12) — a real, if partial, lock-in tendency.

Real scenario — why it wins: a BI dashboard aggregating a year of sales across every region. It needs to scan hundreds of millions of rows, group by a dozen dimensions, and return in seconds when an analyst refreshes a Tableau/Power BI report. A cloud warehouse wins because its entire architecture — columnar storage, serverless/MPP compute — is purpose-built for "read a lot, write rarely." An OLTP engine would either time out or need to be over-provisioned well beyond what its transactional design is meant for.

Category 3 — Embedded / Local (SQLite, DuckDB)

SQLite (row-oriented, the engine behind Parts 1-8 of this series) and DuckDB (columnar, "SQLite for analytics," behind Part 9's pruning demo and Part 10's dbt project). Neither replaces a warehouse at production scale — but for local development or a dataset that fits on one machine, reaching for a cloud warehouse is often overkill.

Pros:

• Zero install, zero server — a single file (SQLite) or an in-process engine (DuckDB), running wherever the app or notebook itself runs.
• Genuinely fast at machine scale — DuckDB in particular rivals warehouse-grade analytical performance for data that fits on one machine.
• Ideal for CI test suites and local development — real SQL semantics, none of the setup cost or cloud account this entire series was built to avoid.

Cons:

• No concurrent multi-writer support at production scale — SQLite in particular locks the whole database file for a write.
• No built-in high availability, replication, or access control — not meant to survive a server dying or serve many simultaneous users.
• A genuine data-volume ceiling — doesn't scale past what fits on one machine's disk/memory.

Real scenario — why it wins: writing and testing this entire series' 14 notebooks. Every concept from JOINs to window functions to a real dbt project needed to run reproducibly on any machine, with zero setup and no cloud account or credit card required. SQLite/DuckDB win here because the job isn't "serve production traffic" — it's "verify the SQL is correct, fast, for free, anywhere" — and spinning up a warehouse account for that would add friction for zero benefit.

In-Memory Databases: A Different Axis Entirely

"In-memory" isn't a fourth category alongside the three above — it's an orthogonal property that cuts across all of them. An in-memory database keeps its working data resident in RAM instead of reading it from disk on every query, which is what makes SQLite's :memory: mode and DuckDB's default in-memory connection (both used throughout this series) so fast for small datasets. The same idea scales up in production: Redis and Memcached are the two most widely used in-memory stores by far, almost always deployed as a caching layer in front of a slower OLTP database or warehouse — a session store, a rate limiter, a leaderboard, or a cache of expensive query results. Purpose-built in-memory SQL engines exist too for when the whole workload needs that speed, not just a cache: SAP HANA and Oracle TimesTen are the enterprise names, built for sub-millisecond transactional and analytical SQL at scale.

Accuracy note: Redis and Memcached are not SQL databases — they're key-value stores with their own APIs. They come up constantly in "in-memory database" conversations, but they don't belong next to SQLite/DuckDB/HANA/TimesTen on a "which SQL engine" decision; they solve a different problem (a fast cache in front of a system of record) rather than replacing that system of record.

Pros:

• Sub-millisecond latency — no disk I/O in the read/write path, often 10-100x faster than the same operation against a disk-backed database.
• Very high throughput for simple, repeated lookups — exactly the shape of a cache hit or a session read.
• Simple mental model (for Redis/Memcached specifically) — mostly key-value gets and sets, easy to reason about and operate.

Cons:

• Volatility risk — data in RAM disappears on a crash or restart unless the specific product adds persistence (snapshotting, an append-only log, replication); never treat a cache as the system of record unless it's explicitly built to be one.
• Cost — RAM is priced far higher per gigabyte than disk, so an in-memory dataset that would be cheap to store on a warehouse gets expensive fast at scale.
• A hard ceiling — the dataset has to fit in the memory of the machine (or cluster) running it; this doesn't scale to "billions of rows" the way a disk-backed warehouse does.

Real scenario — why it wins: a product page showing "1,204 people viewed this today." Recomputing that count from the warehouse on every page load would be slow and expensive at any real traffic volume; incrementing a counter in Redis and reading it back is a sub-millisecond operation that can absorb enormous read traffic. Redis wins here because the job is "serve a hot, frequently-read number fast," not "be the durable record of every view event" — the warehouse or OLTP database still owns that.

The Decision Isn't Permanent

Many real architectures run more than one category at once — an OLTP engine feeding a warehouse via CDC (Orchestration & CDC). Don't default to "whatever's newest": an OLTP question answered with a warehouse (or vice versa) usually surfaces as a cost or performance problem months later, not immediately.