Version history: PostgreSQL
v1
scan: pass2026-08-11 — Ingested from sourceInitial version.
v2
currentscan: pass2026-11-02 — Currency pass 2026-11-01: globs **/* -> **/*.sql; formatting opinions condensed into one section stated as preferences; stray backticks removed from headings; added indexing (concurrently, partial), migration safety (lock_timeout, not valid/validate, batched backfills), CTE inlining since PG12, row level security and uuid guidance; fixed a mismatched example.---- description: This guide defines the definitive best practices for writing clean, performant, and maintainable PostgreSQL SQL, focusing on modern conventions and avoiding common pitfalls. - globs: **/*+ description: PostgreSQL SQL guidelines with BAD/GOOD examples for identity columns, data types, timestamptz, NOT IN and BETWEEN pitfalls, explicit joins, indexing, migrations, least-privilege roles, and transactional tests. + globs: "**/*.sql" + alwaysApply: false --- # 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.+ This document outlines best practices for PostgreSQL SQL development. Adherence ensures consistency, readability, performance, and maintainability across a codebase. The substantive rules (sections 2 to 7) matter more than the formatting preferences in section 1. - ## 1. Code Organization and Structure+ ## 1. Naming and Formatting - ### 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.+ Use `snake_case` for all database identifiers (tables, columns, functions, schemas). Never create quoted mixed-case identifiers: they must be quoted forever after. + The following are preferences, not correctness rules. Pick one style per project and enforce it with a formatter (for example `pg_format` or `sqlfluff`) rather than in code review: + + * Keyword case: either all-lowercase or all-uppercase keywords is fine; the examples here use lowercase. + * Put root keywords (`select`, `from`, `where`, `group by`) on their own line with their arguments indented. + * Use explicit `as` for column and table aliases. + * Use `--` for single-line comments and `/* ... */` for multi-line descriptions. + ❌ **BAD**: ```sql- CREATE TABLE UserData ( - UserID INT PRIMARY KEY, - UserName VARCHAR(255)+ CREATE TABLE "UserData" ( + "UserID" INT PRIMARY KEY, + "UserName" VARCHAR(255) );- - SELECT UserID, UserName FROM UserData; ``` ✅ **GOOD**: ```sql 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**: - ```sql - 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**: - ```sql - 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. - - ```sql - /* - 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.+ Always use explicit `inner join`, `left join`, `right join`, or `full join`. Never use implicit joins in the `from` clause. ❌ **BAD**: ```sql select u.user_name, o.order_date from users u, orders o where u.user_id = o.user_id; ``` ✅ **GOOD**: ```sql select u.user_name, o.order_date- from - users as u - inner join - orders as o - on u.user_id = o.user_id;+ from users as u + inner join orders as o on u.user_id = o.user_id; ``` ### 2.2. Common Table Expressions (CTEs)- Prefer CTEs (`WITH` clauses) over nested subqueries for improved readability and modularity.+ Prefer CTEs (`with` clauses) over nested subqueries for readability. Since PostgreSQL 12 a CTE that is referenced once is inlined into the main query, so it is no longer an optimization fence; add `materialized` only when you deliberately want the CTE evaluated once. ❌ **BAD**: ```sql- select - count(*)+ select count(*) from (- select - user_id - from - orders - where - order_date >= '2023-01-01' - group by - user_id - having - count(*) > 5+ select user_id + from orders + where order_date >= '2023-01-01' + group by user_id + having count(*) > 5 ) as frequent_buyers; ``` ✅ **GOOD**: ```sql with frequent_buyers as (- select - user_id - from - orders - where - order_date >= '2023-01-01' - group by - user_id - having - count(*) > 5+ select user_id + from orders + where order_date >= '2023-01-01' + group by user_id + having count(*) > 5 )- select - count(*) - from - frequent_buyers;+ select count(*) + from frequent_buyers; ``` - ### 2.3. Avoid `NOT IN` - Never use `NOT IN` due to its problematic behavior with `NULL` values. Prefer `NOT EXISTS` or `LEFT JOIN ... IS NULL`.+ ### 2.3. Avoid `NOT IN` with Subqueries + Never use `not in (subquery)`: if the subquery returns a single `null`, the whole predicate is unknown and the query returns no rows. Prefer `not exists` or `left join ... is null`. ❌ **BAD**: ```sql select user_name from users where user_id not in (select user_id from orders where status = 'cancelled'); ``` - ✅ **GOOD` (NOT EXISTS)`**:+ ✅ **GOOD (NOT EXISTS)**: ```sql- select - user_name - from - users as u+ select u.user_name + from users as u where not exists ( select 1 from orders as o where o.user_id = u.user_id and o.status = 'cancelled' ); ``` - ✅ **GOOD` (LEFT JOIN ... IS NULL)`**:+ ✅ **GOOD (LEFT JOIN ... IS NULL)**: ```sql- select - u.user_name - from - users as u - left join - orders as o - on u.user_id = o.user_id and o.status = 'cancelled' - where - o.user_id is null;+ select u.user_name + from users as u + left join orders as o on u.user_id = o.user_id and o.status = 'cancelled' + where o.user_id is null; ``` ## 3. Performance Considerations ### 3.1. Explicit Column Selection- Always specify columns explicitly. Avoid `SELECT *` in production code. This improves performance, reduces network traffic, and prevents issues when schema changes.+ Specify columns explicitly. Avoid `select *` in application code: it moves more data than needed, breaks when columns are added or reordered, and prevents index-only scans. ❌ **BAD**: ```sql select * from products; ``` ✅ **GOOD**: ```sql- select - product_id, - product_name, - price, - stock_quantity - from - products;+ select product_id, product_name, price, stock_quantity + from products; ``` - ### 3.2. Query Optimization - Use `EXPLAIN ANALYZE` to understand and optimize query plans. Focus on reducing sequential scans and improving index usage.+ ### 3.2. Indexing + * Index every foreign key column and every column used in frequent `where`, `join`, or `order by` clauses; PostgreSQL does not create foreign key indexes automatically. + * Build indexes on live tables with `create index concurrently` so writers are not blocked (it cannot run inside a transaction). + * Use partial indexes for predicates that select a small subset, and composite indexes with the equality columns first. + ✅ **GOOD**: ```sql- explain analyze - select - o.order_id, - c.customer_name - from - orders as o - inner join - customers as c - on o.customer_id = c.customer_id - where - o.order_date >= '2023-01-01' - order by - o.order_date desc+ create index concurrently if not exists orders_customer_id_idx on orders (customer_id); + create index concurrently if not exists orders_open_idx on orders (created_at) where status = 'open'; + ``` + + ### 3.3. Query Optimization + Use `explain (analyze, buffers)` to understand and optimize query plans. Focus on removing sequential scans on large tables, avoiding functions on indexed columns in predicates (`where date(created_at) = ...` cannot use an index on `created_at`), and keeping statistics current with `analyze`. + + ```sql + explain (analyze, buffers) + select o.order_id, c.customer_name + from orders as o + inner join customers as c on o.customer_id = c.customer_id + where o.order_date >= '2023-01-01' + order by o.order_date desc limit 100; ``` ## 4. Common Pitfalls and Gotchas ### 4.1. Date/Time Storage- Store all timestamps as `timestamp with time zone` (`timestamptz`) and always in UTC. Convert to local time zones only at the application layer for display.+ Store all timestamps as `timestamp with time zone` (`timestamptz`). PostgreSQL stores them as UTC instants and converts on input and output according to the session `TimeZone`; keep the application's session time zone set to `UTC` and convert for display at the application layer. Never use `timestamp without time zone` for instants in time. ❌ **BAD**: ```sql create table events ( event_id int, event_time timestamp without time zone -- Prone to timezone issues ); ``` ✅ **GOOD**: ```sql create table events ( event_id bigint generated always as identity primary key,- event_time timestamptz default now() -- Always store in UTC+ event_time timestamptz not null default now() ); ``` ### 4.2. `BETWEEN` with Timestamps- Avoid `BETWEEN` for date/time ranges, especially when precision matters. It includes both start and end points, which can lead to off-by-one errors. Use explicit `>=` and `<` operators.+ Avoid `between` for date/time ranges. It is inclusive on both ends, so `between '2023-01-01' and '2023-01-31'` silently drops everything after midnight on the 31st. Use a half-open range with `>=` and `<`. ❌ **BAD**: ```sql select * from orders where order_date between '2023-01-01' and '2023-01-31'; ``` ✅ **GOOD**: ```sql- select - * - from - orders - where - order_date >= '2023-01-01T00:00:00Z' - and order_date < '2023-02-01T00:00:00Z'; -- Correctly handles the entire month of January+ select order_id, order_date + from orders + where order_date >= '2023-01-01T00:00:00Z' + and order_date < '2023-02-01T00:00:00Z'; -- The whole of January, nothing more ``` ### 4.3. Data Type Choices- * **IDs**: Use `bigint generated always as identity` for primary keys. Never use `serial` or `bigserial`. - * **Text**: Prefer `text` over `varchar(n)` unless there's a specific, strict length constraint. Never use `char(n)`. - * **Money**: Never use the `money` type. Use `numeric` or `decimal` with explicit precision (e.g., `numeric(19, 4)`).+ * **IDs**: Use `bigint generated always as identity` for primary keys. Do not use `serial` or `bigserial` in new tables; identity columns are standard SQL, own their sequence, and reject accidental explicit inserts. Use `uuid` (with `gen_random_uuid()`) when ids are generated by clients or must not be guessable. + * **Text**: Prefer `text` over `varchar(n)` unless there is a real, enforced length limit; add a `check (length(...) <= n)` constraint if the limit may change. Never use `char(n)`. + * **Money**: Never use the `money` type. Use `numeric` with explicit precision (e.g., `numeric(19, 4)`). + * **Booleans and enums**: Use `boolean`, not `int` flags. Prefer a `text` column with a `check` constraint or a lookup table over `create type ... as enum` when the set of values will change. ❌ **BAD**: ```sql create table products ( id serial primary key, product_code char(10), description varchar(255), price money ); ``` ✅ **GOOD**: ```sql create table products ( product_id bigint generated always as identity primary key,- product_code varchar(10) not null, -- Use varchar for fixed-length codes+ product_code text not null check (length(product_code) <= 10), description text,- price numeric(19, 4) not null -- Explicit precision for currency+ price numeric(19, 4) not null ); ``` ### 4.4. Avoid Legacy Constructs- Never use PostgreSQL `rules` or `table inheritance`. Use `triggers` for event-driven logic and native `table partitioning` for large tables.+ Never use PostgreSQL `rules` or table inheritance. Use triggers for event-driven logic and declarative partitioning (`partition by range ...`) for large tables. - ## 5. Security Best Practices+ ## 5. Migrations - ### 5.1. Prepared Statements - Always use prepared statements (e.g., via parameterized queries in your application code) to prevent SQL injection vulnerabilities.+ * Every schema change is a versioned migration file checked into the repository, applied in order, and never edited after it has run anywhere. Ship a reverse migration alongside it. + * Set `lock_timeout` (and a `statement_timeout`) in migration sessions so a DDL statement waiting on a lock fails fast instead of queueing behind a long query and blocking all traffic. + * Add columns as nullable or with a constant default (cheap since PostgreSQL 11); backfill in batches; then add the `not null` constraint. Adding a `not null` column with a volatile default rewrites the table. + * Add constraints as `not valid` first, then `validate constraint` separately, so the long validation scan does not hold an exclusive lock. - ❌ **BAD` (Application Code)`**:+ ✅ **GOOD**: + ```sql + set lock_timeout = '5s'; + alter table orders add column channel text; + -- backfill in batches from the application + alter table orders add constraint orders_channel_check check (channel in ('web', 'app', 'pos')) not valid; + alter table orders validate constraint orders_channel_check; + ``` + + ## 6. Security Best Practices + + ### 6.1. Prepared Statements + Always use parameterized queries in application code to prevent SQL injection. Never build SQL by string interpolation of user input. + + ❌ **BAD (application code)**: ```python cursor.execute(f"select * from users where user_name = '{user_input}';") ``` - ✅ **GOOD` (Application Code)`**:+ ✅ **GOOD (application code)**: ```python- cursor.execute("select * from users where user_name = %s;", (user_input,))+ cursor.execute("select user_id, user_name from users where user_name = %s;", (user_input,)) ``` - ### 5.2. Least Privilege - Grant database roles only the minimum necessary permissions. Avoid granting `ALL PRIVILEGES`.+ ### 6.2. Least Privilege + Grant database roles only the minimum necessary permissions. Applications never connect as a superuser or the schema owner; create separate `nologin` roles for reading and writing and grant them to the login roles. ```sql- -- Create a read-only role+ -- Read-only role create role app_reader nologin; grant connect on database my_app_db to app_reader; grant usage on schema public to app_reader; grant select on all tables in schema public to app_reader; alter default privileges in schema public grant select on tables to app_reader; - -- Create a read-write role+ -- Read-write role create role app_writer nologin; grant app_reader to app_writer; -- Inherit read permissions grant insert, update, delete on all tables in schema public to app_writer; alter default privileges in schema public grant insert, update, delete on tables to app_writer; ``` - ## 6. Data Modeling+ Use `row level security` when different tenants share a table, and keep migration privileges on a separate role that the application does not use. - ### 6.1. Foreign Key Naming - Name foreign key columns as `<referenced_table>_id` (singular form of the referenced table name).+ ## 7. Data Modeling + ### 7.1. Foreign Key Naming + Name foreign key columns `<referenced_table_singular>_id` and declare the constraint explicitly with a predictable name and an `on delete` action. + ❌ **BAD**: ```sql create table orders ( order_id bigint generated always as identity primary key,- customerid bigint not null -- Inconsistent naming+ customerid bigint not null -- Inconsistent naming, no constraint ); ``` ✅ **GOOD**: ```sql create table orders ( order_id bigint generated always as identity primary key,- customer_id bigint not null, -- Correct foreign key naming - constraint fk_customer foreign key (customer_id) references customers (customer_id)+ customer_id bigint not null, + constraint orders_customer_id_fkey foreign key (customer_id) references customers (customer_id) on delete restrict ); ``` - ### 6.2. Table Comments - Always add descriptive comments to tables and columns using `COMMENT ON`. This is crucial for documentation and understanding schema intent.+ ### 7.2. Table Comments + Add descriptive comments to tables and non-obvious columns using `comment on`. They show up in `\d+` and in schema browsers. ```sql- comment on table users is 'Stores information about application users.'; - comment on column users.user_name is 'Full name of the user.';+ comment on table users is 'Application users; one row per login.'; + comment on column users.user_name is 'Display name shown in the UI; not unique.'; ``` - ## 7. Testing Approaches+ ## 8. Testing Approaches - ### 7.1. Transactional Tests - Wrap database tests in transactions that are rolled back at the end. This ensures a clean state for each test run.+ ### 8.1. Transactional Tests + Wrap database tests in a transaction that is rolled back at the end, so each test starts from a clean state and tests can run in parallel against the same database. ```sql- begin; -- Start transaction - -- Insert test data+ begin; insert into users (user_name) values ('Test User');+ select count(*) from users where user_name = 'Test User'; -- Expect 1 + rollback; + ``` - -- Run assertions - select count(*) from users where user_name = 'Test User'; -- Should be 1 - - rollback; -- Rollback all changes - ```+ Run tests against the same major PostgreSQL version as production; behaviour of planner, collations, and extensions differs between versions.