PostgreSQL table design
Core rules
- Use modern PostgreSQL 18 features (virtual columns, uuidv7) if possible.
- Prefer
text with check (length(field) <= N) over varchar(N).
- Define a primary key for reference tables (e.g., users, orders). Not needed for time-series, event, or log data. When used, prefer
bigint generated always as identity; use uuid only for global uniqueness or when opacity is required.
- Normalize to 3NF initially to remove redundancy and update anomalies. Denormalize only for proven, high-ROI reads where join performance is an issue. Premature denormalization increases maintenance.
- Add
not null constraints where semantically needed, and use default values for common cases.
- Create indexes for actual query access paths: PK/unique (auto), FK columns (manual), frequent filters/sorts, and join keys.
- Prefer
timestamptz for event times, numeric for money, text for strings, bigint for integers, double precision for floats (or numeric for exact decimal math).
PostgreSQL "Gotchas"
- Identifiers: Unquoted names are lowercased. Avoid quoted/mixed-case. Convention: use
snake_case for tables and columns.
- SQL keywords: lowercase.
- Unique + NULLs:
unique allows multiple NULLs. Use unique (...) nulls not distinct (PG 15+) to allow a single NULL.
- FK indexes: PostgreSQL does not auto-index FK columns; add indexes manually.
- No silent coercions: Overflows raise errors (e.g., 999 into
numeric(2,0) fails). No silent truncation/rounding.
- Sequences/identity gaps are normal due to rollbacks and concurrency. Do not "fix" gaps.
- Heap storage: No clustered PK by default.
cluster is a one-off operation and isn't maintained after further inserts. Row order on disk follows insertion unless explicitly clustered.
- MVCC: Updates/deletes leave dead rows.
vacuum cleans them. Avoid wide-row and high-churn designs when possible.
Data types
- IDs: Prefer
bigint generated always as identity; use uuid when federating/distributing or for opaque IDs. Use uuidv7(); use gen_random_uuid() for PostgreSQL 17 and earlier.
- Integers: Use
bigint unless space matters, then use integer or smallint as appropriate.
- Floats: Use
double precision. Use real only for critical storage constraints. Use numeric for decimals needing full precision.
- Strings: Use
text. For limits, use check (length(col) <= n), not varchar(n); avoid char(n). Use bytea for binary. Large strings/binary (>2KB) are TOASTed. Default extended is optimal. Use collation/expression indexes on lower(col) or citext for case-insensitivity.
- Money: Use
numeric(p, s), not float.
- Time: Use
timestamptz for timestamps, date for dates, interval for durations. Avoid timestamp (without timezone). Use now() for transaction time, clock_timestamp() for wall time.
- Booleans: Use
boolean with not null if appropriate.
- Enums: Use
create type ... as enum for stable sets. For evolving values, use text/int plus check or lookup tables.
- Arrays: Use standard array types for ordered lists. Index with GIN for containment and overlap. Suitable for tags and categories; for relations, use junction tables. Literals:
'{val1,val2}' or array[val1,val2].
- Range types: Use
daterange, numrange, tstzrange for intervals; index with GiST.
- Network types: Use
inet, cidr, macaddr for IP/network/MAC.
- Geometric types: Use
point, line, polygon, circle; index with GiST. For advanced needs, use PostGIS.
- Text search:
tsvector for docs, index with GIN. Specify language in to_tsvector()/to_tsquery().
- Domain types: Use for reusable custom types with validation.
- Composite types: Use for structured columns; access as
(col).field.
- JSONB: Preferred for flexible/optional fields; index with GIN. Use JSON only if order matters.
- Vector types: Use
vector from pgvector for embeddings/similarity search.
Avoid these data types
- Do not use
timestamp (no timezone); use timestamptz.
- Do not use
char(n) or varchar(n); use text.
- Do not use the
money type; use numeric.
- Do not use
timetz; use timestamptz.
- Do not use precision like
timestamptz(0); use timestamptz.
- Do not use
serial; use generated always as identity.
Table types
- Regular: Standard, fully durable/logged.
temporary: Session-scoped, not logged, auto-dropped.
unlogged: Fast, not crash-safe. Use for cache/staging.
Row-level security
Enable with alter table tbl enable row level security, then set policies:
create policy user_access on orders for select to app_users using (user_id = current_user_id());
Constraints
- primary key: Implies unique + not null; adds B-tree index.
- Foreign key: Specify
on delete/on update actions. Index referencing columns explicitly. For circular dependencies, use deferrable initially deferred.
- unique: B-tree index; allows multiple NULLs (unless
nulls not distinct, PG15+). Prefer nulls not distinct unless duplicate NULLs are needed.
- check: Row-level constraints; NULLs pass unless combined with
not null.
- exclude: Prevents overlaps using operators; needs suitable index (often GiST).
Indexing
- B-tree: Default; for equality/range queries/order by.
- Composite: Index uses leftmost-constrained columns first. Place most-filtered columns first.
- Covering: Use
include for index-only scans.
- Partial: For hot subsets, e.g.,
status='active'.
- Expression: Use for computed search keys;
where must match expression.
- GIN: Use for JSONB, arrays, text search.
- GiST: Use for ranges, geometry, exclusion constraints.
- BRIN: For huge, naturally ordered data (e.g., time-series).
Partitioning
- Use for very large tables (>100M rows) with partition-key filtering.
- Useful for periodic pruning or bulk replace.
- Range: For time-series:
partition by range (created_at) and create partitions per period.
- TimescaleDB automates this.
- List: For discrete values:
partition by list (region).
- Hash: For even distribution when no natural key:
partition by hash (user_id).
- Only declarative partitioning; avoid table inheritance.
- Limitations: No global uniques—include partition key. No FKs from partitioned tables; use triggers.
Special considerations
Update-heavy tables
- Separate frequently updated ("hot") columns into a different table.
- Set
fillfactor=90 to support HOT updates.
- Avoid updating indexed columns.
- Partition based on update patterns.
Insert-heavy workloads
- Minimize indexes; create only as needed.
- Use
copy or multi-row insert for bulk loads.
- Use unlogged tables for staging when possible.
- Defer index creation on large loads: drop, load, then recreate.
- Partition by time/hash to distribute load; TimescaleDB can automate this.
- Use a natural key as PK if possible; insert-heavy tables may not need a PK. If surrogate needed, prefer
bigint generated always as identity.
Upsert-friendly design
on conflict (col1, col2) needs a matching unique index (not partial).
- Use
excluded.column for referenced values and only update changed columns.
do nothing is faster than do update if no changes required.
Safe schema evolution
- Most DDL is transactional (can roll back).
- Use
create index concurrently for non-blocking index creation; not in transactions.
- Adding
not null with volatile defaults rewrites entire table. Non-volatile is fast.
- Drop constraints before dropping columns to avoid errors.
- Changing function signatures creates overloads. Drop old as needed.
Generated columns
- Use
generated always as (...) stored for computed, indexable columns. Use virtual for columns that don't need to be stored.
Extensions
- pgcrypto: Password hashing.
- pg_trgm: Fuzzy search; GIN indexes for
like/ilike.
- citext: Case-insensitive text type; prefer expression indexes unless strict constraints required.
- btree_gin/btree_gist: Mixed-type indexes.
- hstore: Key-value pairs; JSONB preferred generally.
- timescaledb: Time-series automation.
- postgis: Geospatial features.
- pgvector: Vector search.
- pgaudit: Audit logging.
JSONB guidance
- Prefer
jsonb with GIN index:
- Containment:
jsonb_col @> '{"k":"v"}'
- Key existence:
jsonb_col ? 'k', any/all: ?|, ?&
- Path containment for nested docs
- Use
jsonb_path_ops GIN for heavy containment-only; disallows key existence ops.
- For equality/range: extract scalar to generated/expression column and index as B-tree.
- Prefer B-tree range queries over runtime casts.
- Use GIN with
@> for JSONB arrays when needed.
- Keep core data as regular columns; reserve JSONB for optional/variable fields.
- Add constraints like
check(jsonb_typeof(config) = 'object') for safety.
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);
1---2name: postgresql-table-design3description: Design and optimize a PostgreSQL-specific schema. Use for PostgreSQL best practices, data types, indexing, constraints, performance patterns, and advanced features.4metadata:5 source: https://skillmd.com/skills/zuoguyoupan2023/postgresql-table-design6---78# PostgreSQL table design910## Core rules1112- Use modern PostgreSQL 18 features (virtual columns, uuidv7) if possible.13- Prefer `text` with `check (length(field) <= N)` over `varchar(N)`.14- Define a **primary key** for reference tables (e.g., users, orders). Not needed for time-series, event, or log data. When used, prefer `bigint generated always as identity`; use `uuid` only for global uniqueness or when opacity is required.15- **Normalize to 3NF** initially to remove redundancy and update anomalies. Denormalize only for proven, high-ROI reads where join performance is an issue. Premature denormalization increases maintenance.16- Add **`not null`** constraints where semantically needed, and use **`default`** values for common cases.17- Create **indexes for actual query access paths**: PK/unique (auto), **FK columns (manual)**, frequent filters/sorts, and join keys.18- Prefer **`timestamptz`** for event times, **`numeric`** for money, **`text`** for strings, **`bigint`** for integers, **`double precision`** for floats (or `numeric` for exact decimal math).1920## PostgreSQL "Gotchas"2122- **Identifiers**: Unquoted names are lowercased. Avoid quoted/mixed-case. Convention: use `snake_case` for tables and columns.23- **SQL keywords**: lowercase.24- **Unique + NULLs**: `unique` allows multiple NULLs. Use `unique (...) nulls not distinct` (PG 15+) to allow a single NULL.25- **FK indexes**: PostgreSQL does not auto-index FK columns; add indexes manually.26- **No silent coercions**: Overflows raise errors (e.g., 999 into `numeric(2,0)` fails). No silent truncation/rounding.27- **Sequences/identity gaps** are normal due to rollbacks and concurrency. Do not "fix" gaps.28- **Heap storage**: No clustered PK by default. `cluster` is a one-off operation and isn't maintained after further inserts. Row order on disk follows insertion unless explicitly clustered.29- **MVCC**: Updates/deletes leave dead rows. `vacuum` cleans them. Avoid wide-row and high-churn designs when possible.3031## Data types3233- **IDs**: Prefer `bigint generated always as identity`; use `uuid` when federating/distributing or for opaque IDs. Use `uuidv7()`; use `gen_random_uuid()` for PostgreSQL 17 and earlier.34- **Integers**: Use `bigint` unless space matters, then use `integer` or `smallint` as appropriate.35- **Floats**: Use `double precision`. Use `real` only for critical storage constraints. Use `numeric` for decimals needing full precision.36- **Strings**: Use `text`. For limits, use `check (length(col) <= n)`, not `varchar(n)`; avoid `char(n)`. Use `bytea` for binary. Large strings/binary (>2KB) are TOASTed. Default `extended` is optimal. Use collation/expression indexes on `lower(col)` or `citext` for case-insensitivity.37- **Money**: Use `numeric(p, s)`, not float.38- **Time**: Use `timestamptz` for timestamps, `date` for dates, `interval` for durations. Avoid `timestamp` (without timezone). Use `now()` for transaction time, `clock_timestamp()` for wall time.39- **Booleans**: Use `boolean` with `not null` if appropriate.40- **Enums**: Use `create type ... as enum` for stable sets. For evolving values, use `text`/`int` plus `check` or lookup tables.41- **Arrays**: Use standard array types for ordered lists. Index with **GIN** for containment and overlap. Suitable for tags and categories; for relations, use junction tables. Literals: `'{val1,val2}'` or `array[val1,val2]`.42- **Range types**: Use `daterange`, `numrange`, `tstzrange` for intervals; index with **GiST**.43- **Network types**: Use `inet`, `cidr`, `macaddr` for IP/network/MAC.44- **Geometric types**: Use `point`, `line`, `polygon`, `circle`; index with **GiST**. For advanced needs, use **PostGIS**.45- **Text search**: `tsvector` for docs, index with **GIN**. Specify language in `to_tsvector()`/`to_tsquery()`.46- **Domain types**: Use for reusable custom types with validation.47- **Composite types**: Use for structured columns; access as `(col).field`.48- **JSONB**: Preferred for flexible/optional fields; index with **GIN**. Use JSON only if order matters.49- **Vector types**: Use `vector` from `pgvector` for embeddings/similarity search.5051### Avoid these data types5253- Do not use `timestamp` (no timezone); use `timestamptz`.54- Do not use `char(n)` or `varchar(n)`; use `text`.55- Do not use the `money` type; use `numeric`.56- Do not use `timetz`; use `timestamptz`.57- Do not use precision like `timestamptz(0)`; use `timestamptz`.58- Do not use `serial`; use `generated always as identity`.5960## Table types6162- **Regular**: Standard, fully durable/logged.63- **`temporary`**: Session-scoped, not logged, auto-dropped.64- **`unlogged`**: Fast, not crash-safe. Use for cache/staging.6566## Row-level security6768Enable with `alter table tbl enable row level security`, then set policies:69```sql70create policy user_access on orders for select to app_users using (user_id = current_user_id());71```7273## Constraints7475- **primary key**: Implies unique + not null; adds B-tree index.76- **Foreign key**: Specify `on delete`/`on update` actions. Index referencing columns explicitly. For circular dependencies, use `deferrable initially deferred`.77- **unique**: B-tree index; allows multiple NULLs (unless `nulls not distinct`, PG15+). Prefer `nulls not distinct` unless duplicate NULLs are needed.78- **check**: Row-level constraints; NULLs pass unless combined with `not null`.79- **exclude**: Prevents overlaps using operators; needs suitable index (often GiST).8081## Indexing8283- **B-tree**: Default; for equality/range queries/order by.84- **Composite**: Index uses leftmost-constrained columns first. Place most-filtered columns first.85- **Covering**: Use `include` for index-only scans.86- **Partial**: For hot subsets, e.g., `status='active'`.87- **Expression**: Use for computed search keys; `where` must match expression.88- **GIN**: Use for JSONB, arrays, text search.89- **GiST**: Use for ranges, geometry, exclusion constraints.90- **BRIN**: For huge, naturally ordered data (e.g., time-series).9192## Partitioning9394- Use for very large tables (>100M rows) with partition-key filtering.95- Useful for periodic pruning or bulk replace.96- **Range**: For time-series: `partition by range (created_at)` and create partitions per period.97 - **TimescaleDB** automates this.98- **List**: For discrete values: `partition by list (region)`.99- **Hash**: For even distribution when no natural key: `partition by hash (user_id)`.100- Only declarative partitioning; avoid table inheritance.101- **Limitations**: No global uniques—include partition key. No FKs from partitioned tables; use triggers.102103## Special considerations104105### Update-heavy tables106107- Separate frequently updated ("hot") columns into a different table.108- Set `fillfactor=90` to support HOT updates.109- Avoid updating indexed columns.110- Partition based on update patterns.111112### Insert-heavy workloads113114- Minimize indexes; create only as needed.115- Use `copy` or multi-row `insert` for bulk loads.116- Use **unlogged tables** for staging when possible.117- Defer index creation on large loads: drop, load, then recreate.118- Partition by time/hash to distribute load; TimescaleDB can automate this.119- Use a natural key as PK if possible; insert-heavy tables may not need a PK. If surrogate needed, prefer `bigint generated always as identity`.120121### Upsert-friendly design122123- `on conflict (col1, col2)` needs a matching unique index (not partial).124- Use `excluded.column` for referenced values and only update changed columns.125- `do nothing` is faster than `do update` if no changes required.126127### Safe schema evolution128129- Most DDL is transactional (can roll back).130- Use `create index concurrently` for non-blocking index creation; not in transactions.131- Adding `not null` with volatile defaults rewrites entire table. Non-volatile is fast.132- Drop constraints before dropping columns to avoid errors.133- Changing function signatures creates overloads. Drop old as needed.134135## Generated columns136137- Use `generated always as (...) stored` for computed, indexable columns. Use `virtual` for columns that don't need to be stored.138139## Extensions140141- **pgcrypto**: Password hashing.142- **pg_trgm**: Fuzzy search; GIN indexes for `like`/`ilike`.143- **citext**: Case-insensitive text type; prefer expression indexes unless strict constraints required.144- **btree_gin/btree_gist**: Mixed-type indexes.145- **hstore**: Key-value pairs; JSONB preferred generally.146- **timescaledb**: Time-series automation.147- **postgis**: Geospatial features.148- **pgvector**: Vector search.149- **pgaudit**: Audit logging.150151## JSONB guidance152153- Prefer `jsonb` with GIN index:154 - Containment: `jsonb_col @> '{"k":"v"}'`155 - Key existence: `jsonb_col ? 'k'`, any/all: `?|`, `?&`156 - Path containment for nested docs157- Use `jsonb_path_ops` GIN for heavy containment-only; disallows key existence ops.158- For equality/range: extract scalar to generated/expression column and index as B-tree.159- Prefer B-tree range queries over runtime casts.160- Use GIN with `@>` for JSONB arrays when needed.161- Keep core data as regular columns; reserve JSONB for optional/variable fields.162- Add constraints like `check(jsonb_typeof(config) = 'object')` for safety.163164## Examples165166### Users167168```sql169create table users (170 user_id bigint generated always as identity primary key,171 email text not null unique,172 name text not null,173 created_at timestamptz not null default now()174);175create unique index on users (lower(email));176create index on users (created_at);177```178179### Orders180181```sql182create table orders (183 order_id bigint generated always as identity primary key,184 user_id bigint not null references users(user_id),185 status text not null default 'PENDING' check (status in ('PENDING','PAID','CANCELED')),186 total numeric(10,2) not null check (total > 0),187 created_at timestamptz not null default now()188);189create index on orders (user_id);190create index on orders (created_at);191```192193### JSONB194195```sql196create table profiles (197 user_id bigint primary key references users(user_id),198 attrs jsonb not null default '{}',199 theme text generated always as (attrs->>'theme') stored200);201create index profiles_attrs_gin on profiles using gin (attrs);202```