470-postgresql

PostgreSQL Engineering Ruleset

Audience: engineers designing and reviewing PostgreSQL database schemas and migrations Goal: Consistent database schema design with clear naming conventions and maintainable patterns

PostgreSQL Philosophy (Core Principles)

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

Applying PostgreSQL Principles:

-- BAD: Inconsistent naming, no constraints, no indexes
CREATE TABLE User (
    userId SERIAL PRIMARY KEY,
    Email VARCHAR(255),
    firstName VARCHAR(100),
    lastName VARCHAR(100)
);

-- GOOD: Consistent naming, constraints, indexes
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),
    CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

Naming Conventions

Tables

Tables use plural snake_case:

--  GOOD: Plural snake_case
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE user_books (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    book_id BIGINT NOT NULL REFERENCES books(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, book_id)
);

--  BAD: Singular or camelCase
CREATE TABLE User ( ... );
CREATE TABLE userBooks ( ... );