database-postgresql

PostgreSQL Database Engineering

Core Principles

• "Consistency is king" - Follow naming conventions consistently across all tables and columns
• "Explicit over implicit" - Use explicit constraints, types, and defaults
• "Performance by design" - Index foreign keys and frequently queried columns
• "Security by default" - Use parameterized queries, RLS, least privilege
• "Migrations are code" - Version control migrations, make them reversible
• "Documentation matters" - Comments explain why, not just what
• "Test your migrations" - Test migrations in staging before production
• "Backup before changes" - Always backup before destructive operations

Quick Reference

Naming Conventions

• Tables: Plural snake_case (users, order_items)
• Columns: Singular snake_case (first_name, email)
• Primary Keys: Always named id (BIGSERIAL)
• Foreign Keys: table_name_id pattern (user_id, product_id)
• Indexes: idx_table_column pattern (idx_users_email)
• Constraints: Named with descriptive pattern (users_email_unique)

Essential Patterns

Timestamps:

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL

Soft Deletes:

deleted_at TIMESTAMP WITH TIME ZONE NULL
CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;

Enums:

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');
status user_status DEFAULT 'active' NOT NULL

Foreign Keys:

user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT

Common Patterns

Basic Table Structure

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    
    CONSTRAINT users_email_unique UNIQUE (email)
);

CREATE INDEX idx_users_email ON users(email);