Postgresql

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

❌ 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

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

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 ('[email protected]', '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

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

• PostgreSQL Official Docs
• PostgreSQL Wiki
• TimescaleDB