Databases
Databases
The database is the most consequential technical decision you make early in a product. It is the hardest component to change after launch, and its design determines what queries are fast, what queries are possible, and how much your infrastructure costs at scale.
Relational Databases (SQL)
PostgreSQL is the default correct choice for 90% of new products. It is battle-tested (30+ years), ACID-compliant, supports complex queries with JOINs and aggregations, and has a rich ecosystem of tooling.
Relational databases organize data into tables with defined schemas. Each table has typed columns (text, integer, boolean, timestamp, UUID, JSONB) and rows. Relationships between tables are expressed via foreign keys.
The relational model excels when:
- Your data has clear relationships (users have orders; orders have line items)
- You need complex queries across multiple entities
- Data integrity is critical (financial transactions, health records)
- You need to ask questions you have not thought of yet (ad-hoc analytics)
Schema Design Principles
Good schema design starts with normalization — organizing data to eliminate redundancy. The three most important normal forms:
1NF: Each column contains atomic values. No arrays of values in a single column (use a separate table or JSONB).
2NF: Every non-key column depends on the full primary key, not a subset. In a table with composite keys, this prevents partial dependencies.
3NF: No non-key column depends on another non-key column (no transitive dependencies). Store city and state separately, not city-plus-state in one column.
In practice, you will denormalize selectively for performance — duplicating frequently-read data to avoid JOINs on hot paths. This is a deliberate trade-off, not an accident.
Indexes: When and What
Without an index, a database satisfies a query by reading every row in the table (a full-table scan). With a B-tree index on the queried column, it finds rows in O(log n) time — roughly 20 comparisons for a million rows.
Add indexes on:
- Columns used in WHERE clauses (
WHERE email = ?) - Columns used in JOIN conditions (
ON orders.user_id = users.id) - Columns used in ORDER BY on large tables
- Foreign key columns (PostgreSQL does not do this automatically)
Do not blindly index everything. Each index adds ~10–30% write overhead and storage cost. A write-heavy table (logs, events) with 20 indexes will be slow. Profile query performance (use EXPLAIN ANALYZE in PostgreSQL) before adding indexes.
NoSQL: When It Is the Right Tool
NoSQL covers a broad family: document stores (MongoDB), key-value stores (Redis), column stores (Cassandra), and graph databases (Neo4j). The common thread: they sacrifice SQL's relational flexibility for scale, speed, or specific data model fit.
Use Redis for: caching (session storage, rate limit counters, computed values that are expensive to recalculate), pub/sub messaging, and leaderboards.
Use MongoDB for: content that varies widely in structure per document (CMSs with variable block types, product catalogs with different attributes per category).
Use Cassandra for: write-heavy time-series data at extreme scale (IoT sensor readings, event logs for hundreds of millions of users). Cassandra writes are O(1); reads require careful query planning.
The most common NoSQL mistake: choosing MongoDB "for flexibility" on a relational dataset, then spending months trying to replicate JOIN behavior in application code.
Connection Pooling
Opening a database connection is expensive — typically 50–100ms. Applications maintain a connection pool: a fixed number of open connections that requests borrow and return. In serverless environments (Next.js API routes, Lambda), each function invocation tries to open a new connection, which can exhaust your database's connection limit. The fix: use a connection pooler (PgBouncer, Supabase's built-in pooler) that manages connections between your functions and the database.
Backups and Point-in-Time Recovery
Managed databases (Supabase, PlanetScale, RDS) take automated daily backups and support point-in-time recovery — restoring to any second in the past 7–30 days. Before choosing a database provider, verify: backup frequency, retention window, restore time objective (how long a restore takes), and whether you can test the restore process without service interruption.