SQL vs NoSQL vs NewSQL: Choosing a Database

The wrong question is 'SQL or NoSQL'. The right question is 'what are my read/write patterns, consistency needs, and scale requirements?'

The database landscape in 2026

• Relational: Postgres, MySQL
• Key-Value: Redis, DynamoDB
• Document: MongoDB, Couchbase
• Wide-column: Cassandra, ScyllaDB
• Graph: Neo4j, Neptune
• NewSQL / Distributed SQL: CockroachDB, Spanner, YugabyteDB
• OLAP / Data warehouse: Snowflake, BigQuery, ClickHouse
• Search: Elasticsearch, OpenSearch
• Time-series: InfluxDB, TimescaleDB
• Vector: Pinecone, Weaviate, pgvector

Start with Postgres unless you have a reason not to

• ACID, rich SQL, mature tooling
• JSONB for semi-structured data
• Full-text search, geospatial (PostGIS)
• Time-series (TimescaleDB), pub/sub (LISTEN/NOTIFY)
• Vector (pgvector), logical replication, CDC
• 'Postgres for everything' is a valid starting architecture for 90% of apps

Reach for NoSQL when specific patterns justify it

• DynamoDB or Redis KV: access purely by key; horizontal scale needed
• MongoDB: document shapes vary wildly; flexible schema
• Cassandra or ScyllaDB: write throughput beyond single-node Postgres (> 100K writes/sec)
• Neo4j: queries are fundamentally graph traversals

Reach for NewSQL when you need both scale and SQL

• Global multi-region ACID
• Outgrew single-node Postgres
• Accept cross-region commit latency (50 to 200ms)

Add specialized stores as needed

• Elasticsearch for search (not primary DB)
• Snowflake or BigQuery for analytics
• Redis for cache
• S3 for blobs
• Vector DB for RAG or semantic search

Decision framework

Understand your workload

• OLTP: short reads/writes, high concurrency, consistency matters
• OLAP: long scans, aggregates, eventual freshness OK
• HTAP: both; usually separate OLTP + pipeline to OLAP
• Real-time streaming: append-heavy time-based
• Search: full-text ranking faceting

Understand your access patterns

• How do you read? By key, by range, by join, by text, by similarity?
• How do you write? Append-only, updates in place, bulk?
• Read:write ratio?
• Scale in rows, ops/sec, data size?
• Consistency: strong, read-your-writes, eventual?

CAP vs PACELC

• CAP: in partition, choose consistency or availability
• In practice, most systems aren't partitioned all the time
• PACELC captures the real trade-off: in partition, availability vs consistency; else latency vs consistency
• Postgres single-node: CP; Cassandra: AP; DynamoDB: tunable; Spanner: CP

Common scenarios

• E-commerce: Postgres + Redis + Elasticsearch + S3 + Snowflake
• Social network: Postgres or Cassandra + Redis + Neo4j + Elasticsearch
• IoT telemetry: Kafka to TimescaleDB or ClickHouse + Postgres metadata
• Gaming leaderboard: Redis sorted sets + Postgres
• Global SaaS: CockroachDB or Spanner + regional Redis
• AI / RAG: pgvector or Pinecone + Postgres metadata

Common mistakes

• Defaulting to NoSQL for 'scale' without needing it
• Thinking Postgres doesn't scale (10TB and 10K+ writes/sec routine)
• Picking MongoDB for relational data; rebuilding joins in app code
• Mixing OLTP and OLAP on same DB; analytics queries kill transactional performance
• Under-modeling DynamoDB access patterns; can't add ad-hoc queries later

Managed vs self-hosted

• Managed (RDS, Aurora, Atlas, DynamoDB, Cockroach Cloud): less ops, more cost
• Self-hosted: full control, lower cost, real ops burden
• For most teams in 2026, managed is the default

Aurora vs RDS vs Postgres on EC2

• Aurora: AWS-rewritten Postgres/MySQL; 3x throughput, shared storage, 128TB
• RDS: managed vanilla Postgres/MySQL
• Postgres on EC2: DIY

The strategic defaults in 2026

• Start with Postgres (managed) for OLTP
• Add Redis for cache and sessions
• Add Elasticsearch for search when SQL LIKE isn't enough
• Add Snowflake or BigQuery when analytics strain OLTP
• Add S3 for blobs
• Add DynamoDB or Cassandra only when proven need for horizontal scale
• Add CockroachDB or Spanner only when multi-region strong consistency is hard requirement
• Add pgvector for RAG; upgrade to specialized at scale

Polyglot persistence cost

• Each system adds operational surface
• 5 DBs equals 5 sets of backups, upgrades, on-call, expertise
• Consolidate when possible

The strategic answer

• Postgres is the default; it's remarkable in 2026
• Reach for specialized stores only when specific patterns justify
• Polyglot persistence is normal but has cost
• Access patterns drive schema in NoSQL, not the other way around
• Managed services are default for most teams
• Don't rebuild joins in application code; use a relational DB
• Migration is costly; err on side of flexibility when uncertain