Database Architecture for High-Traffic Web Applications

Scaling PostgreSQL, Redis, and modern cloud databases to handle millions of users without sacrificing reliability or developer experience.

Database architecture is where many promising applications hit their ceiling. A schema that works beautifully at 1,000 users becomes a bottleneck at 100,000 and a crisis at 1,000,000. Anticipating how data access patterns scale—and making the right architectural decisions early—can save months of painful migration work later. ## The Read/Write Asymmetry Most web applications read far more than they write. A typical content platform might have 100 reads for every write. This asymmetry is the foundation of database scaling strategy: optimize aggressively for read performance while maintaining write reliability. **Read Replicas**: PostgreSQL supports streaming replication to read replicas. Route all queries that can tolerate slight staleness to read replicas, reserving the primary for writes and strongly-consistent reads. In an ORM like Prisma, this is configured at the connection level. **Connection Pooling**: PostgreSQL connection overhead is significant—each connection consumes ~5MB of memory and requires setup/teardown time. Applications should never connect directly to PostgreSQL; they should connect through a pooler (PgBouncer, Supabase Pooler, or RDS Proxy). Set pool sizes based on your PostgreSQL `max_connections` divided by the number of application instances. **Horizontal Sharding**: When a single primary instance can no longer handle write throughput, shard the database. Common sharding keys: user_id (for multi-tenant apps), geographic region, or time range (for time-series data). Sharding dramatically increases operational complexity—use it only when vertical scaling (bigger instances) is exhausted. ## Caching with Redis Redis is the universal accelerator for web applications. Use it to cache: **Query Results**: Cache expensive database queries with a TTL that reflects data freshness requirements. Invalidate cache entries when underlying data changes using cache tags or event-driven invalidation. **Session Storage**: Store session data in Redis for fast, distributed session lookup. Critical for horizontally scaled applications where sessions can't be stored in-process. **Rate Limiting**: Implement sliding window rate limiting with Redis sorted sets or the Redis cell module. Execute as Lua scripts to ensure atomic increment-and-check operations. **Distributed Locks**: Use Redlock (the distributed locking algorithm) to prevent race conditions in distributed systems—for example, ensuring only one instance processes a background job. ```typescript // Redis caching pattern with stale-while-revalidate async function getCachedArticle(slug: string): Promise<Article> { const cached = await redis.get(`article:${slug}`); if (cached) { // Revalidate in background if near expiry const ttl = await redis.ttl(`article:${slug}`); if (ttl < 60) { refreshArticleCache(slug); // non-blocking background refresh } return JSON.parse(cached); } const article = await db.article.findUnique({ where: { slug } }); await redis.setex(`article:${slug}`, 3600, JSON.stringify(article)); return article; } ``` ## Schema Design Principles **Normalize for writes, denormalize for reads**: A fully normalized schema (3NF) minimizes write anomalies but requires joins that are expensive at scale. For read-heavy tables, consider selectively denormalizing—storing computed or joined data directly to eliminate expensive query-time joins. **Partial Indexes**: Index only the subset of rows you actually query. An index on `(status)` where `status = 'active'` is dramatically smaller than a full index and faster for queries that only access active records. **Covering Indexes**: Include all columns needed by a query in the index to enable index-only scans that never touch the main table: ```sql CREATE INDEX idx_articles_slug_published ON articles (slug) INCLUDE (title, author_id, published_at) WHERE published = true; ``` **JSONB for Semi-Structured Data**: PostgreSQL's JSONB column type with GIN indexes supports fast querying of semi-structured data without requiring separate tables. Use for metadata, configuration, and extensible attributes that don't require strict relational constraints. ## Modern Cloud Database Options **PlanetScale (MySQL-compatible)**: Vitess-based branching workflow that brings Git-like database schema migrations. Non-blocking schema changes via online DDL. Excellent for teams that want to avoid migration downtime. **Neon**: Serverless PostgreSQL with branching (perfect for preview environments) and scale-to-zero for development workloads. Point-in-time recovery and instant forking. **Turso (libSQL)**: SQLite-compatible, distributed at the edge. Replicas in every region provide sub-5ms query latency. Ideal for read-heavy applications with low write throughput that need global distribution. **CockroachDB**: Distributed SQL with automatic sharding and multi-region active-active replication. True horizontal write scaling with ACID guarantees. Higher operational complexity and cost than single-region PostgreSQL. ## Database Observability Instrument your database thoroughly: - **Query performance**: Log slow queries (above 100ms), capture EXPLAIN ANALYZE plans for recurring slow queries, and track N+1 patterns at the ORM level - **Connection pool metrics**: Track pool utilization, wait times, and timeouts—pool exhaustion is a common cause of cascading failures - **Replication lag**: Alert when replica lag exceeds your staleness tolerance for read replicas - **Lock contention**: Monitor for lock waits and deadlocks, which indicate schema design issues or inefficient transaction scoping