postgres

Diagnose and optimize PostgreSQL schema, queries, indexes, and production operations.

Updated May 17, 2022

Search Tags:#query optimization #monitoring #indexing #postgresql #replication #database operations #backup recovery

Ready to run

npx skills add https://github.com/kanade0404/dotfiles --skill postgres-kanade0404

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: postgres
Source: https://github.com/kanade0404/dotfiles/tree/main/.opencode/skills/postgres
Command: npx skills add https://github.com/kanade0404/dotfiles --skill postgres-kanade0404

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill helps you diagnose, tune, and operate PostgreSQL systems without guesswork, reducing slow queries, connection issues, bloat, and recovery risk.

Core Features & Use Cases

Query and Index Optimization: Identify missing, duplicate, unused, or invalid indexes and rewrite inefficient SQL patterns for better performance.
Production Operations: Apply guidance for connection pooling, memory tuning, autovacuum, WAL, checkpoints, replication, backup, and restore workflows.
Safe Database Design: Choose better schemas, keys, partitioning strategies, and monitoring practices for reliable long-term PostgreSQL operation.

Quick Start

Ask me to analyze your PostgreSQL schema, query, or operational issue and I will provide concrete tuning, troubleshooting, and safety recommendations.

Frequently Asked Questions about postgres

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

FAQPage Schema

How do I optimize slow PostgreSQL queries and identify missing or unused indexes?▼

To optimize slow PostgreSQL queries, you need to analyze your schema to identify missing, duplicate, or unused indexes and rewrite inefficient SQL patterns. This reduces slow query performance and connection issues without guesswork.

What is the best way to tune PostgreSQL memory, autovacuum, and WAL checkpoints for production operations?▼

Tuning PostgreSQL for production involves applying operational guidance for memory allocation, autovacuum, WAL, and checkpoints. This ensures reliable long-term operation and reduces recovery risk by following best-practice decisions for safe changes.

How do I troubleshoot PostgreSQL replication and backup recovery failures safely?▼

Troubleshooting PostgreSQL replication and backup recovery failures requires diagnosing operational problems and applying validated workflows for restore. This provides concrete tuning and safety recommendations to reduce recovery risk during failure events.

Can I use this for PostgreSQL database design reviews including partitioning and schema choices?▼

Yes, you can use it for safe PostgreSQL database design reviews to choose better schemas, keys, and partitioning strategies. This ensures reliable long-term PostgreSQL operation by validating queries and applying best-practice design decisions.

Why does my PostgreSQL database experience bloat and how do I monitor connection pooling issues?▼

PostgreSQL bloat and connection pooling issues arise from suboptimal autovacuum and memory configurations. You can fix them by applying monitoring practices and operational tuning guidance to diagnose and reduce these specific performance bottlenecks.

When should I not attempt indexing changes or schema modifications directly on my production PostgreSQL database?▼

You should avoid direct schema modifications or indexing changes on production PostgreSQL without validated queries and safe change procedures. Applying best-practice operational decisions prevents downtime and reduces recovery risk during failure recovery.