postgres-query-expert

Query optimization in PostgreSQL involves analyzing execution plans with EXPLAIN ANALYZE, adding indexes on frequently filtered columns, and rewriting queries to reduce scans. This Skill provides indexing recommendations, schema introspection to identify bottlenecks, and patterns for window functions and DISTINCT ON to improve performance across PostgreSQL 16 deployments.

PostgreSQL 16 supports ANSI SQL plus extensions like RETURNING, LATERAL, MERGE, and JSONB operations for building advanced queries. This Skill covers parameterized query construction using $1, $2 syntax, transactional patterns with BEGIN/COMMIT, and DML operations that safely return modified rows.

MERGE performs conditional insert, update, or delete in a single statement, while RETURNING retrieves modified rows without extra queries. Both are PostgreSQL 16 extensions covered here with transactional safety, parameterized construction, and practical DML patterns for schema management.

PostgreSQL 16 supports window functions for ranking and aggregation, array and range types, and JSONB for semi-structured data. This Skill implements these advanced features alongside schema introspection and performance tuning for production deployments.

Debug PostgreSQL queries using EXPLAIN ANALYZE to view execution plans, schema introspection to verify table and column structure, and statement timeout settings via SET LOCAL. This Skill provides error diagnosis, indexing recommendations, and best practices for parameterized, transactional safety.