postgresql

PostgreSQL Best Practices

This document outlines the mandatory best practices for all PostgreSQL SQL development. Adherence ensures consistency, readability, performance, and maintainability across our codebase.

1. Code Organization and Structure

1.1. Naming Conventions

Always use snake_case for all database identifiers (tables, columns, functions, schemas). SQL keywords must be lowercase. Keep names descriptive but concise.

❌ BAD:

CREATE TABLE UserData (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(255)
);

SELECT UserID, UserName FROM UserData;

✅ GOOD:

create table user_data (
    user_id bigint generated always as identity primary key,
    user_name text not null
);

select user_id, user_name from user_data;

1.2. Formatting

Structure queries for maximum readability. Root keywords (SELECT, FROM, WHERE) belong on their own line, with arguments indented. Use explicit AS for all aliases.

❌ BAD:

SELECT t.client_id, DATE(t.created_at) day FROM telemetry t, users u WHERE t.user_id = u.id AND t.submission_date > '2019-07-01' GROUP BY 1, 2;

✅ GOOD:

select
    t.client_id as client_id,
    date(t.created_at) as day
from
    telemetry as t
inner join
    users as u
    on t.user_id = u.id
where
    t.submission_date > '2019-07-01'
    and t.sample_id = '10'
group by
    t.client_id,
    day;

1.3. Comments

Use block comments (/* ... */) for multi-line descriptions and line comments (--) for single-line notes.

/*
This query retrieves active users and their recent orders.
It joins the users table with the orders table.
*/
select
    u.user_id, -- Unique user identifier
    u.user_name,
    o.order_id,
    o.order_date
from
    users as u
inner join
    orders as o
    on u.user_id = o.user_id;

2. Common Patterns and Anti-patterns

2.1. Explicit JOINs

Always use explicit INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN. Never use implicit joins in the FROM clause.