Patrones de PostgreSQL — Cultiva Data

🎯

Contexto aplicado: Cultiva Data SaaS

SaaS B2B de analítica de marketing sobre Supabase (PostgreSQL 16). Tablas críticas: campaigns (5 M filas), leads (200 K), events time-series (50 M), ad_groups, accounts. Problemas detectados: FK sin índice, paginación con OFFSET, queries lentas >800 ms, RLS sin wrapper SELECT, cantidades monetarias en float.

Elección de Índice por Patrón de Consulta

Patrón de Query	Tipo Índice	Ejemplo aplicado a Cultiva Data	Nota
WHERE col = value	B-tree	CREATE INDEX ON campaigns (account_id)	Default; ideal para FK
WHERE col > value	B-tree	CREATE INDEX ON leads (score)	Ordenación + rangos
WHERE a = x AND b > y	Compuesto	CREATE INDEX ON campaigns (status, created_at)	Igualdad primero, rango al final
WHERE jsonb @> '{}'	GIN	CREATE INDEX ON events USING gin (properties)	Full-text y JSONB
WHERE tsv @@ query	GIN	CREATE INDEX ON leads USING gin (search_vector)	Búsqueda full-text
Series temporales (append-only)	BRIN	CREATE INDEX ON events USING brin (occurred_at)	Tabla events 50M filas — enorme ahorro
SELECT incluye cols extra	Covering	CREATE INDEX ON campaigns (id) INCLUDE (name, status, budget)	Evita table lookup

Tipos de Datos Correctos

Uso	Tipo correcto	Evitar	Ejemplo en Cultiva Data
IDs primarias	bigint GENERATED ALWAYS AS IDENTITY	int, random UUID	campaigns.id bigint
Strings de longitud variable	text	varchar(255)	campaigns.name text
Timestamps con zona horaria	timestamptz	timestamp	events.occurred_at timestamptz
Cantidades monetarias	numeric(12,2)	float, double	campaigns.budget numeric(12,2)
Flags booleanos	boolean	varchar, int, smallint	leads.is_qualified boolean
Datos semiestructurados	jsonb	json, text	events.properties jsonb
Enumerados estables	text + CHECK constraint	ENUM type	campaigns.status text CHECK (status IN ('draft','active','paused','ended'))

Patrones SQL — Copiar y Adaptar

Índice Compuesto — fix >800 ms en campaigns

Performance

-- Igualdad primero, rango al final
CREATE INDEX idx_campaigns_status_date
  ON campaigns (status, created_at);

-- Sirve para:
-- WHERE status = 'active' AND created_at > '2025-01-01'
-- WHERE status = 'active' ORDER BY created_at DESC

Índice Covering — listado de campañas sin heap

Performance

-- Evita table lookup para la vista resumen
CREATE INDEX idx_campaigns_cover
  ON campaigns (account_id)
  INCLUDE (name, status, budget, created_at);

-- SELECT name, status, budget, created_at
-- FROM campaigns WHERE account_id = $1
-- Resuelto 100% desde el índice

Índice Parcial — solo leads activos

Performance

-- Índice más pequeño y rápido
CREATE INDEX idx_leads_email_active
  ON leads (email)
  WHERE deleted_at IS NULL;

-- Solo ~180K filas activas en lugar de 200K
-- Actualizaciones soft-delete no lo invalidan

BRIN — tabla events 50 M filas

Time-Series

-- BRIN en vez de B-tree: 300x menos espacio
CREATE INDEX idx_events_brin
  ON events USING brin (occurred_at);

-- Ideal para datos append-only cronológicos
-- Funciona porque filas contiguas en disco
-- tienen rangos de fechas solapados

RLS Optimizado — wrapper SELECT

Seguridad

-- MAL: auth.uid() se evalúa por fila
-- CREATE POLICY ... USING (auth.uid() = user_id)

-- BIEN: wrapper SELECT evalúa una sola vez
CREATE POLICY campaigns_owner_policy
  ON campaigns
  USING ((SELECT auth.uid()) = user_id);

-- Impacto: de O(n) a O(1) por query en Supabase

UPSERT — sincronizar métricas de campañas

Upsert

INSERT INTO campaign_metrics
  (campaign_id, date, impressions, clicks, spend)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (campaign_id, date)
DO UPDATE SET
  impressions = EXCLUDED.impressions,
  clicks      = EXCLUDED.clicks,
  spend       = EXCLUDED.spend,
  updated_at  = now();

Cursor Pagination — listado leads O(1)

Performance

-- MAL: OFFSET es O(n) — cada vez más lento
-- SELECT * FROM leads LIMIT 20 OFFSET 5000

-- BIEN: cursor por id — siempre O(1)
SELECT id, email, score, created_at
FROM leads
WHERE id > $last_id
  AND deleted_at IS NULL
ORDER BY id
LIMIT 20;
-- Devolver el último id como cursor al cliente

Queue Processing — exportaciones async

Queue

UPDATE export_jobs
  SET status = 'processing', started_at = now()
WHERE id = (
  SELECT id FROM export_jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED  -- sin bloqueos entre workers
)
RETURNING *;

Diagnóstico — Detectar Anti-patrones en Producción

FK sin índice

-- Detecta ad_groups → campaigns → accounts sin índice
SELECT
  c.conrelid::regclass AS tabla,
  a.attname            AS columna_fk
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)
  )
ORDER BY tabla;

Queries lentas (>100 ms)

-- Requiere pg_stat_statements activado
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  left(query, 60)  AS query_preview,
  round(mean_exec_time::numeric, 1) AS avg_ms,
  calls,
  round(total_exec_time::numeric/1000,1) AS total_s
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

Table Bloat (dead tuples)

-- Tablas con vacío pendiente
SELECT
  relname          AS tabla,
  n_dead_tup       AS filas_muertas,
  n_live_tup       AS filas_vivas,
  last_vacuum::date,
  last_autovacuum::date
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Plantilla de Configuración del Sistema

postgresql.conf — Ajustes para Cultiva Data (16 GB RAM, 200 conexiones)

-- Conexiones (ajustar según RAM disponible)
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET work_mem = '16MB';          -- RAM / (max_connections * 2)
ALTER SYSTEM SET shared_buffers = '4GB';      -- 25% de la RAM total
ALTER SYSTEM SET effective_cache_size = '12GB';

-- Timeouts (evitar conexiones zombi y queries runaway)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '60s';
ALTER SYSTEM SET lock_timeout = '10s';

-- Monitorización (Supabase activa pg_stat_statements por defecto)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
ALTER SYSTEM SET pg_stat_statements.track = 'all';

-- Seguridad: revocar permisos del schema público
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT USAGE ON SCHEMA public TO service_role;

-- Aplicar cambios sin reiniciar
SELECT pg_reload_conf();