ericrisco/rsc-harness / skills/postgresdb
postgresdb
VerifiedUse when PostgreSQL engine behaviour decides the answer — schema and type design, index choice, reading EXPLAIN on a slow query, zero-downtime DDL and backfills, or ops (roles, RLS, pooling, vacuum, partitioning, PITR). PG16, ORM-agnostic. NOT portable query logic (that is `sql`), NOT a managed provider's platform surface (that is `neon`).
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 17.3 KB |
| evals/README.md | markdown | 2.6 KB |
| evals/cases.yaml | config | 5.9 KB |
| references/migrations.md | markdown | 11.5 KB |
| references/operations-and-security.md | markdown | 11.5 KB |
| references/query-optimization.md | markdown | 13.2 KB |
| references/schema-and-indexing.md | markdown | 14.1 KB |
| scripts/verify.shexecutable | script | 7.1 KB |
evals/cases.yaml
View on GitHubskill: postgresdb
should_trigger:
- prompt: "This query takes 8 seconds and the page won't load. Can you help me figure out why and speed it up?"
why: "Slow query / latency complaint is core scope; the skill owns reading the plan and fixing scans even though the user never says SQL or index."
- prompt: "We need to add a column to a 40-million-row table that's getting live traffic without taking the app down. What's the safe way?"
why: "Zero-downtime migration against a large table with live traffic — expand-contract, lock_timeout, batched backfill — is explicitly in scope."
- prompt: "Here's my EXPLAIN ANALYZE output. Why is it doing a Seq Scan on orders when I have an index on user_id?"
why: "Reading a plan, diagnosing Seq Scan vs index usage, and stale-stats/predicate issues are the heart of query-optimization."
- prompt: "Should I store this as an enum or a separate lookup table with a foreign key? The set of statuses keeps changing."
why: "Schema/DDL type decision (enum-vs-lookup) is a named When-to-use case with a decision rule in the skill."
- prompt: "Different tenants keep seeing each other's rows in our shared table. I want the database itself to enforce isolation per tenant."
why: "This is row-level security (RLS) operational/security scope, even though the user never names RLS or Postgres explicitly."
- prompt: "Our background workers keep grabbing the same job and processing it twice. They poll a jobs table. How do I make concurrent workers not collide?"
why: "Postgres-as-queue with FOR UPDATE SKIP LOCKED is explicitly the one queue pattern in scope; concurrency/locking is core."
- prompt: "I have a list view and every time I load a user's page it fires hundreds of little SELECTs, one per row. How do I fix this?"
why: "Classic N+1 described without the term; skill owns fixing it at the SQL boundary with a set-based query / LATERAL join."
- prompt: "We're running out of Postgres connections under load on our serverless app. How should we set up pooling?"
why: "Connection pooling / PgBouncer is named operations scope (the engine-side answer, not vendor console clicks)."
should_not_trigger:
- prompt: "My Prisma updateMany returns the wrong count and isn't updating the rows I expect. What's the ORM doing?"
route_to: "none"
why: "Explicitly out of scope: ORM-API ergonomics like Prisma's updateMany count trap belong to the ORM's own docs, not this engine-level skill."
- prompt: "I need to add a SQLite full-text search index in my Expo mobile app's local database."
route_to: "flutter"
why: "Non-Postgres engine (SQLite) is explicitly out of scope — different MVCC/planner; mobile-app data lands closer to the app-framework skill, not postgresdb."
- prompt: "How do I configure a managed Postgres instance on RDS through the AWS console and set up automated snapshots in the UI?"
route_to: "deployment"
why: "Cloud-vendor console clicks are explicitly out of scope; provisioning/infra-through-UI is deployment territory, not engine-level SQL."
- prompt: "Build a FastAPI endpoint with a Pydantic request model that returns paginated results."
route_to: "fastapi"
why: "This is API-framework / request-validation work; the SQL it eventually emits could touch postgresdb but the prompt as stated is fastapi's domain."
- prompt: "How do I prevent SQL injection in my Node app — is parameterizing enough, and how should I handle untrusted input generally?"
route_to: "security-audit"
why: "A general application-security question about untrusted input belongs to security-audit; postgresdb owns engine behavior, not app-layer input handling."
- prompt: "Should I cache these hot query results in Redis to cut database load?"
route_to: "none"
why: "App-layer caching / Redis as a product is explicitly out of scope; only Postgres-as-queue via SKIP LOCKED is in scope, not caching layers."
capability:
- scenario: "User has a query `SELECT * FROM orders WHERE user_id = $1 AND created_at >= $2 ORDER BY created_at DESC LIMIT 20` that is slow on a large orders table, and asks which index to add and how to confirm it helps."
must_include:
- "Proposes a composite btree index with equality column first, range/sort column second: ON orders (user_id, created_at DESC)"
- "Explains the index ORDER BY direction must match the query to avoid a sort, and column order rationale (equality then range)"
- "Instructs running EXPLAIN (ANALYZE, BUFFERS) and confirming an Index Cond (not a Filter / Rows Removed by Filter) before declaring success"
- "Recommends CREATE INDEX CONCURRENTLY on a live table (cannot run inside a transaction) rather than plain CREATE INDEX"
- "Recommends keyset/cursor pagination over large OFFSET, or at least flags OFFSET as O(n) for deep pages"
- "Warns against blindly adding an index when selectivity is low or the column is already a left prefix of an existing index"
- scenario: "User wants to add a NOT NULL `country` column to a 50M-row table that serves live traffic, with zero downtime, using a generic migration tool."
must_include:
- "Splits the change into steps (expand-contract): add nullable column first, backfill, then enforce NOT NULL — never ADD COLUMN ... NOT NULL with backfill in one shot on a large table"
- "Notes that a constant (non-volatile) default is instant on PG11+, but a volatile default (e.g. now()/uuid) forces a full table rewrite under ACCESS EXCLUSIVE"
- "Backfills in batches to avoid a long transaction / lock and bloat, rather than a single UPDATE"
- "Adds the NOT NULL via a CHECK ... NOT VALID then VALIDATE CONSTRAINT (or NOT NULL after backfill) to avoid a blocking full-table scan under heavy lock"
- "Wraps DDL with SET lock_timeout and SET statement_timeout so a blocked DDL fails fast instead of queueing behind/ahead of traffic"
- "States migrations are forward-only in prod and never edit an already-applied migration"
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |