Database choice substantially affects application architecture, performance, and operational complexity. Most developers make database decisions based on familiarity, hype, or vendor marketing rather than systematic analysis. Here are 6 common database decisions and what to actually choose.
1. NoSQL vs SQL for general applications
The common mistake: Choosing NoSQL (MongoDB, similar) for typical web applications based on the trend.
The reality: Most web applications benefit substantially from relational databases. The relational model handles most application data more naturally than document models. Transactions, joins, and constraints provide value that document databases require additional work to replicate.
What to do: Default to PostgreSQL for new applications. Choose NoSQL only when you have specific reasons (extremely high write volume, naturally document-shaped data, specific scaling requirements).
2. Microservices and database fragmentation
The common mistake: Splitting databases across many microservices following microservice architecture patterns.
The reality: Database fragmentation introduces substantial operational complexity, makes transactions difficult, and complicates analytical queries. Most applications benefit from fewer, well-designed databases rather than many small ones.
What to do: Resist database fragmentation pressure unless you have specific scale or organizational reasons that justify the complexity. Most teams over-fragment databases.
3. SQLite for production applications
The common mistake: Avoiding SQLite for production applications because it is "embedded."
The reality: SQLite is genuinely production-ready for many applications. Major applications run substantial portions of their data on SQLite. The "real database" snobbery against SQLite is outdated.
What to do: Consider SQLite for applications where it fits — single-server deployments, applications with moderate concurrent users, applications where simplicity matters. Modern SQLite handles substantial workloads.
4. Premature data warehouse adoption
The common mistake: Adopting Snowflake, BigQuery, or similar data warehouse platforms before actually having data warehouse needs.
The reality: Data warehouses are expensive and complex. Most applications can run analytics on their primary database for substantial scale. The premium for dedicated data warehouse should match actual analytical complexity.
What to do: Run analytics on your primary database until you have specific reasons to add data warehouse complexity. Most teams adopt data warehouses prematurely.
5. ORM vs raw SQL choices
The common mistake: Either using ORMs for everything or avoiding them entirely.
The reality: ORMs are useful for routine database operations and substantially less useful for complex queries. Most applications benefit from ORM use for typical operations and raw SQL for complex queries that ORMs handle poorly.
What to do: Use ORM for typical CRUD operations. Drop to raw SQL when ORM-generated queries perform poorly or become unreadable. The hybrid approach typically produces better outcomes than ORM-only or SQL-only approaches.
6. Backup and recovery as afterthought
The common mistake: Treating backup and recovery as operational concerns to be addressed after deployment.
The reality: Backup and recovery should be designed into database choices and operational architecture from initial planning. Backup that is added later often does not work properly when needed.
What to do: Specify recovery point objective (RPO) and recovery time objective (RTO) requirements before choosing database. Test backup and recovery procedures regularly. Treat backup as essential infrastructure rather than operational afterthought.
The bonus mistake
Bonus #7: Database performance optimization without measurement.
Many developers add indexes, restructure queries, or change database schemas based on intuition about what should be slow. The reality is that performance issues are usually different from what intuition suggests.
Effective performance optimization starts with measurement — query analysis, monitoring, profiling. Optimization based on measured bottlenecks produces much better results than optimization based on guesswork.
The sensible default database choices
For typical applications, sensible default choices:
Web applications with reasonable scale: PostgreSQL. Mature, capable, performant for most workloads.
Applications with moderate scale and operational simplicity priority: SQLite. Genuinely sufficient for many real applications.
Applications with extreme write volume or specific NoSQL fit: MongoDB or specialized alternatives. Choose based on specific characteristics of your data and scale.
Applications with Microsoft ecosystem alignment: SQL Server. Mature and well-integrated with Microsoft stack.
Applications with substantial analytical workload: PostgreSQL with read replicas, then dedicated analytical database when scale justifies.
What I am NOT recommending
Database choices that get hyped but warrant skepticism for typical applications:
- Distributed SQL databases (CockroachDB, similar) for typical applications. Substantial complexity with limited benefit at typical scales.
- Graph databases for non-graph data. Specialized tool used for inappropriate applications.
- Time-series databases for typical operational data. Specialized tool for specialized use cases.
- "Multi-model" databases. Marketing concept that often produces complexity without commensurate benefit.
The operational complexity consideration
Database choice substantially affects operational complexity. Choices to minimize operational complexity:
- Use mature, widely-deployed databases
- Use managed services for production deployments when appropriate
- Avoid early adoption of database technologies for production use
- Standardize on fewer database technologies rather than many specialized ones
- Plan for backup, recovery, and operational tooling from initial design
Operational complexity costs accumulate over years. Database decisions that produce sustainable operations typically outperform decisions that produce difficult operations even when initial decisions seemed sophisticated.
The takeaway
Most database decisions can be substantially simpler than common practice suggests. PostgreSQL handles most applications. SQLite handles surprisingly many. NoSQL is genuinely needed less often than its market share suggests.
For developers making database decisions, default to boring well-tested choices. Choose specialized databases only when specific requirements justify the additional complexity. Your applications will be more reliable and your operations more sustainable than if you chase database trends.
