PostgreSQL Database

Problem

PostgreSQL performance issues often come from index choices, connection behavior, type semantics, and operational details rather than SQL syntax. WHERE active = true may benefit from a partial index, WHERE lower(email) = ... needs an expression index, and joins on foreign key columns can miss indexes. Another trap is write overhead: unused indexes increase maintenance costs, low-cardinality indexes may be ignored, and LIKE '%suffix' usually cannot use a plain B-tree.

How It Works

The skill organizes PostgreSQL practice as a checklist:
- Indexes: partial indexes, expression indexes, covering indexes, foreign key columns, and composite index order;
- Queries and concurrency: SELECT FOR UPDATE SKIP LOCKED, pg_advisory_lock, IS NOT DISTINCT FROM, and DISTINCT ON;
- Connections and resources: PgBouncer, statement_timeout, idle_in_transaction_session_timeout, and max_connections tuning;
- Data and operations: IDENTITY, TIMESTAMPTZ, NUMERIC, TEXT, autovacuum, VACUUM ANALYZE, pg_repack, and transaction isolation levels.

Boundaries

It is useful for debugging slow queries, reviewing schema choices, and checking PostgreSQL usage, but it is not a database provisioning tool. In production, decisions should still be validated with EXPLAIN (ANALYZE, BUFFERS), statistics, data volume, and business constraints, especially SERIALIZABLE 40001 retries, full-text search language settings, and lock impact from long transactions.