PostgreSQL: 10 consultas que todo desarrollador debería conocer

PostgreSQL es la base de datos que más uso y la que más respeto. Es potente, fiable y tiene funcionalidades que mucha gente no conoce porque se queda en el SELECT * FROM y poco más. Estas son diez consultas que uso regularmente y que creo que todo desarrollador debería tener en su repertorio.

1. CTEs (Common Table Expressions)

Las CTEs te permiten escribir subqueries con nombre, haciendo que las consultas complejas sean legibles:

WITH monthly_revenue AS (
  SELECT
    date_trunc('month', created_at) AS month,
    SUM(amount) AS total
  FROM invoices
  WHERE status = 'paid'
  GROUP BY 1
)
SELECT
  month,
  total,
  total - LAG(total) OVER (ORDER BY month) AS diff_vs_previous
FROM monthly_revenue
ORDER BY month;

Sin la CTE, esto sería una subquery anidada ilegible. Con ella, primero calculas los ingresos mensuales y luego los comparas con el mes anterior. Cada paso tiene sentido por separado.

2. Window functions

Las window functions hacen cálculos sobre un conjunto de filas relacionadas sin agrupar los resultados. Son el salto cualitativo entre SQL básico y SQL de verdad:

SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_vs_avg
FROM employees;

Esto te da, para cada empleado, su ranking salarial dentro de su departamento y cuánto se desvía de la media. Sin window functions tendrías que hacer múltiples subqueries o joins consigo misma.

3. EXPLAIN ANALYZE

No es una consulta de datos sino una herramienta de diagnóstico. Si una consulta va lenta, EXPLAIN ANALYZE te dice exactamente por qué:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 42
  AND created_at > '2026-01-01';

Lo importante es leer de abajo hacia arriba. Busca los nodos con mayor actual time, los Seq Scan donde esperabas un Index Scan, y los rows= que difieran mucho del estimated=. Esas discrepancias suelen indicar estadísticas desactualizadas o índices faltantes.

4. Índices parciales

Un índice parcial indexa solo las filas que cumplen una condición. Ocupa menos espacio y es más eficiente:

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

Si el 95% de tus pedidos están completados y solo consultas los pendientes, este índice es mucho más pequeño y rápido que un índice completo sobre status. Es una de esas funcionalidades de PostgreSQL que no existen en MySQL y que cuando las descubres no puedes vivir sin ellas.

5. UPSERT (INSERT ... ON CONFLICT)

Insertar si no existe, actualizar si existe. Sin race conditions:

INSERT INTO page_views (url, count)
VALUES ('/post/mi-post', 1)
ON CONFLICT (url)
DO UPDATE SET count = page_views.count + 1;

Antes de que existiera ON CONFLICT había que hacer un SELECT + INSERT o UPDATE con lógica en la aplicación y rezar para que no hubiera una condición de carrera entre medias. Ahora es una operación atómica.

6. generate_series para rellenar huecos

Cuando haces un reporte por fechas, los días sin datos no aparecen. generate_series genera las fechas que faltan:

SELECT
  d::date AS day,
  COALESCE(COUNT(o.id), 0) AS orders
FROM generate_series(
  '2026-01-01'::date,
  '2026-01-31'::date,
  '1 day'::interval
) AS d
LEFT JOIN orders o ON o.created_at::date = d::date
GROUP BY 1
ORDER BY 1;

Esto te da todos los días de enero con el número de pedidos, incluyendo ceros para los días sin actividad. Es fundamental para gráficas y reportes porque un hueco en los datos distorsiona la visualización.

7. Búsqueda de texto con trigrams

Para búsqueda aproximada sin montar un motor de búsqueda externo:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_posts_title_trgm ON posts USING gin (title gin_trgm_ops);

SELECT title, similarity(title, 'posgresql') AS sim
FROM posts
WHERE title % 'posgresql'
ORDER BY sim DESC
LIMIT 10;

El operador % busca coincidencias por similitud. Nota que he escrito "posgresql" mal a propósito: la búsqueda por trigrams encuentra "PostgreSQL" igualmente. Es tolerante a errores tipográficos, que es exactamente lo que necesitas en un buscador.

8. LATERAL JOIN

Un LATERAL JOIN es como un bucle for en SQL: para cada fila de la tabla izquierda, ejecuta una subquery que puede referenciarla:

SELECT
  c.name,
  latest.amount,
  latest.created_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT amount, created_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 3
) AS latest;

Esto te da los tres últimos pedidos de cada cliente. Sin LATERAL tendrías que usar window functions con ROW_NUMBER() y filtrar. Con LATERAL es directo y además PostgreSQL lo optimiza bien porque puede usar el índice de customer_id para cada cliente.

9. Estadísticas de la base de datos

PostgreSQL guarda estadísticas sobre sí misma. Estas vistas del sistema son oro para entender qué pasa en producción:

-- Tablas más grandes
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

-- Índices sin usar
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Cache hit ratio (debería ser >99%)
SELECT
  sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

Los índices sin usar ocupan espacio y ralentizan las escrituras. El cache hit ratio te dice si necesitas más RAM. Estas consultas deberían ejecutarse periódicamente en cualquier base de datos en producción.

10. LISTEN / NOTIFY

PostgreSQL tiene un sistema de pub/sub integrado. Sin colas externas, sin Redis, sin Kafka:

-- En una conexión
LISTEN new_orders;

-- En otra conexión
NOTIFY new_orders, '{"order_id": 42}';

La aplicación que escucha recibe el mensaje en tiempo real. Es perfecto para invalidar caches, disparar webhooks o actualizar dashboards sin polling. No reemplaza a un sistema de colas para volúmenes altos, pero para notificaciones internas entre componentes de la misma aplicación es más que suficiente.

Conclusión

PostgreSQL tiene mucho más de lo que la mayoría de desarrolladores usa. Estas diez consultas cubren patrones que aparecen constantemente en aplicaciones reales: reportes, búsqueda, optimización, datos en tiempo real y operaciones atómicas.

Si solo te llevas una cosa de este post, que sea EXPLAIN ANALYZE. La capacidad de entender por qué una consulta va lenta es la habilidad más valiosa que puedes tener trabajando con bases de datos. Todo lo demás viene después.