---
title: "Choosing the Right Database: A Practical Decision Framework"
description: "A practical framework for choosing between PostgreSQL, MongoDB, Redis, and other databases - based on real decisions across 25+ projects."
date: 2025-04-13T00:00:00.000Z
category: Architecture
readingTime: "4 min read"
---


Across 25+ projects at HMD Developments, we've used PostgreSQL, MongoDB, Redis, SQLite, and DynamoDB. Each was the right choice for its project - and each would have been the wrong choice for another. Here's how I think about database selection now.

## The Framework: Three Questions

Every database decision starts with three questions:

1. **What shape is my data?** - Relational tables or nested documents?
2. **What are my query patterns?** - Reads vs writes? Simple lookups vs complex joins?
3. **What are my consistency requirements?** - Strong consistency or eventual is fine?

The answers determine your database. Not the trending tech, not what your last company used, not what gets the most GitHub stars.

## PostgreSQL: The Default

When I don't have a strong reason to choose something else, I choose PostgreSQL. It's the safest default in software engineering.

**Choose PostgreSQL when:**
- Your data is relational (users, orders, products - things that reference each other)
- You need ACID transactions (financial data, inventory, anything where partial updates are dangerous)
- Your query patterns include joins, aggregations, and complex filtering
- You want one database that handles most use cases adequately

**Real example:** HMD Payments used PostgreSQL for all transaction data. Payments reference customers, customers reference accounts, accounts reference payment methods. The relational model maps perfectly. And for payment processing, ACID transactions aren't optional - they're the entire point.

I wrote more about the payment architecture in [Building a Zero-Fee Payment API](/blog/zero-fee-payments-technical-deep-dive).

**PostgreSQL's superpower:** It's boring in the best way. Decades of battle-testing, exceptional documentation, and a feature set that covers 90% of use cases. JSON columns even give you document-like flexibility when you need it.

## MongoDB: Documents Done Right

MongoDB gets a bad reputation from people who used it incorrectly. Used correctly - for genuinely document-shaped data with known access patterns - it's excellent.

**Choose MongoDB when:**
- Your data is naturally document-shaped (each record is self-contained)
- You rarely need cross-document joins
- Your schema varies per record or evolves frequently
- You need horizontal scaling (sharding) more than complex queries

**Real example:** Chat Guard uses MongoDB for server configurations. Each Discord server has its own configuration document with different features enabled, different settings configured, and different moderation rules. The schema varies dramatically per server. This is MongoDB's sweet spot.

I covered the specific MongoDB patterns in [MongoDB Patterns I Learned from 7,500 Discord Servers](/blog/mongodb-patterns-discord-bots).

**MongoDB's trap:** Using it as a relational database. If you're doing `$lookup` (MongoDB's join equivalent) in most queries, you chose the wrong database. Denormalize or switch to PostgreSQL.

## Redis: The Speed Layer

Redis isn't a primary database - it's a speed layer that sits in front of your primary database.

**Choose Redis for:**
- **Caching** - expensive query results, session data, frequently accessed configuration
- **Rate limiting** - atomic increment operations make it perfect for tracking request counts
- **Queues** - lightweight job queues using lists or streams
- **Real-time features** - pub/sub for WebSocket fan-out, online presence tracking

**Real example:** Chat Guard uses Redis for invite tracking caches across shards and for rate limiting API calls to Discord. The data is ephemeral - if Redis restarts, the cache rebuilds from the primary database. No data loss, just a brief performance dip.

**Redis's limit:** It's in-memory. Your dataset must fit in RAM. For large datasets, use Redis as a cache with eviction policies, not as a primary store.

## SQLite: The Secret Weapon

SQLite is underrated for the right use case.

**Choose SQLite when:**
- Your application is single-server or embedded
- You want zero configuration (no separate database process)
- Your read-to-write ratio is high
- You're building CLI tools, mobile apps, or desktop applications

**Real example:** Several internal tools at HMD Developments use SQLite for local data storage. A CLI tool that processes log files stores results in SQLite. A data analysis script uses SQLite as a working database. No server to configure, no connection string, just a file.

**SQLite's constraint:** One writer at a time. If you need concurrent writes from multiple processes, choose something else.

## DynamoDB: Serverless Scale

AWS DynamoDB is for when you need guaranteed performance at massive scale with minimal operational overhead.

**Choose DynamoDB when:**
- You need single-digit millisecond latency at any scale
- Your access patterns are well-defined and won't change (you design the schema around the queries)
- You want fully managed with zero server administration
- You're already deep in the AWS ecosystem

**DynamoDB's trap:** Designing the schema. DynamoDB requires you to know every access pattern before you write a single line of code. Adding a new query pattern later might require a full table redesign.

## The Decision Cheat Sheet

| Scenario | Choose |
|----------|--------|
| Generic web application | PostgreSQL |
| Content management, user profiles | PostgreSQL or MongoDB |
| Real-time chat, events | MongoDB + Redis |
| Financial transactions | PostgreSQL (ACID required) |
| Session storage, caching | Redis |
| IoT, time-series data | PostgreSQL (TimescaleDB) or DynamoDB |
| Mobile/embedded local storage | SQLite |
| Serverless at massive scale | DynamoDB |

## The Meta-Lesson

The best database is the one your team knows how to operate. A PostgreSQL database managed by someone who understands VACUUM, connection pooling, and index optimization will outperform a MongoDB cluster managed by someone who doesn't understand replica sets.

Technical capability matters less than operational knowledge. Choose the database you can run reliably, then learn when to reach for something specialized.

---

*Not sure which database to pick? Start with PostgreSQL. You can always add Redis for caching and migrate specific workloads later. Starting generic is cheaper than starting specialized and being wrong.*