postgresql

PostgreSQL Table Design

Core Rules

• Define a PRIMARY KEY for reference tables (users, orders, etc.). Time-series, event and log data do not always need one. When you use one, prefer BIGINT GENERATED ALWAYS AS IDENTITY. Use UUID only when you need global uniqueness or opacity.
• Normalize first (to 3NF) to eliminate data redundancy and update anomalies.
  • Denormalize only for measured, high-ROI reads where join performance is proven to be a problem.
  • Premature denormalization creates a maintenance burden.
• Add NOT NULL everywhere it’s semantically required. Use DEFAULTs for common values.
• Create indexes for access paths you actually query:
  • PK/unique (auto)
  • FK columns (manual!)
  • frequent filters and sorts
  • join keys
• Preferred types:
  • TIMESTAMPTZ for event time
  • NUMERIC for money
  • TEXT for strings
  • BIGINT for integer values
  • DOUBLE PRECISION for floats (or NUMERIC for exact decimal arithmetic)

PostgreSQL “Gotchas”

• Identifiers: unquoted names are lowercased. Avoid quoted or mixed-case names. Convention: use snake_case for table and column names.
• Unique + NULLs: UNIQUE allows multiple NULLs. Use UNIQUE (...) NULLS NOT DISTINCT (PG15+) to allow only one NULL.
• FK indexes: PostgreSQL does not auto-index FK columns. Add them.
• No silent coercions: length and precision overflows raise an error (no truncation).
  • Example: inserting 999 into NUMERIC(2,0) fails with an error.
  • Some other databases silently truncate or round instead.
• Sequences/identity have gaps (normal; don't "fix"). Rollbacks, crashes and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior, so don't try to make IDs consecutive.
• Heap storage: there is no clustered PK by default (unlike SQL Server/MySQL InnoDB).
  • Row order on disk is insertion order unless the table is explicitly clustered.
  • CLUSTER is a one-off reorganization. It is not maintained on later inserts.
  • CLUSTER takes an exclusive lock on the whole table while it runs, which blocks reads and writes. Schedule it in a maintenance window with user approval.
• MVCC: updates and deletes leave dead tuples, and vacuum cleans them up. Design to avoid hot wide-row churn.

Data Types

• IDs: prefer BIGINT GENERATED ALWAYS AS IDENTITY (GENERATED BY DEFAULT is also fine).
  • Use UUID when merging, federating, running a distributed system, or needing opaque IDs.
  • Generate UUIDs with uuidv7() (preferred on PG18+) or gen_random_uuid() (on older PG versions).
• Integers: prefer BIGINT unless storage space is critical. Use INTEGER for smaller ranges. Avoid SMALLINT unless the values are constrained.
• Floats: prefer DOUBLE PRECISION over REAL unless storage space is critical. Use NUMERIC for exact decimal arithmetic.
• Strings: prefer TEXT.
  • If you need a length limit, use CHECK (LENGTH(col) <= n) instead of VARCHAR(n). Avoid CHAR(n).
  • Use BYTEA for binary data.
  • Large strings and binary values (above the 2KB default threshold) are stored in TOAST with compression. The TOAST storage strategies are:
    • PLAIN (no TOAST)
    • EXTENDED (compress + out-of-line)
    • EXTERNAL (out-of-line, no compress)
    • MAIN (compress, keep in-line if possible)
  • The default EXTENDED is usually best.
  • Control the strategy with ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy, and the threshold with ALTER TABLE tbl SET (toast_tuple_target = 4096).
  • Case-insensitive text:
    • For locale or accent handling, use non-deterministic collations.
    • For plain ASCII, use expression indexes on LOWER(col) (preferred unless the column needs a case-insensitive PK/FK/UNIQUE) or CITEXT.
• Money: NUMERIC(p,s) (never float).
• Time: use TIMESTAMPTZ for timestamps, DATE for date-only values, and INTERVAL for durations.
  • Avoid TIMESTAMP (without timezone).
  • Use now() for transaction start time and clock_timestamp() for current wall-clock time.
• Booleans: BOOLEAN with a NOT NULL constraint, unless you need tri-state values.
• Enums: use CREATE TYPE ... AS ENUM for small, stable sets (e.g. US states, days of week). For values driven by business logic that change over time (e.g. order statuses), use TEXT (or INT) + CHECK, or a lookup table.
• Arrays: TEXT[], INTEGER[], etc.
  • Use them for ordered lists where you query elements. Good for tags and categories.
  • Avoid them for relations; use junction tables instead.
  • Index with GIN for containment (@>, <@) and overlap (&&) queries.
  • Access: arr[1] (1-indexed), arr[1:3] (slicing).
  • Literal syntax: '{val1,val2}' or ARRAY[val1,val2].
• Range types: daterange, numrange, tstzrange for intervals.
  • They support overlap (&&), containment (@>) and other operators. Index with GiST.
  • Good for scheduling, versioning and 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. They 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 the language: to_tsvector('english', col) and to_tsquery('english', 'query').
  • Never use the single-argument versions. This applies to both index expressions and queries.
• Domain types: use CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') for reusable custom types with validation. A domain enforces its constraints across tables. This pattern is only an illustrative sanity check: it accepts whitespace and malformed addresses. Handle real email validation in the application.
• Composite types: CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) for structured data within columns. Access fields with (col).field syntax.
• JSONB: preferred over JSON; index with GIN.
  • Use only for optional or semi-structured attributes.
  • Use JSON ONLY if the original ordering of the contents MUST be preserved.
• Vector types: the vector type from pgvector for vector similarity search over embeddings.

Do not use the following data types

• DO NOT use timestamp (without time zone); DO use timestamptz instead.
• DO NOT use char(n) or varchar(n); DO use text instead.
• DO NOT use money type; DO use numeric instead.
• DO NOT use timetz type; DO use timestamptz instead.
• DO NOT use timestamptz(0) or any other precision specification; DO use timestamptz instead
• DO NOT use serial type; DO use generated always as identity instead.

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.
  • An unlogged table is emptied after a crash.
  • It is not replicated to standbys and is left out of physical backups.
  • Use it only for fully rebuildable data such as caches and staging.

Row-Level Security

Enable it with ALTER TABLE tbl ENABLE ROW LEVEL SECURITY. Then create policies: CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id()). This gives built-in user-based access control at the row level.

• Bypass: table owners, superusers and roles with the bypass attribute skip policies by default.
  • Also force row level security on the table so policies apply to the owner.
  • Have the application connect as a role that does not own the table and has no bypass rights.
• Write paths: a policy written only for reads leaves inserts and updates unrestricted.
  • Add policies for write commands.
  • Give each one a using clause and a with check clause, so new and changed rows are validated too.
• Identity: current_user_id() is not built in. It is a placeholder for a function the application defines.
  • That function must derive the user identity from a value set on the server side for each transaction.
  • Clients must have no way to set or spoof that value.

Constraints

• PK: implies UNIQUE + NOT NULL and creates a B-tree index.
• FK: specify an ON DELETE/UPDATE action (CASCADE, RESTRICT, SET NULL, SET DEFAULT).
  • Prefer restrict as the safer default. Cascading deletes can silently remove large numbers of dependent rows, so choose cascade deliberately and only where dependent rows have no independent value.
  • Add an explicit index on the referencing column. It speeds up joins and prevents locking issues on parent deletes and updates.
  • Use DEFERRABLE INITIALLY DEFERRED for circular FK dependencies, which are then checked at transaction end.
• UNIQUE: creates a B-tree index and allows multiple NULLs unless you use NULLS NOT DISTINCT (PG15+).
  • Standard behavior: (1, NULL) and (1, NULL) are both allowed.
  • With NULLS NOT DISTINCT: only one (1, NULL) is allowed.
  • Prefer NULLS NOT DISTINCT unless you specifically need duplicate NULLs.
• CHECK: row-local constraints. NULL values pass the check (three-valued logic).
  • Example: CHECK (price > 0) allows NULL prices.
  • Combine with NOT NULL to enforce the rule: price NUMERIC NOT NULL CHECK (price > 0).
• EXCLUDE: prevents overlapping values using operators. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) prevents double-booking rooms. It requires a suitable index type (often GiST).

Indexing

• B-tree: default for equality and range queries (=, <, >, BETWEEN, ORDER BY).
• Composite: order matters. The index is used when there is equality on the leftmost prefix.
  • WHERE a = ? AND b > ? uses an index on (a,b), but WHERE b = ? does not.
  • Put the most selective or most frequently filtered columns first.
• Covering: CREATE INDEX ON tbl (id) INCLUDE (name, email) adds non-key columns, enabling index-only scans that never visit the table.
• Partial: for hot subsets (WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'). Any query with status = 'active' can use this index.
• Expression: for computed search keys (CREATE INDEX ON tbl (LOWER(email))). The expression must match exactly in the WHERE clause: WHERE LOWER(email) = 'user@example.com'.
• GIN: JSONB containment and existence, arrays (@>, ?), full-text search (@@).
• GiST: ranges, geometry, exclusion constraints.
• BRIN: very large, naturally ordered data (time-series), with minimal storage overhead. It works well when row order on disk correlates with the indexed column (insertion order or after CLUSTER).

Partitioning

• Use partitioning for very large tables (>100M rows) where queries consistently filter on the partition key (often time or date).
• Another use: tables whose data maintenance requires it, e.g. data pruned or bulk replaced periodically.
• RANGE: common for time-series (PARTITION BY RANGE (created_at)).
  • Create partitions: CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01').
  • TimescaleDB handles time-based or ID-based partitioning, along with retention policies and compression.
• LIST: for discrete values (PARTITION BY LIST (region)). Example: FOR VALUES IN ('us-east', 'us-west').
• HASH: for even distribution when there is no natural key (PARTITION BY HASH (user_id)). It creates N partitions using a modulus.
• Partition pruning: declarative partitioning (PG10+) prunes partitions using the partition bounds themselves. CHECK-based constraint exclusion applies only to legacy inheritance partitioning.
• Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
• Limitations:
  • There are no global UNIQUE constraints, so include the partition key in the PK/UNIQUE.
  • Modern releases support foreign keys on partitioned tables. Use native foreign keys rather than triggers.

Special Considerations

Update-Heavy Tables

• Separate hot/cold columns: put frequently updated columns in a separate table to minimize bloat.
• Use fillfactor=90 to leave space for HOT updates, which avoid index maintenance.
• Avoid updating indexed columns, because that prevents the beneficial HOT updates.
• Partition by update patterns: keep 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 give much faster writes. Their contents are emptied after a crash and are not replicated or backed up.
• Defer index creation for bulk loads: drop index, load data, recreate indexes.
  • Apply this only to non-unique indexes. Keep primary key and unique indexes in place so integrity is enforced during the load; duplicates would also make the recreate step fail.
  • Do this on staging or offline loads, with user confirmation.
  • Prefer the concurrent forms of drop and create index.
• Partition by time/hash to distribute load. TimescaleDB handles partitioning and compression of insert-heavy data.
• Use a natural key for primary key, such as (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 a UNIQUE index on the conflict target columns: ON CONFLICT (col1, col2) needs a matching unique index. A partial unique index also works if the conflict target repeats the index's WHERE predicate.
• Use EXCLUDED.column to reference the would-be-inserted values. Only update columns that actually changed, to reduce write overhead.
• DO NOTHING is faster than DO UPDATE when no actual update is needed.

Safe Schema Evolution

• Safety first: before running schema changes or destructive statements against a live database:
  • get explicit user confirmation
  • verify a recent backup
  • confirm the target environment
  • set lock and statement timeouts
• Transactional DDL: most DDL operations can run in transactions and be rolled back, e.g. BEGIN; ALTER TABLE...; ROLLBACK;. This still takes heavy locks, so do such testing on a staging copy rather than in production.
• Concurrent index creation: CREATE INDEX CONCURRENTLY avoids blocking writes but can't run in transactions.
• Volatile defaults cause rewrites: adding any column with a volatile default (e.g., now(), gen_random_uuid()) rewrites the entire table under an exclusive lock, whether or not the column is NOT NULL. In modern releases, non-volatile defaults are a fast, metadata-only change.
• Drop constraints before columns: run ALTER TABLE DROP CONSTRAINT then DROP COLUMN to avoid dependency issues. Treat both as destructive and get user confirmation first.
• Function signature changes: CREATE OR REPLACE with different arguments creates overloads, not replacements. DROP the old version if you don't want an overload.

Generated Columns

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

Extensions

• Install extensions only from trusted, official package sources or a managed provider's approved list, with pinned versions. Installing them may need elevated privileges, and their licensing should be reviewed first.
• pgcrypto: prefer hashing passwords in the application with a slow, modern password hashing algorithm, and store only the hash.
  • Passwords sent through SQL can appear in statement logs, query statistics and audit trails.
  • crypt() is a fallback. If you use it, choose the strongest salt type with a high cost factor.
• uuid-ossp: gen_random_uuid() is built into modern PostgreSQL and uuidv7() is built into PG18+, so new projects need no extension for UUIDs. Use uuid-ossp or pgcrypto only on older versions.
• pg_trgm: fuzzy text search with the % operator and similarity() function. Index with GIN to accelerate LIKE '%pattern%'.
• 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., a GIN index on both JSONB and text columns).
• hstore: key-value pairs. Mostly superseded by JSONB but useful for simple string mappings.
• timescaledb: consider for time-series. It provides partitioning, retention, compression and continuous aggregates. Review its licensing, since features are split across licenses.
• postgis: consider for location-based applications. It provides comprehensive geospatial support beyond the basic geometric types.
• pgvector: vector similarity search for embeddings.
• pgaudit: audit logging scoped to the needed classes, such as schema changes, role changes and writes on sensitive tables.
  • Logged statement text may contain secrets and personal data.
  • Restrict access to the logs and apply a retention policy.

JSONB Guidance

• Prefer JSONB with a 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"}'])
• For heavy @> workloads, consider the jsonb_path_ops opclass for smaller, faster containment-only indexes:
  • CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
  • Trade-off: it loses support for key existence (?, ?|, ?&) queries and only supports containment (@>).
• For equality or range on a specific scalar field, extract it and index it 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 an index.
• Arrays inside JSONB: use GIN + @> for containment (e.g., tags). Consider jsonb_path_ops if you only do containment.
• Keep core relations in tables. Use JSONB for optional or variable attributes.
• Use constraints to limit the 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,
  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);