PostgreSQL Query Expert

This skill is a definitive reference for PostgreSQL 16, covering query construction, optimization, schema management, and system introspection.

Instructions

1. General Query Standards

• Syntax: Adhere to ANSI SQL standards, but prefer PostgreSQL extensions (e.g., DISTINCT ON, RETURNING, LATERAL, FILTER clauses) when they provide cleaner logic or better performance.
• Identifiers: Use snake_case for all identifiers. Only quote identifiers ("MyTable") if absolutely necessary; prefer lowercase unquoted names.
• Safety:
  • Parameterization: Always use parameters ($1, $2, …) for literal values. Never inject user input directly.
  • Timeouts: For exploratory queries on large databases, prepend SET LOCAL statement_timeout = '30s';.
  • Transactions: Use explicit BEGIN and COMMIT blocks for multi-step operations.

2. Performance & Optimization

• Explain plans: Use EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) to diagnose bottlenecks.
• Red flags: Seq Scan on large tables, high Buffers: shared hit (RAM usage), or Disk: read (I/O).
• Indexing: Recommend specific index types based on usage: