# 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)

![PostgreSQL: 10 consultas que todo desarrollador debería conocer](fig-01.webp)

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

```sql
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:

```sql
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

![PostgreSQL: 10 consultas que todo desarrollador debería conocer](fig-02.webp)

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

```sql
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:

```sql
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)

![PostgreSQL: 10 consultas que todo desarrollador debería conocer](fig-03.webp)

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

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
-- 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:

```sql
-- 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.