Database design and optimization specialist. Schema design, query optimization, indexing strategies, data modeling, and migration planning for relational and NoSQL databases.
Database design and optimization specialist. Schema design, query optimization, indexing strategies, data modeling, and migration planning for relational and NoSQL databases.
version
1.1.1
model
sonnet
invoked_by
both
user_invocable
true
tools
["Read","Write","Edit","Bash","Glob","Grep"]
best_practices
["Normalize to 3NF unless performance requires denormalization","Always plan indexes based on query patterns","Use migrations for all schema changes","Document data models and relationships","Use pgvector for AI embedding storage alongside relational data","Prefer Supavisor or PgBouncer for connection pooling in production"]
error_handling
graceful
streaming
supported
verified
true
lastVerifiedAt
"2026-02-22T00:00:00.000Z"
source
builtin
trust_score
100
provenance_sha
5514cb81834daa7b
Database Architect Skill
Database Architect Skill - Designs efficient database schemas, optimizes queries, plans indexes, and creates migration strategies for both relational (PostgreSQL, MySQL) and NoSQL (MongoDB, Redis) databases.
- Designing normalized and denormalized schemas
- Query optimization and execution plan analysis
- Index strategy planning
- Data modeling (ER diagrams, relationships)
- Migration planning and versioning
- Performance troubleshooting
Step 1: Understand Data Requirements
Gather requirements:
Entities: What data needs to be stored?
Relationships: How do entities relate (1:1, 1:N, N:M)?
Access Patterns: How will data be queried?
Volume: Expected data size and growth rate
Consistency: ACID requirements vs eventual consistency
Step 2: Design Schema
For Relational Databases:
Normalize: Start with 3NF to reduce redundancy
Define Primary Keys: Use surrogate keys (UUID/SERIAL) or natural keys
Consider Denormalization: Only for proven performance needs
For NoSQL Databases:
Model for Queries: Design documents/collections around access patterns
Embed vs Reference: Embed for 1:1/1:few, reference for 1:many
Shard Key Selection: Choose keys that distribute evenly
Step 3: Plan Indexes
Index strategy based on query patterns:
-- Example: Users table with common queriesCREATE INDEX idx_users_email ON users(email); -- Exact matchCREATE INDEX idx_users_name ON users(last_name, first_name); -- Range/sortCREATE INDEX idx_users_created ON users(created_at DESC); -- Ordering
Index Guidelines:
Index columns used in WHERE, JOIN, ORDER BY
Consider composite indexes for multi-column queries
Leverage PostgreSQL 17 capabilities where applicable:
Performance improvements:
New VACUUM memory management — up to 20x lower memory footprint; vacuum now runs faster on busy systems
Streaming I/O interface accelerates sequential scans on large datasets
BRIN indexes support parallel builds
B-tree indexes are more efficient for IN clause queries
Optimized CTE (Common Table Expression) planning
SQL/JSON enhancements (PG 17):
JSON_TABLE() — converts JSON data into relational table representation
JSON constructors and identity functions (JSON(), JSON_SCALAR(), JSON_ARRAY(), JSON_OBJECT())
Use jsonpath for expressive path-based queries over JSONB columns
Incremental backups:
pg_basebackup supports incremental backup; combine with pg_upgrade for zero-data-loss major version upgrades
Logical replication improvements:
Failover control for logical replication slots
pg_createsubscriber creates logical replicas from physical standbys
pg_upgrade now preserves logical replication slots across major version upgrades
Security:
New MAINTAIN privilege — grants targeted maintenance rights without full superuser access
sslnegotiation=direct client option for direct TLS handshake (avoids round-trip)
COPY improvements:
COPY ... ON_ERROR ignore — continues import on row-level errors instead of aborting
Step 7: pgvector for AI Embeddings
Store and query vector embeddings alongside relational data to avoid a separate vector database:
-- Install extensionCREATE EXTENSION IF NOTEXISTS vector;
-- Table with embedding column (1536 dims for OpenAI text-embedding-3-small)CREATE TABLE documents (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
content TEXT NOT NULL,
embedding vector(1536),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- IVFFlat index for approximate nearest neighbor (ANN) search-- lists = sqrt(row_count) is a good starting valueCREATE INDEX idx_documents_embedding ON documents
USING ivfflat (embedding vector_cosine_ops) WITH (lists =100);
-- HNSW index (faster queries, more memory; preferred for < 1M vectors)CREATE INDEX idx_documents_embedding_hnsw ON documents
USING hnsw (embedding vector_cosine_ops) WITH (m =16, ef_construction =64);
-- Similarity search (cosine distance)SELECT id, content, 1- (embedding <=> $1::vector) AS similarity
FROM documents
ORDERBY embedding <=> $1::vector
LIMIT 10;
When to use pgvector vs. dedicated vector DB:
Up to ~10M vectors: pgvector is sufficient (sub-50ms queries with HNSW index)
Above 10M vectors or requiring specialized ANN algorithms: consider Pinecone, Weaviate, or Qdrant
pgvector advantage: same backups, replication, and connection pooling as the rest of PostgreSQL
Step 8: Table Partitioning Strategies
Use declarative partitioning for tables expected to exceed available RAM:
-- Range partitioning by date (common for time-series / logs)CREATE TABLE events (
id BIGSERIAL,
created_at TIMESTAMPTZ NOT NULL,
event_type TEXT NOT NULL,
payload JSONB
) PARTITIONBYRANGE (created_at);
-- Monthly partitionsCREATE TABLE events_2025_01 PARTITIONOF events
FORVALUESFROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITIONOF events
FORVALUESFROM ('2025-02-01') TO ('2025-03-01');
-- Hash partitioning for even distribution (e.g., multi-tenant)CREATE TABLE orders (
id UUID NOT NULL,
tenant_id UUID NOT NULL,
total DECIMAL(12,2)
) PARTITIONBY HASH (tenant_id);
CREATE TABLE orders_p0 PARTITIONOF orders FORVALUESWITH (modulus 4, remainder 0);
CREATE TABLE orders_p1 PARTITIONOF orders FORVALUESWITH (modulus 4, remainder 1);
CREATE TABLE orders_p2 PARTITIONOF orders FORVALUESWITH (modulus 4, remainder 2);
CREATE TABLE orders_p3 PARTITIONOF orders FORVALUESWITH (modulus 4, remainder 3);
Partition pruning: PostgreSQL automatically skips irrelevant partitions when the partition key appears in WHERE. Always include the partition key in queries.
Index on partitioned tables: Indexes created on the parent table are automatically created on all child partitions.
Step 9: JSONB Patterns at Scale
-- Generated columns promote hot JSONB fields to indexed native columnsCREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
data JSONB NOT NULL,
-- Promote frequently filtered fields to B-tree indexed generated columns
country TEXT GENERATED ALWAYS AS (data->>'country') STORED,
signup_date DATE GENERATED ALWAYS AS ((data->>'signup_date')::DATE) STORED
);
CREATE INDEX idx_customers_country ON customers (country);
CREATE INDEX idx_customers_signup ON customers (signup_date);
-- GIN index for containment / key-existence queriesCREATE INDEX idx_customers_data_gin ON customers USING GIN (data);
-- Partial GIN index for large tables (index only active records)CREATE INDEX idx_customers_data_active ON customers
USING GIN (data) WHERE (data->>'status') ='active';
-- jsonpath query example (PG 17)SELECT*FROM customers
WHERE data @? '$.tags[*] ? (@ == "premium")';
Step 10: Connection Pooling
Use a connection pooler in front of PostgreSQL for all production deployments:
When to use workflow: For comprehensive database design including requirements analysis, schema design, query optimization, migration planning, and testing (multi-phase, multi-agent)
When to use skill directly: For quick schema reviews or single-agent database tasks
Iron Laws
NEVER make schema changes without versioned migrations that include both UP and DOWN scripts — manual DDL in production is not recoverable.
ALWAYS normalize to at least 3NF before considering denormalization — never prematurely optimize without measured performance evidence.
ALWAYS plan indexes based on actual query patterns from EXPLAIN ANALYZE — never add indexes speculatively before profiling real workloads.
NEVER test or deploy a migration without running it against production-like data first — schema issues surface under realistic volume, not on empty tables.
ALWAYS use connection pooling (Supavisor or PgBouncer) in production — direct connections from serverless functions exhaust the database connection limit under load.
Anti-Patterns
Anti-Pattern
Why It Fails
Correct Approach
Manual DDL directly on production
No rollback path; breaks migration history
Always use versioned migrations with DOWN scripts
Premature denormalization
Adds complexity before profiling; often no measurable gain
Normalize first, denormalize only after EXPLAIN ANALYZE reveals bottleneck
