Relational vs NoSQL

One of the first and most consequential decisions in any custom application is choosing between relational and NoSQL databases. This choice affects how you structure data, write queries, scale the system, and maintain code over time. The decision is not as binary as marketing materials suggest—the real world is full of tradeoffs.

Relational Databases: The Structured Default

Relational databases store data in tables with predefined schemas. PostgreSQL, MySQL, and SQLite are the most common. Each table has columns with specific data types. Related data is split across tables and joined together through foreign keys. The defining features are strict schemas and ACID transactions.

A schema is a contract. When you define a table, you commit to a structure: this column is an integer, that column is text, this one is a date. The database enforces this contract. Data either fits the schema or it doesn't. This constraint, which sounds limiting, is actually powerful. It prevents data quality problems before they happen. Bad data can't enter the database.

ACID transactions guarantee data consistency. If you transfer money between accounts, both the debit and credit succeed, or neither does. No halfway states. This matters enormously for financial systems, inventory management, and any application where data integrity is non-negotiable.

NoSQL Databases: Flexible but Tricky

NoSQL encompasses several different paradigms. Document stores like MongoDB store JSON-like documents without enforcing a rigid schema. Key-value stores like Redis don't enforce structure at all—you store and retrieve data by key. Graph databases like Neo4j optimize for relationships. Each solves different problems.

The appeal of NoSQL is schema flexibility. You can store documents with completely different structures in the same collection. This sounds liberating until you've spent weeks debugging inconsistencies caused by documents having different field names or types. Schemaless sounds good in theory but in practice introduces data quality problems.

The "Scale Myth"

One of the most persistent misconceptions is that relational databases don't scale and NoSQL is required for scale. This is false. Notion, one of the most heavily used productivity tools, runs on PostgreSQL. GitHub ran on MySQL for years. Relational databases handle enormous volumes. The constraint is not the database type, it's query efficiency and connection pooling. A properly indexed PostgreSQL query is faster than a poorly designed MongoDB query.

Where NoSQL genuinely excels is horizontal scaling—automatically sharding data across many servers. DynamoDB and MongoDB are built for this. PostgreSQL can be replicated and load-balanced, but horizontal sharding is more complex. For most custom applications, this is irrelevant. You'll hit database query optimization problems long before you hit the limits of a single PostgreSQL instance on modern hardware.

When Relational Wins

Use relational databases when you have complex relationships between entities that need to be queried flexibly. An order has line items, which reference products, which have inventory. You need to query this from many angles—orders by customer, inventory by warehouse, products by category. ACID transactions matter when you need guarantees about data consistency. Most custom applications fall into this category.

When NoSQL Has a Real Advantage

Document stores shine when you have truly unstructured or highly variable data. For example, a logging system where each event is a different structure. A document store naturally accommodates this. You don't have to normalize events into tables. Key-value stores like Redis are invaluable for caching, sessions, rate limiting, and pub-sub messaging—where you don't need relational queries. Graph databases win for relationship-heavy data like social networks or recommendation engines.

The Schema Flexibility Trap

Developers often choose document stores believing they won't need to migrate data later. In reality, every successful application evolves. A schemaless system means the schema lives in application code. You're responsible for handling records created before the new schema, records with missing fields, and different versions of a field. This is more fragile than database-enforced schemas.

If you use MongoDB, you can add schema validation. You're not actually getting the flexibility you thought. You're just paying the cost of both—the complexity of multiple document structures plus enforcement overhead.

Default Recommendation

Start with PostgreSQL for almost everything. Use it for the main transactional database. Combine it with Redis for caching and sessions. If you have unstructured logs or events, a separate document store can coexist with PostgreSQL. This hybrid approach gives you the best of both worlds.