postgresql-patterns

Guide PostgreSQL schema design, indexing, and advanced query patterns.

2|Updated Mar 15, 2025

Search Tags:#indexing #schema design #database #postgresql #sql #performance tuning #jsonb

Ready to run

npx skills add https://github.com/dandudzi/dotfiles --skill postgresql-patterns-dandudzi

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: postgresql-patterns
Source: https://github.com/dandudzi/dotfiles/tree/main/dot_claude/skills/postgresql-patterns
Command: npx skills add https://github.com/dandudzi/dotfiles --skill postgresql-patterns-dandudzi

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill provides comprehensive guidance on designing efficient PostgreSQL schemas, optimizing data types, indexing strategies, and implementing advanced patterns for building robust and scalable database solutions.

Core Features & Use Cases

Schema Design: Best practices for data types, constraints, and normalization.
Indexing Strategies: Guidance on B-tree, GIN, GiST, and partial indexes for performance.
Advanced Patterns: JSONB querying, window functions, CTEs, and upserts.
Use Case: Optimize slow-running queries by understanding composite index ordering and implementing partial indexes for frequently accessed subsets of data.

Quick Start

Use the postgresql-patterns skill to understand how to create a partial index on the 'orders' table for 'pending' status.

Frequently Asked Questions about postgresql-patterns

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

FAQPage Schema

How do I optimize slow PostgreSQL queries with indexing?▼

Optimize slow PostgreSQL queries by applying B-tree, GIN, and GiST indexes, or creating partial indexes for frequently accessed subsets. Understanding composite index ordering is also crucial for efficient data retrieval and performance tuning.

What is the best way to query JSONB data in PostgreSQL?▼

Querying JSONB data in PostgreSQL involves using specialized operators and indexing strategies like GIN indexes. This approach enables efficient storage and retrieval of semi-structured data within a relational database schema.

When should I use CTEs and window functions in SQL?▼

Use CTEs and window functions in SQL for complex analytical queries requiring row-by-row computations across related rows. These PostgreSQL patterns simplify complex aggregations and hierarchical data processing.

How do I implement upsert patterns in PostgreSQL?▼

Implement PostgreSQL upsert patterns using INSERT ON CONFLICT statements to update existing rows or insert new ones. This ensures data consistency without needing separate update and insert queries.

What transaction isolation levels does PostgreSQL support?▼

PostgreSQL supports multiple transaction isolation levels to manage concurrency and data visibility. Understanding these levels, alongside row-level security, is essential for building robust and scalable relational database systems.