postgresql
via PatrickJS/awesome-cursorrules
Safe PostgreSQL migrations, parameterized queries, and production-grade indexing strategy.
What is postgresql?
Expert PostgreSQL rules for production systems covering schema design, query safety, and migration patterns. Use this rule when writing SQL, designing schemas, or managing database changes to enforce parameterized queries, proper indexing, safe migrations, and transaction handling.
- Enforce TIMESTAMPTZ for all timestamps and UUID/BIGSERIAL for IDs
- Require parameterized queries and explicit column selection (no SELECT *)
- Index foreign keys and use CREATE INDEX CONCURRENTLY for live tables
- Implement versioned migrations with rollback testing and multi-step large changes
- Apply statement timeouts and row-level locking for transaction safety
- Prevent string interpolation, schema changes during peak traffic, and plaintext passwords
Applies to
File patterns this rule matches.
Rule definition (reference)
Source of truth, from the repository.
PostgreSQL Rules
Expert PostgreSQL developer. Safe migrations, parameterized queries, proper indexing.
Schema
- TIMESTAMPTZ for all timestamps (not TIMESTAMP without timezone)
- UUID for public IDs, BIGSERIAL for internal keys
- NOT NULL by default — nullable only when intentional
- FK with explicit ON DELETE behavior
- Check constraints for domain invariants
Queries
- Parameterized always — never string interpolation
- SELECT explicit columns, never SELECT *
- LIMIT on all potentially large result sets
- EXPLAIN ANALYZE before shipping complex queries
Indexes
- Index every FK column
- CREATE INDEX CONCURRENTLY for live tables (non-blocking)
- Partial indexes for frequently filtered subsets
- Remove unused indexes
Migrations
- Versioned files: V001__create_table.sql
- Large column additions: multi-step with backfill
- Test rollback before deploying
Transactions
- Explicit BEGIN/COMMIT for multi-statement changes
- statement_timeout to prevent runaway queries
- SELECT ... FOR UPDATE for row locking
Forbidden
- No SELECT *
- No string-interpolated SQL
- No schema changes during peak traffic
- No plaintext passwords in DB
- No TRUNCATE in app code
Related rules
Senior full-stack TypeScript, React, Node.js guidance with clean architecture, testing, and WHY-oriented reasoning.
Quantitative factor research skills for designing, evaluating, and mining alpha factors in equities markets.
Android development with Jetpack Compose, clean architecture, and Material Design 3.
Angular development with Novo Elements UI library using standalone components.
Expert Angular 18 + TypeScript development with Jest, emphasizing clean code and performance.
Manage Kubernetes clusters, add-ons, stacks, and credentials via the Ankra CLI platform.