skills.rest
selrai-company
selrai-companyOFFICIAL
Explore all skills by this author →
WebsiteGitHub

postgresql-optimization

Analyze EXPLAIN ANALYZE and pg_stat_statements to recommend PostgreSQL indexes and configuration.

Updated Aug 23, 2026
Search Tags:#performance#indexing#postgresql#partitioning#tuning#explain#pgbouncer
One-click install
npx skills add https://github.com/selrai-company/claude-workshop-kit --skill postgresql-optimization-selrai-company
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-optimization
Source: https://github.com/selrai-company/claude-workshop-kit/tree/main/skills/postgresql-optimization
Command: npx skills add https://github.com/selrai-company/claude-workshop-kit --skill postgresql-optimization-selrai-company

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This Skill helps diagnose and resolve slow PostgreSQL queries and suboptimal database configurations so applications run faster and more reliably under production workloads.

Core Features & Use Cases

  • EXPLAIN analysis & query tuning: Interpret EXPLAIN/EXPLAIN ANALYZE output, identify sequential scans, and recommend query rewrites or join optimizations.
  • Indexing & partitioning strategies: Advise on B-tree, GIN, GiST, BRIN, partial and expression indexes, covering indexes for index-only scans, and range/list/hash partitioning for very large tables.
  • Production configuration & pooling: Recommend memory, autovacuum, WAL, checkpoint, and connection pooling (PgBouncer/Supavisor) settings for OLTP and analytical workloads.
  • Operational diagnostics: Use pg_stat_statements and table bloat analysis to find high-impact queries, IO hotspots, and vacuuming needs.
  • Use case: Improve a slow reporting endpoint by analyzing the query plan, adding a targeted partial index, and tuning work_mem and shared_buffers for the database server.

Quick Start

Use the postgresql-optimization skill to analyze an EXPLAIN ANALYZE output and get index, query, and configuration recommendations.

Frequently Asked Questions about postgresql-optimization

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

FAQPage Schema
How do I analyze EXPLAIN ANALYZE output to fix slow PostgreSQL queries?▼

Analyze EXPLAIN ANALYZE output to identify sequential scans and high-cost nodes in the query plan, then apply targeted query rewrites or join optimizations to resolve slow PostgreSQL queries.

What is the best way to design PostgreSQL indexes for large tables?▼

The best way to design PostgreSQL indexes is to use B-tree, GIN, GiST, BRIN, partial, expression, or covering indexes to enable index-only scans and accelerate query performance on large tables.

How does table partitioning improve PostgreSQL performance for large datasets?▼

Table partitioning improves PostgreSQL performance by splitting very large tables into range, list, or hash partitions, reducing scan overhead and making maintenance operations more efficient.

How do I tune PostgreSQL configuration for OLTP workloads?▼

Tune PostgreSQL configuration for OLTP workloads by adjusting memory, autovacuum, WAL, and checkpoint settings, and configuring connection pooling via PgBouncer or Supavisor to maximize throughput.

Can I use pg_stat_statements to find high-impact queries in PostgreSQL?▼

Yes, you can use pg_stat_statements to find high-impact queries in PostgreSQL by analyzing execution statistics to pinpoint IO hotspots, table bloat, and vacuuming needs.

Related Skills

Explore More Software Engineering Skills→
kobogithubkobogithub

postgres-optimization

Identify and optimize PostgreSQL performance bottlenecks through query analysis, indexing, and configuration tuning.

Community
Advanced
HieubkavHieubkav

sql-optimization-patterns

Analyze SQL query plans with EXPLAIN and implement indexing strategies.

Community
Advanced
rnavarychrnavarych
14

postgres-deep

Analyzes PostgreSQL slow queries and recommends indexing and tuning strategies.

Community
Advanced
gemini-cli-extensionsgemini-cli-extensions
42

cloud-sql-postgres-health

Audit Cloud SQL for PostgreSQL instance health and performance bottlenecks.

Official
Intermediate