PostgreSQL

Write efficient PostgreSQL queries and design schemas with proper indexing and patterns.

Security Scan

The skill is an instruction-only PostgreSQL guidance helper and its requirements (optional psql/pgcli) and content align with that purpose; there are no unexpected installs or credential requests.

Purpose & Capability

Name/description match the SKILL.md content (Postgres indexing, queries, connection management, vacuuming, FTS, etc.). Declared optional binaries (psql or pgcli) are appropriate for a query/schema helper. No unrelated env vars, binaries, or config paths are requested.

Instruction Scope

SKILL.md contains best-practice guidance and examples (indexes, EXPLAIN, timeouts, pg_bouncer, pg_repack, extensions) but does not instruct the agent to read arbitrary host files, access unrelated environment variables, or exfiltrate data. It does mention operations that, if executed (e.g., running diagnostic queries or using pg_repack), require DB access and privileges — this is expected for a DB helper but worth noting.

Install Mechanism

No install spec and no code files — lowest-risk model. The doc references third-party tools/extensions (pg_repack, pg_trgm, PgBouncer) but does not attempt to download or install them; users must install these externally if needed.

Credentials

The skill declares no required environment variables or credentials. That is proportionate for an advice-only skill. Note: to run interactive diagnostics via psql/pgcli the agent (or user) would need DB credentials — the skill does not request them, which is appropriate.

Persistence & Privilege

always is false, agent invocation is normal. The skill does not request persistent system presence, nor does it modify other skills or system-wide settings.

Assessment

This is an instruction-only PostgreSQL advice skill and appears internally consistent. If you plan to let an agent actually execute commands (psql/pgcli) against your database, be careful: only grant least-privilege credentials, prefer a read-only user for diagnostics, and avoid giving superuser access (pg_repack, extensions, or config changes often require elevated rights). Install any referenced tools (pg_repack, pg_trgm, PgBouncer) yourself from trusted sources. If you keep the skill as guidance-only (no DB credentials provided), the risk is minimal.

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