Skip to content
postgres-patterns logo

PostgreSQL Patterns

postgres-patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices.

affaan-m/ecc11.9Kinstalls273.3Kstars

SKILL.md

Full skill instructions

PostgreSQL Patterns

Quick reference for PostgreSQL best practices. For detailed guidance, use the database-reviewer agent.

When to Activate

  • Writing SQL queries or migrations
  • Designing database schemas
  • Troubleshooting slow queries
  • Implementing Row Level Security
  • Setting up connection pooling

Quick Reference

Index Cheat Sheet

Query PatternIndex TypeExample
WHERE col = valueB-tree (default)CREATE INDEX idx ON t (col)
WHERE col > valueB-treeCREATE INDEX idx ON t (col)
WHERE a = x AND b > yCompositeCREATE INDEX idx ON t (a, b)
WHERE jsonb @> '{}'GINCREATE INDEX idx ON t USING gin (col)
WHERE tsv @@ queryGINCREATE INDEX idx ON t USING gin (col)
Time-series rangesBRINCREATE INDEX idx ON t USING brin (col)

Data Type Quick Reference

Use CaseCorrect TypeAvoid
IDsbigintint, random UUID
Stringstextvarchar(255)
Timestampstimestamptztimestamp
Moneynumeric(10,2)float
Flagsbooleanvarchar, int

Common Patterns

Composite Index Order:

-- 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'

Covering Index:

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

Partial Index:

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

RLS Policy (Optimized):

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

UPSERT:

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

Cursor Pagination:

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

Queue Processing:

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 *;

Anti-Pattern Detection

-- 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;

Configuration Template

-- 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();

Related

  • Agent: database-reviewer - Full database review workflow
  • Skill: clickhouse-io - ClickHouse analytics patterns
  • Skill: backend-patterns - API and backend patterns

Based on Supabase Agent Skills (credit: Supabase team) (MIT License)

More skills from affaan-m

coding-standards logo
affaan-m/ecc

coding-standards

Baseline cross-project coding conventions for naming, readability, immutability, and code-quality review. Use detailed frontend or backend skills for framework-specific patterns.

273.3K 14K
View
golang-testing logo
affaan-m/ecc

golang-testing

Go testing patterns including table-driven tests, subtests, benchmarks, fuzzing, and test coverage. Follows TDD methodology with idiomatic Go practices.

273.3K 13.1K
View
docker-patterns logo
affaan-m/ecc

docker-patterns

Docker and Docker Compose patterns for local development, container security, networking, volume strategies, and multi-service orchestration.

273.3K 12.7K
View

Spring Boot architecture patterns, REST API design, layered services, data access, caching, async processing, and logging. Use for Java Spring Boot backend work.

273.3K 12.2K
View
video-editing logo
affaan-m/ecc

video-editing

AI-assisted video editing workflows for cutting, structuring, and augmenting real footage. Covers the full pipeline from raw capture through FFmpeg, Remotion, ElevenLabs, fal.ai, and final polish in Descript or CapCut. Use when the user wants to edit video, cut footage, create vlogs, or build video content.

273.3K 12.2K
View
python-patterns logo
affaan-m/ecc

python-patterns

Pythonic idioms, PEP 8 standards, type hints, and best practices for building robust, efficient, and maintainable Python applications.

273.3K 12.1K
View
frontend-slides logo
affaan-m/ecc

frontend-slides

Create stunning, animation-rich HTML presentations from scratch or by converting PowerPoint files. Use when the user wants to build a presentation, convert a PPT/PPTX to web, or create slides for a talk/pitch. Helps non-designers discover their aesthetic through visual exploration rather than abstract choices.

273.3K 12.1K
View
api-design logo
affaan-m/ecc

api-design

REST API design patterns including resource naming, status codes, pagination, filtering, error responses, versioning, and rate limiting for production APIs.

273.3K 11.9K
View
django-patterns logo
affaan-m/ecc

django-patterns

Django architecture patterns, REST API design with DRF, ORM best practices, caching, signals, middleware, and production-grade Django apps.

273.3K 11.9K
View
tdd-workflow logo
affaan-m/ecc

tdd-workflow

Use this skill when writing new features, fixing bugs, or refactoring code. Enforces test-driven development with 80%+ coverage including unit, integration, and E2E tests.

273.3K 11.6K
View
prompt-optimizer logo
affaan-m/ecc

prompt-optimizer

Analyze raw prompts, identify intent and gaps, match ECC components (skills/commands/agents/hooks), and output a ready-to-paste optimized prompt. Advisory role only — never executes the task itself. TRIGGER when: user says "optimize prompt", "improve my prompt", "how to write a prompt for", "help me prompt", "rewrite this prompt", or explicitly asks to enhance prompt quality. Also triggers on Chinese equivalents: "优化prompt", "改进prompt", "怎么写prompt", "帮我优化这个指令". DO NOT TRIGGER when: user wants the task executed directly, or says "just do it" / "直接做". DO NOT TRIGGER when user says "优化代码", "优化性能", "optimize performance", "optimize this code" — those are refactoring/performance tasks, not prompt optimization.

273.3K 11.3K
View

Java coding standards for Spring Boot and Quarkus services: naming, immutability, Optional usage, streams, exceptions, generics, CDI, reactive patterns, and project layout. Automatically applies framework-specific conventions.

273.3K 11.3K
View

Popular AI tools

Kaiber logo
Video

Kaiber

Generate, edit, and beat-sync AI video with leading models in one workspace.

Paid
View
Vimcal logo
Productivity

Vimcal

The world's fastest calendar for remote work

Free
View

Transform Your Design with AI Designer by ImgCreator.ai

Freemium
View
Akool AI logo
Content & writing

Akool AI

Revolutionizing Video Production with AI-Powered Creativity

Paid
View

Extend an image past the frame and let AI fill the new aspect ratio.

Freemium
View
StarByFace logo
Security

StarByFace

Discover your celebrity doppelgänger with StarByFace!

Free
View
C

ChainClarity explains 700+ crypto whitepapers in plain English, with layered summaries, comparisons, research tools, alerts, and a $4.99 Pro plan.

Freemium
View
Opus Clip logo
Coding & apps

Opus Clip

Opus.ai: Revolutionize Your Web Experience

Free
View