postgres-code-review

What This Skill Does

The postgres-code-review skill is a specialized diagnostic tool designed to analyze PostgreSQL-specific code patterns, database schemas, and application-level database interaction logic. It acts as an expert DBA reviewer that inspects your implementation for common bottlenecks, security risks, and architectural anti-patterns. By focusing on four pillars—indexing, JSONB utilization, connection management, and transaction safety—the skill ensures that your database layer remains performant, resilient, and secure.

Installation

To integrate this skill into your OpenClaw environment, execute the following command in your terminal: clawhub install openclaw/skills/skills/anderskev/postgres-code-review

Use Cases

This skill is ideal for:

• Auditing performance of SELECT queries involving complex JOINs and WHERE clauses.
• Optimizing JSONB field access, including verifying appropriate GIN or BTREE index support for document storage.
• Debugging connection leakage in high-concurrency environments or ensuring proper cleanup in Python/Node/Go database drivers.
• Analyzing transactional logic to identify race conditions, deadlocks, or inappropriate isolation level usage.
• Verifying the implementation of application-level locking using advisory locks.

Example Prompts

1. "Review this code snippet for potential connection leaks and check if the WHERE clause will hit an index: [Paste Code]"
2. "I am seeing high CPU usage on my Postgres instance with JSONB queries. Can you check if my schema and operators are optimized?"
3. "Analyze these three functions for transaction safety and potential deadlocks: [Link or Code]"

Tips & Limitations

To get the best results, provide the schema definition alongside your application code. The skill is highly accurate at identifying static code issues, but cannot execute queries against your live database to measure latency. Always test suggested index changes in a staging environment using EXPLAIN ANALYZE before applying them to production. If your database uses custom extensions like PostGIS or Citus, explicitly mention these in your prompt, as optimization strategies may differ significantly from standard Postgres configurations.