skills.rest
ryanmiville
ryanmivilleCOMMUNITY
Explore all skills by this author →
GitHub

postgres-best-practices

Consolidate PostgreSQL best practices for query optimization, indexing, and maintenance.

Updated Dec 21, 2024
Search Tags:#performance#rls#partitioning#indexes#postgres#explain-analyze#vacuum
One-click install
npx skills add https://github.com/ryanmiville/dotfiles --skill postgres-best-practices-ryanmiville
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-best-practices
Source: https://github.com/ryanmiville/dotfiles/tree/main/home/.agents/skills/postgres-best-practices
Command: npx skills add https://github.com/ryanmiville/dotfiles --skill postgres-best-practices-ryanmiville

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This Skill consolidates PostgreSQL best practices into a practical guide to optimize queries, indexing, partitioning, and maintenance for scalable, reliable databases.

Core Features & Use Cases

  • Query optimization: guidance on indexing, explain analyze usage, and execution plan interpretation.
  • Schema & maintenance: recommendations for VACUUM/ANALYZE, autovacuum tuning, and table statistics maintenance.
  • Security & reliability: RLS considerations, proper privileges, and connection management patterns.
  • Use Case: a team tuning a high-traffic Postgres deployment (like Supabase) to reduce latency and improve throughput.

Quick Start

Run a quick Postgres health check, enable explain analyze on slow queries, identify missing or ineffective indexes, and apply partitioning or vacuum improvements as appropriate.

Frequently Asked Questions about postgres-best-practices

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

FAQPage Schema
What are the best practices for optimizing PostgreSQL queries in high-traffic deployments?▼

Use EXPLAIN ANALYZE on slow PostgreSQL queries to interpret execution plans, identify missing or ineffective indexes, and apply partitioning or VACUUM tuning to reduce latency and improve throughput in high-traffic deployments.

How do I tune autovacuum and table statistics maintenance in Postgres?▼

Tune Postgres autovacuum by applying VACUUM and ANALYZE recommendations alongside proper table statistics maintenance to ensure scalable and reliable database performance under heavy load.

Does this guide cover Row Level Security (RLS) and connection management for Supabase?▼

Yes, it provides RLS considerations, proper privilege configurations, and connection management patterns specifically targeting teams tuning high-traffic Postgres deployments like Supabase.

How do I identify missing or ineffective indexes in PostgreSQL?▼

Identify missing or ineffective PostgreSQL indexes by running a quick health check, enabling EXPLAIN ANALYZE on slow queries, and interpreting the resulting execution plans to apply concrete indexing patterns.

When should I use table partitioning in PostgreSQL?▼

Apply PostgreSQL table partitioning when optimizing high-traffic deployments to reduce latency, combining concrete partitioning patterns with indexing and VACUUM tuning to improve overall database throughput.

Related Skills

Explore More Software Engineering Skills→
rabbicserabbicse

database-optimizer

Optimize slow PostgreSQL and MySQL queries with EXPLAIN ANALYZE validation.

Community
Advanced
CenredJunCenredJun

postgresql-optimization

Analyze EXPLAIN ANALYZE output to optimize PostgreSQL queries and configuration.

Community
Advanced
djankiesdjankies

configuring-connection-pools

Configure Prisma Client connection pools for PostgreSQL deployments.

Community
Advanced
erikhowarderikhoward

database-optimizer

Analyze execution plans and tune queries, indexes, and caching across PostgreSQL, MySQL, and SQL Server.

Community
Advanced