Version history: PostgreSQL

v1

scan: pass2026-08-11 — Ingested from source

Initial 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.