postgres-perf

What This Skill Does

The postgres-perf skill is a specialized technical assistant designed to optimize PostgreSQL databases, ensuring peak performance, security, and scalability. It provides comprehensive guidance on SQL query optimization, database schema architecture, advanced indexing strategies, and connection pool management. Whether you are debugging slow queries using EXPLAIN ANALYZE, designing robust RLS (Row Level Security) policies, or managing concurrency in high-traffic environments, this skill delivers expert-level insights aligned with industry best practices.

Installation

You can install the skill by executing the following command in your terminal within the OpenClaw environment: clawhub install openclaw/skills/skills/kjaylee/postgres-perf

Use Cases

• Query Optimization: Analyzing slow SELECT, UPDATE, or DELETE operations to identify bottlenecks like sequential scans.
• Schema Architecture: Deciding between normalized designs or strategic denormalization and implementing efficient indexing strategies like partial or composite indexes.
• Security Hardening: Configuring Row Level Security (RLS) policies for multi-tenant applications while ensuring performance overhead is minimized.
• Concurrency Management: Refactoring long-running transactions to avoid lock contention and deadlocks in highly concurrent environments.
• Connection Tuning: Guidance on implementing pgBouncer or serverless connection pooling to prevent exhaustion.

Example Prompts

1. "My query to fetch active user orders is taking over 2 seconds; can you help me optimize the schema or the SQL using EXPLAIN ANALYZE?"
2. "I need to implement RLS for my multi-tenant blog system. How can I ensure the policies don't degrade the performance of the index lookups?"
3. "Is it better to use UUIDs or sequence integers for my primary keys in a distributed system, and how does this affect indexing overhead?"

Tips & Limitations

• Habitual Profiling: Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) during development to understand how Postgres executes your logic.
• Selective Indexing: Avoid 'index everything' syndrome. Excess indexes degrade write performance and increase storage costs. Utilize partial indexes (WHERE clauses) for frequently queried subsets.
• Connection Discipline: Never connect directly to the DB in serverless environments; always use connection poolers like pgBouncer.
• Transaction Management: Keep transactions as short as possible to prevent row locks from affecting other sessions.
• Limitations: This skill is a consultant tool; it cannot directly execute DDL/DML commands on your production database without your explicit authorization and review of the proposed migrations.