PostgreSQL

What This Skill Does

The PostgreSQL skill for OpenClaw provides advanced database optimization and engineering capabilities. It assists developers in designing robust schemas, crafting high-performance SQL queries, and troubleshooting common PostgreSQL pitfalls such as indexing inefficiencies, connection management, and bloat. Whether you are scaling a production application, migrating from legacy SERIAL types, or debugging slow performance via EXPLAIN ANALYZE, this agent acts as your senior DBA.

Installation

Run the following command in your terminal to integrate the skill into your project: clawhub install openclaw/skills/skills/ivangdavila/pg

Use Cases

• Database Schema Design: Refactor legacy schemas by replacing SERIAL with GENERATED ALWAYS AS IDENTITY and ensuring optimal data types like TIMESTAMPTZ.
• Performance Tuning: Audit your existing indexes to remove unused entries that hinder write operations and add missing covering indexes to satisfy EXPLAIN (ANALYZE, BUFFERS) requirements.
• Query Optimization: Rewrite complex queries using modern PostgreSQL patterns like DISTINCT ON or IS NOT DISTINCT FROM to ensure correctness and speed.
• Production Stability: Analyze connection pooling needs, suggest statement timeouts, and configure autovacuum settings to prevent transaction wraparound and table bloat.

Example Prompts

1. "Analyze this EXPLAIN output for a slow query; I'm seeing 'Heap Fetches: 1000' and the index isn't being fully utilized. How should I adjust my index?"
2. "I have a massive table that is frequently updated. What are the best practices for managing bloat and autovacuum settings without locking the production table?"
3. "Convert this legacy table definition to modern PostgreSQL standards, ensuring I use the correct types for money and timestamps, and suggest a partial index for the 'active' status column."

Tips & Limitations

• Indexes are double-edged: While they accelerate reads, excessive indexing will drastically slow down INSERT/UPDATE operations. Always check pg_stat_user_indexes before adding new ones.
• Monitor Bloat: Do not ignore autovacuum. If your tables have high volatility, consider pg_repack for maintenance.
• Data Types Matter: Avoid float for currency calculations; use NUMERIC or integer cents to maintain precision.
• Connection Limits: If you exceed 50 concurrent connections, do not rely solely on PostgreSQL; implement PgBouncer at the transaction level to prevent memory exhaustion.