skills.rest
pjherron
pjherronCOMMUNITY
Explore all skills by this author →
GitHub

postgres-patterns

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

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

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Writing efficient PostgreSQL queries and schemas requires knowing which index types, data types, and patterns fit each situation, and mistakes like missing indexes or wrong types cause slow queries and production issues. ## Core Features & Use Cases - Index Selection Guide: Cheat sheet mapping query patterns to B-tree, GIN, BRIN, composite, covering, and partial indexes. - Anti-Pattern Detection: Ready-to-run SQL queries that find unindexed foreign keys, slow queries via pg_stat_statements, and table bloat. - Security & Configuration Templates: Optimized Row Level Security policies, UPSERT and cursor pagination patterns, and connection/timeout configuration defaults. - Use Case: When a query on your orders table is slow, consult the index cheat sheet to add a composite index on (status, created_at) and verify the fix with pg_stat_statements. ## Quick Start Ask the assistant to review your PostgreSQL query or schema using postgres-patterns and suggest the right index 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 PostgreSQL index type?▼

Match the index to your query pattern: B-tree for equality and range filters, 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 find slow queries in PostgreSQL?▼

Enable the pg_stat_statements extension and query it for statements with high mean_exec_time, for example filtering above 100ms and ordering by mean execution time. This surfaces the queries most in need of optimization or indexing.

What data types should I use in PostgreSQL schema design?▼

Use bigint for IDs, text instead of varchar(255) for strings, timestamptz for timestamps, numeric(10,2) for money, and boolean for flags. Avoid float for currency and plain timestamp without time zone.

How do I write an optimized Row Level Security policy?▼

Wrap the auth function call in a SELECT subquery, such as USING ((SELECT auth.uid()) = user_id), so PostgreSQL evaluates it once per query instead of once per row. This significantly improves RLS policy performance.

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) and scales to large tables.

Related Skills

Explore More Software Engineering Skills→
vix-4800vix-4800
1

database-patterns

Review database schemas, migrations, and ORM query strategies for safe data evolution.

Community
Intermediate
selrai-companyselrai-company

postgresql-optimization

Analyze EXPLAIN ANALYZE and pg_stat_statements to recommend PostgreSQL indexes and configuration.

Official
Advanced
allanninalallanninal
1

postgres-best-practices

Optimize PostgreSQL performance, security, and schema design.

Community
Intermediate
sumeetonline90sumeetonline90

clickhouse-io

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

Community
Intermediate