SQL Style Guide

General Rules

• You MUST use snake_case for all identifiers
• You SHOULD avoid prefixes like tbl_, vw_, sp_
• You MUST keep names under 63 characters (PostgreSQL limit)
• You MUST NOT use SQL reserved keywords

Tables

Table names MUST be plural nouns in snake_case.

-- Plural nouns
CREATE TABLE users (...);
CREATE TABLE order_items (...);
CREATE TABLE user_preferences (...);

-- NOT
CREATE TABLE user (...);
CREATE TABLE tbl_users (...);

Columns

Column names MUST use descriptive snake_case and SHOULD avoid ambiguous single-word names.

-- Descriptive snake_case
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email_address TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true,
    year_founded INTEGER  -- NOT just "year"
);

Foreign Keys

Foreign key columns MUST follow the format: referenced_table_singular_id.

-- Format: referenced_table_singular_id
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),  -- NOT users_id
    shipping_address_id BIGINT REFERENCES addresses(id)
);

Indexes

Index names MUST follow the format: idx_table_column(s) with optional _unique suffix.

-- Format: idx_table_column(s)
CREATE INDEX idx_users_email ON users(email_address);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email_address);

Constraints

Constraint names MUST follow these formats:

• Primary keys: table_pkey
• Unique: table_column_key
• Foreign keys: table_column_fkey
• Check: table_column_check

ALTER TABLE users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id),
    ADD CONSTRAINT users_email_key UNIQUE (email_address),
    ADD CONSTRAINT users_age_check CHECK (age >= 0);

ALTER TABLE orders
    ADD CONSTRAINT orders_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES users(id);

Query Formatting

Queries MUST use consistent formatting with keywords in UPPERCASE and identifiers in lowercase. SQL clauses SHOULD be on separate lines for readability.

Prefer

You SHOULD prefer these data types:

-- Text
TEXT                    -- Variable length, no artificial limit
VARCHAR(255)            -- Only when limit is meaningful

-- Numbers
BIGINT                  -- IDs, counts
INTEGER                 -- Regular integers
NUMERIC(10,2)          -- Money, precise decimals
REAL, DOUBLE PRECISION -- Scientific, approximate

-- Dates
TIMESTAMP WITH TIME ZONE  -- Always use timezone-aware (MUST for timestamps)
DATE                      -- Date only
TIME WITH TIME ZONE       -- Time only

-- Other
UUID                    -- For distributed IDs
BOOLEAN                 -- MUST use for boolean values, NOT integers
JSONB                   -- JSON data (MUST use JSONB, NOT JSON)

Avoid

You SHOULD NOT use these deprecated or problematic types:

-- CHAR(n)       -- Pads with spaces, no benefit
-- MONEY         -- PostgreSQL-specific, imprecise
-- SERIAL        -- Use IDENTITY instead
-- JSON          -- Use JSONB

Naming

Migration files MUST follow the timestamp-based naming convention:

YYYYMMDDHHMMSS_descriptive_name.sql
20240115143022_create_users_table.sql
20240115143523_add_email_index_to_users.sql

Safe Patterns

No migration is safe on its own. Every migration MUST set a short lock_timeout before any statement that takes an ACCESS EXCLUSIVE lock, and the migration runner MUST retry a migration that the lock timeout cancels. Migrations SHOULD also set a statement_timeout sized for the table being changed. Every statement MUST be re-runnable, either inside an explicit transaction or guarded by IF NOT EXISTS.⁸

-- Do: bound the lock wait, bound the work, keep the statements re-runnable.
SET lock_timeout = '3s';
SET statement_timeout = '30s';

BEGIN;
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone_number TEXT;
-- A constant default is a catalogue change; existing rows are not rewritten.
ALTER TABLE users ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'active';
COMMIT;

-- Don't: unbounded lock wait, and a partial failure cannot be rerun.
ALTER TABLE users ADD COLUMN phone_number TEXT;

Adding a Required Column

When the value cannot come from a constant default, you MUST split the change so that no table scan runs under an ACCESS EXCLUSIVE lock: add the column nullable, backfill in batches, then add the constraint NOT VALID and validate it separately.

-- Step 1: add the column nullable, so no row is rewritten.
SET lock_timeout = '3s';
SET statement_timeout = '30s';
ALTER TABLE users ADD COLUMN IF NOT EXISTS signup_source TEXT;

-- Step 2: backfill in bounded batches, committing each batch, and repeat
-- until the statement reports zero rows.
UPDATE users SET signup_source = 'import'
WHERE id IN (
    SELECT id FROM users WHERE signup_source IS NULL ORDER BY id LIMIT 1000
);

-- Step 3: record the constraint without scanning the table.
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_signup_source_not_null;
ALTER TABLE users ADD CONSTRAINT users_signup_source_not_null
    CHECK (signup_source IS NOT NULL) NOT VALID;

-- Step 4: validate under SHARE UPDATE EXCLUSIVE, which does not block writes.
SET statement_timeout = '10min';
ALTER TABLE users VALIDATE CONSTRAINT users_signup_source_not_null;

BEGIN;
ALTER TABLE users ALTER COLUMN signup_source SET NOT NULL;
COMMIT;

Creating Indexes on Live Tables

Indexes on tables that carry production traffic MUST be created with CREATE INDEX CONCURRENTLY, and that statement MUST NOT run inside a transaction block. A concurrent build is not lock-free: it takes a SHARE UPDATE EXCLUSIVE lock, scans the table twice, waits for existing transactions to finish, and takes significantly longer than an ordinary build.¹⁰ It permits reads and writes, but blocks schema changes and any other concurrent build on the same table.

-- Do: outside any transaction, with a bounded lock wait and an unbounded
-- build, because cancelling a concurrent build leaves an invalid index.
SET lock_timeout = '3s';
SET statement_timeout = 0;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_phone_number
    ON users (phone_number);

A failed concurrent build leaves an INVALID index that keeps its name, is ignored by queries, and still costs write overhead.¹⁰ IF NOT EXISTS matches that name, so a rerun reports success while the index remains unusable. After any failed build you MUST check the catalog and drop the invalid index before rebuilding.

SELECT c.relname AS index_name
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

-- Recovery: drop the invalid index, then rerun the concurrent build.
SET lock_timeout = '3s';
SET statement_timeout = '30s';
DROP INDEX CONCURRENTLY IF EXISTS idx_users_phone_number;

An ordinary CREATE INDEX SHOULD be used where no production traffic can reach the table yet, such as a table created in the same migration: it is faster and runs inside the transaction. Concurrent builds are not supported on partitioned parents; build the index on each partition concurrently, then create the parent index non-concurrently as a metadata-only operation.¹⁰

Renaming Columns

Renaming a column is not a safe pattern. The rename takes effect for every session at commit, so any deployed code still using the old name fails immediately. You MUST expand and contract instead: add the new column, write both columns, backfill, move reads across, and drop the old column in a later migration once no deployed code refers to it.

-- Don't: every client using the old name breaks the moment this commits.
ALTER TABLE users RENAME COLUMN phone_number TO phone;

Squawk (PostgreSQL Migration Linter)

Squawk’s PyPI distribution is squawk-cli; the distribution named squawk is an unrelated project. You MUST install squawk-cli and MUST pin the version so local runs and CI apply the same rules.

# Install (the distribution is squawk-cli, the command is squawk)
pip install squawk-cli==2.64.0

# Lint migrations
squawk migrations/*.sql

Squawk¹¹ catches unsafe migration patterns specific to PostgreSQL. It exits non-zero when a migration is unsafe, so it MUST run in CI as well as locally.

Comments

You SHOULD add comments to tables and columns to document their purpose. Comments MUST be updated when schema changes.

-- Table comments
COMMENT ON TABLE users IS 'Registered user accounts';

-- Column comments
COMMENT ON COLUMN users.email_address IS 'Primary email, used for login';

-- Update comments when schema changes!

Entity-Attribute-Value (EAV) Tables

You MUST NOT use EAV tables for structured data. EAV destroys type safety, prevents foreign keys, and makes queries complex.

-- BAD: EAV pattern - loses type safety, can't use foreign keys
CREATE TABLE product_attributes (
    product_id BIGINT,
    attribute_name TEXT,    -- "price", "weight", "color"
    attribute_value TEXT    -- Everything is a string!
);

-- Querying EAV is painful
SELECT
    p.name,
    MAX(CASE WHEN a.attribute_name = 'price' THEN a.attribute_value END) AS price,
    MAX(CASE WHEN a.attribute_name = 'color' THEN a.attribute_value END) AS color
FROM products AS p
LEFT JOIN product_attributes AS a ON p.id = a.product_id
GROUP BY p.id, p.name;

-- GOOD: Use proper columns with real types
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,  -- Real type!
    weight_kg NUMERIC(8,3),
    color TEXT
);

-- Use JSONB for truly dynamic attributes
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    custom_attributes JSONB DEFAULT '{}'  -- For genuinely variable data
);

Time-Partitioned Table Names

You MUST NOT encode time periods in table names. This creates maintenance burden and breaks queries when periods change.

-- BAD: Table names encode time
CREATE TABLE events_2024_01 (...);
CREATE TABLE events_2024_02 (...);
-- Now every query needs UNION ALL across all tables
-- Adding a new month requires schema changes

-- GOOD: Use database-native partitioning
CREATE TABLE events (
    id BIGINT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    event_type TEXT,
    payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Queries work on the parent table automatically
SELECT * FROM events WHERE created_at >= '2024-02-15';

Separate Value and Unit Columns

You SHOULD NOT store numeric values separately from their units. This leads to unit mismatch bugs.

-- BAD: Unit stored separately - easy to mix up
CREATE TABLE measurements (
    id BIGINT PRIMARY KEY,
    value NUMERIC,
    unit TEXT  -- "kg", "lb", "g" - which is it?
);

-- GOOD: Unit in column name, single canonical unit
CREATE TABLE measurements (
    id BIGINT PRIMARY KEY,
    weight_kg NUMERIC(10,3),  -- Always kilograms
    distance_m NUMERIC(12,2)  -- Always meters
);

-- GOOD: For multi-unit storage, use composite type or normalize on write
CREATE TABLE shipments (
    id BIGINT PRIMARY KEY,
    weight_kg NUMERIC(10,3) NOT NULL,  -- Canonical unit
    weight_display_unit TEXT DEFAULT 'kg'  -- For UI only
);