skills.rest
Briian3306
Briian3306COMMUNITY
Explore all skills by this author →
GitHub

supabase-postgres-best-practices

Provides Postgres performance optimization rules and SQL examples for queries, schemas, and RLS policies.

Updated Jan 31, 2023
Search Tags:#query optimization#indexing#supabase#row level security#sql#connection pooling#postgres
One-click install
npx skills add https://github.com/Briian3306/Transporte --skill supabase-postgres-best-practices-briian3306
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: supabase-postgres-best-practices
Source: https://github.com/Briian3306/Transporte/tree/main/ibarra-app/.agents/skills/supabase-postgres-best-practices
Command: npx skills add https://github.com/Briian3306/Transporte --skill supabase-postgres-best-practices-briian3306

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Slow queries, missing indexes, connection exhaustion, and insecure Row-Level Security policies are common Postgres issues that are hard to diagnose without deep expertise. This Skill gives AI agents a structured, prioritized rule set so generated or reviewed SQL follows proven performance and security practices. ## Core Features & Use Cases - Prioritized Rule Library: 20+ rules across 8 categories (query performance, connection management, security & RLS, schema design, locking, data access, monitoring, advanced features), each ranked by impact from CRITICAL to LOW. - Incorrect vs. Correct SQL Examples: Every rule shows the anti-pattern first, then the optimized rewrite, with quantified impact (e.g., "100-1000x faster queries", "10x smaller index"). - Supabase-Specific Guidance: Covers RLS policy optimization, SECURITY DEFINER functions, connection pooling modes, and prepared statement behavior with Supabase's pooler. - Use Case: When asked to review a slow endpoint, the agent consults rules like query-missing-indexes, data-n-plus-one, and security-rls-performance to rewrite the query with proper indexes, batch loading, and cached RLS checks. ## Quick Start Ask the agent to review your Postgres query or schema design for performance and security issues using the Supabase best practices rules.

Frequently Asked Questions about supabase-postgres-best-practices

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

FAQPage Schema
How do I optimize slow Postgres queries in Supabase?▼

Start by adding indexes on WHERE and JOIN columns, then use EXPLAIN ANALYZE to confirm the query plan changed from sequential scan to index scan. The skill's query performance rules cover missing indexes, composite indexes, partial indexes, and covering indexes with measurable impact estimates.

How to fix N+1 queries in Postgres applications?▼

Replace per-item queries in loops with a single batch query using WHERE id = ANY($1::bigint[]) or a JOIN. This reduces database round trips from N+1 to one, typically yielding 10-100x fewer round trips according to the data-n-plus-one rule.

Why is my RLS policy slow on large tables?▼

RLS policies that call auth.uid() directly invoke the function once per row. Wrapping it in a subselect like (select auth.uid()) caches the result, giving 100x+ speedups, and adding an index on the policy column further improves performance.

Does connection pooling work with prepared statements in Supabase?▼

Named prepared statements fail in transaction-mode pooling because connections are shared between requests. Use unnamed prepared statements, deallocate after use, or switch to session mode pooling where the connection persists for the whole session.

When should I use OFFSET pagination versus cursor pagination?▼

OFFSET pagination scans all skipped rows, so page 10000 reads 200,000 rows. Cursor (keyset) pagination with WHERE id > last_seen_id uses the index and stays O(1) regardless of page depth, making it the right choice for deep pagination.

What are the limitations of random UUIDs as primary keys?▼

Random UUIDv4 primary keys cause index fragmentation and scattered inserts on large tables. The skill recommends bigint identity columns for single databases, or time-ordered UUIDv7 via the pg_uuidv7 extension for distributed systems.

Related Skills

Explore More Software Engineering Skills→
sgcarstrendssgcarstrends
21

schema-design

Design and modify Drizzle ORM schemas with relationships, constraints, and indexes.

Official
Advanced
selrai-companyselrai-company

postgresql-optimization

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

Official
Advanced
Spectaculous-CodeSpectaculous-Code

rls-policy-validator

Validate and generate Supabase Row Level Security policies with SQL functions.

Official
Advanced
Marshall-HallenbeckMarshall-Hallenbeck
5

supabase-postgres-best-practices

Optimize Supabase PostgreSQL queries, schemas, connections, and security with rule-based guidance.

Community
Advanced