Data Management
Workflows
-
Schema Design: Define tables, relationships, constraints
-
Migrations: Version control schema changes
-
Indexing: Add indexes for query performance
-
Backup: Ensure data recovery capability
Schema Design Principles
Normalization
-
1NF: Atomic values, no repeating groups
-
2NF: No partial dependencies
-
3NF: No transitive dependencies
When to Denormalize
-
Read-heavy workloads
-
Reporting/analytics
-
Caching layers
Migration Best Practices
Forward-Only Migrations
Each migration should be a single forward step.
-- migrations/001_create_users.sql CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() );
CREATE INDEX idx_users_email ON users(email);
Safe Migrations
-
Add columns as nullable first
-
Create indexes concurrently
-
Never drop columns in the same deploy
Indexing Strategy
-- B-tree (default): Equality and range queries CREATE INDEX idx_users_email ON users(email);
-- Partial index: When you query a subset CREATE INDEX idx_active_users ON users(id) WHERE active = true;
-- Composite index: Multiple columns CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
Connection Management
// Use connection pooling const pool = new Pool({ max: 20, // Max connections idleTimeoutMillis: 30000, // Close idle connections connectionTimeoutMillis: 2000 });
Data Integrity
-
Use foreign key constraints
-
Add NOT NULL where appropriate
-
Use CHECK constraints for validation
-
Consider using ENUM types for fixed values