postgres-patterns

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

1|Updated Mar 10, 2026

Search Tags:#security #indexing #schema design #database #postgresql #optimization #sql

Ready to run

npx skills add https://github.com/aleonsa/claude-config --skill postgres-patterns-aleonsa

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: postgres-patterns
Source: https://github.com/aleonsa/claude-config/tree/main/claude/skills/postgres-patterns
Command: npx skills add https://github.com/aleonsa/claude-config --skill postgres-patterns-aleonsa

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill provides best practices and quick references for optimizing PostgreSQL database performance, schema design, and security.

Core Features & Use Cases

Query Optimization: Guidance on indexing strategies (B-tree, GIN, BRIN), composite and covering indexes.
Schema Design: Recommendations for appropriate data types and common patterns like UPSERT and cursor pagination.
Security: Best practices for Row Level Security (RLS) policies.
Troubleshooting: Anti-pattern detection for slow queries, unindexed foreign keys, and table bloat.

Quick Start

Use the postgres-patterns skill to find the correct index type for a WHERE clause on a JSONB column.

Frequently Asked Questions about postgres-patterns

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

FAQPage Schema

How do I optimize PostgreSQL queries with the right index type?▼

To optimize PostgreSQL queries, use B-tree indexes for standard equality and range lookups, GIN indexes for JSONB or array columns, and BRIN indexes for large, physically sorted tables. Selecting the correct index type resolves slow query performance by ensuring efficient data retrieval.

What is the best way to implement cursor pagination in a PostgreSQL schema?▼

Cursor pagination in a PostgreSQL schema is best implemented using ordered, indexed columns to track the last fetched row. This approach avoids the performance overhead of OFFSET clauses on large datasets and provides stable, efficient pagination results.

How does Row Level Security work in PostgreSQL?▼

Row Level Security (RLS) in PostgreSQL works by attaching security policies directly to tables, restricting query access to specific rows based on the current user. Implementing RLS policies ensures database-level access control and enforces data isolation.

Why does my PostgreSQL query slow down when filtering JSONB columns?▼

Filtering PostgreSQL JSONB columns slows down when missing appropriate indexing. Creating GIN indexes on the JSONB column accelerates containment and key lookup operations, resolving the anti-pattern of sequentially scanning large JSON payloads.

When should I use a covering index instead of a composite index in PostgreSQL?▼

Use a covering index in PostgreSQL when you want to satisfy a query entirely from the index without accessing the table, including columns in the INCLUDE clause. A composite index is preferred when multiple columns are actively filtered or joined together in WHERE clauses.