# PostgreSQL taste of a backend engineer - Use bigint identity columns (GENERATED ALWAYS AS IDENTITY) for primary keys; reach for UUIDv7 only when ids must be generated outside the database. Confidence: 0.80 - Store timestamps as timestamptz in UTC, never timestamp without time zone. Confidence: 0.95 - Use text with a CHECK constraint instead of varchar(n) for length limits. Confidence: 0.80 - Store money as numeric or as integer minor units, never as float. Confidence: 0.95 - Name tables in plural snake_case and foreign keys as <singular>_id. Confidence: 0.70 - Declare NOT NULL by default and allow NULL only when "unknown" is a real state. Confidence: 0.85 - Enforce invariants in the database with foreign keys, UNIQUE, and CHECK constraints, not only in application code. Confidence: 0.90 - Index every foreign key column that is used in joins or cascading deletes. Confidence: 0.85 - Read EXPLAIN (ANALYZE, BUFFERS) before adding an index, and again after to prove it helped. Confidence: 0.90 - Prefer partial and expression indexes over wide composite indexes for skewed filters. Confidence: 0.70 - Create indexes on large tables with CREATE INDEX CONCURRENTLY. Confidence: 0.90 - Use jsonb for genuinely schemaless attributes, but promote fields you filter on into real columns. Confidence: 0.80 - Paginate with keyset (WHERE id > $last ORDER BY id LIMIT n) instead of large OFFSET values. Confidence: 0.85 - Keep transactions short and never hold one open across a network call. Confidence: 0.90 - Set statement_timeout and idle_in_transaction_session_timeout per application role. Confidence: 0.85 - Put a connection pooler such as PgBouncer in transaction mode in front of the database. Confidence: 0.80 - Use SELECT ... FOR UPDATE SKIP LOCKED for simple job queues before adding a separate broker. Confidence: 0.70 - Use INSERT ... ON CONFLICT for idempotent upserts. Confidence: 0.85 - Give each service its own role with least privilege; the application never connects as a superuser. Confidence: 0.90 - Turn on pg_stat_statements and review the slowest queries every week. Confidence: 0.85 - Write CTEs for readability, knowing Postgres 12+ inlines them unless you add MATERIALIZED. Confidence: 0.65 - Test point-in-time recovery from backups regularly; an untested backup does not count. Confidence: 0.95
Reviews PostgreSQL and MySQL tables (schema.sql, a dump or a pull request's CREATE TABLE) for design flaws that are cheap to fix before data exists - missing primary keys, money in float columns, foreign keys with mismatched types or no index, 32-bit ids, timestamps without time zone, nullable flags, free-text statuses, reserved names and case-sensitive unique emails. Returns corrected DDL. Includes a stdlib Python linter with --self-test.
Reviews a schema and slow SQL queries, proposes composite indexes in the right column order (equality, sort, range) with covering columns and tradeoffs, flags non-sargable filters like functions on columns and leading-wildcard LIKE, and warns about duplicate, redundant, and low-selectivity indexes. Use when a user shares a slow query, EXPLAIN output, or schema and asks "what index do I need?" or "too many indexes?". Includes a tested stdlib Python helper.
Reviews PostgreSQL and MySQL schema migrations (raw SQL or ORM-generated) for table locks, rewrites, data loss, and breaking changes, then proposes safe zero-downtime rewrites with a clear verdict.
Reviews SQL queries and optional EXPLAIN output for common performance problems such as SELECT *, functions or casts on indexed columns, leading-wildcard LIKE, N+1 query loops, OR across columns vs UNION ALL, AND/OR precedence slips, NOT IN subqueries, deep OFFSET paging, seq scans and disk sorts. Explains each problem and gives a rewritten query. Includes a stdlib Python checker with --self-test.
