PostgreSQL Table Design
When to Use
- Designing PostgreSQL schemas from scratch
- Choosing correct data types for columns
- Planning indexing strategy
- Partitioning large tables (>100M rows)
- Query optimization patterns
- JSONB design decisions
Core Rules
| Rule |
Details |
| Primary Key |
BIGINT GENERATED ALWAYS AS IDENTITY (or UUID for distributed) |
| Normalize First |
Start with 3NF, denormalize only for proven ROI |
| NOT NULL |
Add everywhere semantically required + DEFAULTs |
| FK Indexes |
PostgreSQL does NOT auto-index FK columns - add them manually |
| Types |
TIMESTAMPTZ, NUMERIC (money), TEXT, BIGINT, DOUBLE PRECISION |
PostgreSQL Gotchas
-- Identifiers: unquoted → lowercased (use snake_case)
-- UNIQUE allows multiple NULLs (PG15+: NULLS NOT DISTINCT)
-- Sequences have gaps - normal behavior, don't "fix"
-- MVCC: updates/deletes leave dead tuples → vacuum needed
-- No clustered PK by default (unlike MySQL InnoDB)
Data Types
✅ Preferred Types
| Use Case |
Type |
Notes |
| IDs |
BIGINT GENERATED ALWAYS AS IDENTITY |
Use UUID for distributed/merged systems |
| Strings |
TEXT |
Never VARCHAR(n) - use CHECK (LENGTH(col) <= n) |
| Money |
NUMERIC(p,s) |
Never float for money |
| Timestamps |
TIMESTAMPTZ |
Never TIMESTAMP (without timezone) |
| Booleans |
BOOLEAN NOT NULL |
Unless tri-state needed |
| JSON |
JSONB |
With GIN index |
| Arrays |
TEXT[], INTEGER[] |
Index with GIN for @>, && |
| Full-text |
TSVECTOR |
With GIN index, always specify language |
❌ Never Use
-- ANTI-PATTERNS:
timestamp -- → use timestamptz
char(n)/varchar(n) -- → use text
money type -- → use numeric
timetz -- → use timestamptz
serial -- → use generated always as identity
Constraints
-- PK: implicit UNIQUE + NOT NULL, creates B-tree index
-- FK: always specify ON DELETE/UPDATE action
ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE;
-- UNIQUE: allows multiple NULLs (PG15+: NULLS NOT DISTINCT)
-- CHECK: NULL values pass - combine with NOT NULL
price NUMERIC NOT NULL CHECK (price > 0)
-- EXCLUDE: prevent overlapping values
EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)
Indexing Strategy
| Type |
Use Case |
| B-tree |
Default for =, <, >, BETWEEN, ORDER BY |
| Composite |
Order matters - leftmost prefix for equality |
| Covering |
INCLUDE (col1, col2) for index-only scans |
| Partial |
Hot subsets: WHERE status = 'active' |
| Expression |
LOWER(email) for case-insensitive |
| GIN |
JSONB, arrays, full-text (@>, ?, @@) |
| GiST |
Ranges, geometry, exclusion constraints |
| BRIN |
Large time-series, minimal storage |
-- Covering index example
CREATE INDEX ON orders (user_id) INCLUDE (total, status);
-- Partial index for hot subset
CREATE INDEX ON orders (created_at) WHERE status = 'active';
-- JSONB GIN index
CREATE INDEX ON profiles USING GIN (attrs);
-- Or containment-only (smaller):
CREATE INDEX ON profiles USING GIN (attrs jsonb_path_ops);
Partitioning (>100M rows)
| Type |
Use Case |
| RANGE |
Time-series (PARTITION BY RANGE (created_at)) |
| LIST |
Discrete values (PARTITION BY LIST (region)) |
| HASH |
Even distribution (PARTITION BY HASH (user_id)) |
CREATE TABLE logs (
id BIGINT GENERATED ALWAYS AS IDENTITY,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
data JSONB
) PARTITION BY RANGE (created_at);
CREATE TABLE logs_2024_01 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
Limitations:
- No global UNIQUE constraints - include partition key in PK
- FKs from partitioned tables not supported
Special Patterns
Update-Heavy Tables
-- Separate hot/cold columns into different tables
-- Use fillfactor=90 for HOT updates
ALTER TABLE orders SET (fillfactor = 90);
-- Avoid updating indexed columns
Insert-Heavy Workloads
-- Minimize indexes
-- Use COPY or multi-row INSERT
INSERT INTO logs (data) VALUES
('{"a":1}'), ('{"a":2}'), ('{"a":3}');
-- Consider UNLOGGED tables for staging
-- Defer index creation for bulk loads
Upsert (ON CONFLICT)
INSERT INTO users (email, name)
VALUES ('user@example.com', 'John')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;
-- Requires exact matching UNIQUE index
JSONB Best Practices
-- Default GIN index (supports @>, ?, ?|, ?&)
CREATE INDEX ON profiles USING GIN (attrs);
-- Path ops (smaller, only @>)
CREATE INDEX ON profiles USING GIN (attrs jsonb_path_ops);
-- Extract field for B-tree index
ALTER TABLE profiles
ADD COLUMN theme TEXT
GENERATED ALWAYS AS (attrs->>'theme') STORED;
CREATE INDEX ON profiles (theme);
Essential Extensions
| Extension |
Purpose |
pgcrypto |
Password hashing (crypt()) |
pg_trgm |
Fuzzy text search (LIKE '%pattern%') |
timescaledb |
Time-series automation |
postgis |
Geospatial features |
pgvector |
Vector similarity (embeddings) |
pgaudit |
Audit logging |
Example Schema
CREATE TABLE users (
user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
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);
Related Skills
- database-architect - DB selection and architecture design
- repo-analysis - Analyze existing PostgreSQL patterns in repositories
Resources
digi4care