pg

by ivangdavila

Install this Skill

curl -fsSL -o pg.zip https://skill.seembox.com/api/v1/skills/pg/download && unzip pg.zip

SKILL.md About

---
name: PostgreSQL
description: Write efficient PostgreSQL queries and design schemas with proper indexing and patterns.
metadata: {"clawdbot":{"emoji":"🐘","requires":{"anyBins":["psql","pgcli"]},"os":["linux","darwin","win32"]}}
---

## Indexes I Forget to Create

- Partial index `WHERE active = true`—80% smaller when most rows inactive; suggest for status columns
- Expression index `ON lower(email)`—must match query exactly; without it, `WHERE lower(email)` scans
- Covering index `INCLUDE (name, email)`—enables index-only scan; check EXPLAIN for "Heap Fetches"
- Foreign key columns—not auto-indexed in PG; JOINs and ON DELETE CASCADE need them
- Composite index order matters—`(a, b)` helps `WHERE a = ?` but not `WHERE b = ?`

## Index Traps

- Unused indexes hurt every INSERT/UPDATE—query `pg_stat_user_indexes` for `idx_scan = 0`, drop them
- Too many indexes on write-heavy tables—balance carefully
- Index on low-cardinality column (boolean, status) often useless—PG prefers seq scan
- `LIKE '%suffix'` can't use B-tree—need pg_trgm GIN index or reverse() expression index

## Query Patterns I Underuse

- `SELECT FOR UPDATE SKIP LOCKED`—job queue without external tools; skip rows being processed
- `pg_advisory_lock(key)`—application-level mutex without table; unlock explicitly or on disconnect
- `IS NOT DISTINCT FROM`—NULL-safe equality; cleaner than `(a = b OR (a IS NULL AND b IS NULL))`
- `DISTINCT ON (x) ORDER BY x, y`—first row per group without subquery; PG-specific but powerful

## Connection Management (Often Ignored)

- PgBouncer essential with >50 connections—each PG connection uses ~10MB; pool at transaction level
- `statement_timeout = '30s'` per role—prevents runaway queries from killing database
- `idle_in_transaction_session_timeout = '5min'`—kills abandoned transactions holding locks
- Default 100 max_connections too low for production, too high wastes memory—tune based on RAM

## Data Types I Get Wrong

- `SERIAL` deprecated—use `GENERATED ALWAYS AS IDENTITY`
- `TIMESTAMP` without timezone—almost always wrong; use `TIMESTAMPTZ`, PG stores as UTC
- Float for money—use `NUMERIC(12,2)` or integer cents; float math breaks: 0.1 + 0.2 ≠ 0.3
- VARCHAR(n) vs TEXT—no performance difference in PG; use TEXT unless constraint needed

## Vacuum & Bloat (Never Think About)

- High-UPDATE tables bloat—dead tuples accumulate; `pg_repack` reclaims without locks
- `VACUUM ANALYZE` after bulk insert—updates statistics; query planner needs current data
- Autovacuum lag on big tables—tune `autovacuum_vacuum_cost_delay` or manual vacuum
- Transaction wraparound: if `xid` exhausted, DB stops—autovacuum prevents but monitor

## EXPLAIN I Don't Read Right

- Always `EXPLAIN (ANALYZE, BUFFERS)`—actual times + I/O; estimate-only misleads
- "Heap Fetches: 1000" with index—missing columns, add INCLUDE to index
- Seq scan not always bad—faster than index for >10-20% of table; check row estimates
- "Rows" estimate way off—run ANALYZE or check if stats target too low

## Full-Text Search Mistakes

- Creating tsvector on the fly—precompute as stored generated column with GIN index
- `plainto_tsquery` for user input—handles spaces without syntax errors; not `to_tsquery`
- Missing language parameter—'english' stems words; 'simple' exact match
- FTS is word-based—`LIKE '%exact phrase%'` still needed for substring match

## Transaction Isolation

- Default READ COMMITTED—phantom reads in reports; use REPEATABLE READ for consistency
- SERIALIZABLE catches conflicts—but must handle 40001 error with retry loop
- Long transactions block vacuum and hold locks—keep under seconds, not minutes

_meta.json Metadata

{
  "ownerId": "kn73vp5rarc3b14rc7wjcw8f8580t5d1",
  "publishedAt": 1770689196480,
  "slug": "pg",
  "version": "1.0.0"
}