Postgresql Table Design

PostgreSQL table design: details

Deep material for the postgresql-table-design skill. SKILL.md carries the rules and the decision points; this file carries the full data-type catalog, workload patterns, extensions, JSONB indexing, and worked DDL.

Data type catalog

• 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.

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.

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 (owner = current_user) when database roles map to users, or compare against an app-set setting such as current_setting('app.user_id')::bigint. Built-in user-based access control at the row level.

Workload patterns

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.

Constraint and index notes

• FK: an explicit index on the referencing column speeds up joins and prevents locking issues on parent deletes/updates.

• UNIQUE: standard behavior allows both (1, NULL) and (1, NULL); with NULLS NOT DISTINCT (PG15+) only one (1, NULL) is allowed.

• Partial index: CREATE INDEX ON tbl (user_id) WHERE status = 'active' serves any query that includes status = 'active'.

• Expression index: the expression must match the WHERE clause exactly: WHERE LOWER(email) = '[email protected]'.

• BRIN: effective when row order on disk correlates with the indexed column (insertion order or after CLUSTER).

• Heap storage: no clustered PK by default (unlike SQL Server/MySQL InnoDB); row order on disk is insertion order unless explicitly clustered, and CLUSTER is not maintained on later inserts.

Partitioning details

• Use for very large tables (>100M rows) where queries consistently filter on the partition key (often time/date), or for tables whose maintenance (pruning, periodic bulk replacement) follows a key.

• RANGE: PARTITION BY RANGE (created_at); create partitions with 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: PARTITION BY LIST (region) with FOR VALUES IN ('us-east', 'us-west').

• HASH: PARTITION BY HASH (user_id) creates N partitions by modulus, for even distribution when there is no natural key.

• Constraint exclusion: requires CHECK constraints on partitions for the planner to prune; auto-created for declarative partitioning (PG10+).

Generated columns

• ... GENERATED ALWAYS AS (<expr>) STORED for computed, indexable fields. PG18+ adds VIRTUAL columns (computed on read, not stored).

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.

• 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.

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

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