postgresql
via PatrickJS/awesome-cursorrules
PostgreSQL production best practices: safe migrations, parameterized queries, and indexing strategy.
What is postgresql?
Expert guidance for PostgreSQL development covering schema design, query safety, indexing, and migrations. Use this rule when writing SQL for production systems to ensure data integrity, performance, and security.
- Enforce TIMESTAMPTZ for all timestamps and UUID/BIGSERIAL for keys
- Require parameterized queries and explicit column selection to prevent SQL injection
- Guide index strategy including FK indexing and concurrent index creation
- Establish safe migration patterns with versioning and rollback testing
- Enforce transaction safety with explicit BEGIN/COMMIT and row locking
- Prevent dangerous patterns like SELECT *, string interpolation, 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
Focused PR review framework with severity ranking, file citations, and four review angles: security, performance, tests, architecture.
Standardized PR templates for structured code reviews and team collaboration.
Create well-structured project epics and user stories aligned with agile methodologies.
Expert FastAPI backend development with async patterns, Pydantic validation, and scalable API design.
PyQt6 GUI development with EEG signal processing and real-time analysis integration.
PySpark ETL development with idiomatic code style, joins, window functions, and Iceberg patterns.