skills.rest
Inteligentsensingsolutions
InteligentsensingsolutionsOFFICIAL
Explore all skills by this author →
GitHub

postgres-best-practices

Optimize PostgreSQL performance with EXPLAIN ANALYZE, indexing, and connection pooling guidance.

1|Updated Apr 13, 2026
Search Tags:#performance#monitoring#indexing#postgresql#rls#query-optimization#schema-design
One-click install
npx skills add https://github.com/Inteligentsensingsolutions/tdd-dev-workflow --skill postgres-best-practices-inteligentsensingsolutions
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-best-practices
Source: https://github.com/Inteligentsensingsolutions/tdd-dev-workflow/tree/main/skills/postgres-best-practices
Command: npx skills add https://github.com/Inteligentsensingsolutions/tdd-dev-workflow --skill postgres-best-practices-inteligentsensingsolutions

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

Postgres deployments suffer from slow queries, inefficient indexes, connection exhaustion, insecure row-level access, and fragile schema choices that degrade performance and risk data exposure; this Skill provides actionable, prioritized guidance to address those issues and prevent regressions.

Core Features & Use Cases

  • Query performance diagnostics: interpret EXPLAIN ANALYZE output, identify sequential scans, expensive joins, and sorting bottlenecks and recommend targeted fixes.
  • Indexing & schema strategy: advise on single-column, composite, partial, covering, GIN/BRIN indexes, primary key choices, partitioning, and data type selection.
  • Connection and RLS guidance: recommend pooling configs, timeouts, prepared-statement patterns for pooled environments, and robust Row-Level Security policies with performance-safe patterns.
  • Concurrency, maintenance, and monitoring: recommend SKIP LOCKED job patterns, advisory locks, autovacuum/analyze tuning, and queries for pg_stat_statements and pg_stat_user_tables.
  • Use case example: turn an EXPLAIN ANALYZE of a slow endpoint into an ordered action plan: index changes, query rewrites, and monitoring checks to validate improvement.

Quick Start

Provide the slow SQL query and its EXPLAIN ANALYZE output and ask for index, schema, and RLS recommendations.

Frequently Asked Questions about postgres-best-practices

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

FAQPage Schema
How do I interpret EXPLAIN ANALYZE output for slow PostgreSQL queries?▼

PostgreSQL indexing strategy involves selecting single-column, composite, partial, covering, or GIN/BRIN indexes. This Skill advises on the right index types and partitioning strategies to optimize both OLTP and analytical workloads without degrading write speed.

How do I configure connection pooling to prevent PostgreSQL connection exhaustion?▼

Configuring connection pooling requires adjusting pool sizes, timeouts, and prepared-statement patterns for pooled environments. This Skill provides specific pooling settings to prevent connection exhaustion and stabilize PostgreSQL database connections under load.

How do I write performance-safe Row-Level Security policies in PostgreSQL?▼

Performance-safe Row-Level Security policies require careful pattern design to avoid adding query overhead. This Skill recommends robust RLS policy patterns that secure row-level access in PostgreSQL without causing slow queries or data exposure.

How do I tune autovacuum and monitor PostgreSQL with pg_stat_statements?▼

Tuning autovacuum and monitoring PostgreSQL involves adjusting analyze settings and querying pg_stat_statements. This Skill delivers specific autovacuum tuning parameters and monitoring queries to maintain database health and prevent transaction ID wraparound.

What is the best way to design a PostgreSQL schema for high concurrency?▼

Designing a PostgreSQL schema for high concurrency involves selecting appropriate data types, primary keys, and partitioning strategies. This Skill provides schema design guidance alongside SKIP LOCKED job patterns and advisory locks to handle concurrent transactions safely.

Related Skills

Explore More Software Engineering Skills→
duanbiao2000duanbiao2000
3

sql-optimization-patterns

Analyze slow SQL queries using EXPLAIN plans and indexing strategies.

Community
Advanced
dieu-donneedieu-donnee

database-optimizer

Identify slow queries and validate index strategies with EXPLAIN ANALYZE across PostgreSQL and MySQL.

Community
Advanced
rongarederongarede
4

postgres-patterns

Apply PostgreSQL patterns for query tuning, indexing, and Row Level Security.

Community
Intermediate
sumeetonline90sumeetonline90

clickhouse-io

Optimize ClickHouse schemas, queries, and data ingestion for OLAP workloads.

Community
Intermediate