Raw view: PostgreSQL

No invisible, zero-width, or bidi-control characters detected.

---
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 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. Naming and Formatting

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)
);
```

✅ **GOOD**:
```sql
create table user_data (
    user_id bigint generated always as identity primary key,
    user_name text not null
);
```

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

❌ **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;
```

### 2.2. Common Table Expressions (CTEs)
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(*)
from (
    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 count(*)
from frequent_buyers;
```

### 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)**:
```sql
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)**:
```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;
```

## 3. Performance Considerations

### 3.1. Explicit Column Selection
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;
```

### 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
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`). 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 not null default now()
);
```

### 4.2. `BETWEEN` with Timestamps
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 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. 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 text not null check (length(product_code) <= 10),
    description text,
    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 declarative partitioning (`partition by range ...`) for large tables.

## 5. Migrations

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

✅ **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)**:
```python
cursor.execute("select user_id, user_name from users where user_name = %s;", (user_input,))
```

### 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
-- 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;

-- 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;
```

Use `row level security` when different tenants share a table, and keep migration privileges on a separate role that the application does not use.

## 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, no constraint
);
```

✅ **GOOD**:
```sql
create table orders (
    order_id bigint generated always as identity primary key,
    customer_id bigint not null,
    constraint orders_customer_id_fkey foreign key (customer_id) references customers (customer_id) on delete restrict
);
```

### 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 'Application users; one row per login.';
comment on column users.user_name is 'Display name shown in the UI; not unique.';
```

## 8. Testing Approaches

### 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;
    insert into users (user_name) values ('Test User');
    select count(*) from users where user_name = 'Test User'; -- Expect 1
rollback;
```

Run tests against the same major PostgreSQL version as production; behaviour of planner, collations, and extensions differs between versions.