Choosing the Right Database for Your Next Project

The database question comes up in every project. And unlike most architectural decisions, this one has real lock-in — migrating data at scale is expensive and risky enough that you want to get it reasonably right the first time.

The honest answer is that for most business applications, several databases would work fine. The decision is less about finding the perfect tool and more about avoiding obvious mismatches.

Start with your access patterns

Before picking a database, write down how your data will be read and written:

• Do you need to query by multiple fields in combination?
• Do you need full-text search?
• Do you join data from multiple tables frequently?
• Is your data highly relational (entities with many foreign keys)?
• Do you need geospatial queries?
• Will you write far more than you read, or the reverse?
• How important is strict consistency vs. eventual consistency?

The answers determine what database characteristics matter. A read-heavy reporting workload has different needs than a write-heavy event stream.

The case for PostgreSQL (almost always)

PostgreSQL is the right choice for most new projects. It’s:

• Relational, with full ACID compliance — correct by default
• Capable of JSONB — you can store and query semi-structured data without giving up relational integrity
• Full-text search built in — good enough for most use cases, replacing a separate Elasticsearch deployment
• Battle-tested — runs some of the largest databases in the world
• Well-supported everywhere — managed services on AWS (RDS, Aurora), GCP (Cloud SQL), Azure, Supabase, Neon

We use PostgreSQL as the default and only deviate when there’s a specific reason to.

When to reach for something else

SQLite for local development, embedded applications, and anything that doesn’t need concurrent writes. A blog with 5,000 posts that gets 1,000 readers a day does not need a server-based database. SQLite with Litestream for replication is a legitimate production stack for this scale.

Redis for caching, session storage, rate limiting, and pub/sub. Use it as a complement to your primary database, not a replacement. Its persistence guarantees are weaker and it’s optimized for speed, not durability.

MongoDB when your data is genuinely document-shaped and the document boundaries align well with your read patterns. It’s misused most often when developers want to skip defining a schema — the schema just moves to application code, where it’s harder to enforce. If you find yourself doing a lot of lookups across documents, you’ve probably chosen the wrong tool.

ClickHouse or TimescaleDB for analytics, time-series data, or very high-volume append-heavy workloads. These are specialized tools for specific problems.

On managed vs. self-hosted

For almost every client project, we use a managed database service. The operational cost of running your own PostgreSQL — backups, failover, upgrades, monitoring — is significant and not differentiated work.

Managed services (RDS, Cloud SQL, Supabase, Neon) handle the undifferentiated parts. You pay more per GB, but you pay less in engineering time.

Self-hosting makes sense when: you have a database administrator, your data residency requirements exclude cloud providers, or your scale is large enough that managed pricing becomes prohibitive.

A note on ORMs

The database choice and the ORM choice are separate decisions. Entity Framework Core works with PostgreSQL, MySQL, SQLite, and SQL Server. Dapper works with anything. Choosing the database doesn’t lock you into a specific data access approach.

What does matter: understand what SQL your ORM generates. N+1 queries are easy to miss in code review and painful to diagnose in production. The best ORMs let you see the generated SQL; use that visibility.

---

When in doubt: start with PostgreSQL on a managed service. Migrate away from it only when you have a concrete reason, not because a different tool sounds more interesting.