postgres-patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Quick reference for common patterns, index types, data types, and anti-pattern detection. Based on Supabase best practices.

Jump to install

Repository: affaan-m/ECC
Last source activity: March 23, 2026 at 04:55
Detected SKILL.md language: English
Stars: 244,512
Forks: 36,960

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/affaan-m/ECC --skill postgres-patterns

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.

SKILL.md

Source instructions · Read-only preview

Back to search Back to installation

CREATE INDEX idx ON t USING gin (col)

-- Equality columns first, then range columns
CREATE INDEX idx ON orders (status, created_at);
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- Avoids table lookup for SELECT email, name, created_at

CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- Smaller index, only includes active users

CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- Wrap in SELECT!

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) vs OFFSET which is O(n)

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

-- Find unindexed foreign keys
SELECT conrelid::regclass, a.attname
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)
  );

-- Find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- Check table bloat
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Connection limits (adjust for RAM)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- Timeouts
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- Monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Security defaults
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

Query Pattern	Index Type	Example
`WHERE col = value`	B-tree (default)	`CREATE INDEX idx ON t (col)`
`WHERE col > value`	B-tree	`CREATE INDEX idx ON t (col)`
`WHERE a = x AND b > y`	Composite	`CREATE INDEX idx ON t (a, b)`
`WHERE jsonb @> '{}'`	GIN	`CREATE INDEX idx ON t USING gin (col)`
`WHERE tsv @@ query`	GIN

Query Pattern	Index Type	Example
`WHERE col = value`	B-tree (default)	`CREATE INDEX idx ON t (col)`
`WHERE col > value`	B-tree	`CREATE INDEX idx ON t (col)`
`WHERE a = x AND b > y`	Composite	`CREATE INDEX idx ON t (a, b)`
`WHERE jsonb @> '{}'`	GIN	`CREATE INDEX idx ON t USING gin (col)`
`WHERE tsv @@ query`	GIN

Use Case	Correct Type	Avoid
IDs	`bigint`	`int`, random UUID
Strings	`text`	`varchar(255)`
Timestamps	`timestamptz`	`timestamp`
Money	`numeric(10,2)`	`float`
Flags	`boolean`	`varchar`, `int`

postgres-patterns

Install options

Review the source files

PostgreSQL Patterns

When to Activate

Quick Reference

Index Cheat Sheet

PostgreSQL Patterns

When to Activate

Quick Reference

Index Cheat Sheet

Data Type Quick Reference

Common Patterns

Anti-Pattern Detection

Configuration Template

Related

When to Use This Skill

name	postgres-patterns
description	PostgreSQL database patterns for query optimization, schema design, indexing, and security. Quick reference for common patterns, index types, data types, and anti-pattern detection. Based on Supabase best practices.
metadata	{"origin":"ECC","credit":"Supabase team (MIT License)"}

postgres-patterns

Source facts

Install options

Review the source files

More from this repository

More from this repository

PostgreSQL Patterns

When to Activate

Quick Reference

Index Cheat Sheet

PostgreSQL Patterns

When to Activate

Quick Reference

Index Cheat Sheet

Data Type Quick Reference

Common Patterns

Anti-Pattern Detection

Configuration Template

Related

When to Use This Skill