supabase-postgres

name	supabase-postgres
description	Postgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.

Supabase Postgres Best Practices

Use this skill when working with PostgreSQL databases — writing queries, designing schemas, optimizing performance, or debugging database issues.

When to Use

• Writing or reviewing SQL queries
• Designing or modifying database schemas
• Optimizing query performance (EXPLAIN ANALYZE, indexes, etc.)
• Debugging connection issues, timeouts, or lock contention
• Working with pgvector extensions for embeddings
• Configuring connection pooling (PgBouncer, Supabase Pooler)

Key Patterns

Query Optimization

• Always use EXPLAIN (ANALYZE, BUFFERS) for slow queries
• Prefer EXISTS over IN for subqueries
• Use partial indexes for filtered queries
• Avoid SELECT * — specify columns explicitly

Schema Design

• Use timestamptz (not timestamp) for all datetime columns
• Prefer uuid with gen_random_uuid() over serial IDs
• Use jsonb (not json) for document columns
• Add ON DELETE CASCADE or ON DELETE SET NULL explicitly

Connection Management

• Use connection pooling for production workloads
• Set statement_timeout and lock_timeout per-query when needed
• Monitor pg_stat_activity for long-running queries

pgvector

• Use vector(1536) or vector(768) for embedding columns
• Create HNSW indexes for approximate nearest neighbor search
• Use ivfflat for smaller datasets (<1M rows)

Integration with hiai-bob

When the Researcher or Writer agents need database context:

1. Use docker exec ai-core-postgres psql -U aiuser -d ai_orchestration -c "..." (port 5433) for ai-core database
2. Use docker exec webs-postgres psql -U admin -d webs -c "..." (port 5432) for webs database
3. Always check table structures, foreign keys, and metadata BEFORE code changes

References

• Supabase Postgres Docs
• PostgreSQL EXPLAIN
• pgvector Documentation