Postgres Patterns

Data Type Reference

Cashe-Specific Patterns

RLS Policy (Optimized)

-- Use subquery pattern to avoid per-row function call
CREATE POLICY "Users own data" ON movements
  FOR ALL USING ((SELECT auth.uid()) = user_id);

Account Balances RPC

-- Already exists: get_account_balances
-- Uses CTEs for movements and transfers in 1 query
-- Replaced N*4 queries pattern with 2 parallel queries

Common Indexes for Cashe

CREATE INDEX idx_movements_user_date ON movements (user_id, date DESC);
CREATE INDEX idx_movements_user_type ON movements (user_id, type);
CREATE INDEX idx_transfers_user_date ON transfers (user_id, date DESC);
CREATE INDEX idx_categories_user_type ON categories (user_id, type);
CREATE INDEX idx_accounts_user ON accounts (user_id);

Cursor Pagination

-- For movement lists with infinite scroll
SELECT * FROM movements
WHERE user_id = $1 AND date < $cursor_date
ORDER BY date DESC LIMIT 20;

Batch Operations

-- GOOD: Multi-row insert for installment purchases
INSERT INTO movements (user_id, type, date, amount, account_id, category_id, installment_purchase_id, installment_number, total_installments)
VALUES
  ($1, 'expense', $2, $3, $4, $5, $6, 1, $7),
  ($1, 'expense', $2 + interval '1 month', $3, $4, $5, $6, 2, $7);

Anti-Pattern Detection

-- Find unindexed foreign keys
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- Find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

Migration Safety

• Every change is a migration, never alter production manually
• New columns: nullable or with DEFAULT (never NOT NULL without default on existing tables)
• Indexes: CREATE INDEX CONCURRENTLY for existing tables
• Column removal: remove code first, drop column in next deploy
• Test migrations against production-sized data