postgres-query-expert_skill

Overview

This skill is a practical, compact reference for interacting with PostgreSQL 16. It focuses on building and optimizing SQL queries, debugging problems, managing schema objects, and inspecting database structure. Use it to produce safe, performant SQL and to interpret server-side metrics and plans.

How this skill works

The skill recommends PostgreSQL-specific syntax and ANSI-compliant patterns, enforces parameterization, and suggests protective settings like statement timeouts. It supplies diagnostic queries for schema introspection and server stats, examples for advanced SQL constructs (CTEs, window functions, MERGE, JSONB and arrays), and guidance on indexing and explain-plan analysis.

When to use it

• Writing or refactoring queries for PostgreSQL 16 to improve clarity or performance
• Diagnosing slow queries using EXPLAIN ANALYZE and buffer/I/O indicators
• Exploring an unfamiliar database schema and extracting column/type metadata
• Implementing safe data changes with transactions, MERGE, or INSERT ... ON CONFLICT
• Designing indexes and choosing appropriate index types (B-tree, GIN, GiST)

Best practices

• Always parameterize user input (use $1, $2, ...); never inject raw values
• Use SET LOCAL statement_timeout for exploratory queries on large tables
• Prefer snake_case unquoted identifiers and lowercase names unless quoting is required
• Use EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) to find Seq Scan or heavy I/O
• Choose index types by access patterns: B-tree for ranges/equality, GIN for jsonb/arrays, GiST for geometric/range

Example use cases

• Inspecting all non-system tables and their descriptions to map a database
• Fetching column names and types for a target table to scaffold migrations or API models
• Optimizing a reporting query with window functions and GROUPING SETS for aggregated rolls
• Implementing safe upserts with MERGE or INSERT ... ON CONFLICT and returning affected rows
• Killing runaway queries and checking table disk usage during maintenance