Agent skill · data analytics · aiskillstore
postgres-patterns
PostgreSQL patterns for reviewing migrations and writing efficient queries. Use when reviewing Alembic migrations, optimizing queries, or debugging database issues.
Why this skill is useful
Provides specific PostgreSQL patterns for optimizing migrations and queries that the AI wouldn't reliably generate on its own.
What it needs
About 5k tokens when loaded. Last updated 2026-08-07. 405 stars on the source repository.
What this skill does
PostgreSQL Patterns Problem Statement Alembic generates migrations but doesn't understand PostgreSQL performance implications. This skill covers reviewing migrations for PostgreSQL-specific issues and writing efficient queries. --- Pattern: Index Review When to Add Indexes When NOT to Add Indexes Index Column Order Matters --- Pattern: Partial Indexes Problem: Full index on column where you only query subset of values. In Alembic: --- Pattern: JSONB Indexes --- Pattern: Concurrent Index Creation Problem: CREATE INDEX locks the table. On large tables, this blocks writes. In Alembic: --- Pattern: Query Performance Analysis Key metrics: cost: Estimated units (lower is better) rows: Estimated row count actual time: Real milliseconds loops: How many times executed --- Pattern: UUID Performance --- Pattern: Constraint Review --- Pattern: Bulk Operations --- Pattern: Table Locking Awareness Know what locks what: Operation Lock Type Blocks ----------- ----------- -------- SELECT AccessShare Nothing INSERT/UPDATE/DELETE RowExclusive Nothing (row-level) CREATE INDEX ShareLock INSERT/UPDATE/DELETE CREATE INDEX CONCURRENTLY ShareUpdateExclusive Other schema changes ALTER TABLE (most) AccessExclusive Everything DROP TABLE AccessExclusive Everything Danger zone: --- Pattern: Connection Management --- Pattern: Data Type Choices Use Case Type Notes ---------- ------ ------- Primary key UUID Use uuid7 for ordering if possible Foreign key Match parent type Timestamps TIMESTAMPTZ Always with timezone Money NUMERIC(12,2) Never FLOAT JSON data JSONB Not JSON (JSONB is faster) Short strings VARCHAR(n) With reasonable limit Long text TEXT No length limit Boolean BOOLEAN Not integer Enum-like VARCHAR or native ENUM VARCHAR is more flexible --- Migration Review Checklist (PostgreSQL-Specific) [ ] Large table indexes use CONCURRENTLY [ ] Foreign keys have ON DELETE behavior specified [ ] Constraints have explicit names [ ] Non-nullable columns on existing tables use 3-step process [ ] Indexe …
How to use it
Reference it in AdaL, Claude Code, Cursor or any coding agent — nothing to install:
@skills aiskillstore/postgres-patterns