skills.rest
Femad-6
Femad-6COMMUNITY
Explore all skills by this author →
WebsiteGitHub

postgres-patterns

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

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

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 sheets 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 rules and data type reference to avoid common mistakes like using float for money or timestamp without time zone. ## Quick Start Ask the assistant to review your PostgreSQL schema or slow query using the postgres-patterns skill and suggest appropriate indexes.

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 composite indexes with equality columns first, then range columns.

How do I optimize a slow PostgreSQL query?▼

Start by checking pg_stat_statements to find queries with high mean execution time, then verify appropriate indexes exist for your WHERE clauses. Replace OFFSET pagination with cursor-based pagination using WHERE id > last_id for O(1) performance.

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

Use bigint for IDs, text instead of varchar(255), timestamptz instead of timestamp, numeric(10,2) for money instead of float, and boolean for flags. These choices avoid precision loss, timezone bugs, and storage inefficiency.

How do I write an efficient Row Level Security policy in PostgreSQL?▼

Wrap the auth function call in a SELECT subquery, such as USING ((SELECT auth.uid()) = user_id). This prevents the function from being re-evaluated for every row, significantly improving RLS policy performance.

How do I find unindexed foreign keys in PostgreSQL?▼

Query pg_constraint joined with pg_attribute to list foreign key columns, then check pg_index for missing indexes on those columns. Unindexed foreign keys cause slow joins and locking issues during deletes on parent tables.

Related Skills

Explore More Software Engineering Skills→
Mark393295827Mark393295827
1

postgres-patterns

Optimize PostgreSQL query performance and schema design with indexing patterns.

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