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