Database Design Patterns for Scale

Introduction

Database design decisions made early in a project often become the hardest to change later. Understanding scalability patterns from the start helps avoid painful migrations and performance crises.

Choosing Between SQL and NoSQL

When to Choose PostgreSQL (SQL)

-- PostgreSQL excels with:
-- 1. Complex queries and joins
SELECT 
  o.id,
  o.total_amount,
  c.name as customer_name,
  json_agg(
    json_build_object(
      'product', p.name,
      'quantity', oi.quantity,
      'price', oi.unit_price
    )
  ) as items
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY o.id, c.name
HAVING COUNT(oi.id) > 3;

-- 2. ACID transactions
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  INSERT INTO transactions (from_id, to_id, amount) VALUES (1, 2, 100);
COMMIT;

-- 3. Complex constraints
ALTER TABLE orders ADD CONSTRAINT valid_status 
  CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered'));

ALTER TABLE order_items ADD CONSTRAINT positive_quantity
  CHECK (quantity > 0);

When to Choose MongoDB (NoSQL)

// MongoDB excels with:
// 1. Flexible schemas that evolve
interface Product {
  _id: ObjectId;
  name: string;
  price: number;
  // Different products have different attributes
  attributes: Record<string, unknown>;
}

// Electronics might have
{ attributes: { screenSize: 15.6, ram: 16, storage: 512 } }

// Clothing might have
{ attributes: { size: 'M', color: 'blue', material: 'cotton' } }

// 2. Document-based access patterns
// Embed related data that's always accessed together
interface Order {
  _id: ObjectId;
  customer: {
    id: ObjectId;
    name: string;
    email: string;
  };
  items: Array<{
    productId: ObjectId;
    name: string;
    quantity: number;
    price: number;
  }>;
  shippingAddress: Address;
  status: string;
}

// Single query gets everything needed to display order
const order = await orders.findOne({ _id: orderId });

// 3. High write throughput with eventual consistency
await collection.insertMany(events, { 
  ordered: false,  // Continue on errors
  writeConcern: { w: 1 }  // Acknowledge after primary write
});

Indexing Strategies

PostgreSQL Indexing

-- B-tree index for equality and range queries
CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, created_at DESC);

-- Partial index for common queries on subset of data
CREATE INDEX idx_orders_pending 
ON orders(created_at) 
WHERE status = 'pending';

-- GIN index for full-text search
CREATE INDEX idx_products_search 
ON products USING GIN(to_tsvector('english', name || ' ' || description));

-- Covering index to avoid table lookups
CREATE INDEX idx_orders_summary 
ON orders(customer_id, created_at) 
INCLUDE (status, total_amount);

-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders 
WHERE customer_id = 123 
AND created_at > '2024-01-01';

MongoDB Indexing

// Compound index following ESR rule (Equality, Sort, Range)
await collection.createIndex(
  { status: 1, createdAt: -1, totalAmount: 1 },
  { name: 'idx_orders_status_date_amount' }
);

// Text index for search
await collection.createIndex(
  { name: 'text', description: 'text' },
  { weights: { name: 10, description: 5 } }
);

// TTL index for automatic cleanup
await collection.createIndex(
  { expiresAt: 1 },
  { expireAfterSeconds: 0 }
);

// Partial index for active records only
await collection.createIndex(
  { email: 1 },
  { 
    unique: true,
    partialFilterExpression: { status: 'active' }
  }
);

Partitioning and Sharding

PostgreSQL Partitioning

-- Range partitioning by date
CREATE TABLE orders (
  id BIGSERIAL,
  customer_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  total_amount DECIMAL(10,2),
  status VARCHAR(50)
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Automatic partition creation with pg_partman
SELECT partman.create_parent(
  p_parent_table := 'public.orders',
  p_control := 'created_at',
  p_type := 'native',
  p_interval := 'monthly',
  p_premake := 3
);

MongoDB Sharding

// Choose shard key carefully - it determines data distribution
// Good shard key: high cardinality, even distribution, query isolation

// Enable sharding on database
sh.enableSharding("ecommerce");

// Shard collection with hashed key for even distribution
sh.shardCollection(
  "ecommerce.orders",
  { customerId: "hashed" }
);

// Or range-based for query locality
sh.shardCollection(
  "ecommerce.orders",
  { customerId: 1, createdAt: 1 }
);

// Zone sharding for data locality (e.g., EU data in EU)
sh.addShardTag("shard-eu-1", "EU");
sh.addShardTag("shard-us-1", "US");

sh.addTagRange(
  "ecommerce.customers",
  { region: "EU" },
  { region: "EU" + MaxKey },
  "EU"
);

Replication Patterns

Read Replicas

// Application-level read/write splitting
class DatabasePool {
  private primary: Pool;
  private replicas: Pool[];
  private replicaIndex = 0;

  async query(sql: string, params: unknown[], options?: { readonly?: boolean }) {
    if (options?.readonly && this.replicas.length > 0) {
      // Round-robin across replicas
      const replica = this.replicas[this.replicaIndex];
      this.replicaIndex = (this.replicaIndex + 1) % this.replicas.length;
      return replica.query(sql, params);
    }
    return this.primary.query(sql, params);
  }

  async transaction<T>(fn: (client: PoolClient) => Promise<T>): Promise<T> {
    // Transactions always go to primary
    const client = await this.primary.connect();
    try {
      await client.query('BEGIN');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

Query Optimization

-- Avoid SELECT *
-- ❌ Bad
SELECT * FROM orders WHERE customer_id = 123;

-- ✅ Good
SELECT id, status, total_amount, created_at 
FROM orders 
WHERE customer_id = 123;

-- Use EXISTS instead of IN for subqueries
-- ❌ Slower
SELECT * FROM customers 
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- ✅ Faster
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.customer_id = c.id AND o.total > 1000
);

-- Batch operations
-- ❌ N+1 queries
for (const id of orderIds) {
  await db.query('UPDATE orders SET status = $1 WHERE id = $2', ['shipped', id]);
}

-- ✅ Single query
await db.query(
  'UPDATE orders SET status = $1 WHERE id = ANY($2)',
  ['shipped', orderIds]
);

Conclusion

Database design for scale requires:

1. Choose the right database for your access patterns
2. Index strategically based on actual query patterns
3. Partition early if you expect data growth
4. Use read replicas to scale read-heavy workloads
5. Optimize queries before adding hardware

The best database architecture is one that matches your specific workload patterns and growth expectations.