skills.rest
bilacchi
bilacchiCOMMUNITY
Explore all skills by this author →
GitHub

postgres-patterns

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

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

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 unindexed foreign keys or wrong data types cause slow queries and maintenance pain. ## Core Features & Use Cases - Index Selection Guide: Cheat sheet mapping query patterns to the correct index type (B-tree, GIN, BRIN, composite, covering, partial). - Data Type Reference: Guidance on choosing correct types such as bigint for IDs, timestamptz for timestamps, and numeric for money. - Anti-Pattern Detection: Ready-to-run SQL queries that find unindexed foreign keys, slow queries via pg_stat_statements, and table bloat. - Use Case: When designing a new orders table, use this Skill to pick the right composite index for status-plus-date queries, apply an optimized RLS policy, and configure connection limits and timeouts. ## Quick Start Ask the AI to review your PostgreSQL schema or SQL query using the postgres-patterns skill and suggest indexes, data types, and security improvements.

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 for my query?▼

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

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

Use bigint for IDs, text instead of varchar(255) for strings, timestamptz instead of timestamp for timestamps, numeric(10,2) for money instead of float, and boolean for flags rather than varchar or int.

How do I find unindexed foreign keys in PostgreSQL?▼

Query pg_constraint joined with pg_attribute for foreign key constraints, then check pg_index for missing indexes on those columns. The skill provides a ready-to-run SQL query that returns the table and column names lacking indexes.

How do I optimize a Row Level Security policy in PostgreSQL?▼

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 policy performance on large tables.

Why is OFFSET pagination slow in PostgreSQL?▼

OFFSET pagination scans and discards 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 to jump directly to the starting point.

How do I detect slow queries and table bloat in PostgreSQL?▼

Enable the pg_stat_statements extension and query it for statements with high mean_exec_time to find slow queries. For bloat, check pg_stat_user_tables for tables with high n_dead_tup counts that need vacuuming.

Related Skills

Explore More Software Engineering Skills→
Zenobia000Zenobia000
2

postgres-patterns

Review PostgreSQL schemas and queries for optimization and security patterns.

Community
Intermediate
Hopin-incHopin-inc
3

db-migration

Generates Prisma migration plans and reviews SQL for TypeScript projects.

Official
Advanced
tonone-aitonone-ai
69

flux-recon

Inventory database schema, migrations, backups, connections, and query patterns.

Official
Advanced
andrewn6andrewn6

db-migration

Generate Drizzle ORM SQL migrations for Encore.ts PostgreSQL schemas.

Community
Intermediate