skills.rest
RogerioSobrinho
RogerioSobrinhoCOMMUNITY
Explore all skills by this author →
GitHub

postgres-patterns

Identify and optimize slow PostgreSQL queries with indexing, partitioning, and configuration patterns.

Updated Mar 26, 2026
Search Tags:#performance#indexing#postgresql#partitioning#query-optimization#explain#jsonb
One-click install
npx skills add https://github.com/RogerioSobrinho/codeme-copilot --skill postgres-patterns-rogeriosobrinho
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-patterns
Source: https://github.com/RogerioSobrinho/codeme-copilot/tree/main/skills/postgres-patterns
Command: npx skills add https://github.com/RogerioSobrinho/codeme-copilot --skill postgres-patterns-rogeriosobrinho

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Slow PostgreSQL workloads are common in growing apps, and identifying effective patterns for indexing, partitioning, and configuration is essential to restore responsiveness and scale.

Core Features & Use Cases

  • B-tree and partial indexing strategies for typical equality and range predicates to speed up queries.
  • GIN indexing for JSONB and text search patterns, plus efficient containments and key existence checks.
  • Explain Analyze driven tuning, partitioning by range for large tables, and safe zero-downtime DDL patterns.
  • HikariCP tuning guidelines to align connection pool sizing with database capacity and workload.

Quick Start

Run Explain Analyze on a representative slow query and apply the recommended indexing and partitioning patterns to optimize performance.

Frequently Asked Questions about postgres-patterns

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

FAQPage Schema
How do I identify slow PostgreSQL queries using EXPLAIN ANALYZE?▼

To identify slow PostgreSQL queries, run EXPLAIN ANALYZE on a representative query to inspect execution plans. This exposes bottlenecks like sequential scans, enabling targeted query tuning through B-tree indexing, GIN indexing, or partitioning strategies.

What is the best way to optimize JSONB queries in PostgreSQL?▼

The best way to optimize JSONB queries in PostgreSQL is applying GIN indexing patterns. GIN indexes accelerate text search, containment checks, and key existence operations, significantly improving query performance for JSONB document workloads.

How do I tune HikariCP connection pool sizing for PostgreSQL?▼

Tune HikariCP connection pool sizing by aligning parameters with your database capacity and specific workload. Proper HikariCP tuning prevents connection exhaustion and ensures responsive OLTP and analytical PostgreSQL deployments.

When do I need range partitioning for large PostgreSQL tables?▼

You need range partitioning for large PostgreSQL tables when query performance degrades due to data volume. Partitioning strategies divide large tables into manageable segments, accelerating range predicates and easing maintenance tasks.

Can I perform zero-downtime DDL migrations on large PostgreSQL datasets?▼

Yes, you can perform zero-downtime DDL migrations on large PostgreSQL datasets by applying safe patterns. These migration patterns ensure schema changes occur without locking tables, maintaining continuous availability for OLTP workloads.

Related Skills

Explore More Software Engineering Skills→
rabbicserabbicse

database-optimizer

Optimize slow PostgreSQL and MySQL queries with EXPLAIN ANALYZE validation.

Community
Advanced
gabi-agentgabi-agent

database-operations

Optimize PostgreSQL schema design, migrations, and query performance.

Community
Advanced
agnaldo4jagnaldo4j
1

db-migrations

Manage PostgreSQL schema lifecycles with versioned Flyway migrations and HikariCP pooling.

Community
Advanced
stephanjstephanj
9

database-optimizer

Analyze slow database queries and execution plans for PostgreSQL and MySQL.

Community
Advanced