Postgres Conventions

Applies Database Design Basics@markdowners/database-design-basics specifically to PostgreSQL — its type system and extensions give you more precise tools than the generic rules alone cover.

Naming

• Use snake_case for all identifiers (tables, columns, functions) — Postgres folds unquoted identifiers to lowercase, so mixed-case names silently require quoting everywhere forever once introduced; avoid the problem entirely by never using mixed case.
• Name tables in the plural (modules, users), foreign key columns as <singular_referenced_table>_id (author_id referencing profiles), and junction tables as <table_a>_<table_b> in a consistent, alphabetically-or-logically fixed order.

Types worth knowing

• Use citext for case-insensitive unique text (usernames, emails) instead of text plus a manually lowercased comparison — it enforces the invariant at the type level instead of relying on every query remembering to lowercase both sides.
• Use timestamptz, never bare timestamp, for anything that records a real-world moment — timestamp silently discards timezone information, which becomes a bug the moment the app or its users span more than one timezone.
• Use jsonb (not json) for semi-structured data that needs querying or indexing — jsonb is binary-stored and indexable; json is stored as text and re-parsed on every access.
• Use native enum types or a text plus CHECK constraint for closed sets — prefer CHECK when the set might grow, since altering a native enum type has historically had more operational friction around adding new values inside a transaction.

Constraints and indexes

• Add a partial index (WHERE status = 'published') when a query pattern only ever filters a large table down to a specific, common subset — it's smaller and faster than indexing the whole table for that access pattern.
• Use trigram (gin_trgm_ops) indexes for full-text or fuzzy/substring search (ILIKE '%term%') — a plain b-tree index cannot serve those query patterns at all.
• Always name constraints explicitly (a named check (...), not an anonymous one) so a future constraint-violation error message is diagnosable instead of a cryptic auto-generated identifier.

Row-level security

• If using RLS, enable it explicitly per table and write policies that are readable as plain English (using (author_id = auth.uid())) — untested or overly permissive RLS policies are a common, hard-to-notice source of data leaks, since a missing policy fails differently depending on configuration and default-deny setup.

Performance

• Use EXPLAIN ANALYZE on any query suspected of being slow before guessing at an index — intuition about which index would help is wrong often enough that it's not worth skipping the actual query plan.