skills.rest
trudyan141
trudyan141COMMUNITY
Explore all skills by this author →
GitHub

postgres-expert

Diagnose and optimize PostgreSQL queries, indexes, partitioning, and replication configurations.

3|Updated Jan 15, 2026
Search Tags:#query optimization#indexing#postgresql#partitioning#replication#jsonb#pgbouncer
One-click install
npx skills add https://github.com/trudyan141/my-antigravity-agents-kit --skill postgres-expert-trudyan141
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-expert
Source: https://github.com/trudyan141/my-antigravity-agents-kit/tree/main/templates/.agent/skills/postgres-expert
Command: npx skills add https://github.com/trudyan141/my-antigravity-agents-kit --skill postgres-expert-trudyan141

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? PostgreSQL performance issues like slow queries, bloated tables, connection exhaustion, and replication lag are hard to diagnose without deep knowledge of PostgreSQL internals. This Skill provides structured diagnostics and targeted fixes for PostgreSQL-specific problems. ## Core Features & Use Cases - Query & Index Optimization: Analyze EXPLAIN output, pg_stat_statements, and buffer hit ratios to fix slow queries, then apply the right index type (B-tree, GIN, GiST, BRIN, partial, expression). - JSONB & Advanced Features: Optimize JSONB containment queries with proper GIN operator classes, implement full-text search with tsvector, and use window functions, recursive CTEs, and UPSERTs. - Operations & Administration: Tune autovacuum, configure PgBouncer connection pooling, set up table partitioning, and monitor streaming replication lag. - Use Case: Your API endpoints are timing out because JSONB queries scan the full table. Use this Skill to identify the missing GIN index with jsonb_path_ops and verify the fix with EXPLAIN ANALYZE. ## Quick Start Ask the assistant to analyze your slow PostgreSQL query and recommend indexes and configuration changes based on your server version and settings.

Frequently Asked Questions about postgres-expert

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I optimize a slow PostgreSQL query?▼

Run EXPLAIN (ANALYZE, BUFFERS) on the query to inspect the execution plan, then check pg_stat_statements for cumulative costs. Common fixes include adding B-tree indexes on WHERE and JOIN columns, running ANALYZE to refresh statistics, and creating composite or covering indexes.

Which index type should I use for JSONB columns in PostgreSQL?▼

Use a GIN index for JSONB columns. The default jsonb_ops supports more operators, while jsonb_path_ops is smaller and faster for containment queries. For frequently queried specific paths, create expression indexes on individual keys.

How do I fix too many connections errors in PostgreSQL?▼

Deploy PgBouncer in transaction pooling mode between your application and PostgreSQL. Set max_client_conn and default_pool_size appropriately, and monitor pg_stat_activity to identify idle or long-running connections consuming slots.

When should I use table partitioning in PostgreSQL?▼

Use declarative partitioning when tables grow large enough that queries and maintenance slow down despite indexes. Range partitioning suits time-series data, list partitioning suits categorical values, and hash partitioning distributes rows evenly.

Why is my PostgreSQL table bloated and how do I fix it?▼

Bloat comes from dead tuples left by UPDATE and DELETE under MVCC. Tune autovacuum scale factors per table, increase maintenance_work_mem, and monitor n_dead_tup in pg_stat_user_tables to keep vacuuming effective.

How do I monitor PostgreSQL replication lag?▼

Query pg_stat_replication on the primary for sent, write, flush, and replay LSN positions plus lag intervals. Check pg_replication_slots for retained WAL size, and on the standby use pg_last_wal_replay_lsn to confirm replay progress.

Related Skills

Explore More Software Engineering Skills→
zardusai-cyberzardusai-cyber
1

postgres-patterns

Optimize PostgreSQL performance and schema design with indexing and security patterns.

Community
Advanced
alexander-danilenkoalexander-danilenko
14

database-optimizer

Analyze PostgreSQL and MySQL execution plans to optimize slow queries.

Community
Advanced
L3DigitalNetL3DigitalNet
6

iostat

Provide CPU and per-device disk I/O statistics for Linux environments.

Official
Intermediate
marcoamumarcoamu
1

sql-toolkit

Automate relational-database design, querying, and migrations across SQLite, PostgreSQL, and MySQL.

Community
Intermediate