postgresql-table-design

What it does

• 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/sorts, and join keys.
• Identifiers: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use snakecase for table/column names.
• Unique + NULLs: UNIQUE allows multiple NULLs. Use UNIQUE (...) NULLS NOT DISTINCT (PG15+) to restrict to one NULL.
• FK indexes: PostgreSQL does not auto-index FK columns. Add them.
• MVCC: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.
• Integers: prefer BIGINT unless storage space is critical; INTEGER for smaller ranges; avoid SMALLINT unless constrained.

Requirements & configuration

• EXCLUDE: prevents overlapping values using operators. EXCLUDE USING gist (roomid WITH =, bookingperiod WITH &&) prevents double-booking rooms. Requires appropriate index type (often GiST).
• Constraint exclusion: requires CHECK constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).
• Use a natural key for primary key such as a (timestamp, deviceid) 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.
• Requires UNIQUE index on conflict target columns—ON CONFLICT (col1, col2) needs exact matching unique index (partial indexes don't work).

Configuration: BIGINTUUIDNUMERICUNIQUECLUSTERGENERATEDINTEGERSMALLINT