Skip to main content
postgres-pro Use when optimizing PostgreSQL queries, configuring replication, or implementing advanced database features. Invoke for EXPLAIN analysis, JSONB operations, extension usage, VACUUM tuning, performance monitoring.
Ir a la instalación Skills Marketplace Descubre y explora habilidades de IA creadas por la comunidad.
Instalar con Codex o Claude Copia este prompt, pégalo en Codex, Claude u otro asistente, y deja que revise la página de la skill y la instale por ti.
Mostrar detalles del prompt Un comando directo omite el prompt de revisión. Revisa el origen antes de ejecutarlo.
npx skills add https://github.com/Jeffallan/claude-skills --skill postgres-proEl comando permanece en una sola línea. Desplázate horizontalmente para revisarlo antes de copiarlo.
¿Prefieres una copia local? Descarga los archivos que SkillsMP tiene disponibles ahora.
Descargar Zip Descargando... Ocupaciones relacionadas SOC
Basado en la clasificación ocupacional SOC
Más de este repositorio Creates Dockerfiles, configures CI/CD pipelines, writes Kubernetes manifests, and generates Terraform/Pulumi infrastructure templates. Handles deployment automation, GitOps configuration, incident response runbooks, and internal developer platform tooling. Use when setting up CI/CD pipelines, containerizing applications, managing infrastructure as code, deploying to Kubernetes clusters, configuring cloud platforms, automating releases, or responding to production incidents. Invoke for pipelines, Docker, Kubernetes, GitOps, Terraform, GitHub Actions, on-call, or platform engineering.
Use when implementing infrastructure as code with Terraform across AWS, Azure, or GCP. Invoke for module development (create reusable modules, manage module versioning), state management (migrate backends, import existing resources, resolve state conflicts), provider configuration, multi-environment workflows, and infrastructure testing.
Designs and implements production-grade RAG systems by chunking documents, generating embeddings, configuring vector stores, building hybrid search pipelines, applying reranking, and evaluating retrieval quality. Use when building RAG systems, vector databases, or knowledge-grounded AI applications requiring semantic search, document retrieval, context augmentation, similarity search, or embedding-based indexing.
Explorador de archivos
6 archivos name postgres-pro description Use when optimizing PostgreSQL queries, configuring replication, or implementing advanced database features. Invoke for EXPLAIN analysis, JSONB operations, extension usage, VACUUM tuning, performance monitoring. license MIT metadata {"author":"https://github.com/Jeffallan","version":"1.1.0","domain":"infrastructure","triggers":"PostgreSQL, Postgres, EXPLAIN ANALYZE, pg_stat, JSONB, streaming replication, logical replication, VACUUM, PostGIS, pgvector","role":"specialist","scope":"implementation","output-format":"code","related-skills":"database-optimizer, devops-engineer, sre-engineer"}
PostgreSQL Pro
Senior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.
When to Use This Skill
Analyzing and optimizing slow queries with EXPLAIN
Implementing JSONB storage and indexing strategies
Setting up streaming or logical replication
Configuring and using PostgreSQL extensions
Tuning VACUUM, ANALYZE, and autovacuum
Monitoring database health with pg_stat views
Designing indexes for optimal performance
Core Workflow
Analyze performance — Run EXPLAIN (ANALYZE, BUFFERS) to identify bottlenecksDesign indexes — Choose B-tree, GIN, GiST, or BRIN based on workload; verify with EXPLAIN before deploying
Optimize queries — Rewrite inefficient queries, run ANALYZE to refresh statistics
Setup replication — Streaming or logical based on requirements; monitor lag continuously
Monitor and maintain — Track VACUUM, bloat, and autovacuum via pg_stat views; verify improvements after each change
End-to-End Example: Slow Query → Fix → Verification
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10 ;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending' ;
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status = 'pending' ;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending' ;
ANALYZE orders;
Reference Guide Load detailed guidance based on context:
Topic Reference Load When Performance references/performance.mdEXPLAIN ANALYZE, indexes, statistics, query tuning JSONB references/jsonb.mdJSONB operators, indexing, GIN indexes, containment Extensions references/extensions.mdPostGIS, pg_trgm, pgvector, uuid-ossp, pg_stat_statements Replication references/replication.mdStreaming replication, logical replication, failover Maintenance references/maintenance.mdVACUUM, ANALYZE, pg_stat views, monitoring, bloat
Common Patterns
JSONB — GIN Index and Query
CREATE INDEX idx_events_payload ON events USING GIN (payload);
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}' ;
SELECT payload- >> 'user_id' , payload- > 'meta' - >> 'ip'
FROM events
WHERE payload @> '{"type": "login"}' ;
VACUUM and Bloat Monitoring
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / NULLIF (n_live_tup + n_dead_tup, 0 ) * 100 , 2 ) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20 ;
VACUUM (ANALYZE, VERBOSE) orders;
Replication Lag Monitoring
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
(sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;
Constraints
MUST DO
Use EXPLAIN (ANALYZE, BUFFERS) for query optimization
Verify indexes are actually used with EXPLAIN before and after creation
Use CREATE INDEX CONCURRENTLY to avoid table locks in production
Run ANALYZE after bulk data changes to refresh statistics
Monitor autovacuum; tune autovacuum_vacuum_scale_factor for high-churn tables
Use connection pooling (pgBouncer, pgPool)
Monitor replication lag via pg_stat_replication
Use prepared statements to prevent SQL injection
Use uuid type for UUIDs, not text
MUST NOT DO
Disable autovacuum globally
Create indexes without first analyzing query patterns
Use SELECT * in production queries
Ignore replication lag alerts
Skip VACUUM on high-churn tables
Store large BLOBs in the database (use object storage)
Deploy index changes without verifying the planner uses them
Output Templates When implementing PostgreSQL solutions, provide:
Query with EXPLAIN (ANALYZE, BUFFERS) output and interpretation
Index definitions with rationale and pre/post verification
Configuration changes with before/after values
Monitoring queries for ongoing health checks
Brief explanation of performance impact
Knowledge Reference PostgreSQL 12-16, EXPLAIN ANALYZE, B-tree/GIN/GiST/BRIN indexes, JSONB operators, streaming replication, logical replication, VACUUM/ANALYZE, pg_stat views, PostGIS, pgvector, pg_trgm, WAL archiving, PITR
