skills.rest
Kimjiman
KimjimanCOMMUNITY
Explore all skills by this author →
GitHub

postgres-patterns

Guide PostgreSQL database patterns for query optimization, schema design, and security.

Updated May 24, 2023
Search Tags:#indexing#schema design#database#postgresql#rls#optimization#sql
One-click install
npx skills add https://github.com/Kimjiman/basic-arch --skill postgres-patterns-kimjiman
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-patterns
Source: https://github.com/Kimjiman/basic-arch/tree/main/.claude/skills/postgres-patterns
Command: npx skills add https://github.com/Kimjiman/basic-arch --skill postgres-patterns-kimjiman

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill provides a quick reference to PostgreSQL best practices, helping developers optimize database queries, design efficient schemas, implement effective indexing strategies, and enhance security.

Core Features & Use Cases

  • Query Optimization: Guidance on writing efficient SQL queries.
  • Schema Design: Best practices for data types and table structures.
  • Indexing Strategies: Recommendations for B-tree, GIN, BRIN, composite, covering, and partial indexes.
  • Security: Row Level Security (RLS) policy examples.
  • Advanced Patterns: UPSERT, cursor pagination, and queue processing examples.
  • Use Case: Troubleshoot a slow-running report by applying the correct index strategy or refactor a complex query using UPSERT for better performance.

Quick Start

Provide guidance on creating a composite index for a query involving equality and range conditions.

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 for slow running reports?▼

To optimize PostgreSQL queries, apply the correct indexing strategies such as composite or partial indexes, and refactor complex operations using advanced patterns like UPSERT to improve query performance.

What is the best way to create a composite index for equality and range conditions in PostgreSQL?▼

Creating a composite index for equality and range conditions involves placing the equality columns first in the index definition, followed by the range columns, to optimize PostgreSQL query execution.

How does Row Level Security (RLS) work for implementing database security measures?▼

Row Level Security (RLS) works by applying specific policy examples to database tables, restricting row access based on user roles to enhance PostgreSQL security measures and protect data.

When do I need to use advanced PostgreSQL patterns like cursor pagination or UPSERT?▼

You need to use advanced PostgreSQL patterns like cursor pagination and UPSERT when managing large datasets efficiently, ensuring data synchronization, and refactoring complex queries for better performance.

What PostgreSQL data types and schema design best practices should I follow for Supabase?▼

For robust schema design and Supabase best practices, select appropriate PostgreSQL data types and table structures that ensure efficient data modeling and facilitate optimized database management.

Related Skills

Explore More Software Engineering Skills→
aleonsaaleonsa

postgres-patterns

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

Community
Intermediate
zinohomezinohome
1

sql-query-optimizer

Analyze SQL EXPLAIN plans and recommend index strategies.

Community
Advanced
nopkhunnopkhun

backend-engineer

Integrate frontend applications with Supabase for backend services.

Community
Advanced
nist0nist0

postgres

Provide PostgreSQL schema design and query optimization advice.

Community
Advanced