Database Design Patterns for High-Scale Applications

Database Design Patterns for High-Scale Applications

When your application starts gaining traction, database performance becomes the bottleneck faster than you’d expect. I’ve seen perfectly designed applications grind to a halt because nobody thought about database scalability until it was too late. Let’s talk about patterns that actually work when you need to handle serious scale.

Why Database Design Matters More Than You Think

Here’s the uncomfortable truth: most scalability problems trace back to database decisions made early on. Unlike application code that you can refactor relatively easily, database migrations with millions of records are expensive, risky, and time-consuming.

The good news? A handful of proven patterns can take you from struggling with a few thousand concurrent users to smoothly handling millions. You don’t need to be a database expert—you just need to understand the fundamentals and apply them correctly.

Understanding Your Scaling Constraints

Before diving into patterns, let’s understand what actually limits database performance:

Read vs Write Load: Different applications have wildly different ratios. Social media is read-heavy (90% reads), while analytics platforms might be write-heavy (70% writes). Your pattern choices depend heavily on this.

Data Access Patterns: How do users query your data? Are they looking up specific records by ID, or running complex aggregations across millions of rows?

Consistency Requirements: Does every read need the absolute latest data, or can you tolerate eventual consistency for better performance?

Pattern 1: Read Replicas for Read-Heavy Workloads

This is your first line of defense when reads start overwhelming your primary database.

How It Works

Your primary database handles all writes. These changes automatically replicate to one or more read replicas. Your application routes read queries to replicas, distributing the load.

Real-World Implementation

// Simple read/write routing
class DatabaseRouter {
  async write(query) {
    return await primaryDb.execute(query);
  }

  async read(query) {
    const replica = this.selectReplica();
    return await replica.execute(query);
  }

  selectReplica() {
    // Round-robin or least-connections strategy
    return replicas[currentIndex++ % replicas.length];
  }
}

When to Use It

• Read-to-write ratio is at least 3:1
• Slight data lag (typically milliseconds) is acceptable
• You need to scale reads without changing application logic

Gotchas

Watch out for replication lag. If a user creates a record and immediately queries for it, they might not see their own data if the query hits a replica that hasn’t caught up. Use session affinity or read-after-write consistency patterns to handle this.

Pattern 2: Database Sharding

When a single database can’t handle your data volume, sharding splits it across multiple databases.

Horizontal Sharding (Most Common)

Split data by a shard key—usually a customer ID, user ID, or geographic region.

def get_shard(user_id):
    shard_count = 16
    shard_num = hash(user_id) % shard_count
    return shards[shard_num]

def get_user(user_id):
    shard = get_shard(user_id)
    return shard.query("SELECT * FROM users WHERE id = ?", user_id)

Choosing Your Shard Key

This is critical. A good shard key:

• Distributes data evenly across shards
• Minimizes cross-shard queries
• Aligns with your most common access patterns

Good: user_id for a SaaS app where queries are user-scoped Bad: created_at which creates hotspots as recent data piles up in one shard

The Cross-Shard Query Problem

Queries spanning multiple shards are expensive. You either:

1. Query all shards and merge results (slow)
2. Denormalize data to avoid cross-shard queries
3. Accept that some queries will be slower

We typically denormalize. If users need to search across all customers, we maintain a separate search index (Elasticsearch) rather than querying across shards.

Pattern 3: Partitioning for Time-Series Data

Time-series data (logs, metrics, events) has unique characteristics that make traditional indexing inadequate.

Table Partitioning

Partition tables by time range:

CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

Why This Works

When you query recent data (which is 95% of queries), the database only scans relevant partitions. Old partitions can be archived or dropped entirely.

Automated Partition Management

// Create next month's partition automatically
async function ensurePartitions() {
  const nextMonth = getNextMonth();
  const partitionExists = await checkPartition(nextMonth);

  if (!partitionExists) {
    await createPartition(nextMonth);
  }

  // Archive partitions older than 90 days
  const oldPartitions = await getPartitionsOlderThan(90);
  for (const partition of oldPartitions) {
    await archiveToS3(partition);
    await dropPartition(partition);
  }
}

Pattern 4: CQRS (Command Query Responsibility Segregation)

Separate your read and write models entirely. Sounds complex, but it’s powerful for specific use cases.

The Basic Idea

Write commands go to a normalized database optimized for writes. Read queries hit denormalized read models optimized for specific query patterns.

// Write side - normalized
interface CreateOrderCommand {
  userId: string;
  items: OrderItem[];
}

async function createOrder(cmd: CreateOrderCommand) {
  await db.orders.insert({
    userId: cmd.userId,
    createdAt: new Date()
  });

  await db.orderItems.insertMany(cmd.items);

  // Trigger read model update
  await eventBus.publish('OrderCreated', cmd);
}

// Read side - denormalized for display
async function getOrderSummary(orderId: string) {
  // Pre-joined, pre-calculated data
  return await readDb.orderSummaries.findOne({ orderId });
}

When CQRS Makes Sense

• Complex domain logic with different read/write needs
• High read-to-write ratios with expensive aggregations
• When eventual consistency is acceptable

Don’t use it for simple CRUD apps. The complexity isn’t worth it.

Pattern 5: Denormalization for Performance

Sometimes the textbook normalized schema just doesn’t cut it.

Strategic Denormalization

Instead of joining three tables for every product page view:

-- Normalized (slow with millions of records)
SELECT p.*, c.name as category, b.name as brand
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN brands b ON p.brand_id = b.id
WHERE p.id = ?

Denormalize the commonly accessed fields:

-- Denormalized (fast)
SELECT id, name, category_name, brand_name, price
FROM products_denormalized
WHERE id = ?

Keeping Denormalized Data in Sync

// Update denormalized data when source changes
async function updateBrandName(brandId, newName) {
  await db.brands.update({ id: brandId }, { name: newName });

  // Update all denormalized references
  await db.products_denormalized.updateMany(
    { brand_id: brandId },
    { brand_name: newName }
  );
}

Yes, it’s more complex. But if that query runs 10,000 times per second, the tradeoff makes sense.

Pattern 6: Connection Pooling

Not exactly a design pattern, but critical for scale. Every database connection consumes resources. Connection pooling reuses connections instead of creating new ones for each query.

Proper Pool Configuration

const pool = new Pool({
  min: 10,              // Minimum connections
  max: 100,             // Maximum connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Wrong - creates new connection each time
async function badQuery() {
  const client = await new Client().connect();
  const result = await client.query('SELECT * FROM users');
  await client.end();
  return result;
}

// Right - uses pool
async function goodQuery() {
  const client = await pool.connect();
  try {
    return await client.query('SELECT * FROM users');
  } finally {
    client.release(); // Returns to pool
  }
}

Connection Pool Math

Calculate your max connections based on:

• Application server count × connections per server
• Database connection limit (Postgres default: 100)
• Expected concurrent queries

If you have 10 app servers and each needs 20 connections, you need at least 200 connection capacity on your database.

Pattern 7: Caching Strategies

The fastest database query is the one you never make.

Multi-Layer Caching

async function getUser(userId) {
  // Layer 1: Application memory
  if (memoryCache.has(userId)) {
    return memoryCache.get(userId);
  }

  // Layer 2: Redis
  const cached = await redis.get(`user:${userId}`);
  if (cached) {
    memoryCache.set(userId, cached);
    return cached;
  }

  // Layer 3: Database
  const user = await db.users.findOne({ id: userId });

  // Cache for next time
  await redis.setex(`user:${userId}`, 3600, user);
  memoryCache.set(userId, user);

  return user;
}

Cache Invalidation Strategies

Time-based: Cache for X seconds, then refresh

• Simple to implement
• Guaranteed fresh data after expiry
• May serve stale data before expiry

Event-based: Invalidate when data changes

• Always fresh data
• More complex to implement
• Requires coordination across services

We typically use time-based for rarely-changing data (product catalogs) and event-based for user-specific data (cart contents).

Common Pitfalls and How to Avoid Them

Premature Sharding

Don’t shard until you actually need to. Sharding adds massive complexity. We’ve seen teams shard prematurely, then struggle with development velocity for months.

When to shard: When vertical scaling (bigger database server) becomes cost-prohibitive or impossible, and you’ve exhausted read replicas and caching.

Ignoring Query Patterns

Beautiful schema design means nothing if it doesn’t match your queries. Always profile actual production queries, not theoretical ones.

Over-Indexing

Every index speeds up reads but slows down writes. More isn’t always better. We typically limit indexes to:

• Primary keys
• Foreign keys that are frequently joined
• Columns in WHERE clauses of common queries
• Columns used for sorting

Under-Monitoring

You can’t optimize what you don’t measure. Essential metrics:

• Query latency (p50, p95, p99)
• Connection pool utilization
• Replication lag
• Table sizes and growth rates
• Slow query logs

Practical Migration Strategies

Changing database architecture with live traffic is nerve-wracking. Here’s how we do it safely:

Blue-Green Database Migration

1. Set up new database with updated schema
2. Dual-write to both databases
3. Backfill historical data to new database
4. Verify data consistency
5. Switch reads to new database
6. Monitor for issues
7. Stop writing to old database
8. Decommission old database

Schema Migrations

// Safe schema change process
async function addColumnSafely() {
  // Step 1: Add nullable column
  await db.query('ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL');

  // Step 2: Backfill data (in batches)
  let offset = 0;
  while (true) {
    const users = await db.query(
      'SELECT id FROM users WHERE last_login IS NULL LIMIT 1000 OFFSET ?',
      [offset]
    );

    if (users.length === 0) break;

    for (const user of users) {
      await updateLastLogin(user.id);
    }

    offset += 1000;
  }

  // Step 3: Make NOT NULL after backfill complete
  await db.query('ALTER TABLE users ALTER COLUMN last_login SET NOT NULL');
}

Choosing the Right Pattern

There’s no one-size-fits-all solution. Here’s our decision framework:

Start Simple: Single database with good indexes and query optimization

Add Read Replicas: When reads dominate and single database CPU is maxed

Add Caching: When the same data is queried repeatedly

Consider Partitioning: For time-series data with known access patterns

Shard Last: When you’ve exhausted other options and need horizontal scaling

Conclusion

Database scaling isn’t about knowing every pattern—it’s about understanding your specific workload and applying the right pattern at the right time. Start simple, measure constantly, and add complexity only when the data justifies it.

The patterns we’ve covered have helped our clients scale from thousands to millions of users without database rewrites. But remember: the best pattern is the one that solves your specific problem without adding unnecessary complexity.

Running into database performance issues? VooStack specializes in database architecture and optimization for high-scale applications. Let’s talk about your scaling challenges.