Skip to content

Parsa-29/loop-config / skills/supabase-postgres-best-practices

supabase-postgres-best-practices

Verified

Postgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.

FileKindSize
CHANGELOG.mdmarkdown2.0 KB
SKILL.mdskill md2.5 KB
references/_contributing.mdmarkdown4.0 KB
references/_sections.mdmarkdown1.6 KB
references/_template.mdmarkdown1.0 KB
references/advanced-full-text-search.mdmarkdown1.4 KB
references/advanced-jsonb-indexing.mdmarkdown1.4 KB
references/conn-idle-timeout.mdmarkdown1.3 KB
references/conn-limits.mdmarkdown1.3 KB
references/conn-pooling.mdmarkdown1.3 KB
references/conn-prepared-statements.mdmarkdown1.5 KB
references/data-batch-inserts.mdmarkdown1.3 KB
references/data-n-plus-one.mdmarkdown1.3 KB
references/data-pagination.mdmarkdown1.3 KB
references/data-upsert.mdmarkdown1.4 KB
references/lock-advisory.mdmarkdown1.5 KB
references/lock-deadlock-prevention.mdmarkdown1.8 KB
references/lock-short-transactions.mdmarkdown1.3 KB
references/lock-skip-locked.mdmarkdown1.3 KB
references/monitor-explain-analyze.mdmarkdown1.4 KB
references/monitor-pg-stat-statements.mdmarkdown1.4 KB
references/monitor-vacuum-analyze.mdmarkdown1.5 KB
references/query-composite-indexes.mdmarkdown1.4 KB
references/query-covering-indexes.mdmarkdown1.3 KB
references/query-index-types.mdmarkdown1.5 KB
references/query-missing-indexes.mdmarkdown1.2 KB
references/query-partial-indexes.mdmarkdown1.3 KB
references/schema-constraints.mdmarkdown1.8 KB
references/schema-data-types.mdmarkdown1.4 KB
references/schema-foreign-key-indexes.mdmarkdown1.6 KB
references/schema-lowercase-identifiers.mdmarkdown1.7 KB
references/schema-partitioning.mdmarkdown1.5 KB
references/schema-primary-keys.mdmarkdown1.8 KB
references/security-privileges.mdmarkdown1.5 KB
references/security-rls-basics.mdmarkdown1.3 KB
references/security-rls-performance.mdmarkdown2.1 KB

references/monitor-vacuum-analyze.md

View on GitHub
---
title: Maintain Table Statistics with VACUUM and ANALYZE
impact: MEDIUM
impactDescription: 2-10x better query plans with accurate statistics
tags: vacuum, analyze, statistics, maintenance, autovacuum
---

## Maintain Table Statistics with VACUUM and ANALYZE

Outdated statistics cause the query planner to make poor decisions. VACUUM reclaims space, ANALYZE updates statistics.

**Incorrect (stale statistics):**

```sql
-- Table has 1M rows but stats say 1000
-- Query planner chooses wrong strategy
explain select * from orders where status = 'pending';
-- Shows: Seq Scan (because stats show small table)
-- Actually: Index Scan would be much faster
```

**Correct (maintain fresh statistics):**

```sql
-- Manually analyze after large data changes
analyze orders;

-- Analyze specific columns used in WHERE clauses
analyze orders (status, created_at);

-- Check when tables were last analyzed
select
  relname,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
from pg_stat_user_tables
order by last_analyze nulls first;
```

Autovacuum tuning for busy tables:

```sql
-- Increase frequency for high-churn tables
alter table orders set (
  autovacuum_vacuum_scale_factor = 0.05,     -- Vacuum at 5% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.02     -- Analyze at 2% changes (default 10%)
);

-- Check autovacuum status
select * from pg_stat_progress_vacuum;
```

Reference: [VACUUM](https://supabase.com/docs/guides/database/database-size#vacuum-operations)
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall