PostgreSQL — Technology Guidance

Scope: organization
Status: enabled
Path: technologies/postgresql.md

Overview

PostgreSQL is the organization's primary relational database. This document covers naming conventions, query patterns, index strategy, and operational rules that apply to all services using Postgres.

Schema Conventions

Naming

Tables: lowercase snake_case, plural nouns (users, audit_events, api_keys).
Columns: lowercase snake_case. Primary key is always id. Created/updated timestamps are always created_at / updated_at with type TIMESTAMPTZ.
Foreign keys: {referenced_table_singular}_id (e.g., user_id, tenant_id).
Indexes: idx_{table}_{columns} (e.g., idx_users_email, idx_events_tenant_id_created_at).
Constraints: chk_{table}_{condition}, uq_{table}_{columns}.

Required Columns on Every Table

id          BIGSERIAL PRIMARY KEY,
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()

Add an updated_at trigger or handle it at the ORM layer — the column must stay accurate.

Migrations

Use Alembic (Python) or Flyway (JVM) for schema migrations. Raw psql scripts applied manually are prohibited in production.
Every migration must be reversible. Write both upgrade() and downgrade().
Never alter a column type or drop a column in the same migration that removes code using it — separate the migration from the code change by at least one deploy cycle (expand/contract pattern).
Migrations run in CI before tests. If the migration fails, the PR cannot merge.

Safe vs. Unsafe Operations

Operation	Safety	Notes
Add nullable column	✅ Safe	Instant
Add column with default (Postgres 11+)	✅ Safe	Metadata-only
Add index `CONCURRENTLY`	✅ Safe	Runs without table lock
Drop unused index	✅ Safe	No data change
Add `NOT NULL` constraint	⚠️ Careful	Rewrites table; use check + validate pattern
Add plain index (not concurrent)	⚠️ Careful	Locks table during build
Drop column	⚠️ Careful	Follow expand/contract
Rename column/table	❌ Unsafe	Breaks existing queries without a view bridge

Query Patterns

Always Use Parameterized Queries

See cwes/remediation/CWE-89.md for the full rationale. Short form: no string interpolation in queries.

Avoid `SELECT *`

Specify columns explicitly. SELECT * breaks when columns are added and may fetch data the caller never uses.

Pagination

Use keyset (cursor) pagination for large, frequently-updated tables. Offset pagination degrades at high page numbers:

-- ✅ Keyset — O(log n) regardless of page depth
SELECT id, name, created_at
FROM users
WHERE created_at < :cursor_timestamp
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- ⚠️ Offset — scans all prior rows at high offsets
SELECT id, name FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

N+1 Query Detection

Always check query counts in tests for list endpoints. Use SQLAlchemy's selectinload / joinedload for related objects rather than hitting the DB inside a loop.

Index Strategy

Every foreign key column must have an index (Postgres does not add these automatically).
Add composite indexes for queries that filter on multiple columns — order matters: put the most selective column first.
Use pg_stat_user_indexes to identify unused indexes before adding new ones.
Text search: use tsvector + GIN index, not LIKE '%term%' on large tables.

-- Full-text search index
ALTER TABLE knowledge_documents
  ADD COLUMN body_tsv TSVECTOR
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;

CREATE INDEX idx_knowledge_documents_body_tsv
  ON knowledge_documents USING GIN (body_tsv);

Connection Management

Always use a connection pool (pgBouncer or the driver's built-in pool). Direct connections from application code without pooling are prohibited on production databases.
Set statement_timeout and lock_timeout on application connections to prevent runaway queries from blocking migrations or other traffic:

# SQLAlchemy engine setup
engine = create_async_engine(
    DATABASE_URL,
    connect_args={
        "server_settings": {
            "statement_timeout": "30000",   # 30 s
            "lock_timeout": "5000",         # 5 s
        }
    },
    pool_size=10,
    max_overflow=20,
)

Operational Rules

Backups are automated. Verify restore procedures quarterly.
Never run DDL on prod directly. All schema changes go through migrations in CI/CD.
EXPLAIN ANALYZE any new query expected to run against tables > 100k rows before shipping it.
Long-running transactions (> 30 s) block autovacuum and cause table bloat. Avoid transactions that span network calls.

FilesExpand file tree

postgresql.md

Latest commit

History