Database Design Patterns for Scalable Apps

Database design is crucial for application scalability and performance. In this post, we'll explore various patterns and best practices for designing databases that can grow with your application.

Understanding Scalability Challenges

As applications grow, databases often become the bottleneck. Common issues include:

• Slow query performance
• Data consistency problems
• High maintenance overhead
• Difficulty in horizontal scaling

Key Design Patterns

1. CQRS (Command Query Responsibility Segregation)

Separate read and write operations for better performance:

// Command - Write operations
interface CreateUserCommand {
  type: 'CREATE_USER';
  payload: {
    email: string;
    name: string;
  };
}

// Query - Read operations
interface GetUserQuery {
  type: 'GET_USER';
  payload: {
    userId: string;
  };
}

2. Event Sourcing

Store all changes as a sequence of events:

interface UserEvent {
  id: string;
  type: 'USER_CREATED' | 'USER_UPDATED' | 'USER_DELETED';
  data: any;
  timestamp: Date;
  version: number;
}

3. Database Sharding

Split large tables across multiple database instances:

-- Hash-based sharding
CREATE TABLE users_shard_0 (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(255)
);

CREATE TABLE users_shard_1 (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(255)
);

Indexing Strategies

Composite Indexes

Create indexes on multiple columns for complex queries:

-- Index for user search queries
CREATE INDEX idx_users_email_name ON users(email, name);

-- Index for time-based queries
CREATE INDEX idx_posts_created_at_desc ON posts(created_at DESC);

Partial Indexes

Index only relevant rows to reduce storage and improve performance:

-- Index only active users
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

Data Partitioning

Range Partitioning

Partition data based on date ranges:

-- Partition orders by month
CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

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

List Partitioning

Partition data based on categorical values:

-- Partition users by region
CREATE TABLE users_asia PARTITION OF users
    FOR VALUES IN ('asia');

CREATE TABLE users_europe PARTITION OF users
    FOR VALUES IN ('europe');

Performance Monitoring

Query Analysis

Use EXPLAIN to understand query execution:

EXPLAIN ANALYZE
SELECT * FROM users
WHERE created_at > '2025-01-01'
ORDER BY created_at DESC
LIMIT 10;

Connection Pooling

Implement connection pooling to manage database connections efficiently:

import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20, // Maximum number of connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Migration Strategies

Zero-Downtime Migrations

Implement migrations that don't require application downtime:

// Migration script
export async function migrateUsersTable() {
  // Create new table with updated schema
  await db.execute(`
    CREATE TABLE users_new (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(255) NOT NULL,
      created_at TIMESTAMP DEFAULT NOW()
    )
  `);

  // Copy data in batches
  await db.execute(`
    INSERT INTO users_new (id, email, name, created_at)
    SELECT id, email, name, created_at FROM users
  `);

  // Rename tables
  await db.execute('ALTER TABLE users RENAME TO users_old');
  await db.execute('ALTER TABLE users_new RENAME TO users');
}

These patterns and strategies will help you design databases that can scale effectively while maintaining performance and reliability.