postgres-best-practices

Supabase PostgreSQL best practices library — 33 impact-rated rules (CRITICAL→LOW) covering connection pooling, RLS, locking, VACUUM, full-text search, and JSONB indexing. Use when writing, reviewing, or optimizing PostgreSQL queries, schema, or configuration.

Jump to install

Repository: kumaran-is/claude-code-onboarding
Last source activity: March 29, 2026 at 11:31
Detected SKILL.md language: English
Stars: 34
Forks: 21

Install options

The review-first prompt is selected by default. You can switch to a direct command or download a local copy.

Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.

A direct command skips the review prompt. Inspect the source before running it.

npx skills add https://github.com/kumaran-is/claude-code-onboarding --skill postgres-best-practices

The command stays on one line. Scroll horizontally to inspect it before copying.

Prefer a local copy? Download the files currently available to SkillsMP.

Review the source files

Read SKILL.md and any companion files shown by SkillsMP before deciding whether to install.

File Explorer

33 files

SKILL.md

Source instructions · Read-only preview

Back to search Back to installation

systematic-debugging

Root-cause-first debugging methodology for any bug, test failure, exception, or unexpected behavior — mandates investigation before proposing fixes. Use when something is broken, not working, crashing, or throwing errors; triggers: debug, error, bug, exception, stack trace, not working, crash.

2026-06-30

browser-testing

Browser automation and testing using playwright-cli (stateful Bash CLI for scripted tests — network inspection, console monitoring, screenshots, tracing) and Browser-Use MCP (autonomous agent flows). Use when the user needs to test web apps, debug browser issues, analyze performance, fill forms, run E2E user flows, or inspect network/console activity.

2026-05-26

decision-frameworks

Use when working through a specific problem or decision using a single reasoning framework applied deeply and interactively. Covers First Principles (break assumptions, rebuild from truth), Inversion (guarantee failure, then flip), Regret Minimization (decide from age 80), and Opportunity Cost (make tradeoffs visible). Triggers: "first principles", "inversion", "regret minimization", "opportunity cost", "help me think through", "challenge my assumptions", "what am I giving up", "work backwards from failure", "what would I regret".

2026-05-26

View all from this repository

Iron Law

NEVER TUNE WHAT YOU HAVEN'T MEASURED — CHECK pg_stat_statements AND pg_stat_activity BEFORE CHANGING CONFIGURATION OR ADDING INDEXES

When to Use This Skill

Configuring connection pooling (PgBouncer, Prisma pool, asyncpg pool, R2DBC pool)
Implementing Row Level Security (RLS) for multi-tenant data isolation
Designing worker queues with SKIP LOCKED and advisory locks
Preventing deadlocks in concurrent write workloads
Configuring VACUUM/ANALYZE and autovacuum tuning
Adding full-text search with tsvector/tsquery
Indexing JSONB columns (jsonb_ops vs jsonb_path_ops)
Reviewing any PostgreSQL schema or query against the 8-category rule library

Do Not Use This Skill When

You need query EXPLAIN analysis and index pattern SQL (use sql-optimization-patterns)
You need schema migration design or ERDs (use database-schema-designer)
You need cloud analytics or HTAP (use sql-pro)

Rule Categories by Priority

Priority	Category	Impact	Rules
1	Query Performance	CRITICAL	`rules/query-*.md` (5 rules)
2	Connection Management	CRITICAL	`rules/conn-*.md` (4 rules)
3	Security & RLS	CRITICAL	`rules/security-*.md` (3 rules)
4	Schema Design	HIGH	`rules/schema-*.md` (6 rules)
5	Concurrency & Locking	MEDIUM-HIGH	`rules/lock-*.md` (4 rules)
6	Data Access Patterns	MEDIUM	`rules/data-*.md` (4 rules)
7	Monitoring & Diagnostics	LOW-MEDIUM	`rules/monitor-*.md` (3 rules)
8	Advanced Features	LOW	`rules/advanced-*.md` (2 rules)

Quick Rule Index

Connection Management (NEW — not in other skills)

rules/conn-pooling.md — PgBouncer setup, pool size formula (cores × 2) + spindles, transaction vs session mode
rules/conn-limits.md — max_connections calculation, prevent database OOM crash
rules/conn-idle-timeout.md — Reclaim 30-50% idle connection slots
rules/conn-prepared-statements.md — Prepared statement conflicts in transaction-mode pooling

Concurrency & Locking (NEW — not in other skills)

rules/lock-skip-locked.md — Atomic claim-and-update queue pattern, 10x worker throughput
rules/lock-deadlock-prevention.md — Consistent lock ordering, deadlock prevention patterns
rules/lock-short-transactions.md — Minimize transaction duration, 3-5x throughput
rules/lock-advisory.md — Application-level coordination without row-level locks

Security & RLS (NEW depth — not in other skills)

rules/security-rls-basics.md — ALTER TABLE ... ENABLE ROW LEVEL SECURITY + policy patterns
rules/security-rls-performance.md — SECURITY DEFINER functions, 5-10x faster RLS queries
rules/security-privileges.md — Principle of least privilege, specific GRANT patterns

Monitoring & Maintenance (NEW — not in other skills)

rules/monitor-vacuum-analyze.md — VACUUM/ANALYZE patterns, autovacuum tuning
rules/monitor-pg-stat-statements.md — Enable and query pg_stat_statements
rules/monitor-explain-analyze.md — EXPLAIN ANALYZE usage guide

Advanced Features (NEW — not in other skills)

rules/advanced-full-text-search.md — tsvector, ts_rank, GIN full-text index, 100x vs LIKE
rules/advanced-jsonb-indexing.md — GIN index, jsonb_ops vs jsonb_path_ops trade-off

Query Performance (complementary to sql-optimization-patterns)

rules/query-missing-indexes.md — EXPLAIN Seq Scan detection
rules/query-index-types.md — B-tree/GIN/BRIN/Hash selection
rules/query-composite-indexes.md — Leftmost prefix rule
rules/query-covering-indexes.md — INCLUDE columns for Index Only Scan
rules/query-partial-indexes.md — Filtered index subsets

Schema Design (complementary to database-schema-designer)

rules/schema-primary-keys.md — IDENTITY vs UUID vs UUIDv7
rules/schema-data-types.md — Forbidden types, correct alternatives
rules/schema-foreign-key-indexes.md — Diagnostic SQL to find all missing FK indexes
rules/schema-lowercase-identifiers.md — snake_case, no quoted identifiers
rules/schema-partitioning.md — Range partitioning patterns
rules/schema-foreign-key-indexes.md — FK index enforcement

Stack Integration

Python / FastAPI (asyncpg + SQLAlchemy)

Connection pooling: asyncpg pool min_size/max_size configuration (see rules/conn-pooling.md)
RLS: set app.current_user_id session variable before queries
SKIP LOCKED: use in background task workers

NestJS / Prisma

Connection pooling: DATABASE_URL=...?connection_limit=10&pool_timeout=20 (see rules/conn-pooling.md)
Prepared statements: Prisma uses prepared statements — use session mode or pgbouncer=true parameter
SKIP LOCKED: use prisma.$queryRaw for queue worker queries

Java / Spring Boot WebFlux (R2DBC)

Connection pooling: r2dbc.pool.max-size, r2dbc.pool.initial-size (see rules/conn-pooling.md)
RLS: use ConnectionFactory with per-request session variable injection

Related Skills

database-schema-designer — Schema design, Flyway migrations, normalization
sql-optimization-patterns — EXPLAIN analysis, N+1, cursor pagination, batch ops
postgresql — PostgreSQL-specific types, gotchas, extensions, JSONB depth
sql-pro — Cloud analytics, HTAP, BigQuery/Snowflake
vector-database — pgvector HNSW/IVFFlat alignment

name	postgres-best-practices
description	Supabase PostgreSQL best practices library — 33 impact-rated rules (CRITICAL→LOW) covering connection pooling, RLS, locking, VACUUM, full-text search, and JSONB indexing. Use when writing, reviewing, or optimizing PostgreSQL queries, schema, or configuration.
argument-hint	[category or specific rule name]
allowed-tools	Read
context	fork
metadata	{"triggers":"connection pooling, pgbouncer, RLS row level security, SKIP LOCKED, queue workers, deadlock, advisory lock, VACUUM ANALYZE, full-text search tsvector, JSONB GIN index, pg_stat_statements, connection limits, max_connections, idle timeout, prepared statements, transaction mode pooling","related-skills":"database-schema-designer, sql-optimization-patterns, postgresql, sql-pro, vector-database","domain":"infrastructure","role":"optimizer","scope":"implementation","output-format":"document"}
last-reviewed

name	postgres-best-practices
description	Supabase PostgreSQL best practices library — 33 impact-rated rules (CRITICAL→LOW) covering connection pooling, RLS, locking, VACUUM, full-text search, and JSONB indexing. Use when writing, reviewing, or optimizing PostgreSQL queries, schema, or configuration.
argument-hint	[category or specific rule name]
allowed-tools	Read
context	fork
metadata	{"triggers":"connection pooling, pgbouncer, RLS row level security, SKIP LOCKED, queue workers, deadlock, advisory lock, VACUUM ANALYZE, full-text search tsvector, JSONB GIN index, pg_stat_statements, connection limits, max_connections, idle timeout, prepared statements, transaction mode pooling","related-skills":"database-schema-designer, sql-optimization-patterns, postgresql, sql-pro, vector-database","domain":"infrastructure","role":"optimizer","scope":"implementation","output-format":"document"}
last-reviewed