davila7/claude-code-templates / cli-tool/components/skills/development/postgres-best-practices
postgres-best-practices
VerifiedPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.
rules/schema-foreign-key-indexes.md
View on GitHub---
title: Index Foreign Key Columns
impact: HIGH
impactDescription: 10-100x faster JOINs and CASCADE operations
tags: foreign-key, indexes, joins, schema
---
## Index Foreign Key Columns
Postgres does not automatically index foreign key columns. Missing indexes cause slow JOINs and CASCADE operations.
**Incorrect (unindexed foreign key):**
```sql
create table orders (
id bigint generated always as identity primary key,
customer_id bigint references customers(id) on delete cascade,
total numeric(10,2)
);
-- No index on customer_id!
-- JOINs and ON DELETE CASCADE both require full table scan
select * from orders where customer_id = 123; -- Seq Scan
delete from customers where id = 123; -- Locks table, scans all orders
```
**Correct (indexed foreign key):**
```sql
create table orders (
id bigint generated always as identity primary key,
customer_id bigint references customers(id) on delete cascade,
total numeric(10,2)
);
-- Always index the FK column
create index orders_customer_id_idx on orders (customer_id);
-- Now JOINs and cascades are fast
select * from orders where customer_id = 123; -- Index Scan
delete from customers where id = 123; -- Uses index, fast cascade
```
Find missing FK indexes:
```sql
select
conrelid::regclass as table_name,
a.attname as fk_column
from pg_constraint c
join pg_attribute a on a.attrelid = c.conrelid and a.attnum = any(c.conkey)
where c.contype = 'f'
and not exists (
select 1 from pg_index i
where i.indrelid = c.conrelid and a.attnum = any(i.indkey)
);
```
Reference: [Foreign Keys](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK)
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |