skills.rest
adriancodes
adriancodesCOMMUNITY
Explore all skills by this author →
GitHub

ecc-postgres-patterns

Reviews PostgreSQL schemas, indexes, queries, and RLS policies against established patterns.

Updated Apr 18, 2025
Search Tags:#query optimization#indexing#schema design#postgresql#supabase#row level security#database review
One-click install
npx skills add https://github.com/adriancodes/dotfiles --skill ecc-postgres-patterns-adriancodes
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: ecc-postgres-patterns
Source: https://github.com/adriancodes/dotfiles/tree/main/dot_agents/skills/ecc-postgres-patterns
Command: npx skills add https://github.com/adriancodes/dotfiles --skill ecc-postgres-patterns-adriancodes

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Slow queries, missing indexes, and unsafe schema or Row Level Security designs are hard to catch during development. This Skill provides a static reference of PostgreSQL patterns so reviews of schemas, migrations, and queries follow proven indexing, data type, and security practices. ## Core Features & Use Cases - Index Selection Guidance: Cheat sheets map query patterns (equality, range, JSONB containment, full-text search) to the right index type such as B-tree, GIN, or BRIN. - Schema and Data Type Patterns: Reference tables for choosing IDs, timestamps, money, and string types, plus composite, covering, and partial index examples. - Anti-Pattern Detection Queries: Ready-made catalog queries to find unindexed foreign keys, slow statements via pg_stat_statements, and tables with high dead-tuple counts. - Use Case: While writing a migration that adds an orders table, use this Skill to confirm the composite index column order, add an optimized RLS policy, and verify no foreign keys are missing indexes. ## Quick Start Ask the AI to review your PostgreSQL schema or slow query using the postgres patterns skill and suggest indexes, data types, and RLS improvements.

Frequently Asked Questions about ecc-postgres-patterns

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

FAQPage Schema
How do I choose the right PostgreSQL index type for a query?▼

Match the query pattern to the index type: B-tree for equality and range comparisons, GIN for JSONB containment and full-text search, and BRIN for ranges correlated with physical row order. Composite indexes should place equality columns before range columns.

How to find slow queries in PostgreSQL?▼

Query pg_stat_statements and sort by mean_exec_time to identify the slowest statements. The Skill provides a detection query filtering statements averaging over 100 milliseconds, which you can then optimize with appropriate indexes.

How do I write an optimized Row Level Security policy in Supabase?▼

Enable RLS on the table and create a policy comparing the owner column to (SELECT auth.uid()), wrapping the function call in a subselect so it is evaluated once per query. Also verify table ownership and other policies, since one policy alone does not prove isolation.

Does this Skill run SQL against my live database?▼

No. It is a static review reference only and does not authorize running SQL or changing a live database. Recommendations should be validated against your PostgreSQL version, domain, and workload before applying them.

How to detect missing indexes on foreign keys in PostgreSQL?▼

Use the provided catalog query joining pg_constraint, pg_attribute, and pg_index to list foreign key columns lacking index coverage. Treat results as candidates and verify composite coverage, index validity, and partial-index predicates before concluding an index is missing.

Related Skills

Explore More Software Engineering Skills→
paultyngpaultyng
9

review-database

Review PostgreSQL and MySQL migrations, queries, and schema designs.

Community
Advanced
JCETools-PetraJCETools-Petra
16

sql-database

Analyze slow SQL queries and generate indexing and migration plans.

Community
Advanced
paulinett1508-devpaulinett1508-dev

query-compliance

Review MongoDB and SQL queries and migrations for security and performance compliance.

Community
Intermediate
selrai-companyselrai-company

postgresql-optimization

Analyze EXPLAIN ANALYZE and pg_stat_statements to recommend PostgreSQL indexes and configuration.

Official
Advanced