postgres-patterns

Name: postgres-patterns
Availability: InStock
Author: flag3

Provide PostgreSQL query, schema, indexing, and security patterns aligned with Supabase best practices.

1|Updated Mar 20, 2024

Search Tags:#security #indexing #postgresql #rls #patterns #query-optimization #schema-design

Ready to run

npx skills add https://github.com/flag3/dotfiles --skill postgres-patterns

💬 Prompt for ChatGPT / Claude Chat▼

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

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Solves PostgreSQL query inefficiencies and poor schema decisions by providing proven patterns.

Core Features & Use Cases

Query optimization templates: pattern-based guidance for common WHERE, JOIN, and aggregation scenarios to reduce latency.
Indexing and schema design: recommendations for B-tree, BRIN, GIN for text and JSON, partial and composite indexes, and effective table design.
Security and governance: guidance on Row Level Security (RLS) and safe defaults to control data access.
Use Case: Engineers refactor a slow reporting query by applying a composite index and an appropriate filter strategy to speed up execution.

Quick Start

To start, review your current workload and run a pattern audit against a sample schema to identify candidate indexes and RLS policies that align with Supabase best practices.

Frequently Asked Questions about postgres-patterns

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

FAQPage Schema

How do I optimize slow PostgreSQL queries?▼

Query optimization uses proven patterns like composite indexes, efficient JOIN strategies, and targeted WHERE filters to reduce latency. Apply pattern-based guidance to your workload—composite indexes on frequently filtered columns and appropriate filter strategies typically deliver the fastest results.

What indexing strategies work best for PostgreSQL?▼

PostgreSQL indexing strategies include B-tree for standard columns, BRIN for large sequential data, and GIN for text and JSON fields. Partial and composite indexes further reduce query cost by indexing only relevant data subsets, aligned with your access patterns.

How do I set up Row Level Security in PostgreSQL?▼

Row Level Security (RLS) enforces data access control at the row level by applying policies that filter query results based on user context. RLS combined with safe security defaults prevents unauthorized data exposure and aligns with production governance best practices.

Can I apply these PostgreSQL patterns to my schema refactor?▼

Yes. Schema design patterns guide effective table structure, indexing decisions, and migration work across backend services and analytics environments. Run a pattern audit against your current schema to identify candidate indexes and RLS policies matching your workload.

What's the difference between index types for JSON and text fields?▼

GIN indexes optimize text search and JSON queries by indexing individual elements, enabling fast containment and equality checks. B-tree and BRIN serve different workloads—choose GIN for unstructured search, B-tree for range queries, and BRIN for large sequential datasets.

Why should I use partial indexes in PostgreSQL?▼

Partial indexes reduce index size and maintenance cost by indexing only rows matching a WHERE condition, speeding up queries on filtered subsets. They're ideal when most queries target a specific data slice, delivering faster execution and lower storage overhead.