Agent skill · data analytics · aiskillstore
postgres-performance
High-performance PostgreSQL patterns. Use when optimizing queries, designing for scale, or debugging performance issues.
Why this skill is useful
Provides specific PostgreSQL performance optimization patterns and SQL commands that the AI wouldn't reliably generate on its own.
What it needs
About 6k tokens when loaded. Last updated 2026-08-07. 405 stars on the source repository.
What this skill does
PostgreSQL Performance Engineering Problem Statement Performance problems compound. A query that takes 50ms at 1K rows takes 5s at 100K rows. This skill covers patterns for building performant database interactions from the start and fixing performance issues. --- Pattern: Query Optimization Workflow Step 1: Identify Slow Queries Step 2: Analyze Query Plan What to look for: Warning Sign Problem Solution -------------- --------- ---------- Seq Scan on large table Missing index Add index High loops count N+1 in join Rewrite query, add index Sort with high cost No index for ORDER BY Covering index Hash/Merge Join with high rows Large intermediate result Filter earlier, better indexes Buffers: shared read high Data not cached More RAM, or query less data Step 3: Fix and Verify --- Pattern: Covering Indexes (Index-Only Scans) Problem: Query reads index, then fetches rows from table (heap fetch). When to use: Frequently run queries Queries selecting few columns Tables with many columns (heap fetch is expensive) --- Pattern: Pagination at Scale In SQLAlchemy: --- Pattern: Batch Processing In Python: --- Pattern: Efficient Aggregations --- Pattern: Connection Pool Tuning PostgreSQL side: --- Pattern: Read Replicas --- Pattern: Denormalization for Read Performance Tradeoffs: ✅ Much faster reads ❌ More complex writes (must update denormalized data) ❌ Potential for stale data --- Pattern: Partitioning Large Tables --- Pattern: Caching Strategy --- Performance Monitoring Queries --- Performance Checklist Before deploying: [ ] Slow queries identified and optimized [ ] Indexes match query patterns [ ] Covering indexes for frequent queries [ ] Pagination uses cursor-based (not OFFSET) [ ] Large tables partitioned if > 10M rows [ ] Connection pool sized appropriately [ ] Cache layer for hot data [ ] Monitoring in place for slow queries [ ] VACUUM and ANALYZE scheduled
How to use it
Reference it in AdaL, Claude Code, Cursor or any coding agent — nothing to install:
@skills aiskillstore/postgres-performance