postgresql_skill

Overview

This skill provides practical, hands-on guidance for working with PostgreSQL databases. It covers SQL syntax, advanced features like window functions and CTEs, JSON support, full-text search, and performance tuning. Use it to write complex queries, design schemas, and diagnose performance problems. The guidance focuses on actionable examples and best practices for production systems.

How this skill works

The skill inspects user questions and database scenarios to produce targeted SQL examples, configuration recommendations, and troubleshooting steps. It explains query plans, index strategies, and storage settings, and generates sample SQL or psql commands you can run directly. Where relevant, it outlines risks and trade-offs for options such as partitioning, replication, and vacuum settings.

When to use it

• Writing or optimizing complex SQL queries (joins, window functions, CTEs).
• Modeling schemas and choosing data types including JSON/JSONB.
• Implementing or tuning full-text search with tsvector and GIN indexes.
• Diagnosing slow queries using EXPLAIN/ANALYZE and indexing advice.
• Configuring performance settings (work_mem, shared_buffers, autovacuum).

Best practices

• Prefer explicit column lists and parameterized queries to avoid inefficiency and SQL injection.
• Use JSONB for semi-structured data and index frequently queried keys with GIN if needed.
• Analyze query plans with EXPLAIN (ANALYZE, BUFFERS) before adding indexes.
• Keep autovacuum tuned for your workload; monitor bloat and VACUUM / REINDEX when necessary.
• Use connection pooling (pgbouncer) for high-concurrency applications.

Example use cases

• Convert a slow JOIN into an indexed join and show before/after EXPLAIN output.
• Design a schema that mixes relational tables with JSONB for flexible attributes.
• Implement full-text search with weighted tsvector fields and appropriate GIN indexes.
• Tune work_mem and shared_buffers for a reporting query that spills to disk.
• Set up basic logical replication for near-real-time read scaling.