Choosing the Right Database

A decision framework — structure, query patterns, scale, and team skills.

Intermediate12 min read

Read these first

Graph Databases with Neo4j

Why this matters

Wrong store choices surface as rewrite projects: hot partitions, missing joins, or six-figure managed bills.
System design interviews expect a justified recommendation, not a technology laundry list.
VaultCommerce runs five engines today because each solves one problem well — the skill is knowing which problem you're solving.

SQL (Postgres)

ACID transactions

Complex joins

Fixed schema

NoSQL

Flexible schema

Horizontal scale

Specialised access

Structured relations with joins favour SQL. Flexible schema and horizontal scale favour NoSQL.

The five-question framework

Work through these dimensions before committing:

Selection checklist

Data shape — relational rows, nested documents, time-series events, graph edges?
Access pattern — point lookups, range scans, full-text search, aggregations?
Consistency — must checkout be strongly consistent, or is eventual OK for a feed?
Scale trajectory — write QPS, storage growth, multi-region needs in three years?
Team and ops — managed service, existing expertise, on-call capacity?

Java

# VaultCommerce checkout service sketch
workload: order_placement
entities: [users, carts, inventory, payments]
hot_query: "deduct inventory and charge card atomically"
read_write_ratio: 1:3
consistency: strong
scale_3yr: 50k orders/min peak
recommendation: postgres

Mapping VaultCommerce workloads

Workload	Engine	Why
Orders and payments	Postgres	ACID, foreign keys, mature tooling
Product catalog pages	MongoDB	Nested variants, flexible schema
Clickstream events	Cassandra	Partitioned append-only writes
Site search	Elasticsearch	Inverted index, fuzzy match
Sessions and cart cache	Redis	Sub-ms latency, TTL native
Fraud graph	Neo4j	Multi-hop relationship traversal

Orders and payments
EnginePostgres
WhyACID, foreign keys, mature tooling
Product catalog pages
EngineMongoDB
WhyNested variants, flexible schema
Clickstream events
EngineCassandra
WhyPartitioned append-only writes
Site search
EngineElasticsearch
WhyInverted index, fuzzy match
Sessions and cart cache
EngineRedis
WhySub-ms latency, TTL native
Fraud graph
EngineNeo4j
WhyMulti-hop relationship traversal

No single engine wins every row. The framework picks the best fit per access pattern, then accepts the integration cost.

Managed vs self-hosted

Aspect	Managed (RDS, Atlas, DynamoDB)	Self-hosted (Postgres on VMs, Cassandra cluster)
Time to prod	Hours with defaults	Weeks of tuning
Cost at small scale	Higher per GB	Cheaper if you have DBAs
Failover	Automated promotion	Your runbooks
VaultCommerce default	Yes for new services	Legacy only

Time to prod
Managed (RDS, Atlas, DynamoDB)Hours with defaults
Self-hosted (Postgres on VMs, Cassandra cluster)Weeks of tuning
Cost at small scale
Managed (RDS, Atlas, DynamoDB)Higher per GB
Self-hosted (Postgres on VMs, Cassandra cluster)Cheaper if you have DBAs
Failover
Managed (RDS, Atlas, DynamoDB)Automated promotion
Self-hosted (Postgres on VMs, Cassandra cluster)Your runbooks
VaultCommerce default
Managed (RDS, Atlas, DynamoDB)Yes for new services
Self-hosted (Postgres on VMs, Cassandra cluster)Legacy only

Early-stage teams should bias toward managed services. Self-hosting earns its keep at scale with dedicated platform engineers — not because licensing is cheaper on a spreadsheet.

Red flags that force a rethink

When your current store is wrong

Cross-shard joins appearing in application code for core flows.
Hot keys saturating one partition despite vertical scale.
Schema migrations blocking launches weekly because one table models everything.
Replica lag breaking user-visible consistency on paths that need freshness.

VaultCommerce migrated search off Postgres full-text indexes when autocomplete latency crossed 200 ms at 2M SKUs — the signal was access-pattern mismatch, not "Postgres is slow."

Quick recall

Everything you need if you only revisit this box.

Select on access patterns and consistency — not logos or hype.
Run the five-question framework before proposing any engine.
VaultCommerce uses Postgres, MongoDB, Cassandra, Elasticsearch, Redis, and Neo4j — each for a distinct hot path.
Prefer managed services until scale and expertise justify self-hosting.
Migration signals are hot partitions, cross-shard joins, and schema pain — not vague performance complaints.

Test yourself

Answer these before moving on — recall is what makes it stick.