postgresql-expert-best-practices-code-review
PostgreSQL database design, migration, and performance optimization best practices. This skill should be used when writing, reviewing, or refactoring database schemas, migrations, or query patterns. Triggers on tasks involving PostgreSQL databases, schema design, migration optimization, or data modeling.
Works with
--- name: postgresql-expert-best-practices-code-review description: PostgreSQL database design, migration, and performance optimization best practices. This skill should be used when writing, reviewing, or refactoring database schemas, migrations, or query patterns. Triggers on tasks involving PostgreSQL databases, schema design, migration optimization, or data modeling. license: MIT --- # PostgreSQL Expert Best Practices Simple, pragmatic, opinionated. Only what matters for writing production-grade PostgreSQL queries. ## When to Apply Reference these guidelines when: - Writing database migrations or schema changes - Creating or modifying PostgreSQL tables and columns - Adding indexes, constraints, or foreign keys - Reviewing database schema for performance issues - Refactoring existing database structures - Optimizing query performance or database design ## Rule Categories by Priority | Priority | Category | Impact | Prefix | |----------|----------|--------|--------| | 1 | Index Management | CRITICAL-HIGH | `only-concurrent-indexes`, `add-index-for-foreign-key` | | 2 | Constraint Safety | HIGH | `unique-constraint`, `split-foreign-key`, `set-column-not-null` | | 3 | Schema Design | MEDIUM | `only-jsonb`, `always-include-columns`, `limit-non-unique-index` | | 4 | Naming Standards | LOW | `index-naming-standards`, `column-naming-standards` | ## Quick Reference - `only-concurrent-indexes` - Always use CONCURRENTLY to prevent blocking writes during index creation - `add-index-for-foreign-key` - Create indexes for foreign keys to improve query performance - `unique-constraint` - Split unique constraint creation into concurrent index + constraint steps - `split-foreign-key` - Add foreign keys without validation first, then validate separately - `set-column-not-null` - Use check constraints before setting NOT NULL to avoid table locks - `only-jsonb` - Use JSONB instead of JSON for better performance and indexing capabilities - `always-include-columns` - Include id, created_at, and updated_at in all tables for auditability - `limit-non-unique-index` - Limit non-unique indexes to maximum three columns for efficiency - `index-naming-standards` - Use consistent index naming: idx_tablename_columnname - `column-naming-standards` - Maintain consistent snake_case naming and use id suffix for foreign keys ## How to Use Read individual rule files for detailed explanations and code examples: ``` rules/only-concurrent-indexes.md rules/add-index-for-foreign-key.md rules/unique-constraint.md rules/split-foreign-key.md rules/set-column-not-null.md rules/only-jsonb.md rules/always-include-columns.md rules/limit-non-unique-index.md rules/index-naming-standards.md rules/column-naming-standards.md ``` Each rule file contains: - Brief explanation of why it matters - Impact level and description - Incorrect migration example with explanation - Correct migration example with best practices - Additional context and references
More Database skills
supabase-postgres-best-practices
supabase/agent-skills
Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pg_cron, pgmq), vector/semantic search (pgvector), and restoring dumps (pg_restore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query.
prisma-database-setup
prisma/skills
Guides for configuring Prisma with different database providers (PostgreSQL, MySQL, SQLite, MongoDB, etc.). Use when setting up a new project, changing databases, or troubleshooting connection issues. Triggers on "configure postgres", "connect to mysql", "setup mongodb", "sqlite setup".
prisma-postgres
prisma/skills
Prisma Postgres setup and operations guidance across Console, create-db CLI, Management API, and Management API SDK. Use when creating Prisma Postgres databases, working in Prisma Console, provisioning with create-db/create-pg/create-postgres, or integrating programmatic provisioning with service tokens or OAuth.
