skills.rest
dandudzi
dandudziCOMMUNITY
Explore all skills by this author →
GitHub

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
One-click install
npx skills add https://github.com/dandudzi/dotfiles --skill postgresql-patterns-dandudzi
Or copy as Structured Prompt for Agent▼
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.

Related Skills

Explore More Software Engineering Skills→
yashvinthanyashvinthan

postgresql-table-design

Design PostgreSQL database schemas with data types, indexing, and constraints.

Community
Advanced
randoneeringrandoneering

postgresql

Analyze PostgreSQL configurations, queries, and health metrics for optimization.

Community
Advanced
ExpeditoJExpeditoJ

aiox-data-engineer

Design PostgreSQL schemas, RLS policies, and query optimizations.

Community
Intermediate
rnavarychrnavarych
14

role-database:newsql-distributed

Compare 12 NewSQL and distributed SQL databases for consistency and sharding.

Community
Advanced