PostgreSQL desde cero (III): SQL avanzado

Tercera entrega de la serie PostgreSQL desde cero a pro. Tiempo de lectura estimado: 13 minutos.

Ya sabes instalar PostgreSQL (I) y diseñar un buen esquema con tipos y restricciones (II). Ahora toca la parte divertida: las partes del lenguaje SQL que separan a alguien que "sabe SQL" de alguien que de verdad saca partido a PostgreSQL.

Si ya tienes algo de experiencia, partes de este post te sonarán del post PostgreSQL: 10 consultas que todo desarrollador debería conocer. Lo que hago aquí es sistematizar y ampliar.

CTEs (Common Table Expressions)

Una CTE es una subquery con nombre. La sintaxis es WITH ... AS (...) y se puede encadenar:

WITH paid_invoices AS (
  SELECT * FROM invoices WHERE status = 'paid'
),
monthly AS (
  SELECT
    date_trunc('month', paid_at) AS month,
    sum(amount) AS total
  FROM paid_invoices
  GROUP BY 1
)
SELECT
  month,
  total,
  total - lag(total) OVER (ORDER BY month) AS diff
FROM monthly
ORDER BY month;

Ventajas:

Legibilidad: rompe consultas grandes en pasos con nombre.
Reutilización dentro de la consulta: una CTE puede referenciarse varias veces.
Separación de lógica: cada CTE es una unidad comprensible por sí sola.

Históricamente, PostgreSQL trataba las CTEs como optimization fences (materializaba el resultado intermedio). Desde la versión 12, el planner puede inline la CTE cuando es mejor, salvo que la marques como WITH ... AS MATERIALIZED (...) o NOT MATERIALIZED.

CTEs recursivas

El nombre asusta pero la idea es simple: una CTE que se auto-referencia. La forma canónica:

WITH RECURSIVE
  anchor AS (SELECT ... base_case ...),
  recursive_step AS (SELECT ... FROM anchor ...)
SELECT * FROM (anchor UNION ALL recursive_step);

En la práctica se escribe así:

WITH RECURSIVE tree AS (
  -- Caso base
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Paso recursivo
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

Para cualquier estructura jerárquica (categorías, comentarios anidados, árbol de organización, grafo acíclico) esto es el patrón de libro.

Mutating CTEs

Las CTEs pueden contener INSERT, UPDATE o DELETE con RETURNING:

WITH deleted_rows AS (
  DELETE FROM expired_sessions WHERE expires_at < now() RETURNING id, user_id
)
INSERT INTO audit_log (action, entity, entity_id, user_id)
SELECT 'session_expired', 'session', id, user_id FROM deleted_rows;

Borrar y auditar en una sola consulta, con garantías transaccionales. Muy útil para pipelines de limpieza.

Window functions

Las window functions calculan un valor por fila usando una ventana de filas relacionadas, sin colapsar el resultado como hace GROUP BY.

Estructura general:

función() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...)

Funciones de ranking

SELECT
  department,
  employee,
  salary,
  row_number()  OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  rank()        OVER (PARTITION BY department ORDER BY salary DESC) AS rk,
  dense_rank()  OVER (PARTITION BY department ORDER BY salary DESC) AS drk
FROM employees;

Diferencia clave:

row_number(): números consecutivos únicos dentro de la partición.
rank(): misma posición para empates, salta números (1, 2, 2, 4).
dense_rank(): misma posición para empates, sin saltar (1, 2, 2, 3).

Top N por grupo, un clásico:

SELECT *
FROM (
  SELECT
    category_id, product_id, views,
    row_number() OVER (PARTITION BY category_id ORDER BY views DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;

Agregaciones como ventana

Cualquier función de agregación (sum, avg, count, min, max, string_agg, array_agg) puede usarse como window:

SELECT
  order_id,
  customer_id,
  total,
  sum(total) OVER (PARTITION BY customer_id ORDER BY created_at
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

lag, lead, first_value, last_value

SELECT
  day,
  value,
  lag(value, 1)  OVER (ORDER BY day) AS prev_day,
  lead(value, 1) OVER (ORDER BY day) AS next_day,
  value - lag(value, 1) OVER (ORDER BY day) AS diff
FROM metrics;

lag(x, n) devuelve el valor n filas antes; lead(x, n) lo contrario. Para series temporales, cálculo de deltas y movings, son insustituibles.

FILTER

Desde PostgreSQL 9.4, puedes filtrar dentro de una agregación sin usar CASE:

SELECT
  department,
  count(*)                                           AS total,
  count(*) FILTER (WHERE status = 'active')          AS active,
  count(*) FILTER (WHERE hired_at > now() - '1 year'::interval) AS new,
  avg(salary) FILTER (WHERE status = 'active')       AS avg_active_salary
FROM employees
GROUP BY department;

Es legible, rápido y hace la misma pasada por los datos.

LATERAL joins

LATERAL permite que una subquery a la derecha de un join se referencie a columnas de la izquierda. Es un "para cada fila de la izquierda, ejecuta esta subquery".

Ejemplo: últimos 3 pedidos de cada cliente, todo en una consulta:

SELECT
  c.id,
  c.name,
  o.created_at,
  o.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT created_at, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 3
) o ON true;

Equivalente funcional al "top N por grupo" que antes hacíamos con window + filtro, a veces más eficiente, a veces menos. Mide.

UPSERT con ON CONFLICT

Insertar-o-actualizar en una sola instrucción atómica:

INSERT INTO page_views (path, day, views)
VALUES ('/home', CURRENT_DATE, 1)
ON CONFLICT (path, day)
DO UPDATE SET views = page_views.views + EXCLUDED.views;

ON CONFLICT (...) declara el conflicto (una restricción única).
DO UPDATE indica qué hacer si hay conflicto.
EXCLUDED representa los valores que intentabas insertar.
DO NOTHING es la variante "INSERT IGNORE".

Esto es fundamental para contadores incrementales, estado denormalizado y cualquier flujo idempotente.

RETURNING

Cualquier INSERT, UPDATE o DELETE puede devolver filas:

UPDATE orders
SET status = 'shipped', shipped_at = now()
WHERE status = 'paid' AND paid_at < now() - '1 hour'::interval
RETURNING id, customer_id;

Una sola round trip para cambiar estado y recuperar los IDs afectados. Ideal para pipelines de procesamiento en lote.

Manipulación de JSONB

Recuperación y filtrado ya los vimos en la entrega II. Para modificar JSONB:

-- Añadir / sobreescribir clave
UPDATE events
SET data = data || '{"processed": true}'::jsonb
WHERE id = 42;

-- Eliminar clave
UPDATE events SET data = data - 'tmp' WHERE ...;

-- Ruta profunda con jsonb_set
UPDATE events
SET data = jsonb_set(data, '{user,plan}', '"premium"', true)
WHERE id = 42;

Desde PostgreSQL 16, el soporte de JSON_TABLE y expresiones jsonpath SQL/JSON es enorme. Para consultas muy específicas, míratelo.

Generate series

Una función que genera filas artificiales:

SELECT * FROM generate_series(1, 10);

SELECT generate_series(
  date_trunc('month', now()) - interval '11 months',
  date_trunc('month', now()),
  interval '1 month'
) AS month;

Muy útil para rellenar huecos en series temporales con LEFT JOIN:

SELECT
  gs.day,
  coalesce(count(v.id), 0) AS views
FROM generate_series(
  CURRENT_DATE - 30,
  CURRENT_DATE,
  interval '1 day'
) AS gs(day)
LEFT JOIN page_views v ON v.created_at::date = gs.day
GROUP BY gs.day
ORDER BY gs.day;

Si vienes de la entrega III de ClickHouse, esto es el equivalente a WITH FILL pero más verboso.

DISTINCT ON

Una extensión de PostgreSQL muy útil:

SELECT DISTINCT ON (user_id)
  user_id, event_type, created_at
FROM events
ORDER BY user_id, created_at DESC;

Devuelve "la primera fila por user_id según el orden dado". Equivalente a un window + filtro, pero mucho más conciso para el caso de "la última fila de cada grupo".

Full text search

PostgreSQL incluye búsqueda full-text integrada:

-- Columna auto-generada con el vector de búsqueda
ALTER TABLE posts
ADD COLUMN search tsvector GENERATED ALWAYS AS (
  to_tsvector('spanish', coalesce(title, '') || ' ' || coalesce(body, ''))
) STORED;

CREATE INDEX posts_search_idx ON posts USING GIN (search);

SELECT id, title, ts_rank(search, q) AS rank
FROM posts, plainto_tsquery('spanish', 'postgresql rendimiento') AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 10;

No reemplaza a Elasticsearch o Typesense para búsquedas muy sofisticadas, pero para un blog, un catálogo mediano o una documentación es más que suficiente y no añade infraestructura.

Ejemplo completo

Funnel de conversión calculado en una sola consulta:

WITH sessions AS (
  SELECT
    session_id,
    user_id,
    min(created_at) FILTER (WHERE event = 'visit')     AS visited_at,
    min(created_at) FILTER (WHERE event = 'signup')    AS signed_up_at,
    min(created_at) FILTER (WHERE event = 'purchase')  AS purchased_at
  FROM events
  WHERE created_at >= now() - '30 days'::interval
  GROUP BY session_id, user_id
),
funnel AS (
  SELECT
    count(*)                                              AS visits,
    count(*) FILTER (WHERE signed_up_at IS NOT NULL)      AS signups,
    count(*) FILTER (WHERE purchased_at IS NOT NULL)      AS purchases
  FROM sessions
  WHERE visited_at IS NOT NULL
)
SELECT
  visits,
  signups,
  purchases,
  round(100.0 * signups   / nullif(visits, 0), 2)   AS signup_rate,
  round(100.0 * purchases / nullif(signups, 0), 2)  AS conversion_rate
FROM funnel;

CTEs, FILTER, agregaciones condicionales y nullif para evitar divisiones por cero. Todo en una consulta declarativa que se lee de arriba a abajo.

Por dónde seguir

IV: índices, EXPLAIN y rendimiento — cómo averiguar por qué esta consulta que acabamos de escribir tarda tres segundos.
V: replicación, backups y producción — último tramo de la serie.

Si te perdiste algo: I o II.

PostgreSQL desde cero (III): CTEs, window functions y consultas avanzadas