skills.rest
adamreger
adamregerCOMMUNITY
Explore all skills by this author →
GitHub

postgres-patterns

Provides PostgreSQL patterns for indexing, schema design, query optimization, and row-level security.

2|Updated Feb 25, 2026
Search Tags:#query optimization#indexing#schema design#database#postgresql#row level security#sql
One-click install
npx skills add https://github.com/adamreger/ecc-antigravity --skill postgres-patterns-adamreger
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-patterns
Source: https://github.com/adamreger/ecc-antigravity/tree/main/skills/postgres-patterns
Command: npx skills add https://github.com/adamreger/ecc-antigravity --skill postgres-patterns-adamreger

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Writing efficient PostgreSQL queries and schemas requires knowing which index types, data types, and query patterns to use, and mistakes like unindexed foreign keys or OFFSET pagination cause slow queries and bloated tables. ## Core Features & Use Cases - Index Selection Guide: Cheat sheet mapping query patterns to B-tree, GIN, BRIN, composite, covering, and partial indexes. - Query Pattern Library: Ready-to-use SQL for UPSERT, cursor pagination, queue processing with SKIP LOCKED, and optimized RLS policies. - Anti-Pattern Detection: Diagnostic queries to find unindexed foreign keys, slow queries via pg_stat_statements, and table bloat. - Use Case: When designing a new orders table, use the composite index ordering rule (equality columns first, then range columns) and the data type reference to pick bigint IDs and timestamptz timestamps. ## Quick Start Ask the agent to review your SQL migration or slow query using the postgres-patterns skill and suggest the right indexes and data types.

Frequently Asked Questions about postgres-patterns

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I choose the right index type in PostgreSQL?▼

Match the index to your query pattern: B-tree for equality and range comparisons, GIN for jsonb containment and full-text search, and BRIN for time-series ranges. For multi-column filters, use a composite index with equality columns first, then range columns.

How do I optimize Row Level Security policies in PostgreSQL?▼

Wrap function calls like auth.uid() in a SELECT subquery inside the policy, for example USING ((SELECT auth.uid()) = user_id). This lets PostgreSQL cache the value per query instead of re-evaluating it for every row.

How do I find slow queries in PostgreSQL?▼

Enable the pg_stat_statements extension, then query it for statements with high mean_exec_time ordered by average execution time. This shows which queries consume the most time and how often they run.

Why is OFFSET pagination slow in PostgreSQL?▼

OFFSET requires scanning and discarding all skipped rows, making it O(n) as page depth grows. Cursor pagination using WHERE id > last_id ORDER BY id LIMIT n is O(1) because it uses the index directly.

What data types should I use for IDs and timestamps in PostgreSQL?▼

Use bigint for IDs instead of int or random UUIDs, and timestamptz instead of timestamp so timezone information is preserved. For money use numeric(10,2) rather than float to avoid rounding errors.

Related Skills

Explore More Software Engineering Skills→
dthuy62dthuy62

ck:databases

Design and optimize MongoDB and PostgreSQL schemas, queries, and indexes.

Community
Advanced
jeremylongshorejeremylongshore
2.6k

analyzing-query-performance

Analyze EXPLAIN plans and query metrics to identify database bottlenecks.

Community
Intermediate
OmniNode-aiOmniNode-ai
3

check-database-health

Monitor PostgreSQL database health, table statistics, connection pools, and query performance.

Official
Intermediate
sumeetonline90sumeetonline90

clickhouse-io

Optimize ClickHouse schemas, queries, and data ingestion for OLAP workloads.

Community
Intermediate