How do I decide which columns to index?

Index columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses for queries that run frequently and on large tables. Key factors: (1) Selectivity — an index on a column with high cardinality (many distinct values, like email or user_id) is much more useful than one on a low-cardinality column (like status with 3 values — a table scan may be faster). (2) Query frequency — index to benefit your most common queries, not hypothetical ones. (3) Write cost — every index slows down INSERT/UPDATE/DELETE by a small amount. Do not add indexes speculatively. Use EXPLAIN ANALYZE on your actual slow queries to identify missing indexes.

When should I denormalize my database schema?

Denormalization (intentionally adding redundancy) is appropriate when: (1) A JOIN is on the critical path of a very high-traffic query and profiling shows it is a genuine bottleneck. (2) You need to pre-compute aggregates (total_order_count on a users table) because computing them at query time is too slow. (3) Your read-to-write ratio is very high and you can afford to update redundant data on writes. Before denormalizing, exhaust all index optimization options — partial indexes, covering indexes, materialized views. Denormalization trades write complexity and potential data inconsistency for read speed. Always have an explicit strategy for keeping redundant data in sync.

What is the difference between INNER JOIN, LEFT JOIN, and CROSS JOIN?

INNER JOIN returns only rows where the join condition matches in both tables — unmatched rows from either side are excluded. LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table plus matching rows from the right table; where there is no match, the right table columns are NULL. This is used to retrieve records with optional related data (all users, including those with no orders). RIGHT JOIN is the mirror. CROSS JOIN returns the Cartesian product — every row from the left table paired with every row from the right (m × n rows). CROSS JOINs are rarely useful in application queries but appear in calendar generation and combination problems.

What is a materialized view and when should I use it?

A materialized view is a pre-computed query result stored as a physical table. Unlike a regular view (which re-executes the query on every access), a materialized view is fast to read but requires explicit refreshing when the underlying data changes. Use materialized views for: expensive aggregation queries (total sales by category, user activity summaries) that are queried frequently but the underlying data changes infrequently. In PostgreSQL: CREATE MATERIALIZED VIEW monthly_revenue AS SELECT DATE_TRUNC("month", created_at), SUM(amount) FROM orders GROUP BY 1. Refresh with REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue (CONCURRENTLY allows reads during refresh). Schedule the refresh in a cron job or trigger it after significant data updates.

How does connection pooling work and why is it critical for serverless?

PostgreSQL creates a dedicated OS process (~5 MB RAM each) for every client connection. Applications with many short-lived requests — especially serverless functions that spin up and down — can create hundreds of simultaneous connections during a traffic spike, exhausting the server limit (default max_connections is 100–200). A connection pooler like PgBouncer maintains a small pool of long-lived database connections and routes incoming application connections to pool members. In transaction mode, a database connection is held only for the duration of a single transaction, then returned to the pool. For Supabase users, use port 6543 (the Supabase Vitest/PgBouncer endpoint) instead of 5432 for all serverless deployments. Without pooling, a moderate traffic spike can cause "too many connections" errors and a database outage.

Engineering·9 min read·June 21, 2026

🗄️ Database Design Patterns: SQL, NoSQL, and NewSQL

A practical guide to database design patterns: normalization (1NF through BCNF), indexing strategies (B-tree, hash, composite), query optimization with EXPLAIN, the N+1 problem, PostgreSQL vs MongoDB use cases, and connection pooling.

📖Full-Stack SaaS with Next.js, Supabase & Groq AI (Full Access)

View →

Why Database Design Decisions Are Hard to Undo

Application code is easy to refactor. Database schema changes in production are painful — they require migrations that may lock tables, break existing queries, and require coordinated deploys. Getting the schema right, the indexes right, and the normalization level right from the start saves enormous effort. This guide covers the core patterns that experienced database engineers apply on every project.

Topics covered

database design patternsSQL normalization 1NF 2NF 3NFdatabase indexing strategiesB-tree index explainedcomposite index optimizationEXPLAIN query PostgreSQLN+1 problem databasePostgreSQL vs MongoDBconnection pooling PgBouncerdatabase query optimizationBCNF normal formcovering index SQLpartial index PostgreSQLindex selectivity databaseSQL joins performancedatabase schema designforeign key constraintsNewSQL distributed database