Database Architect

Decision Criteria

1. Data structure → Relational vs Document vs Graph vs Key-Value
2. Scale expectations → Read-heavy vs Write-heavy vs Balanced
3. Consistency needs → Strong (ACID) vs Eventual (BASE)
4. Query patterns → Simple CRUD vs Complex joins vs Analytics
5. Team expertise → Familiar vs Learning curve acceptable
6. Operational budget → Managed vs Self-hosted

By Use Case

Schema Design Principles

Normalization Levels

When to Denormalize

-- ONLY denormalize when:
-- 1. Measured performance problem with JOINs
-- 2. Read-to-write ratio is very high (>100:1)
-- 3. Data is relatively static

-- Example: Pre-computed aggregate
ALTER TABLE products
  ADD COLUMN order_count INT GENERATED ALWAYS AS (
    (SELECT COUNT(*) FROM orders WHERE product_id = products.id)
  ) STORED;  -- PostgreSQL only, otherwise use trigger/view

Common Patterns

Indexing Strategy

Index Types

Index Decision Tree

Is it equality lookups only? → B-tree (default)
Is it a JSONB column? → GIN
Is it a range query? → B-tree or GiST
Is it full-text search? → GIN on tsvector
Is it >100M rows with time ordering? → BRIN
Is it a composite query? → Composite B-tree (order matters!)

Common Mistakes

-- ❌ Too many indexes (slows writes)
-- ❌ Indexing low-cardinality columns (status with 2 values)
-- ❌ Not indexing foreign keys (PostgreSQL!)
-- ❌ Wrong column order in composite index
-- ❌ Not using partial indexes for hot subsets

Scalability Patterns

Vertical vs Horizontal

Partitioning Strategies

Caching Architecture

Multi-Tier Strategy

┌─────────────────┐
│  L1: App Cache  │  ← In-process, fastest
├─────────────────┤
│  L2: Redis      │  ← Distributed, shared
├─────────────────┤
│  L3: DB Buffer  │  ← PostgreSQL shared_buffers
└─────────────────┘

Cache Invalidation

Migration Strategy

Zero-Downtime Approach

1. Create new schema alongside old
2. Dual-write to both schemas
3. Backfill historical data
4. Verify data integrity
5. Switch reads to new schema
6. Stop writes to old schema
7. Remove old schema

Migration Tools

Security Checklist

• Encrypted connections (TLS)
• At-rest encryption
• RBAC (Role-Based Access Control)
• Row-Level Security (PostgreSQL)
• SQL injection prevention (parameterized queries)
• Audit logging
• Regular backups with tested restores
• Data retention policies (GDPR)

Response Approach

When asked for database architecture advice:

1. Understand requirements - Domain, scale, consistency needs
2. Recommend technology - With clear rationale and trade-offs
3. Design schema - Tables, relationships, constraints
4. Plan indexing - Based on query patterns
5. Design caching - Multi-tier strategy
6. Plan scalability - Growth projections
7. Refer to skill - Point to postgresql (or project-specific implementation docs) for implementation

Example Interactions

Related Skills

Resources

• references/database-selection.md → Detailed comparison matrix
• CAP Theorem → Consistency vs Availability trade-offs
• ACID vs BASE → Transaction model decisions