Picking the Right Database — A Decision Framework

After workload shape, ask: when two clients read the same data, how fresh must that data be?

• Strong / ACID — every read sees the latest committed write. Non-negotiable for financial systems, inventory, healthcare records.
• Eventual — replicas converge over time (seconds to minutes). Fine for social feeds, analytics counters, search indexes.
• Tunable — per-query consistency level. Cassandra's QUORUM vs ONE; DynamoDB's strongly consistent reads. Flexible but adds complexity.
• Monotonic read — you never see an older version after reading a newer one, but you might read slightly stale data. Good middle ground for user-facing apps.

Why this matters: strong consistency requires coordination between replicas (quorum reads, consensus writes). That coordination costs latency. If your app can tolerate eventual consistency, you unlock databases that are far simpler to scale horizontally — but the trade-off is real bugs if your code assumes freshness it doesn't actually have.

Be honest about your growth trajectory. Where do you realistically land in 3 years?

• Small (< 100 GB, single region) — almost any database works. Optimize for developer familiarity and simplicity.
• Medium (100 GB–5 TB, single region) — Postgres with read replicas handles most of this. Add connection pooling (PgBouncer) and proper indexes before reaching for anything exotic.
• Large (5 TB+, single region) — consider partitioning, sharding, or a distributed SQL engine (CockroachDB, YugabyteDB).
• Global (multi-region, low-latency reads worldwide) — distributed NewSQL (Spanner, CockroachDB), globally replicated caches (Redis Global), or regional Postgres with replication + routing logic.

Common mistake: picking a globally-distributed database for a 5,000-user app because "we might go global." Distributed systems add latency, operational overhead, and debugging complexity. Scale when you must; not before.

The best database for your workload is useless if your team can't operate it reliably. Ask: how much operational complexity can we absorb?

• Managed vs self-hosted — RDS / Aurora / Atlas handle upgrades, backups, and HA for you. Self-hosted gives full control but requires expertise.
• Team experience — a team that knows Postgres deeply will outperform a team struggling with a "better" database they barely understand.
• On-call burden — distributed databases (Cassandra, CockroachDB) have failure modes that require 3 AM debugging by people who understand consensus, replication lag, and node decomissioning. Is your team ready for that?
• Ecosystem maturity — observability tooling, ORMs, migration frameworks, managed hosting options. Postgres wins this by a wide margin over almost everything else.

Cost is a tiebreaker, not a primary driver — but it can flip a close decision. Understand what you're actually paying for:

• Per-node — you pay for compute/memory regardless of query volume. Postgres, MySQL, Cassandra. Predictable but idle capacity wastes money.
• Per-query — you pay for what you use. BigQuery, Athena. Great for infrequent large scans; can be shocking if queries are poorly written.
• Per-GB stored + per-read/write unit — DynamoDB's model. Predictable at known access patterns; expensive if you scan large tables.
• License — MongoDB's SSPL license and Oracle's pricing are real concerns at enterprise scale. Postgres is fully open-source (PostgreSQL License).

At small scale, cost differences are negligible. At 10 TB+ or high QPS, a 3× cost difference between databases matters. Model your actual access patterns against each pricing model before committing.

Now imagine an analyst pulling up a dashboard to see "total revenue by region, by product category, for Q3 last year" — one big, slow query that touches millions of rows but runs infrequently. That's OLAP: few queries, but each one scans enormous amounts of data.

Technical definition: workloads characterized by low concurrency, long-duration scans, aggregations across millions of rows (SUM, AVG, COUNT GROUP BY), and read-only access patterns. BI dashboards, data warehouses, and reporting systems are all OLAP workloads.

Why it matters: OLAP databases use columnar storage — reading only the columns your query needs — and massively parallel query execution. BigQuery, Redshift, Snowflake, ClickHouse, and DuckDB are OLAP databases.

The obvious question after OLTP vs OLAP: can one system do both? HTAP systems try to answer yes — handle your transactional writes AND serve analytical queries on fresh data, without replication lag or separate ETL pipelines.

Technical definition: a database architecture that serves both OLTP and OLAP workloads from the same system, typically by maintaining both row-oriented storage (for OLTP) and columnar storage (for OLAP) on the same data, kept in sync automatically.

Real examples: TiDB (open-source HTAP from PingCAP), SingleStore, YugabyteDB with read replicas. Google Spanner recently added HTAP capabilities.

Honest caveat: HTAP systems are more complex to operate and often underperform dedicated OLTP or OLAP databases at the extremes. Use them when the freshness guarantee (analytics on data seconds old, not hours old) is a genuine business requirement — not just convenience.

The idea is simple: use the right database for each job, even if that means using five different databases in one application. Your user profiles in Postgres, your session tokens in Redis, your product catalog in Elasticsearch, your event log in Kafka/S3, your recommendation graph in Neo4j.

Technical definition: an architectural pattern where different components of an application use different data storage technologies, each chosen to best fit that component's access patterns.

The trade-off: each additional database adds operational overhead (monitoring, backups, upgrades, access control), introduces potential consistency boundaries between systems, and adds to onboarding complexity for new engineers. Polyglot persistence is a deliberate tool — not the default. Start with one database and only add a second when a measured bottleneck demands it.

Imagine your database is spread across two cities and the cable between them gets cut. You have to choose: refuse the request (boring but correct), or answer with possibly-old data (helpful but maybe wrong). That's the heart of the CAP theorem. Formally: a distributed database can only guarantee two of three properties — Consistency (every read sees the latest write), Availability (every request gets a response), and Partition tolerance (the system keeps working if network links between nodes fail). Since network partitions are unavoidable in real distributed systems, every distributed DB is really choosing between CA and CP behavior when a partition occurs.

PACELC extends this: even without a partition, there's a trade-off between latency and consistency. A system that achieves strong consistency requires coordination between replicas — that coordination takes time (adds latency). A system that answers immediately may return slightly stale data. PACELC captures both trade-offs: during a Partition → CA vs CP; Else → Latency vs Consistency.

Practical takeaway: don't pick a database based on CAP alone. CAP tells you what happens during a network failure (rare). PACELC's latency vs consistency trade-off affects every single request.

Vendor lock-in happens when you use database-specific features so deeply that migrating away becomes extremely painful. Once you've built your application around DynamoDB's single-table design pattern, or used dozens of proprietary Firestore collection group queries, or relied on Aurora-specific parallel query features, the "exit cost" is enormous.

Why it matters for DB selection: proprietary managed databases often have the best performance and simplest operations, but they tie your infrastructure to one cloud vendor. Open standards (Postgres-compatible APIs, MySQL-compatible protocols) give you portability — you can move from RDS to Aurora to Supabase to self-hosted without rewriting application code.

The honest answer: some lock-in is acceptable if the productivity and performance gains are real. The risk to manage is lock-in to a pricing model that becomes punitive at scale, or lock-in to a vendor that gets acquired or deprecated.

Some domains genuinely have variable structure. A CMS where every article type has different metadata fields. A product catalog where a TV has HDMI ports and a shirt has a size chart. A user profile system where optional fields vary by feature flags. In these cases, a rigid relational schema fights you at every schema migration.

Why document stores win here: each document is a self-contained JSON blob — no schema migration needed to add a new field to some documents. Nested objects and arrays map naturally to the app's object model. Horizontal scaling via sharding is built-in.

Honest warning: "variable-shape" is often used as an excuse for not designing the data model properly. Before choosing a document store, ask: do your queries actually need the flexibility, or is the schema just 95% consistent with 5% variation? Postgres's JSONB column handles the 95/5 case elegantly without abandoning relational structure.

Sessions, rate-limit counters, leaderboard scores, feature flags, ephemeral locks — all of these share one property: you access them by a single known key, you need sub-millisecond response times, and you don't need joins or complex queries.

Why Redis wins here: all data lives in RAM (disk persistence is optional), access is O(1) by key, and Redis supports rich data structures (sorted sets for leaderboards, streams for queues, pub/sub for notifications) that make common patterns trivial to implement.

When not to use Redis as primary storage: data that must survive crashes (use a durable store for source of truth, Redis for the cache/hot layer), and data that's too large to fit in RAM economically.

If your data is append-only measurements — "temperature sensor 42 read 23.4°C at 14:32:07" — repeated billions of times, you have a time-series workload. The key insight: this data is never updated in place. It's always written once and then read as a range scan (last 24 hours, last 7 days).

Why TSDBs win here: they automatically partition data by time, apply columnar compression per time chunk (time-series data is highly compressible — adjacent timestamps differ by milliseconds, adjacent float values differ by tiny amounts), and answer time-range aggregations using chunk-level metadata to skip irrelevant partitions entirely.

The result: TimescaleDB query benchmarks typically show 100–1000× speedup over vanilla Postgres for time-range aggregations on large datasets, plus 90%+ storage compression. These aren't marginal gains — they're the difference between a usable and an unusable system.

When users search your app — typing "blue waterproof hiking boots size 10" — they expect results that match semantics, not just exact strings. Facets ("filter by brand: Nike"), highlighting ("here's where your search term appears"), and relevance scoring ("this result matches 4 of 5 terms; this one matches 2") make search feel like search.

Why search engines win here: they build an inverted index — a structure that maps every word to every document containing it. Querying "boots" returns all matching documents in O(1) regardless of corpus size. SQL's LIKE '%boots%' scans every row sequentially: O(n).

Use Postgres FTS for simple cases: pg_trgm and built-in full-text search handle simple text matching well. Switch to Elasticsearch/OpenSearch when you need faceted filtering, real-time indexing at high volume, or semantic/vector search.

Some problems are fundamentally about connections. Fraud detection: "is this new account connected to any known fraudulent account within 3 hops?" Social networks: "suggest friends based on mutual connections." Recommendation engines: "users who bought X also bought Y, and those users also liked Z — what does that tell me about you?"

Why graph databases win here: in a relational database, finding all nodes within N hops requires N self-joins — each additional hop multiplies query cost. A graph database stores relationships as first-class objects with direct physical pointers, so a 3-hop traversal is 3 pointer dereferences per node, not 3 full table scans.

When graph DBs are overkill: if your "graph" queries are simple parent-child relationships (one level deep) with low edge counts, a recursive CTE in Postgres handles it cleanly. Graph databases pay off when traversals go many hops deep across millions of edges.

Every programming language has a mature Postgres driver. Every ORM (SQLAlchemy, Hibernate, Prisma, ActiveRecord, GORM) has first-class Postgres support. Every hosting platform (AWS RDS, Google Cloud SQL, Azure Database, Supabase, Neon, Railway) offers managed Postgres. Every monitoring tool understands Postgres query plans.

This ecosystem depth means you're never the first person to do what you're trying to do. Schema migration tools, connection poolers, read replica libraries, logical replication setups — all of this is solved, documented, and maintained by thousands of contributors.

Postgres's extension system lets you install specialized storage engines and query types without changing databases:

• pgvector — store and query ML embedding vectors; power semantic search and recommendation systems. Used in production by Supabase and many AI startups.
• TimescaleDB — automatic time-partitioned hypertables + columnar compression for time-series workloads. 10–1000× faster than vanilla Postgres for time-range queries.
• PostGIS — full geospatial support: store geometries, query "all restaurants within 2 km," compute polygon intersections. Industry standard for spatial data.
• pg_trgm + built-in FTS — trigram indexes for fuzzy string matching; full-text search with ranking, stemming, and language support for many simple search use cases.
• Citus — horizontal sharding that turns Postgres into a distributed database, acquired by Microsoft and available on Azure.

Every database you add to your stack multiplies operational overhead: monitoring dashboards, backup procedures, upgrade runbooks, access control policies, connection limits, failover procedures, oncall alert rules. Running one database means running one set of all of these.

With Postgres, you get all your data in one place — no data sync pipelines, no eventual consistency between systems, no cross-system transactions. A complex query that joins user profiles, order history, product metadata, and recent reviews is a single SQL statement. In a polyglot persistence setup, that same query requires 4 separate API calls, application-level join logic, and careful cache invalidation.

Operational simplicity compounds over time: fewer systems to monitor, fewer incidents to respond to, and faster onboarding for new engineers who already know SQL.

What Postgres and most relational databases offer by default. All transactions execute as if they ran one at a time in some order — but that order doesn't have to match wall-clock time exactly. Within a single node (or synchronous replica set) this is extremely efficient. The WHY: a single master with a write-ahead log naturally gives you serializable reads without any inter-DC coordination — just one process, one clock, one log.

You only ever see data that has been committed — no dirty reads from in-progress transactions. But two reads in the same transaction can return different values if someone commits a change between them (non-repeatable reads). This is the default isolation level for most databases including Postgres, MySQL, and Oracle. Why default here? Because it's the sweet spot: it prevents the most common anomalies (dirty reads) while adding minimal lock overhead.

The system guarantees that eventually all replicas converge to the same value — but a read right after a write might return the old value. Cassandra's default, DynamoDB's default, S3, DNS — all eventual. Why choose this? Because you get dramatically higher write throughput (no coordination overhead) and near-100% availability even during network partitions. The cost: your application must be designed to tolerate stale reads — idempotent operations, conflict-resolution logic, or "last-write-wins" semantics.

A middle ground that preserves the most important human intuition — "I just wrote this, I should be able to read it back." Formally: if operation B happens because of operation A (B was issued after A completed, on the same session or via an explicit token), then B will always see A's effects. Unrelated operations from other sessions may still be stale. MongoDB sessions and Yugabyte's bounded-staleness reads use this model. The WHY it's cheaper: you only need to track which operations follow which (a small token per session), not synchronise clocks across every node in the world.

Every write to a durable database goes through the Write-Ahead Log (WAL): the database writes the change to a sequential log file, fsyncs it to disk, then applies it to the data pages. This design is intentional — sequential log writes are fast, and they give you crash recovery. But the fsync call is the bottleneck: each fsync blocks until the OS confirms the write hit durable storage. SSDs help (roughly 10K–100K fsyncs/sec), but a single Postgres node doing write-heavy OLTP typically tops out around 50K–100K writes/sec, depending on write size and index count.

If you replicate data across regions (US-East to EU-West, for example), every write that requires synchronous confirmation from the remote replica adds the round-trip time for that link — roughly 80–150 ms for transatlantic, 150–200 ms for US-to-Asia. This isn't a database limitation; it's the speed of light. The WHY matters here: strong consistency across regions is physically bounded by how fast information can travel. Spanner uses TrueTime to minimise this overhead, but it can't eliminate the physics. Most cross-region read replicas are asynchronous precisely to avoid blocking writes on this latency.

This one's often missed: your team's ability to operate the database is itself a ceiling. A 3-person startup running a self-managed Cassandra cluster with manual resharding is operating close to their ops ceiling — a single late-night incident can be catastrophic. Sharding and distributed-SQL systems add deployment, monitoring, capacity planning, and failure-recovery complexity that grows super-linearly with the number of nodes. The practical ceiling here is "how many on-call engineers do you have, and how familiar are they with this system under fire?"

Online Transaction Processing — the heartbeat of e-commerce, banking, and SaaS applications. Roughly equal reads and writes, often in small transactions that touch a handful of rows at a time. Postgres and MySQL were built for exactly this: B-tree indexes make point reads fast, the WAL makes writes durable, and MVCC lets reads and writes run concurrently without blocking each other. At higher scale, CockroachDB or Aurora add horizontal write capacity while preserving the SQL interface your team already knows.

When writes dominate, B-tree indexes become the enemy — every write must update every index covering that table, and those updates are random I/O. Cassandra and ScyllaDB solve this with the LSM-tree (Log-Structured Merge-tree): writes go to an in-memory structure (memtable) first, then are flushed to immutable sorted files (SSTables) in a sequential batch. No random writes, no index thrashing. The trade-off: reads may need to merge data from multiple SSTables, making point reads more expensive than in a B-tree system. This is why Cassandra shines for write-heavy workloads but struggles for ad-hoc complex queries.

The most extreme write pattern: you never update or delete, you only append. Server logs, IoT sensor readings, user click events, financial audit trails — all append-only. Time-series databases like TimescaleDB, InfluxDB, and Prometheus are designed specifically for this: they compress sequential timestamps aggressively (delta encoding: store differences rather than full values), automatically partition data by time window, and let you drop old partitions with a single operation instead of running expensive DELETE queries. For even higher throughput, Kafka is a write-ahead log at internet scale — designed for millions of appends per second from many producers simultaneously.

Everything that happens after you ship: automated backups and restore tests, version upgrades (Postgres major version upgrades require careful planning), monitoring query performance as data grows, and capacity planning before you hit limits. Managed services handle most of this automatically — AWS RDS handles minor version patching during maintenance windows, takes daily snapshots, and alerts on disk usage. Self-hosted systems require you to build and own all of this.

What happens when a node crashes, a disk fails, or a region goes down? Managed services give you automatic failover — RDS Multi-AZ switches to a standby replica in under 30 seconds. Self-hosted Postgres requires a tool like Patroni or repmgr plus a runbook that every on-call engineer must know cold. Self-hosted Cassandra requires understanding anti-entropy repair, hinted handoff, and the implications of a node being out of the ring for more than the grace period — complexity that requires dedicated database engineering expertise.

SQL is the most widely known query language in software engineering. Switching to Cassandra Query Language (CQL) or DynamoDB's access-pattern-first data model requires retraining — and CQL in particular involves mental model shifts that take months to internalize (no JOINs, primary key must encode your query patterns, partition key determines physical locality). The vendor support dimension is also real: community Postgres is excellent but enterprise tiers (AWS RDS support, Google Cloud SQL support) provide SLAs, architecture reviews, and 24/7 incident escalation. Community forums don't page someone at 3 am.

DynamoDB on-demand charges roughly $0.125 per million read request units and $0.625 per million write request units in US East after AWS's November 2024 50% price cut (other regions can still run closer to the older $0.25 / $1.25 figures, so always check the calculator for your region). Aurora Serverless v2 scales capacity in ACU (Aurora Capacity Units) up and down within seconds. This model is ideal for variable workloads: you pay nothing for idle periods and scale seamlessly for traffic spikes. The trap: at sustained high volume, the cost curve becomes linear and often exceeds a reserved per-node instance. A rough rule of thumb: if your traffic is below roughly 50K requests/day, serverless wins; above that, per-node reserved capacity starts winning.

BigQuery charges around $0.023/GB/month for storage, and only charges for query compute when you actually run a query ($6.25/TB scanned). Snowflake's storage pricing is similar. This model is perfectly suited for analytics and cold data: you store petabytes cheaply and query them occasionally. For hot OLTP data with thousands of queries per second, this model is expensive per-query — it's not designed for sub-millisecond response times. The WHY: cloud warehouses are built on object storage (S3 / GCS) which is ~$0.023/GB/month versus block SSD at ~$0.11/GB/month — they pass that storage discount to you.

Oracle and Microsoft SQL Server charge per-core licensing fees that can run tens of thousands of dollars per year, separate from hardware costs. These models are typically locked to enterprise contracts, include certified support SLAs, and historically come with features (row-level security, advanced partitioning, in-memory columnar engines) that weren't available in open-source alternatives. The WHY these still exist: large enterprises with compliance requirements (finance, healthcare, government) need licensed software with indemnification and contractual support. The practical implication: the license is often the single largest line item, which makes growth very expensive.

Postgres has types that don't exist elsewhere: JSONB with GIN indexing, hstore (key-value pairs inside a column), PostGIS geography types, ARRAY columns, range types. If your schema uses Postgres-specific types heavily, migrating to MySQL or any other database requires schema redesign, not just a data dump. Spanner has interleaved tables — a physical layout where child rows are stored adjacent to their parent rows on disk for performance. It's powerful, but it encodes Spanner's physical storage model directly into your schema design, making the schema non-portable by definition.

Your backup scripts, monitoring dashboards, schema migration tools, and data pipeline connectors may be built for one database's specific APIs. AWS RDS has native integrations with CloudWatch, AWS Backup, and Parameter Store. Migrating to Google Cloud SQL means rebuilding your monitoring setup, testing new backup procedures, and updating every integration. This tooling lock-in is often underestimated because it's not visible in the database itself — it's distributed across your entire infrastructure. The deeper your ops tooling integrates with vendor-specific APIs, the higher the migration coordination cost.

Moving data out of a cloud provider's network costs real money. AWS charges roughly $0.09/GB for data leaving its network. A 10 TB database migration costs around $900 in egress fees alone — before the engineering time to validate the migration and cut over. For larger databases (100 TB+), egress costs can reach tens of thousands of dollars, turning a technically straightforward migration into a budget discussion. This is one of the most underappreciated forms of lock-in: you might be able to run the same SQL queries on a different cloud, but getting the data there first is expensive.

Relational databases enforce a schema upfront — every row in a table has the same columns. This feels restrictive, but it's actually a guarantee: your application code can always assume column X is present and has type Y. The database becomes the enforcement layer so your application code doesn't have to be.

Document databases store each record as a JSON-like object with potentially different keys per document. This feels liberating, but the flexibility doesn't disappear — it moves into your application code, which now has to handle missing fields, null checks, and version migrations. Neither model is inherently better; the question is where you want the complexity to live.

WHY it matters: If your entities genuinely have variable structure (a product catalog where some items have 3 attributes and others have 50), documents save real pain. If your entities are uniform (every user has an email, a created_at, and a role), a schema just enforces what you already know is true.

SQL is 50 years old and universal for a reason: its declarative style lets you describe what data you want without specifying how to retrieve it, and the query planner figures out the execution plan. Joins, aggregations, window functions, CTEs — all expressible in concise, readable queries.

Document query languages (MongoDB's aggregation pipeline, DynamoDB's expression syntax) are more imperative and can feel awkward for multi-entity queries. They're excellent for single-document lookups and document-scoped aggregations, but cross-document "join" logic usually pushes into application code.

WHY it matters: If your queries frequently combine data from multiple entity types (users + orders + products), SQL's join model is dramatically more natural. If most queries fetch a single document by ID or a small set by a known key, document query APIs are fine.

A transaction is a group of operations that either all succeed or all fail together — atomically. This is critical any time a business operation touches more than one piece of data. Transferring money between accounts means debiting one row and crediting another; if your database crashes between those two writes, you need the database to roll back both, not leave the money in limbo.

Traditional relational databases have had full ACID transactions for decades. NoSQL databases historically traded transactions for write throughput and distribution. That trade-off has narrowed: MongoDB supports multi-document transactions, DynamoDB has transactional writes for up to 25 items. But the original design philosophy still shows — NoSQL transactions carry more overhead and behavioral caveats than native SQL transactions.

WHY it matters: Any time you have an "all-or-nothing" business rule that spans multiple records (financial transfers, inventory reservation, order placement), make sure the database's transaction semantics genuinely support it — not just in theory but in the way your access patterns actually work.

Scaling a database means handling more data or more requests. The two strategies are vertical (bigger hardware) and horizontal (more nodes). Relational databases traditionally scale vertically — you buy a larger server. This works surprisingly far: a modern high-memory server running Postgres can handle tens of thousands of queries per second and store multiple terabytes of data.

Distributed databases (Cassandra, DynamoDB, CockroachDB) scale horizontally — you add nodes, and the database shards data across them. This is more complex operationally, but it removes the hard ceiling of a single machine. The cost is that distributed coordination adds latency and complexity, especially for transactions.

WHY it matters: Most applications never need horizontal scaling. Vertical Postgres gets you surprisingly far. Reach for distributed databases only when you have measured evidence that vertical scaling won't get you where you need to go — and only after evaluating read replicas, connection pooling, and query optimization as cheaper alternatives.

Search databases are built around an inverted index — the same structure behind a book's index. When you search "payment failed," the database doesn't scan every document; it looks up "payment" and "failed" in the index and intersects the document lists in milliseconds.

Use Elasticsearch when: keyword search with relevance ranking is a core product feature, you need faceted filtering (filter by category + price + brand simultaneously), or you're indexing semi-structured logs and need full-text queries across them. Don't use it as a primary data store — Elasticsearch's eventual-consistency model and lack of strong transactions make it a poor choice for authoritative data.

WHY Elasticsearch over Postgres full-text search? Postgres has tsvector full-text search that's fine for basic cases. Elasticsearch wins when you need multi-language stemming, fuzzy matching, BM25 relevance scoring, nested faceting, and real-time index updates at high write volume — all simultaneously.

Time-series databases are optimized for data that is always appended (never updated in place), always has a timestamp as the primary lookup dimension, and is queried with time-range filters and windowed aggregations. Think IoT sensor readings, server metrics, financial tick data, application performance data.

TimescaleDB gives you Postgres-compatible SQL plus automatic time partitioning (hypertables) and columnar compression. InfluxDB uses a custom query language (Flux) and purpose-built storage engine optimized for float-heavy append workloads.

WHY not just Postgres? At tens of millions of rows, Postgres's MVCC model accumulates dead tuple bloat on every insert, autovacuum struggles to keep up, and time-range scans across billions of rows get slow even with indexes. TimescaleDB's chunk-based architecture means a 7-day window query only reads 7 chunks, not the whole table.

Graph databases store data as nodes (entities) and edges (relationships) and traverse relationships as a native operation. When you ask "find all users who are friends-of-friends with User 42 and also purchased Product X," a graph database walks edges directly — it doesn't need to join tables.

The classic sign you need a graph database: your SQL queries contain recursive CTEs or 3+ self-joins on an adjacency table, and they're getting slow. Each hop in a graph DB is O(1) — you follow a pointer. Each hop in SQL is O(n) — you do a join that scales with table size. At 4+ hops, SQL explodes; graphs stay fast.

Real use cases: social network recommendation engines, fraud detection (find all accounts connected to a known bad actor within 3 hops), knowledge graphs, and route-finding in maps or logistics networks.

Vector databases store high-dimensional floating-point arrays (embeddings) produced by AI models and answer queries like "which stored vectors are most similar to this query vector?" This is the backbone of semantic search and RAG (Retrieval-Augmented Generation) pipelines for AI applications.

Vector databases use ANN (approximate nearest-neighbor) indexes like HNSW or IVF to make similarity search fast. A brute-force comparison of a query against 10 million 1536-dimension vectors would take seconds; ANN search does it in milliseconds.

Alternative: pgvector (Postgres extension) is a reasonable starting point for up to ~1 million vectors if you want to avoid a new infrastructure component. Purpose-built vector DBs (Pinecone, Qdrant, Weaviate) handle larger scale and offer richer filtering with less operational tuning.

OLAP databases store data in columnar format — all values for a column stored together. When an analytics query reads only 4 of 80 columns across 500 million rows, columnar storage reads only 4 column files, ignoring the rest. Row-oriented databases like Postgres must read every column in every row even when you only need 4.

Use OLAP databases for dashboards, business intelligence, and data warehouse queries — anything where you're aggregating large historical datasets with GROUP BY and time-range filters. Don't use them as operational databases — OLAP DBs are poorly suited to point lookups and frequent updates.

ClickHouse is self-hostable and extremely fast for real-time analytics. BigQuery and Snowflake are fully managed serverless options — you pay per query, not per server, which makes costs variable but removes all ops burden.

Embedded databases run inside the same process as your application — no separate server, no network hop. They're used when you need local storage that's more structured than a flat file: mobile apps, desktop applications, edge devices, and test environments.

SQLite is the world's most deployed database — it runs in every phone, every browser, every Electron app. It's a full SQL engine in a single library, with no configuration and a single-file storage model. For read-heavy workloads or applications with one writer, it's remarkably capable.

RocksDB is an embedded key-value store optimized for write-heavy workloads on SSDs. It underlies many other databases (Cassandra uses it optionally; TiKV uses it as its storage engine) — a sign that even major distributed databases find value in a well-tuned embedded layer.

The simplest strategy: stop all writes to the old database, copy the data to the new one, update the application's connection strings, and restart traffic. The migration window is the downtime window — all traffic is blocked until the migration completes.

When it works: small datasets (gigabytes, not terabytes), internal tools or batch systems where a maintenance window is acceptable, or development/staging environments where downtime is free.

Why it fails at scale: copying a 2 TB database takes hours. Users don't tolerate hours of downtime. Even with fast hardware and parallel copy tools, big bang migrations for any customer-facing production system with meaningful data size are almost never acceptable.

WHY it still exists: simplicity has value. Big bang migrations are trivially reversible (switch the connection string back), require zero code changes during the migration window, and have no dual-write consistency concerns. If your dataset fits in the downtime window, big bang is often the right choice.

Dual-write is the most common production migration strategy. The idea: update your application to write every new record to both the old and new databases simultaneously. Then, separately, run a backfill job to copy all existing historical data to the new database. Once the backfill completes and you've validated that both databases contain the same data, you gradually move read traffic to the new database and eventually stop writing to the old one.

The hard part: write ordering. If a record is updated in the old DB before the backfill copies it to the new DB, and then the dual-write layer writes the update to both, you might end up with the update applied twice or applied to a stale base. Robust dual-write systems use version numbers or timestamps to resolve conflicts.

WHY dual-write over big bang for production: zero downtime. Users never experience an outage. You can pause, roll back, or slow down at any point. The risk is distributed over months rather than concentrated in a single high-stakes maintenance window.

Every database already keeps a private diary of every change it makes — used internally for crash recovery. The clever idea here is: read that diary and stream each entry to the new database as it happens. That technique is called Change Data Capture (CDC), and the diary has a name in each database: Postgres calls it the WAL (Write-Ahead Log), MySQL calls it the binlog, MongoDB calls it the oplog. Tools like Debezium read these logs and publish change events to Kafka, which your migration pipeline consumes to populate the new database.

Key advantage: no application code changes required during migration. The application keeps writing to the old database normally; the CDC pipeline silently keeps the new database synchronized.

Complexity trade-off: CDC requires understanding the source database's change log format, handling schema changes correctly (a column rename in the old DB needs to be translated in the CDC pipeline), and managing lag (the new DB may be seconds to minutes behind the old one depending on throughput).

The strangler fig pattern — named after a tree that slowly grows around a host and eventually replaces it — migrates data feature by feature rather than all at once. Each time you build a new feature or refactor an old one, you store that data in the new database. Over time, the old database "strangles" as its tables empty out and all active data lives in the new system.

This is the lowest-risk migration strategy because each step is small and independently reversible. If the migration of the "user profiles" feature goes wrong, only user profiles are affected — orders, payments, and product catalog are untouched.

WHY this works better for large legacy systems: big bang migrations of large legacy systems almost always uncover hidden dependencies, undocumented schemas, and data quality issues. The strangler fig pattern gives you time to discover and fix these one feature at a time instead of discovering them all during a single high-stakes cutover weekend.

The downside: you're operating a polyglot system — data split across two databases — for months or years. Application code must know which database each entity lives in. This adds cognitive overhead and can be confusing for new engineers joining mid-migration.

The single most reliable rule: under 10 engineers, use managed. A team of 5 engineers trying to self-host Postgres with proper HA, backups, failover, and monitoring will spend 20–30% of their engineering capacity on database operations. That's one full-time engineer just keeping the database alive — capacity that won't ship features.

Managed services (RDS, Aurora, Atlas) handle automated backups, point-in-time recovery, read replica provisioning, and minor version patching. These seem like small conveniences, but each one represents hours of work per month when done manually — and skipped maintenance has a way of becoming a 3 AM incident.

WHY the threshold is roughly 10 engineers: at that size, a team typically has at least one engineer with enough infrastructure depth to own database operations without it consuming their entire role. Below that, the ops burden competes directly with product velocity.

Some industries — healthcare (HIPAA), finance, government — have regulations that restrict where data can physically reside or require specific audit trails that some cloud providers can't certify for. In these cases, self-hosting isn't a cost choice — it's a compliance requirement.

That said, major cloud providers (AWS, Azure, GCP) all offer HIPAA-compliant managed database services with BAAs. Don't assume self-host is required for regulated industries — verify with your compliance team whether specific managed offerings can satisfy your regulatory requirements before building self-host infrastructure.

At very large scale — hundreds of terabytes, thousands of queries per second — managed database pricing can become significant. Aurora can cost 3–5x the equivalent EC2 + storage cost for the same workload. At that scale, teams with mature infrastructure practices often move to self-managed Postgres on optimized EC2 instances, saving millions per year.

But this math only works if you have the engineering expertise to do it safely. The engineers needed to run a large Postgres fleet safely are expensive — senior DBAs or SREs with deep database expertise. The real calculation is: cloud premium vs. (DBA salary × headcount × years). Many teams find that the cloud premium is cheaper than the team it would take to match what the managed service does automatically.

Rule of thumb: unless you're spending more than ~$20K/month on managed database services and you have at least 2 engineers with deep database expertise, the math almost never favors self-hosting.

Managed database services often receive new features earlier than self-hosted equivalents because the cloud provider's team is focused exclusively on operating that database at scale. Aurora Serverless v2, Spanner autoscaling, Atlas's in-memory online index builds — these capabilities appear in managed services months or years before they're operationally safe to run self-hosted.

If your team wants to use cutting-edge database features without deep expertise in operating them safely, managed services provide a sandbox for experimentation without the risk of misconfiguration bringing down production.

Vendor lock-in caveat: the more proprietary managed features you use (Aurora's storage engine, Spanner's external consistency API, DynamoDB's DAX caching), the harder it becomes to migrate later. Use standard SQL and standard APIs wherever possible, and treat proprietary managed features as technical debt that needs a migration story.

The temptation: adopting a new, exciting database technology because the team wants to learn it or add it to their resumes. The result: a production system running a database that nobody on the team has operated at scale, chosen for career reasons rather than technical fit.

This is more common than anyone admits. Kafka for a queue that would work fine with a Postgres table. CockroachDB for a single-region app with 1,000 users. Elasticsearch as a primary store because inverted indexes sounded interesting.

The fix: explicitly separate "technologies I want to learn" from "technologies I choose for production." Personal projects, side projects, hackathons — great places to learn new databases. Production systems with real users — choose boring, proven technology. Boring technology has known failure modes, abundant Stack Overflow answers, and junior engineers who can operate it without months of training.

Distributed databases solve a real problem — scaling beyond a single machine — but they introduce significant operational complexity: split-brain scenarios, distributed transaction coordination, network partition handling, and cross-shard query routing. These problems don't exist in a single-node database.

The mistake: adopting Cassandra, CockroachDB, or sharded MongoDB because you might need to scale someday, before you have any evidence that a single Postgres instance is insufficient. A well-tuned Postgres instance on modern hardware can handle tens of thousands of queries per second and store multiple terabytes of data. Most applications never come close to that ceiling.

The fix: run a single Postgres. Add read replicas when reads need to scale. Consider partitioning when tables exceed hundreds of millions of rows. Only graduate to distributed when you have measured, reproducible evidence that Postgres can't handle the load — not because you expect it might not someday.

The pitch: "Our schema is changing rapidly. With MongoDB, we don't need migrations — we just add fields to documents." The reality: the schema doesn't disappear. It moves from the database (where it's enforced automatically) into the application code (where it's invisible and inconsistently applied).

Six months in, you have documents with 15 different shapes because the schema evolved without formal migrations. Your application has null checks, try-catch blocks, and version detection logic scattered everywhere. New engineers spend days understanding what fields exist. Reporting queries require complex $project stages to normalize the data before aggregating it.

The fix: analyze your actual data shape before choosing. If your entities genuinely have highly variable attributes that are impossible to model in a table, a document DB may be right. But most "our schema changes fast" concerns are really about schema migration velocity — which Postgres handles fine with tools like Flyway or Alembic.

Vendor lock-in happens when you adopt proprietary database features — Aurora-specific storage behaviors, DynamoDB-specific access patterns, Cosmos DB-specific consistency APIs — without a plan for what happens if you need to migrate. The risk: your vendor raises prices, sunsets a feature, or changes terms, and you discover that migrating away would cost more than accepting the new terms.

Lock-in isn't inherently bad — it's a trade-off. You get better performance or features in exchange for reduced negotiating leverage and higher migration cost. The problem is doing it unaware, without a conscious decision that the trade-off is worth it.

The fix: document every proprietary feature your system uses. For each one, estimate the migration cost if you had to replace it. Make that list visible to the team and revisit it annually. Use standard SQL and standard access patterns wherever performance allows, and treat proprietary features as technical debt with a known cost.

Some databases are excellent on paper — powerful, scalable, well-documented — but operationally demanding in practice. Cassandra's ring topology, token management, compaction tuning, and repair operations require deep expertise to manage correctly. Elasticsearch's JVM heap tuning, shard rebalancing, and mapping explosion risks bite teams that don't have Elasticsearch specialists.

The mistake: evaluating a database purely on feature capabilities and benchmarks, without evaluating the operational overhead of running it safely in production. The database that performs best on a benchmark is worthless if your team can't operate it without constant fires.

The fix: before committing to any database, ask: "Who on our team has operated this database in production? What are the top 3 most common failure modes, and how do we recover from each?" If no one on the team can answer confidently, either use a managed service or choose a simpler database that the team genuinely understands.

The classic version: picking Cassandra "because we'll need it when we have millions of users," when the product currently has 100 users and no clear path to a million. The distributed complexity costs you now — slower development, harder debugging, more complex queries, additional infra. The scale benefits come later, if they ever come.

The pattern is expensive because distributed systems are more complex to build on. Every feature takes longer to implement correctly. Every bug is harder to diagnose. Every new engineer takes longer to become productive. These costs are real and recurring; the "future scale" benefit is speculative.

The fix: size the database for your current scale plus roughly one order of magnitude growth. If you have 1,000 users today and can imagine 10,000 users in a year, you don't need Cassandra — you need well-indexed Postgres. When you hit 10,000 users and can project 100,000, revisit. Build for the next milestone, not the hypothetical endpoint.

Start by describing your data and queries in plain English — not in terms of any database technology, but in terms of what the data looks like and how your application will access it.

Key questions to answer: What are your main entity types? How do they relate to each other? What are the three most common queries your application will run? What percentage of operations are reads vs writes? Does your schema change frequently, or is it stable once defined?

WHY this comes first: the workload shape determines which data model fits naturally. Relational entities with joins → relational DB. Variable-schema documents with no join needs → document DB. Millions of timestamped measurements → time-series DB. This one answer eliminates most of the candidate list immediately, which is why it's first in the funnel.

Ask which operations must be atomic and what "wrong" data would cost you. The answer tells you how strict your consistency requirements are.

Strong consistency is required when: money changes hands, inventory is decremented, access permissions are modified, or any state change that can't be undone is recorded. Eventual consistency is acceptable when: you're reading product catalog data, displaying aggregate statistics, or serving content that's slightly stale for a few seconds.

Why this is step 2: consistency requirements are a hard constraint, not a preference. If your business logic requires atomically updating two records and one database family can't do that reliably, it's eliminated immediately regardless of how good its benchmark numbers look. Determine hard constraints before soft preferences.

Write down two numbers: your expected scale at one year and your expected scale at three years. Express them in users, requests per second, and data volume in gigabytes or terabytes. Be specific — "a lot" is not a scale estimate.

Then ask: does a single well-tuned Postgres instance handle this? If yes, use Postgres. If no, what's the specific bottleneck — storage, write throughput, read throughput, or data volume? Only add complexity to address specific, measured bottlenecks.

WHY hard numbers matter: "we'll need to scale eventually" has led more teams into premature distribution than any other phrase. When you write "200 QPS peak" on paper, it becomes obvious that a single Postgres instance handles that easily. The specificity prevents hypothetical scale from driving real engineering decisions.

Ask honestly: who on the team has operated this database in production before? Not played with it locally — actually run it in production, handled incidents, done major version upgrades, restored from backups, and diagnosed slow queries under load.

If the answer is nobody, factor that into your choice. Either use a managed service that handles the hard parts automatically, or choose a database your team already knows well. The learning curve tax is real — it shows up as slower feature development, more incidents, and longer incident recovery times for the first 6–12 months of operating an unfamiliar database.

Practical output: if you pick a database that no one knows and won't use managed services, budget explicitly for 3–6 months of learning curve time in your project plan. Don't pretend the learning is free.

Total cost has five components that all matter: (1) raw infrastructure cost (per-node, per-GB, per-query pricing), (2) ops labor cost (how many engineer-hours per month does operating this safely require?), (3) training cost (how long until the team is proficient?), (4) migration risk (how hard is it to move away in 2 years if circumstances change?), and (5) incident cost (what's the business cost of a 2-hour database outage?).

Many teams compare databases on infrastructure cost alone and ignore the other four components. A database that costs $200/month more than the alternative but requires zero ops labor and has a trivial migration path is often cheaper in total cost than the "cheaper" alternative that requires 20 engineer-hours per month of maintenance.

WHY migration risk matters: circumstances change — team size, business model, scale, cloud providers. A database with a clear, well-documented migration path keeps your options open. A database built around proprietary APIs without migration tooling is a bet that your current situation is permanent.

Fill out the worksheet as a team — not solo. Different engineers will have different answers to steps 1–3 based on what they know about the business, and that disagreement is valuable. If your backend engineer thinks the workload is relational and your data engineer thinks it's time-series, you probably need a more detailed discussion about what queries matter most.

Document the answers even briefly. Six months later, when someone asks "why did we pick Postgres?" you want a written answer that explains the reasoning, not a shrug. The documentation also helps you know when to revisit the decision — if your actual scale in year 2 diverges significantly from your step 3 estimate, the worksheet tells you which assumptions have changed.

Time investment: a thorough worksheet session takes 30 to 60 minutes. That's a bargain compared to the cost of a wrong decision — which, as the migration patterns section described, can consume 3 to 12 months of engineering time to reverse.