Postgres Best Practices

PostgreSQL Best Practices

When to Use This Skill

• Writing SQL queries or designing schemas
• Implementing indexes or query optimization
• Reviewing database performance issues
• Configuring connection pooling or scaling
• Working with Row-Level Security (RLS)
• Supabase or any PostgreSQL database work

Priority Categories

1. Query Performance (Critical)

-- BAD: SELECT * pulls unnecessary columns
SELECT * FROM users WHERE id = 1;

-- GOOD: Select only needed columns
SELECT id, name, email FROM users WHERE id = 1;

-- BAD: N+1 queries
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users);

-- GOOD: Use JOINs
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;

Index Guidelines:

• Create indexes on columns used in WHERE, JOIN, ORDER BY
• Use composite indexes for multi-column queries (order matters!)
• Prefer partial indexes for filtered queries
• Use EXPLAIN ANALYZE to verify index usage

-- Composite index (col1, col2) supports:
-- WHERE col1 = x
-- WHERE col1 = x AND col2 = y
-- ORDER BY col1, col2

-- But NOT:
-- WHERE col2 = y (without col1)

2. Connection Management (Critical)

Connection Pooling:

• Use PgBouncer or Supabase's built-in pooler
• Transaction pooling mode for serverless
• Session pooling for persistent connections
• Keep connections under pool limits

// Serverless function best practice
const pool = new Pool({
  max: 10,           // Limit connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

3. Schema Design (High)

Normalization:

• Follow 3NF unless performance requires denormalization
• Use proper data types (don't store numbers as text)
• Add constraints (NOT NULL, CHECK, FOREIGN KEY)

-- Use appropriate types
CREATE TABLE products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  quantity INTEGER NOT NULL DEFAULT 0 CHECK (quantity >= 0),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Naming Conventions:

• Tables: plural, snake_case (users, order_items)
• Columns: snake_case (created_at, user_id)
• Indexes: idx_table_column (idx_users_email)
• Constraints: table_column_type (users_email_unique)

4. Concurrency & Locking (Medium-High)

-- Use row-level locks appropriately
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Avoid long transactions
BEGIN;
  -- Keep transaction short!
COMMIT;

-- Use SKIP LOCKED for job queues
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

5. Security & RLS (Medium-High)

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

-- Create policies
CREATE POLICY "Users see own docs" ON documents
  FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users insert own docs" ON documents
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

Security Checklist:

• RLS enabled on all user-facing tables
• Parameterized queries (never string concatenation)
• Minimal privileges per role
• Audit logging for sensitive operations

6. Data Access Patterns (Medium)

Pagination:

-- BAD: OFFSET is slow for large pages
SELECT * FROM items LIMIT 20 OFFSET 10000;

-- GOOD: Cursor-based pagination
SELECT * FROM items
WHERE id > $last_seen_id
ORDER BY id
LIMIT 20;

Batch Operations:

-- Use bulk inserts
INSERT INTO items (name, value) VALUES
  ('a', 1), ('b', 2), ('c', 3);

-- Use COPY for large imports
COPY items FROM '/path/to/data.csv' WITH CSV HEADER;

7. Monitoring & Diagnostics (Low-Medium)

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Monitor connections
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

8. Advanced Features (Low)

Full-Text Search:

-- Create search index
CREATE INDEX idx_docs_search ON documents
USING GIN (to_tsvector('english', content));

-- Query with ranking
SELECT *, ts_rank(to_tsvector(content), query) AS rank
FROM documents, plainto_tsquery('search terms') query
WHERE to_tsvector('english', content) @@ query
ORDER BY rank DESC;

JSON Operations:

-- Store and query JSONB
SELECT data->>'name' AS name
FROM records
WHERE data @> '{"active": true}';

Quick Reference