Choosing the Right Database for Each Workload

We run roughly five databases in production: Postgres (RDS), DynamoDB, Redis (ElastiCache), Elasticsearch / OpenSearch, and Snowflake. Each handles a slice of our workload that the others don't fit. After a few years of operating them, this is the working decision tree we use when a new service comes up and asks "what should I store this in?"

The default: Postgres#

Most workloads start in Postgres unless there's a specific reason otherwise. The reasons:

• Relational schema is well-understood; most developers can query SQL
• Transactions, foreign keys, joins all work the way you expect
• Operations are mature (backups, failover, monitoring)
• Performance is great for ~95% of workloads up to many TB
• pgvector adds vector search if needed
• JSONB for semi-structured data when needed

We have ~30 Postgres databases (mostly RDS multi-AZ). They store user data, transactional data, anything that benefits from relational queries.

When Postgres isn't the answer:

• The access pattern is "lookup a single key, fast, very high throughput" — DynamoDB
• The data is hot but ephemeral — Redis
• The query pattern is full-text search over documents — Elasticsearch
• The data is analytical (large aggregations, mostly read-only) — Snowflake / BigQuery

DynamoDB: when key-value at scale matters#

DynamoDB is right for:

• Very high write throughput (millions of writes/sec is achievable)
• Predictable single-key lookup pattern
• Low latency requirements (single-digit ms p99)
• Workloads where horizontal scaling matters

We use DynamoDB for:

• Session state (looked up by session ID)
• Idempotency keys for distributed systems
• High-volume event streams (with TTL for auto-expiry)
• Some user-preference and feature-flag-state storage

What DynamoDB is bad at:

• Complex queries (joins, aggregations, range queries on non-key fields)
• Workloads where the access pattern changes — DynamoDB performs by your access pattern; if you need to query differently, you redesign
• Workloads where consistent read-after-write across many writers matters — DynamoDB has eventual consistency by default

The DynamoDB modeling discipline matters. Single-table designs, GSIs (Global Secondary Indexes) for alternate access patterns, sort keys to encode hierarchy. Done well, DynamoDB scales beautifully. Done poorly (treated like a relational DB), it's expensive and slow.

Redis: ephemeral hot data#

Redis is right for:

• Caching (the canonical use case)
• Session state when DynamoDB-level scale isn't needed
• Sorted sets for leaderboards and time-series
• Pub/sub for lightweight messaging
• Distributed locks (with caveats)

We use Redis for:

• HTTP response caching at the application layer
• Rate limiting (token buckets)
• Counter aggregation (per-user request counts)
• Real-time data that's regenerated from primary stores

What Redis is bad at:

• Anything where data loss is unacceptable — Redis is fast partly because durability is optional
• Long-term storage — Redis loves RAM; storing 100GB+ gets expensive
• Complex queries — it's a key-value store with some data structures, not a database

We don't use Redis as a primary store. It always has a backing source-of-truth elsewhere (Postgres, DynamoDB) that can rebuild the cache.

Elasticsearch: full-text and unstructured search#

Elasticsearch (or OpenSearch) is right for:

• Full-text search over documents
• Aggregations on log data
• Complex faceted search (filters + sorts + ranges)
• Analytics on event streams

We use Elasticsearch for:

• Customer support knowledge base search
• Log aggregation (the hot tier of our logging stack)
• Internal product search
• Some real-time analytics dashboards

What Elasticsearch is bad at:

• Strong consistency or transactions — it's eventually consistent
• Workloads that need joins across indices
• Very high write throughput on a single index
• Long-term archival of data — gets expensive at scale

ES is a heavyweight piece of infrastructure. Operating it well is non-trivial. We've cycled through running it ourselves vs managed; managed is currently winning on operational simplicity even at the cost premium.

Snowflake: analytical workloads#

Snowflake is right for:

• Large-scale analytical queries
• Reporting dashboards
• Joining data from many sources
• ETL pipelines feeding business intelligence

We use Snowflake for:

• Product analytics (events from the application stream in)
• Financial reporting
• Cohort analysis
• Cross-database joins (data from Postgres + Salesforce + Stripe + etc.)

What Snowflake is bad at:

• Operational queries (sub-second latency is rare)
• Per-record CRUD (it's built for batch / analytical, not transactional)
• High-volume small writes — works better with batch loads

Snowflake is the OLAP side of the OLTP/OLAP split. Operational data goes to Postgres / DynamoDB; aggregated for analytics, it lands in Snowflake. We use Fivetran for most of the replication.

The decision tree#

When a new workload comes up:

1. Is it transactional, with relational queries, < few TB? → Postgres.
2. Is it key-value, very high throughput? → DynamoDB.
3. Is it ephemeral hot data with a backing source? → Redis.
4. Is it full-text search over documents? → Elasticsearch.
5. Is it analytics over large amounts of data? → Snowflake.
6. Is it specifically vectors for ML retrieval? → pgvector (if Postgres) or Pinecone/Weaviate.
7. Time-series with large volume? → ClickHouse or Timescale (we don't currently use these but they're on the radar).

Most workloads end at step 1 or step 2. The other databases are for specific access patterns the first two don't fit.

Common antipatterns#

Mistakes we've made (or seen):

Using Postgres for high-volume event ingestion. A service writing 50k events/sec to Postgres will have a bad time. Either move to DynamoDB or write to Kafka and aggregate.

Using DynamoDB as a relational DB. "I'll just add a GSI for every query I need." 6 GSIs later, costs explode and consistency becomes weird. If you're doing this, Postgres was the right answer.

Using Redis as a primary store. "We don't need persistence; we'll just cache it." Until the Redis goes down and you've lost data. Always have a source of truth.

Using Elasticsearch for transactional data. Not strongly consistent, not transactional. ES is for search and analytics, not for storing the canonical user state.

Using Snowflake for operational queries. A user-facing dashboard hits Snowflake; queries are 5-10s. Frustrated users. Cache aggregations to Postgres / DynamoDB for operational use.

Operational comparison#

Rough operational shape per database:

DB	Managed cost	Ops time	Restore time
Postgres (RDS multi-AZ)	$$	Low	Hours (point-in-time)
DynamoDB	$$$	Very low	Minutes (PITR)
Redis (ElastiCache)	$$	Low	Fast (rebuild from source)
Elasticsearch (managed)	$$$	Medium	Slow (snapshot restore)
Snowflake	$$$$	Very low	Time travel built in

Cost-per-query / cost-per-record varies wildly. DynamoDB at high throughput is much cheaper per-query than Postgres scaled up. Snowflake at low query volume is much more expensive per-query than Postgres.

What we've migrated between#

Migrations we've done:

Postgres → DynamoDB for a session storage system. Postgres was struggling at 30k writes/sec. DynamoDB handled it without breaking a sweat at lower cost.

DynamoDB → Postgres for a feature that grew query complexity. Started simple in DynamoDB; the queries got more complex over time; Postgres became the right fit.

Self-managed Elasticsearch → managed. Operational cost of running ES well wasn't worth the savings. Switched to AWS OpenSearch managed.

Custom analytics on Postgres → Snowflake. As our analytics scaled, Postgres queries got slow. Snowflake handles the scale; we use Fivetran to replicate.

Migrations are real work — typically weeks per service. Pick the right tool initially when possible.

What I'd tell a team starting#

Default to Postgres. Most workloads fit. Mature ops. Familiar query language.

DynamoDB when the access pattern is clear and stable. Single-table design, careful modeling.

Redis only with a source of truth. Never the only place your data lives.

Elasticsearch for search; Snowflake for analytics. Don't try to make Postgres do these at scale.

Be willing to migrate. When the access pattern outgrows the database, switch. Migrations are expensive but staying on the wrong tool is more expensive over time.

The right database is the one that fits the access pattern, scale, and consistency requirements. The wrong database is the one that fits "what we already use" without checking. Most teams don't really need 5 databases; we ended up with that many because each workload has different shape. A team starting fresh probably wants Postgres + Redis (caching) and adds the rest only as specific needs arise.