postgres-best-practices（Postgres 最佳实践）

Postgres Best Practices

Postgres performance optimization guidelines from Supabase, prioritized by impact.

Categories (Priority Order)

1. Query Performance (Critical)

• Use EXPLAIN ANALYZE to understand query plans
• Add indexes on frequently queried columns (WHERE, JOIN, ORDER BY)
• Avoid SELECT * - specify only needed columns
• Use LIMIT for large result sets
• Optimize JOINs - ensure foreign keys are indexed
• Use prepared statements (Supabase client does this automatically)
• Batch operations when possible

2. Connection Management (Critical)

• Use connection pooling (Supabase provides this)
• Close connections properly
• Avoid connection leaks - use connection limits
• Monitor connection usage in Supabase dashboard
• Use server-side clients for server components
• Use client-side clients only in client components

3. Schema Design (High)

• Choose appropriate data types (UUID vs INTEGER, VARCHAR vs TEXT)
• Use NOT NULL constraints where appropriate
• Add foreign key constraints for data integrity
• Use ENUMs for fixed value sets
• Normalize appropriately - balance with query performance
• Use JSONB for flexible schema (products, metadata)

4. Concurrency & Locking (Medium-High)

• Use transactions for atomic operations
• Keep transactions short - avoid long-running transactions
• Use appropriate isolation levels
• Avoid deadlocks - acquire locks in consistent order
• Use SELECT FOR UPDATE carefully (can cause blocking)

5. Security & RLS (Medium-High)

• Enable RLS on all tables
• Create policies for tenant isolation in multitenant systems
• Test RLS policies thoroughly
• Use service role only when necessary (bypasses RLS)
• Validate inputs before database operations
• Use parameterized queries (Supabase client does this)

6. Data Access Patterns (Medium)

• Use pagination for large datasets (.range() in Supabase)
• Implement caching for frequently accessed data
• Use materialized views for complex aggregations
• Consider read replicas for read-heavy workloads
• Optimize for common query patterns

7. Monitoring & Diagnostics (Low-Medium)

• Monitor slow queries in Supabase dashboard
• Use pg_stat_statements for query analysis
• Set up alerts for performance degradation
• Review query logs regularly
• Track connection pool usage

8. Advanced Features (Low)

• Use full-text search (PostgreSQL tsvector)
• Consider partitioning for very large tables
• Use triggers judiciously
• Leverage Postgres extensions when needed

Common Patterns

Index Creation

-- Single column index
CREATE INDEX idx_products_tenant_id ON products(tenant_id);

-- Composite index (order matters!)
CREATE INDEX idx_products_tenant_category ON products(tenant_id, category_id);

-- Partial index (for filtered queries)
CREATE INDEX idx_products_active ON products(tenant_id) WHERE active = true;

-- Unique index
CREATE UNIQUE INDEX idx_products_sku ON products(tenant_id, sku);

Query Optimization

// ❌ Bad: SELECT * and no limit
const { data } = await supabase
  .from('products')
  .select('*');

// ✅ Good: Specific columns with limit
const { data } = await supabase
  .from('products')
  .select('id, name, price, image')
  .eq('tenant_id', tenant.id)
  .eq('active', true)
  .order('created_at', { ascending: false })
  .limit(20);

Pagination

// Use range for pagination
const pageSize = 20;
const page = 1;

const { data, error } = await supabase
  .from('products')
  .select('id, name, price')
  .eq('tenant_id', tenant.id)
  .range((page - 1) * pageSize, page * pageSize - 1);

RLS Policy Best Practices

-- Enable RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policy
CREATE POLICY "tenant_isolation" ON products
  FOR ALL
  USING (
    tenant_id = (
      SELECT id FROM tenants
      WHERE slug = current_setting('app.tenant_slug', true)
    )
  );

-- Public read policy (if needed)
CREATE POLICY "public_read_active" ON products
  FOR SELECT
  USING (
    active = true AND
    tenant_id = (
      SELECT id FROM tenants
      WHERE slug = current_setting('app.tenant_slug', true)
    )
  );

Connection Usage

// ✅ Server component - use server client
import { createClient } from '@/lib/supabase/server';

export async function ServerComponent() {
  const supabase = createClient();
  // Use supabase
}

// ✅ Client component - use client
'use client';
import { createClient } from '@/lib/supabase/client';

export function ClientComponent() {
  const supabase = createClient();
  // Use supabase
}

Performance Checklist

• Queries use indexes on WHERE/JOIN columns
• SELECT statements specify columns (not *)
• Large queries use LIMIT or pagination
• RLS policies are optimized (not too complex)
• Foreign keys have indexes
• Transactions are kept short
• Connection pooling is configured
• Slow queries are identified and optimized

Key Files

• supabase/migrations/ - Schema and indexes
• src/lib/supabase/ - Client configuration
• Supabase Dashboard - Query performance monitoring