PostgreSQL Best Practices
Comprehensive PostgreSQL reference consolidating performance optimization (Supabase), common patterns, and schema design guidance. Covers 8 rule categories prioritized by impact, plus detailed data type and table design reference.
When to Use
Reference these guidelines when:
- Writing SQL queries, migrations, or designing schemas
- Implementing indexes or query optimization
- Reviewing database performance issues
- Configuring connection pooling or scaling
- Optimizing for Postgres-specific features
- Working with Row-Level Security (RLS)
- Choosing data types, constraints, or table structures
- Troubleshooting slow queries
Rule Categories by Priority
| Priority |
Category |
Impact |
Prefix |
| 1 |
Query Performance |
CRITICAL |
query- |
| 2 |
Connection Management |
CRITICAL |
conn- |
| 3 |
Security & RLS |
CRITICAL |
security- |
| 4 |
Schema Design |
HIGH |
schema- |
| 5 |
Concurrency & Locking |
MEDIUM-HIGH |
lock- |
| 6 |
Data Access Patterns |
MEDIUM |
data- |
| 7 |
Monitoring & Diagnostics |
LOW-MEDIUM |
monitor- |
| 8 |
Advanced Features |
LOW |
advanced- |
Detailed Rule Files
Read individual rule files for detailed explanations and SQL examples:
rules/query-missing-indexes.md
rules/schema-partial-indexes.md
rules/_sections.md
Each rule file contains:
- Brief explanation of why it matters
- Incorrect SQL example with explanation
- Correct SQL example with explanation
- Optional EXPLAIN output or metrics
- Additional context and references
- Supabase-specific notes (when applicable)
For the complete compiled guide with all rules expanded: AGENTS.md
Schema Design: Core Rules
- Define a PRIMARY KEY for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer
BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.
- Normalize first (to 3NF) to eliminate data redundancy and update anomalies; denormalize only for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden.
- Add NOT NULL everywhere it's semantically required; use DEFAULTs for common values.
- Create indexes for access paths you actually query: PK/unique (auto), FK columns (manual!), frequent filters/sorts, and join keys.
- Prefer TIMESTAMPTZ for event time; NUMERIC for money; TEXT for strings; BIGINT for integer values, DOUBLE PRECISION for floats (or
NUMERIC for exact decimal arithmetic).
PostgreSQL "Gotchas"
- Identifiers: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use
snake_case for table/column names.
- Unique + NULLs: UNIQUE allows multiple NULLs. Use
UNIQUE (...) NULLS NOT DISTINCT (PG15+) to restrict to one NULL.
- FK indexes: PostgreSQL does not auto-index FK columns. Add them.
- No silent coercions: length/precision overflows error out (no truncation). Example: inserting 999 into
NUMERIC(2,0) fails with error, unlike some databases that silently truncate or round.
- Sequences/identity have gaps (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.
- Heap storage: no clustered PK by default (unlike SQL Server/MySQL InnoDB);
CLUSTER is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.
- MVCC: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.
Data Types
- IDs:
BIGINT GENERATED ALWAYS AS IDENTITY preferred (GENERATED BY DEFAULT also fine); UUID when merging/federating/used in a distributed system or for opaque IDs. Generate with uuidv7() (preferred if using PG18+) or gen_random_uuid() (if using an older PG version).
- Integers: prefer
BIGINT unless storage space is critical; INTEGER for smaller ranges; avoid SMALLINT unless constrained.
- Floats: prefer
DOUBLE PRECISION over REAL unless storage space is critical. Use NUMERIC for exact decimal arithmetic.
- Strings: prefer
TEXT; if length limits needed, use CHECK (LENGTH(col) <= n) instead of VARCHAR(n); avoid CHAR(n). Use BYTEA for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: PLAIN (no TOAST), EXTENDED (compress + out-of-line), EXTERNAL (out-of-line, no compress), MAIN (compress, keep in-line if possible). Default EXTENDED usually optimal. Control with ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy and ALTER TABLE tbl SET (toast_tuple_target = 4096) for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on LOWER(col) (preferred unless column needs case-insensitive PK/FK/UNIQUE) or CITEXT.
- Money:
NUMERIC(p,s) (never float).
- Time:
TIMESTAMPTZ for timestamps; DATE for date-only; INTERVAL for durations. Avoid TIMESTAMP (without timezone). Use now() for transaction start time, clock_timestamp() for current wall-clock time.
- Booleans:
BOOLEAN with NOT NULL constraint unless tri-state values are required.
- Enums:
CREATE TYPE ... AS ENUM for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table.
- Arrays:
TEXT[], INTEGER[], etc. Use for ordered lists where you query elements. Index with GIN for containment (@>, <@) and overlap (&&) queries. Access: arr[1] (1-indexed), arr[1:3] (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: '{val1,val2}' or ARRAY[val1,val2].
- Range types:
daterange, numrange, tstzrange for intervals. Support overlap (&&), containment (@>), operators. Index with GiST. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer [) (inclusive/exclusive) by default.
- Network types:
INET for IP addresses, CIDR for network ranges, MACADDR for MAC addresses. Support network operators (<<, >>, &&).
- Geometric types:
POINT, LINE, POLYGON, CIRCLE for 2D spatial data. Index with GiST. Consider PostGIS for advanced spatial features.
- Text search:
TSVECTOR for full-text search documents, TSQUERY for search queries. Index tsvector with GIN. Always specify language: to_tsvector('english', col) and to_tsquery('english', 'query'). Never use single-argument versions. This applies to both index expressions and queries.
- Domain types:
CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') for reusable custom types with validation. Enforces constraints across tables.
- Composite types:
CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) for structured data within columns. Access with (col).field syntax.
- JSONB: preferred over JSON; index with GIN. Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved.
- Vector types:
vector type by pgvector for vector similarity search for embeddings.
Do Not Use These Data Types
- DO NOT use
timestamp (without time zone); DO use timestamptz instead.
- DO NOT use
char(n) or varchar(n); DO use text instead.
- DO NOT use
money type; DO use numeric instead.
- DO NOT use
timetz type; DO use timestamptz instead.
- DO NOT use
timestamptz(0) or any other precision specification; DO use timestamptz instead.
- DO NOT use
serial type; DO use generated always as identity instead.
Data Type Quick Reference
| Use Case |
Correct Type |
Avoid |
| IDs |
bigint |
int, random UUID |
| Strings |
text |
varchar(255) |
| Timestamps |
timestamptz |
timestamp |
| Money |
numeric(10,2) |
float |
| Flags |
boolean |
varchar, int |
Table Types
- Regular: default; fully durable, logged.
- TEMPORARY: session-scoped, auto-dropped, not logged. Faster for scratch work.
- UNLOGGED: persistent but not crash-safe. Faster writes; good for caches/staging.
Constraints
- PK: implicit UNIQUE + NOT NULL; creates a B-tree index.
- FK: specify
ON DELETE/UPDATE action (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use DEFERRABLE INITIALLY DEFERRED for circular FK dependencies checked at transaction end.
- UNIQUE: creates a B-tree index; allows multiple NULLs unless
NULLS NOT DISTINCT (PG15+). Standard behavior: (1, NULL) and (1, NULL) are allowed. With NULLS NOT DISTINCT: only one (1, NULL) allowed. Prefer NULLS NOT DISTINCT unless you specifically need duplicate NULLs.
- CHECK: row-local constraints; NULL values pass the check (three-valued logic). Example:
CHECK (price > 0) allows NULL prices. Combine with NOT NULL to enforce: price NUMERIC NOT NULL CHECK (price > 0).
- EXCLUDE: prevents overlapping values using operators.
EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) prevents double-booking rooms. Requires appropriate index type (often GiST).
Indexing
Index Type Reference
| Query Pattern |
Index Type |
Example |
WHERE col = value |
B-tree (default) |
CREATE INDEX idx ON t (col) |
WHERE col > value |
B-tree |
CREATE INDEX idx ON t (col) |
WHERE a = x AND b > y |
Composite |
CREATE INDEX idx ON t (a, b) |
WHERE jsonb @> '{}' |
GIN |
CREATE INDEX idx ON t USING gin (col) |
WHERE tsv @@ query |
GIN |
CREATE INDEX idx ON t USING gin (col) |
| Time-series ranges |
BRIN |
CREATE INDEX idx ON t USING brin (col) |
Index Types Explained
- B-tree: default for equality/range queries (
=, <, >, BETWEEN, ORDER BY)
- Composite: order matters—index used if equality on leftmost prefix (
WHERE a = ? AND b > ? uses index on (a,b), but WHERE b = ? does not). Put most selective/frequently filtered columns first.
- Covering:
CREATE INDEX ON tbl (id) INCLUDE (name, email) - includes non-key columns for index-only scans without visiting table.
- Partial: for hot subsets (
WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'). Any query with status = 'active' can use this index.
- Expression: for computed search keys (
CREATE INDEX ON tbl (LOWER(email))). Expression must match exactly in WHERE clause: WHERE LOWER(email) = 'user@example.com'.
- GIN: JSONB containment/existence, arrays (
@>, ?), full-text search (@@)
- GiST: ranges, geometry, exclusion constraints
- BRIN: very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after
CLUSTER).
Common Index Patterns
Composite Index Order:
-- Equality columns first, then range columns
CREATE INDEX idx ON orders (status, created_at);
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
Covering Index:
CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- Avoids table lookup for SELECT email, name, created_at
Partial Index:
CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- Smaller index, only includes active users
Row-Level Security
Enable with ALTER TABLE tbl ENABLE ROW LEVEL SECURITY. Create policies: CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id()). Built-in user-based access control at the row level.
Optimized RLS Policy:
CREATE POLICY policy ON orders
USING ((SELECT auth.uid()) = user_id); -- Wrap in SELECT!
Partitioning
- Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date).
- Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically
- RANGE: common for time-series (
PARTITION BY RANGE (created_at)). Create partitions: CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'). TimescaleDB automates time-based or ID-based partitioning with retention policies and compression.
- LIST: for discrete values (
PARTITION BY LIST (region)). Example: FOR VALUES IN ('us-east', 'us-west').
- HASH: for even distribution when no natural key (
PARTITION BY HASH (user_id)). Creates N partitions with modulus.
- Constraint exclusion: requires
CHECK constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).
- Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
- Limitations: no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.
Special Considerations
Update-Heavy Tables
- Separate hot/cold columns—put frequently updated columns in separate table to minimize bloat.
- Use
fillfactor=90 to leave space for HOT updates that avoid index maintenance.
- Avoid updating indexed columns—prevents beneficial HOT updates.
- Partition by update patterns—separate frequently updated rows in a different partition from stable data.
Insert-Heavy Workloads
- Minimize indexes—only create what you query; every index slows inserts.
- Use
COPY or multi-row INSERT instead of single-row inserts.
- UNLOGGED tables for rebuildable staging data—much faster writes.
- Defer index creation for bulk loads—>drop index, load data, recreate indexes.
- Partition by time/hash to distribute load. TimescaleDB automates partitioning and compression of insert-heavy data.
- Use a natural key for primary key such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all.
- If you do need a surrogate key, Prefer
BIGINT GENERATED ALWAYS AS IDENTITY over UUID.
Upsert-Friendly Design
- Requires UNIQUE index on conflict target columns—
ON CONFLICT (col1, col2) needs exact matching unique index (partial indexes don't work).
- Use
EXCLUDED.column to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.
DO NOTHING faster than DO UPDATE when no actual update needed.
Safe Schema Evolution
- Transactional DDL: most DDL operations can run in transactions and be rolled back—
BEGIN; ALTER TABLE...; ROLLBACK; for safe testing.
- Concurrent index creation:
CREATE INDEX CONCURRENTLY avoids blocking writes but can't run in transactions.
- Volatile defaults cause rewrites: adding
NOT NULL columns with volatile defaults (e.g., now(), gen_random_uuid()) rewrites entire table. Non-volatile defaults are fast.
- Drop constraints before columns:
ALTER TABLE DROP CONSTRAINT then DROP COLUMN to avoid dependency issues.
- Function signature changes:
CREATE OR REPLACE with different arguments creates overloads, not replacements. DROP old version if no overload desired.
Generated Columns
... GENERATED ALWAYS AS (<expr>) STORED for computed, indexable fields. PG18+ adds VIRTUAL columns (computed on read, not stored).
JSONB Guidance
- Prefer
JSONB with GIN index.
- Default:
CREATE INDEX ON tbl USING GIN (jsonb_col); → accelerates:
- Containment
jsonb_col @> '{"k":"v"}'
- Key existence
jsonb_col ? 'k', any/all keys ?\|, ?&
- Path containment on nested docs
- Disjunction
jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])
- Heavy
@> workloads: consider opclass jsonb_path_ops for smaller/faster containment-only indexes:
CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
- Trade-off: loses support for key existence (
?, ?|, ?&) queries—only supports containment (@>)
- Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression):
ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;
CREATE INDEX ON tbl (price);
- Prefer queries like
WHERE price BETWEEN 100 AND 500 (uses B-tree) over WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500 without index.
- Arrays inside JSONB: use GIN +
@> for containment (e.g., tags). Consider jsonb_path_ops if only doing containment.
- Keep core relations in tables; use JSONB for optional/variable attributes.
- Use constraints to limit allowed JSONB values in a column e.g.
config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')
Common Patterns
UPSERT:
INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;
Cursor Pagination:
SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) vs OFFSET which is O(n)
Queue Processing:
UPDATE jobs SET status = 'processing'
WHERE id = (
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 1
FOR UPDATE SKIP LOCKED
) RETURNING *;
Anti-Pattern Detection
-- Find unindexed foreign keys
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
);
-- Find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;
-- Check table bloat
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
Configuration Template
-- Connection limits (adjust for RAM)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';
-- Timeouts
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';
-- Monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Security defaults
REVOKE ALL ON SCHEMA public FROM public;
SELECT pg_reload_conf();
Extensions
pgcrypto: crypt() for password hashing.
uuid-ossp: alternative UUID functions; prefer pgcrypto for new projects.
pg_trgm: fuzzy text search with % operator, similarity() function. Index with GIN for LIKE '%pattern%' acceleration.
citext: case-insensitive text type. Prefer expression indexes on LOWER(col) unless you need case-insensitive constraints.
btree_gin/btree_gist: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).
hstore: key-value pairs; mostly superseded by JSONB but useful for simple string mappings.
timescaledb: essential for time-series—automated partitioning, retention, compression, continuous aggregates. Self-hosted and on Tiger Cloud.
postgis: comprehensive geospatial support beyond basic geometric types—essential for location-based applications.
pgvector: vector similarity search for embeddings.
pgaudit: audit logging for all database activity.
Examples
Users
CREATE TABLE users (
user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);
Orders
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
total NUMERIC(10,2) NOT NULL CHECK (total > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
JSONB
CREATE TABLE profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
attrs JSONB NOT NULL DEFAULT '{}',
theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);
Related Resources
- Agent:
database-reviewer - Full database review workflow
- dhamaniasad/awesome-postgres — Curated list of PostgreSQL software, libraries, tools, and resources (11,759+ GitHub stars, CC0-1.0)
Credits
- Supabase Agent Skills (MIT License) — performance rules and patterns
- postgres-schema-design — table design reference (Apache 2.0)
- awesome-postgres by BytesAgain / dhamaniasad — resource curation (CC0-1.0)
1---2name: postgres-best-practices3description: "Comprehensive PostgreSQL reference covering performance optimization, schema design, indexing, query patterns, connection management, security, and advanced features. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, migrations, or database configurations."4risk: unknown5source: community6date_added: "2026-02-27"7metadata:8 source: https://skillmd.com/skills/joshuashepherd/postgres-best-practices9---1011# PostgreSQL Best Practices1213Comprehensive PostgreSQL reference consolidating performance optimization (Supabase), common patterns, and schema design guidance. Covers 8 rule categories prioritized by impact, plus detailed data type and table design reference.1415## When to Use1617Reference these guidelines when:18- Writing SQL queries, migrations, or designing schemas19- Implementing indexes or query optimization20- Reviewing database performance issues21- Configuring connection pooling or scaling22- Optimizing for Postgres-specific features23- Working with Row-Level Security (RLS)24- Choosing data types, constraints, or table structures25- Troubleshooting slow queries2627## Rule Categories by Priority2829| Priority | Category | Impact | Prefix |30|----------|----------|--------|--------|31| 1 | Query Performance | CRITICAL | `query-` |32| 2 | Connection Management | CRITICAL | `conn-` |33| 3 | Security & RLS | CRITICAL | `security-` |34| 4 | Schema Design | HIGH | `schema-` |35| 5 | Concurrency & Locking | MEDIUM-HIGH | `lock-` |36| 6 | Data Access Patterns | MEDIUM | `data-` |37| 7 | Monitoring & Diagnostics | LOW-MEDIUM | `monitor-` |38| 8 | Advanced Features | LOW | `advanced-` |3940## Detailed Rule Files4142Read individual rule files for detailed explanations and SQL examples:4344```45rules/query-missing-indexes.md46rules/schema-partial-indexes.md47rules/_sections.md48```4950Each rule file contains:51- Brief explanation of why it matters52- Incorrect SQL example with explanation53- Correct SQL example with explanation54- Optional EXPLAIN output or metrics55- Additional context and references56- Supabase-specific notes (when applicable)5758For the complete compiled guide with all rules expanded: `AGENTS.md`5960---6162## Schema Design: Core Rules6364- Define a **PRIMARY KEY** for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer `BIGINT GENERATED ALWAYS AS IDENTITY`; use `UUID` only when global uniqueness/opacity is needed.65- **Normalize first (to 3NF)** to eliminate data redundancy and update anomalies; denormalize **only** for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden.66- Add **NOT NULL** everywhere it's semantically required; use **DEFAULT**s for common values.67- Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys.68- Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integer values, **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic).6970## PostgreSQL "Gotchas"7172- **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use `snake_case` for table/column names.73- **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) to restrict to one NULL.74- **FK indexes**: PostgreSQL **does not** auto-index FK columns. Add them.75- **No silent coercions**: length/precision overflows error out (no truncation). Example: inserting 999 into `NUMERIC(2,0)` fails with error, unlike some databases that silently truncate or round.76- **Sequences/identity have gaps** (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.77- **Heap storage**: no clustered PK by default (unlike SQL Server/MySQL InnoDB); `CLUSTER` is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.78- **MVCC**: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.7980## Data Types8182- **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY` preferred (`GENERATED BY DEFAULT` also fine); `UUID` when merging/federating/used in a distributed system or for opaque IDs. Generate with `uuidv7()` (preferred if using PG18+) or `gen_random_uuid()` (if using an older PG version).83- **Integers**: prefer `BIGINT` unless storage space is critical; `INTEGER` for smaller ranges; avoid `SMALLINT` unless constrained.84- **Floats**: prefer `DOUBLE PRECISION` over `REAL` unless storage space is critical. Use `NUMERIC` for exact decimal arithmetic.85- **Strings**: prefer `TEXT`; if length limits needed, use `CHECK (LENGTH(col) <= n)` instead of `VARCHAR(n)`; avoid `CHAR(n)`. Use `BYTEA` for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: `PLAIN` (no TOAST), `EXTENDED` (compress + out-of-line), `EXTERNAL` (out-of-line, no compress), `MAIN` (compress, keep in-line if possible). Default `EXTENDED` usually optimal. Control with `ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy` and `ALTER TABLE tbl SET (toast_tuple_target = 4096)` for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on `LOWER(col)` (preferred unless column needs case-insensitive PK/FK/UNIQUE) or `CITEXT`.86- **Money**: `NUMERIC(p,s)` (never float).87- **Time**: `TIMESTAMPTZ` for timestamps; `DATE` for date-only; `INTERVAL` for durations. Avoid `TIMESTAMP` (without timezone). Use `now()` for transaction start time, `clock_timestamp()` for current wall-clock time.88- **Booleans**: `BOOLEAN` with `NOT NULL` constraint unless tri-state values are required.89- **Enums**: `CREATE TYPE ... AS ENUM` for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table.90- **Arrays**: `TEXT[]`, `INTEGER[]`, etc. Use for ordered lists where you query elements. Index with **GIN** for containment (`@>`, `<@`) and overlap (`&&`) queries. Access: `arr[1]` (1-indexed), `arr[1:3]` (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: `'{val1,val2}'` or `ARRAY[val1,val2]`.91- **Range types**: `daterange`, `numrange`, `tstzrange` for intervals. Support overlap (`&&`), containment (`@>`), operators. Index with **GiST**. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer `[)` (inclusive/exclusive) by default.92- **Network types**: `INET` for IP addresses, `CIDR` for network ranges, `MACADDR` for MAC addresses. Support network operators (`<<`, `>>`, `&&`).93- **Geometric types**: `POINT`, `LINE`, `POLYGON`, `CIRCLE` for 2D spatial data. Index with **GiST**. Consider **PostGIS** for advanced spatial features.94- **Text search**: `TSVECTOR` for full-text search documents, `TSQUERY` for search queries. Index `tsvector` with **GIN**. Always specify language: `to_tsvector('english', col)` and `to_tsquery('english', 'query')`. Never use single-argument versions. This applies to both index expressions and queries.95- **Domain types**: `CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$')` for reusable custom types with validation. Enforces constraints across tables.96- **Composite types**: `CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT)` for structured data within columns. Access with `(col).field` syntax.97- **JSONB**: preferred over JSON; index with **GIN**. Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved.98- **Vector types**: `vector` type by `pgvector` for vector similarity search for embeddings.99100### Do Not Use These Data Types101102- DO NOT use `timestamp` (without time zone); DO use `timestamptz` instead.103- DO NOT use `char(n)` or `varchar(n)`; DO use `text` instead.104- DO NOT use `money` type; DO use `numeric` instead.105- DO NOT use `timetz` type; DO use `timestamptz` instead.106- DO NOT use `timestamptz(0)` or any other precision specification; DO use `timestamptz` instead.107- DO NOT use `serial` type; DO use `generated always as identity` instead.108109### Data Type Quick Reference110111| Use Case | Correct Type | Avoid |112|----------|-------------|-------|113| IDs | `bigint` | `int`, random UUID |114| Strings | `text` | `varchar(255)` |115| Timestamps | `timestamptz` | `timestamp` |116| Money | `numeric(10,2)` | `float` |117| Flags | `boolean` | `varchar`, `int` |118119## Table Types120121- **Regular**: default; fully durable, logged.122- **TEMPORARY**: session-scoped, auto-dropped, not logged. Faster for scratch work.123- **UNLOGGED**: persistent but not crash-safe. Faster writes; good for caches/staging.124125## Constraints126127- **PK**: implicit UNIQUE + NOT NULL; creates a B-tree index.128- **FK**: specify `ON DELETE/UPDATE` action (`CASCADE`, `RESTRICT`, `SET NULL`, `SET DEFAULT`). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use `DEFERRABLE INITIALLY DEFERRED` for circular FK dependencies checked at transaction end.129- **UNIQUE**: creates a B-tree index; allows multiple NULLs unless `NULLS NOT DISTINCT` (PG15+). Standard behavior: `(1, NULL)` and `(1, NULL)` are allowed. With `NULLS NOT DISTINCT`: only one `(1, NULL)` allowed. Prefer `NULLS NOT DISTINCT` unless you specifically need duplicate NULLs.130- **CHECK**: row-local constraints; NULL values pass the check (three-valued logic). Example: `CHECK (price > 0)` allows NULL prices. Combine with `NOT NULL` to enforce: `price NUMERIC NOT NULL CHECK (price > 0)`.131- **EXCLUDE**: prevents overlapping values using operators. `EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)` prevents double-booking rooms. Requires appropriate index type (often GiST).132133## Indexing134135### Index Type Reference136137| Query Pattern | Index Type | Example |138|--------------|------------|---------|139| `WHERE col = value` | B-tree (default) | `CREATE INDEX idx ON t (col)` |140| `WHERE col > value` | B-tree | `CREATE INDEX idx ON t (col)` |141| `WHERE a = x AND b > y` | Composite | `CREATE INDEX idx ON t (a, b)` |142| `WHERE jsonb @> '{}'` | GIN | `CREATE INDEX idx ON t USING gin (col)` |143| `WHERE tsv @@ query` | GIN | `CREATE INDEX idx ON t USING gin (col)` |144| Time-series ranges | BRIN | `CREATE INDEX idx ON t USING brin (col)` |145146### Index Types Explained147148- **B-tree**: default for equality/range queries (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`)149- **Composite**: order matters—index used if equality on leftmost prefix (`WHERE a = ? AND b > ?` uses index on `(a,b)`, but `WHERE b = ?` does not). Put most selective/frequently filtered columns first.150- **Covering**: `CREATE INDEX ON tbl (id) INCLUDE (name, email)` - includes non-key columns for index-only scans without visiting table.151- **Partial**: for hot subsets (`WHERE status = 'active'` → `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`). Any query with `status = 'active'` can use this index.152- **Expression**: for computed search keys (`CREATE INDEX ON tbl (LOWER(email))`). Expression must match exactly in WHERE clause: `WHERE LOWER(email) = 'user@example.com'`.153- **GIN**: JSONB containment/existence, arrays (`@>`, `?`), full-text search (`@@`)154- **GiST**: ranges, geometry, exclusion constraints155- **BRIN**: very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after `CLUSTER`).156157### Common Index Patterns158159**Composite Index Order:**160```sql161-- Equality columns first, then range columns162CREATE INDEX idx ON orders (status, created_at);163-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'164```165166**Covering Index:**167```sql168CREATE INDEX idx ON users (email) INCLUDE (name, created_at);169-- Avoids table lookup for SELECT email, name, created_at170```171172**Partial Index:**173```sql174CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;175-- Smaller index, only includes active users176```177178## Row-Level Security179180Enable with `ALTER TABLE tbl ENABLE ROW LEVEL SECURITY`. Create policies: `CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())`. Built-in user-based access control at the row level.181182**Optimized RLS Policy:**183```sql184CREATE POLICY policy ON orders185 USING ((SELECT auth.uid()) = user_id); -- Wrap in SELECT!186```187188## Partitioning189190- Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date).191- Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically192- **RANGE**: common for time-series (`PARTITION BY RANGE (created_at)`). Create partitions: `CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')`. **TimescaleDB** automates time-based or ID-based partitioning with retention policies and compression.193- **LIST**: for discrete values (`PARTITION BY LIST (region)`). Example: `FOR VALUES IN ('us-east', 'us-west')`.194- **HASH**: for even distribution when no natural key (`PARTITION BY HASH (user_id)`). Creates N partitions with modulus.195- **Constraint exclusion**: requires `CHECK` constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).196- Prefer declarative partitioning or hypertables. Do NOT use table inheritance.197- **Limitations**: no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.198199## Special Considerations200201### Update-Heavy Tables202203- **Separate hot/cold columns**—put frequently updated columns in separate table to minimize bloat.204- **Use `fillfactor=90`** to leave space for HOT updates that avoid index maintenance.205- **Avoid updating indexed columns**—prevents beneficial HOT updates.206- **Partition by update patterns**—separate frequently updated rows in a different partition from stable data.207208### Insert-Heavy Workloads209210- **Minimize indexes**—only create what you query; every index slows inserts.211- **Use `COPY` or multi-row `INSERT`** instead of single-row inserts.212- **UNLOGGED tables** for rebuildable staging data—much faster writes.213- **Defer index creation** for bulk loads—>drop index, load data, recreate indexes.214- **Partition by time/hash** to distribute load. **TimescaleDB** automates partitioning and compression of insert-heavy data.215- **Use a natural key for primary key** such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all.216- If you do need a surrogate key, **Prefer `BIGINT GENERATED ALWAYS AS IDENTITY` over `UUID`**.217218### Upsert-Friendly Design219220- **Requires UNIQUE index** on conflict target columns—`ON CONFLICT (col1, col2)` needs exact matching unique index (partial indexes don't work).221- **Use `EXCLUDED.column`** to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.222- **`DO NOTHING` faster** than `DO UPDATE` when no actual update needed.223224### Safe Schema Evolution225226- **Transactional DDL**: most DDL operations can run in transactions and be rolled back—`BEGIN; ALTER TABLE...; ROLLBACK;` for safe testing.227- **Concurrent index creation**: `CREATE INDEX CONCURRENTLY` avoids blocking writes but can't run in transactions.228- **Volatile defaults cause rewrites**: adding `NOT NULL` columns with volatile defaults (e.g., `now()`, `gen_random_uuid()`) rewrites entire table. Non-volatile defaults are fast.229- **Drop constraints before columns**: `ALTER TABLE DROP CONSTRAINT` then `DROP COLUMN` to avoid dependency issues.230- **Function signature changes**: `CREATE OR REPLACE` with different arguments creates overloads, not replacements. DROP old version if no overload desired.231232## Generated Columns233234- `... GENERATED ALWAYS AS (<expr>) STORED` for computed, indexable fields. PG18+ adds `VIRTUAL` columns (computed on read, not stored).235236## JSONB Guidance237238- Prefer `JSONB` with **GIN** index.239- Default: `CREATE INDEX ON tbl USING GIN (jsonb_col);` → accelerates:240 - **Containment** `jsonb_col @> '{"k":"v"}'`241 - **Key existence** `jsonb_col ? 'k'`, **any/all keys** `?\|`, `?&`242 - **Path containment** on nested docs243 - **Disjunction** `jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])`244- Heavy `@>` workloads: consider opclass `jsonb_path_ops` for smaller/faster containment-only indexes:245 - `CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);`246 - **Trade-off**: loses support for key existence (`?`, `?|`, `?&`) queries—only supports containment (`@>`)247- Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression):248 - `ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;`249 - `CREATE INDEX ON tbl (price);`250 - Prefer queries like `WHERE price BETWEEN 100 AND 500` (uses B-tree) over `WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500` without index.251- Arrays inside JSONB: use GIN + `@>` for containment (e.g., tags). Consider `jsonb_path_ops` if only doing containment.252- Keep core relations in tables; use JSONB for optional/variable attributes.253- Use constraints to limit allowed JSONB values in a column e.g. `config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')`254255## Common Patterns256257**UPSERT:**258```sql259INSERT INTO settings (user_id, key, value)260VALUES (123, 'theme', 'dark')261ON CONFLICT (user_id, key)262DO UPDATE SET value = EXCLUDED.value;263```264265**Cursor Pagination:**266```sql267SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;268-- O(1) vs OFFSET which is O(n)269```270271**Queue Processing:**272```sql273UPDATE jobs SET status = 'processing'274WHERE id = (275 SELECT id FROM jobs WHERE status = 'pending'276 ORDER BY created_at LIMIT 1277 FOR UPDATE SKIP LOCKED278) RETURNING *;279```280281## Anti-Pattern Detection282283```sql284-- Find unindexed foreign keys285SELECT conrelid::regclass, a.attname286FROM pg_constraint c287JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)288WHERE c.contype = 'f'289 AND NOT EXISTS (290 SELECT 1 FROM pg_index i291 WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)292 );293294-- Find slow queries295SELECT query, mean_exec_time, calls296FROM pg_stat_statements297WHERE mean_exec_time > 100298ORDER BY mean_exec_time DESC;299300-- Check table bloat301SELECT relname, n_dead_tup, last_vacuum302FROM pg_stat_user_tables303WHERE n_dead_tup > 1000304ORDER BY n_dead_tup DESC;305```306307## Configuration Template308309```sql310-- Connection limits (adjust for RAM)311ALTER SYSTEM SET max_connections = 100;312ALTER SYSTEM SET work_mem = '8MB';313314-- Timeouts315ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';316ALTER SYSTEM SET statement_timeout = '30s';317318-- Monitoring319CREATE EXTENSION IF NOT EXISTS pg_stat_statements;320321-- Security defaults322REVOKE ALL ON SCHEMA public FROM public;323324SELECT pg_reload_conf();325```326327## Extensions328329- **`pgcrypto`**: `crypt()` for password hashing.330- **`uuid-ossp`**: alternative UUID functions; prefer `pgcrypto` for new projects.331- **`pg_trgm`**: fuzzy text search with `%` operator, `similarity()` function. Index with GIN for `LIKE '%pattern%'` acceleration.332- **`citext`**: case-insensitive text type. Prefer expression indexes on `LOWER(col)` unless you need case-insensitive constraints.333- **`btree_gin`/`btree_gist`**: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).334- **`hstore`**: key-value pairs; mostly superseded by JSONB but useful for simple string mappings.335- **`timescaledb`**: essential for time-series—automated partitioning, retention, compression, continuous aggregates. Self-hosted and on Tiger Cloud.336- **`postgis`**: comprehensive geospatial support beyond basic geometric types—essential for location-based applications.337- **`pgvector`**: vector similarity search for embeddings.338- **`pgaudit`**: audit logging for all database activity.339340## Examples341342### Users343344```sql345CREATE TABLE users (346 user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,347 email TEXT NOT NULL UNIQUE,348 name TEXT NOT NULL,349 created_at TIMESTAMPTZ NOT NULL DEFAULT now()350);351CREATE UNIQUE INDEX ON users (LOWER(email));352CREATE INDEX ON users (created_at);353```354355### Orders356357```sql358CREATE TABLE orders (359 order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,360 user_id BIGINT NOT NULL REFERENCES users(user_id),361 status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),362 total NUMERIC(10,2) NOT NULL CHECK (total > 0),363 created_at TIMESTAMPTZ NOT NULL DEFAULT now()364);365CREATE INDEX ON orders (user_id);366CREATE INDEX ON orders (created_at);367```368369### JSONB370371```sql372CREATE TABLE profiles (373 user_id BIGINT PRIMARY KEY REFERENCES users(user_id),374 attrs JSONB NOT NULL DEFAULT '{}',375 theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED376);377CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);378```379380## Related Resources381382- Agent: `database-reviewer` - Full database review workflow383- [dhamaniasad/awesome-postgres](https://github.com/dhamaniasad/awesome-postgres) — Curated list of PostgreSQL software, libraries, tools, and resources (11,759+ GitHub stars, CC0-1.0)384385## Credits386387- Supabase Agent Skills (MIT License) — performance rules and patterns388- postgres-schema-design — table design reference (Apache 2.0)389- awesome-postgres by BytesAgain / dhamaniasad — resource curation (CC0-1.0)