postgres-code-review

Reviews PostgreSQL code for indexing strategies, JSONB operations, connection pooling, and transaction safety. Use when reviewing SQL queries, database schemas, JSONB usage, or connection management.

⭐5stars🔀1forks📁View on GitHub🕐Updated Jan 3, 2026

When & Why to Use This Skill

The PostgreSQL Code Review skill is a specialized tool designed to audit and optimize database-related code. It focuses on critical performance and safety aspects including indexing strategies, JSONB operations, connection pooling, and transaction integrity, ensuring your database layer is efficient, secure, and scalable.

Use Cases

  • Performance Tuning: Identify missing indexes or inefficient query patterns to eliminate sequential scans and reduce query latency.
  • JSONB Optimization: Review complex JSONB queries and GIN index usage to ensure high-performance document storage and retrieval within PostgreSQL.
  • Resource Management: Audit connection handling logic to prevent resource leaks and optimize connection pool settings for high-traffic applications.
  • Concurrency & Safety: Validate transaction isolation levels and locking strategies to prevent deadlocks and ensure data consistency in multi-user environments.
  • Security Auditing: Scan SQL queries for parameterized statements to prevent SQL injection and ensure secure database interactions.
namepostgres-code-review
descriptionReviews PostgreSQL code for indexing strategies, JSONB operations, connection pooling, and transaction safety. Use when reviewing SQL queries, database schemas, JSONB usage, or connection management.

PostgreSQL Code Review

Quick Reference

Issue Type Reference
Missing indexes, wrong index type, query performance references/indexes.md
JSONB queries, operators, GIN indexes references/jsonb.md
Connection leaks, pool configuration, timeouts references/connections.md
Isolation levels, deadlocks, advisory locks references/transactions.md

Review Checklist

  • WHERE/JOIN columns have appropriate indexes
  • Composite indexes match query patterns (column order matters)
  • JSONB columns use GIN indexes when queried
  • Using proper JSONB operators (->, ->>, @>, ?)
  • Connection pool configured with appropriate limits
  • Connections properly released (context managers, try/finally)
  • Appropriate transaction isolation level for use case
  • No long-running transactions holding locks
  • Advisory locks used for application-level coordination
  • Queries use parameterized statements (no SQL injection)

When to Load References

  • Reviewing SELECT queries with WHERE/JOIN → indexes.md
  • Reviewing JSONB columns or JSON operations → jsonb.md
  • Reviewing database connection code → connections.md
  • Reviewing BEGIN/COMMIT or concurrent updates → transactions.md

Review Questions

  1. Will this query use an index or perform a sequential scan?
  2. Are JSONB operations using appropriate operators and indexes?
  3. Are database connections properly managed and released?
  4. Is the transaction isolation level appropriate for this operation?
  5. Could this cause deadlocks or long-running locks?