Practical Patterns#

Database theory gets you through schema design and query writing. This section covers the patterns you encounter when databases meet production code — migrations, pagination, connection pooling, multi-tenancy, and the N+1 query problem.

Schema Migrations#

The Problem#

Database schemas evolve. Adding columns, changing types, creating indexes — these changes must be applied consistently across dev, staging, and production.

Migration Tools#

Migration Best Practices#

Zero-Downtime Migration Pattern#

1. Add new column (nullable)           ← migration 1
2. Deploy code that writes to BOTH old and new columns
3. Backfill new column from old column ← migration 2
4. Deploy code that reads from new column only
5. Drop old column                     ← migration 3

Never rename or remove a column in a single deployment — the old code still running during rollout will break.

Pagination#

Offset-Based (Simple, Problematic)#

SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 40;

Cursor-Based (Keyset Pagination)#

-- First page
SELECT * FROM products ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page (cursor = last item's created_at + id)
SELECT * FROM products
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Use cursor-based pagination for APIs. Offset-based is acceptable for admin UIs with small datasets.

The N+1 Query Problem#

# N+1: 1 query for orders + N queries for users
orders = db.query("SELECT * FROM orders")  # 1 query
for order in orders:
    user = db.query("SELECT * FROM users WHERE id = ?", order.user_id)  # N queries

Solutions#

-- JOIN (single query)
SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id;

-- Subquery / IN clause (2 queries)
-- Query 1: SELECT * FROM orders
-- Query 2: SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5)

-- ORM eager loading
-- SQLAlchemy: .options(joinedload(Order.user))
-- Django: .select_related('user')
-- ActiveRecord: .includes(:user)

Connection Pooling#

Opening a database connection is expensive (~20-50ms for PostgreSQL with TLS). A connection pool maintains a set of reusable connections.

Pool Configuration#

Sizing Rule#

max_connections_per_instance × number_of_instances ≤ database_max_connections

PostgreSQL default max_connections is 100. With 10 app instances at 20 connections each = 200 — exceeded. Use PgBouncer or RDS Proxy to multiplex.

Connection Proxies#

Multi-Tenancy Patterns#

Shared Database, Shared Schema (Column Discriminator)#

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL,  -- discriminator column
    amount DECIMAL(10, 2),
    -- ...
    INDEX idx_tenant (tenant_id)
);

-- Every query must include tenant_id
SELECT * FROM orders WHERE tenant_id = 42 AND status = 'active';

Risk: A missed WHERE tenant_id = ? leaks data across tenants. Use Row-Level Security (RLS) in PostgreSQL to enforce:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::int);

Shared Database, Separate Schemas#

CREATE SCHEMA tenant_42;
CREATE TABLE tenant_42.orders (...);

SET search_path = tenant_42;
SELECT * FROM orders;  -- automatically scoped

Separate Databases#

One database per tenant. Maximum isolation, simplest security, hardest to manage at scale.

Soft Deletes#

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- "Delete"
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- Query (exclude deleted)
SELECT * FROM users WHERE deleted_at IS NULL;

-- Create a view for convenience
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

Key Takeaways#

• Migrations must be forward-only, backwards-compatible, and tested on production-like data.
• Use cursor-based pagination for APIs. Offset-based is O(offset) and inconsistent under writes.
• The N+1 problem is the #1 ORM performance pitfall. Fix with JOINs, IN queries, or eager loading.
• Connection pools are essential. Size them so instances × pool_size ≤ DB max_connections.
• Multi-tenancy: column discriminator for most SaaS, schema/DB isolation for compliance-heavy tenants.
• Soft deletes are convenient but add complexity to every query. Consider an archive table instead.