Postgresql Table Design by @wshobson

Postgresql Table Design by @wshobson · Skillet

BIGINT GENERATED ALWAYS AS IDENTITY

UUID

Avoid	Use instead
`timestamp` (without time zone)	`timestamptz`
`char(n)`, `varchar(n)`	`text` (+ `CHECK` on length if needed)
`money`	`numeric`
`timetz`	`timestamptz`
`timestamptz(0)` or any precision	`timestamptz`
`serial`	`generated always as identity`

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

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 attributes with a generated, indexable scalar
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);

Postgresql Table Design

PostgreSQL Table Design

When to Use

Core Rules

PostgreSQL Gotchas

Data Types

Types to avoid

Constraints

Indexing

Partitioning

Examples

Going deeper