PostgreSQL Avanzado: Extracción de Datos, Análisis Complejo y Optimización de Consultas a Escala
postgresql sql data-extraction analytics query-optimization performance materialized-views json full-text-search cte window-functions advanced enterprise

PostgreSQL Avanzado: Extracción de Datos, Análisis Complejo y Optimización de Consultas a Escala

Domina PostgreSQL avanzado para análisis. Aprende patrones de extracción, optimización de consultas, vistas materializadas, análisis JSON y tuning de rendimiento para analytics empresarial.

Por Omar Flores
🇬🇧 Read in English

La Realidad del SQL Analítico a Escala

Heredas una base de datos. La aplicación funciona bien. Pero cuando intentas analizarla, todo se rompe.

Ejecutas una consulta para entender el comportamiento del usuario. Toma 45 segundos. Añades un GROUP BY para segmentar los datos. Ahora toma 3 minutos. Añades un JOIN para incluir datos de productos. Se cuelga. El log de transacciones se llena. Tu sistema completo se detiene.

Esta es la diferencia entre SQL transaccional (lo que las aplicaciones CRUD necesitan) y SQL analítico (lo que el entendimiento requiere). La mayoría de ingenieros nunca aprenden SQL analítico correctamente. Escriben consultas transaccionales toda su carrera. Luego cuando necesitan responder una pregunta real sobre sus datos, golpean un muro.

PostgreSQL es una de las mejores bases de datos analíticas. No Redshift. No BigQuery. Solo vanilla PostgreSQL. Pero necesitas saber cómo usarlo.

Esta guía enseña los patrones que funcionan a escala. Cómo extraer datos eficientemente. Cómo analizar miles de millones de filas. Cómo estructurar tu base de datos para analytics sin destruir el rendimiento transaccional.

Parte 1: Entendiendo los Cuellos de Botella de Rendimiento de Consultas

Antes de optimizar, entiende qué estás optimizando.

El Modelo de Costo

Cada consulta en PostgreSQL tiene un costo. No en milisegundos. En operaciones de base de datos.

El planificador de PostgreSQL estima costos basado en:

  • Costo de escaneo secuencial: Leyendo cada fila en una tabla (costoso, pero predecible)
  • Costo de escaneo de índice: Usando un índice para encontrar filas (rápido, pero solo para conjuntos de resultados pequeños)
  • Costo de join: Combinando dos tablas (varía enormemente dependiendo del algoritmo)
  • Costo de ordenamiento: Ordenando resultados (costoso, especialmente para datasets grandes)
  • Costo de hash: Creando una tabla hash para GROUP BY (intensiva en memoria)

La mayoría de consultas son lentas porque el planificador eligió el camino de acceso equivocado. Escribiste una consulta que hace una pregunta sensata. PostgreSQL la interpretó como una operación muy costosa.

EXPLAIN y EXPLAIN ANALYZE

Siempre comienza aquí. No adivines. No optimices por sentimiento.

-- Ver qué hará PostgreSQL (sin ejecutar la consulta)
EXPLAIN
SELECT
  user_id,
  COUNT(*) as order_count,
  SUM(amount) as total_spent
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 10;

-- Ejecuta realmente la consulta y muestra tiempos reales de ejecución
EXPLAIN ANALYZE
SELECT
  user_id,
  COUNT(*) as order_count,
  SUM(amount) as total_spent
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 10;

Busca banderas rojas:

  • Escaneos secuenciales en tablas grandes: Indica un índice faltante
  • Hash aggregates: A menudo más lento que soluciones indexadas para consultas analíticas
  • Nested loops: Al unir tablas grandes, indica que una condición de join no está indexada
  • Altas filas actuales vs estimadas: Las estadísticas del planificador están desactualizadas, ejecuta ANALYZE orders;

Parte 2: Extracción a Escala

Exportar datos eficientemente es un arte. La mayoría de ingenieros usan enfoques ingenuos y exportan lentamente.

Extracción por Lotes con Ventanas de Cursor

Nunca exportes tu tabla completa de una vez. Hazlo en lotes. Usa cursores.

-- Crea una tabla temporal para datos extraídos
CREATE TEMP TABLE extracted_orders AS
WITH ordered_data AS (
  SELECT
    order_id,
    user_id,
    amount,
    created_at,
    ROW_NUMBER() OVER (ORDER BY order_id) as row_num
  FROM orders
  WHERE created_at >= '2024-01-01'
)
SELECT
  order_id,
  user_id,
  amount,
  created_at
FROM ordered_data
WHERE row_num % 100 = 0;  -- Cada 100 filas para muestreo

Para extracción completa sin explosión de memoria:

-- Usa PL/pgSQL para procesar por lotes millones de filas
CREATE OR REPLACE FUNCTION export_orders_batched(
  batch_size INT DEFAULT 10000
) RETURNS TABLE (
  batch_num INT,
  order_id BIGINT,
  user_id BIGINT,
  amount DECIMAL,
  created_at TIMESTAMP
) AS $$
DECLARE
  v_batch INT := 0;
  v_offset BIGINT := 0;
BEGIN
  LOOP
    v_batch := v_batch + 1;

    RETURN QUERY
    SELECT
      v_batch as batch_num,
      o.order_id,
      o.user_id,
      o.amount,
      o.created_at
    FROM orders o
    ORDER BY o.order_id
    LIMIT batch_size
    OFFSET v_offset;

    v_offset := v_offset + batch_size;

    IF NOT FOUND THEN
      EXIT;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Extrae en lotes desde tu aplicación
SELECT * FROM export_orders_batched(10000);

Por qué importan los lotes:

  • PostgreSQL no carga el conjunto de resultados completo en memoria en el servidor
  • Tu aplicación puede procesar lotes y escribir a disco incrementalmente
  • El uso de memoria permanece constante sin importar el tamaño de la tabla
  • Puedes reanudar desde un punto de control si la extracción falla

Parte 3: Patrones Analíticos Complejos

El análisis real requiere combinar múltiples técnicas.

Agregación en Múltiples Etapas

Una única consulta puede tener múltiples operaciones GROUP BY en diferentes niveles.

-- Pregunta de Negocio: Para cada usuario, muestra sus 3 productos principales por ingresos,
-- más el gasto total del usuario y su ranking entre todos los usuarios

WITH user_product_revenue AS (
  -- Etapa 1: Calcula ingresos por usuario por producto
  SELECT
    user_id,
    product_id,
    SUM(amount) as product_revenue,
    COUNT(*) as product_order_count,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY SUM(amount) DESC
    ) as rank_in_user
  FROM order_items
  GROUP BY user_id, product_id
),
user_top_products AS (
  -- Etapa 2: Obtiene top 3 productos por usuario
  SELECT
    user_id,
    product_id,
    product_revenue,
    product_order_count,
    rank_in_user
  FROM user_product_revenue
  WHERE rank_in_user <= 3
),
user_totals AS (
  -- Etapa 3: Calcula totales a nivel de usuario
  SELECT
    user_id,
    SUM(amount) as user_total_spending,
    COUNT(DISTINCT product_id) as unique_products,
    COUNT(*) as total_orders,
    RANK() OVER (ORDER BY SUM(amount) DESC) as user_rank
  FROM order_items
  GROUP BY user_id
)
SELECT
  utp.user_id,
  ut.user_total_spending,
  ut.user_rank,
  ut.unique_products,
  utp.product_id,
  utp.rank_in_user,
  utp.product_revenue,
  p.product_name
FROM user_top_products utp
JOIN user_totals ut ON utp.user_id = ut.user_id
JOIN products p ON utp.product_id = p.product_id
ORDER BY ut.user_rank, utp.rank_in_user;

Este patrón responde preguntas de negocio complejas sin explosión:

  • La Etapa 1 filtra y agrega al nivel más bajo
  • La Etapa 2 aplica ranking y filtrado en agregados
  • La Etapa 3 calcula agregaciones secundarias
  • El SELECT final une todo

Cada etapa puede ser indexada y optimizada independientemente.

Análisis Incremental con Windowing

Evita escaneos de tabla completa. Usa ventanas basadas en tiempo o ID.

-- Analiza solo los últimos 24 horas de datos
WITH recent_data AS (
  SELECT
    order_id,
    user_id,
    amount,
    created_at
  FROM orders
  WHERE created_at > NOW() - INTERVAL '24 hours'
),
hourly_aggregates AS (
  SELECT
    DATE_TRUNC('hour', created_at) as hour,
    COUNT(*) as order_count,
    SUM(amount) as hourly_revenue,
    AVG(amount) as avg_order_value,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) as median_order
  FROM recent_data
  GROUP BY DATE_TRUNC('hour', created_at)
)
SELECT
  hour,
  order_count,
  hourly_revenue,
  avg_order_value,
  median_order,
  LAG(hourly_revenue) OVER (ORDER BY hour) as prev_hour_revenue,
  (hourly_revenue - LAG(hourly_revenue) OVER (ORDER BY hour)) /
    LAG(hourly_revenue) OVER (ORDER BY hour) * 100 as pct_change
FROM hourly_aggregates
ORDER BY hour DESC;

Al crear ventanas para datos recientes:

  • Solo escaneas la partición o índice relevante a las últimas 24 horas
  • Las tablas con particionamiento basado en tiempo escanean una o dos particiones en lugar de la tabla completa
  • El uso de memoria permanece bajo porque el conjunto de trabajo es pequeño
  • Los resultados se actualizan incrementalmente, no desde cero cada vez

Parte 4: Agregaciones Avanzadas y Análisis Estadístico

PostgreSQL tiene funciones de agregación poderosas más allá de COUNT y SUM.

Percentiles y Distribuciones

-- Entiende la distribución de valores de orden
SELECT
  PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY amount) as p10,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) as p25,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) as p50,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) as p75,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) as p90,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY amount) as p99,
  STDDEV_POP(amount) as population_stddev,
  STDDEV_SAMP(amount) as sample_stddev,
  SKEWNESS(amount) as skewness,
  KURTOSIS(amount) as kurtosis
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';

Interpretación:

  • Percentiles: Valor por debajo del cual cae el N% de datos (p50 es la mediana)
  • Desviación estándar: Medida de dispersión (población vs muestra)
  • Skewness: Medida de asimetría (-3 a +3, 0 = simétrico)
  • Kurtosis: Medida del peso de las colas

Esto te dice inmediatamente si tus datos están distribuidos normalmente u tienen outliers.

Agregación de Arrays para Análisis Agrupado

-- Para cada usuario, recopila todos sus montos de orden como un array
-- Luego analiza la distribución
SELECT
  user_id,
  COUNT(*) as order_count,
  ARRAY_AGG(amount ORDER BY amount DESC) as amounts,
  ARRAY_AGG(amount ORDER BY amount DESC)[1] as max_order,
  ARRAY_AGG(amount ORDER BY amount DESC)[ARRAY_LENGTH(
    ARRAY_AGG(amount ORDER BY amount DESC), 1
  )] as min_order,
  SUM(amount) as total_spent,
  AVG(amount) as avg_order,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) as median_order
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5
ORDER BY total_spent DESC;

La agregación de arrays es poderosa porque:

  • Puedes acceder posiciones específicas (la orden más alta/más baja)
  • Puedes medir la longitud del array (para cálculos estadísticos)
  • Puedes ordenar el array durante la agregación
  • Más tarde, puedes deshacer y re-analizar de diferentes formas

Parte 5: Análisis JSON y Datos Semi-Estructurados

Los datos modernos a menudo son semi-estructurados. PostgreSQL tiene excelente soporte para JSON.

Extrayendo y Analizando Datos Anidados

-- Datos de eventos almacenados como JSON. Extrae y analiza patrones.
CREATE TABLE events (
  event_id SERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  event_data JSONB NOT NULL,
  indexed BOOLEAN DEFAULT false
);

-- Evento de muestra:
-- {
--   "event_type": "purchase",
--   "product_id": 123,
--   "amount": 49.99,
--   "metadata": {
--     "source": "mobile",
--     "country": "US",
--     "device": "iPhone"
--   }
-- }

-- Extrae y analiza
SELECT
  event_data->>'event_type' as event_type,
  event_data->'metadata'->>'source' as source,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_id) as unique_users,
  AVG((event_data->>'amount')::DECIMAL) as avg_amount,
  MAX((event_data->>'amount')::DECIMAL) as max_amount
FROM events
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY
  event_data->>'event_type',
  event_data->'metadata'->>'source'
ORDER BY event_count DESC;

-- Crea un índice para consultas JSON más rápidas
CREATE INDEX idx_events_event_type ON events USING GIN (event_data);
CREATE INDEX idx_events_source ON events (
  ((event_data->'metadata'->>'source'))
);

Operaciones JSON:

  • -> retorna el valor como JSONB (aún JSON)
  • ->> retorna el valor como texto
  • Puede convertir a tipos numéricos para cálculos
  • Los índices GIN hacen que las consultas JSON sean rápidas en tablas grandes

Consultas de Ruta JSON (PostgreSQL 12+)

-- Extracción JSON más legible con operador @>
SELECT
  user_id,
  COUNT(*) as purchase_count,
  SUM((event_data->>'amount')::DECIMAL) as total_amount
FROM events
WHERE event_data @> '{"event_type": "purchase"}'
  AND created_at >= NOW() - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_amount DESC;

-- Filtrado anidado
SELECT
  user_id,
  COUNT(*) as mobile_purchase_count
FROM events
WHERE event_data @> '{"event_type": "purchase", "metadata": {"source": "mobile"}}'
GROUP BY user_id
HAVING COUNT(*) >= 10;

Parte 6: Vistas Materializadas para Análisis Incremental

Ejecutar la misma consulta compleja 100 veces al día desperdicia computo. Materialízala una vez.

Creando y Manteniendo Vistas Materializadas

-- Crea una vista materializada de cálculos costosos
CREATE MATERIALIZED VIEW user_spending_summary AS
WITH monthly_data AS (
  SELECT
    user_id,
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as order_count,
    SUM(amount) as monthly_revenue,
    AVG(amount) as avg_order_value,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY DATE_TRUNC('month', created_at) DESC
    ) as month_rank
  FROM orders
  GROUP BY user_id, DATE_TRUNC('month', created_at)
)
SELECT
  user_id,
  month,
  order_count,
  monthly_revenue,
  avg_order_value,
  LAG(monthly_revenue) OVER (
    PARTITION BY user_id
    ORDER BY month
  ) as prev_month_revenue,
  LEAD(monthly_revenue) OVER (
    PARTITION BY user_id
    ORDER BY month
  ) as next_month_revenue,
  month_rank
FROM monthly_data;

-- Indexa la vista materializada para acceso rápido
CREATE INDEX idx_user_spending_user_id ON user_spending_summary(user_id);
CREATE INDEX idx_user_spending_month ON user_spending_summary(month DESC);

-- Actualiza cuando cambian los datos
-- Ejecuta esto en un horario (p.ej., nocturnamente)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_spending_summary;

Las vistas materializadas son poderosas porque:

  • Los resultados están pre-computados y almacenados
  • Las consultas contra la vista son muy rápidas (escaneo de tabla simple)
  • Puedes actualizar en un horario sin bloquear lectores
  • Puedes indexarlas como tablas regulares
  • Las consultas de aplicación son simples: SELECT * FROM user_spending_summary WHERE user_id = ?

Estrategia de Actualización Incremental

-- En lugar de recomputar todo, actualiza solo filas que cambiaron
CREATE OR REPLACE FUNCTION refresh_user_spending_incremental()
RETURNS void AS $$
BEGIN
  -- Elimina filas que podrían haber cambiado
  DELETE FROM user_spending_summary
  WHERE month >= DATE_TRUNC('month', NOW()) - INTERVAL '2 months';

  -- Re-inserta mes actual y mes pasado
  INSERT INTO user_spending_summary (
    user_id, month, order_count, monthly_revenue, avg_order_value
  )
  WITH monthly_data AS (
    SELECT
      user_id,
      DATE_TRUNC('month', created_at) as month,
      COUNT(*) as order_count,
      SUM(amount) as monthly_revenue,
      AVG(amount) as avg_order_value
    FROM orders
    WHERE created_at >= DATE_TRUNC('month', NOW()) - INTERVAL '2 months'
    GROUP BY user_id, DATE_TRUNC('month', created_at)
  )
  SELECT * FROM monthly_data;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

-- Ejecuta esto frecuentemente (cada hora) en lugar de REFRESH todo
SELECT refresh_user_spending_incremental();

Parte 7: Búsqueda de Texto Completo para Insights Analíticos

Busca tu datos semánticamente, no solo con LIKE.

-- Añade capacidad de búsqueda de texto completo a tus documentos
CREATE TABLE documents (
  doc_id SERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  search_vector TSVECTOR,
  created_at TIMESTAMP NOT NULL
);

-- Crea una función para actualizar automáticamente el vector de búsqueda
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    SETWEIGHT(TO_TSVECTOR('english', COALESCE(NEW.title, '')), 'A') ||
    SETWEIGHT(TO_TSVECTOR('english', COALESCE(NEW.content, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_update_search_vector
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

-- Crea un índice GiST o GIN para búsqueda de texto completo
CREATE INDEX idx_documents_search ON documents USING GIN (search_vector);

-- Consulta con ranking de relevancia
SELECT
  doc_id,
  title,
  TS_RANK(search_vector, query) as relevance,
  TS_HEADLINE('english', content, query,
    'StartSel=<mark>, StopSel=</mark>'
  ) as snippet
FROM documents,
  PLAINTO_TSQUERY('english', 'product quality review') as query
WHERE search_vector @@ query
ORDER BY relevance DESC
LIMIT 20;

La búsqueda de texto completo es poderosa para analytics porque:

  • Puedes encontrar documentos por significado semántico, no solo palabras clave
  • El ranking de relevancia muestra qué documentos coinciden mejor
  • TS_HEADLINE muestra contexto alrededor de coincidencias
  • Con stemming adecuado y diccionarios, captures variaciones

Parte 8: Técnicas de Optimización de Consultas

La diferencia entre lento y rápido a menudo es un índice o una reescritura.

Eligiendo el Tipo de Índice Correcto

-- B-tree: Propósito general, para consultas de igualdad y rango
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Hash: Solo para igualdad, más rápido que B-tree para coincidencias exactas
CREATE INDEX idx_orders_order_id ON orders USING HASH (order_id);

-- GiST: Para datos de rango y espaciales
CREATE INDEX idx_orders_amount_range ON orders USING GIST (amount);

-- GIN: Para búsqueda de texto completo y datos de array/JSON
CREATE INDEX idx_events_data ON events USING GIN (event_data);

-- BRIN: Para tablas grandes con orden natural (series temporales)
CREATE INDEX idx_orders_created_brin ON orders USING BRIN (created_at);

-- Índice parcial: Indexa solo filas relevantes
CREATE INDEX idx_recent_orders ON orders(user_id)
WHERE created_at >= NOW() - INTERVAL '30 days';

-- Índice multi-columna: Filtros comunes juntos
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC)
WHERE status = 'completed';

El Problema N+1 en Analytics

-- LENTO: Problema N+1. Una consulta por usuario.
SELECT DISTINCT user_id FROM orders;
-- Luego en aplicación, para cada user_id:
SELECT * FROM orders WHERE user_id = ?;

-- RÁPIDO: Agrupa todos los datos en una consulta
SELECT
  user_id,
  ARRAY_AGG(order_id) as order_ids,
  COUNT(*) as order_count,
  ARRAY_AGG(amount ORDER BY amount DESC) as amounts
FROM orders
GROUP BY user_id;

-- O usa LATERAL para relaciones más complejas
SELECT
  u.user_id,
  u.username,
  recent_orders.order_id,
  recent_orders.amount,
  recent_orders.created_at
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, amount, created_at
  FROM orders
  WHERE orders.user_id = u.user_id
  ORDER BY created_at DESC
  LIMIT 5
) as recent_orders;

Parte 9: Rendimiento a Escala Empresarial

Cuando tienes miles de millones de filas, todo cambia.

Particionamiento de Tablas

-- Particiona por fecha para datos de series temporales
CREATE TABLE orders (
  order_id BIGINT,
  user_id BIGINT,
  amount DECIMAL,
  created_at TIMESTAMP,
  status VARCHAR(50)
) PARTITION BY RANGE (DATE_TRUNC('month', created_at));

-- Crea particiones para cada mes
CREATE TABLE orders_2024_01 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... etc

-- Al consultar datos recientes, PostgreSQL solo escanea particiones relevantes
EXPLAIN SELECT COUNT(*) FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';
-- Muestra: solo escanea la partición del mes actual

Beneficios del particionamiento:

  • Los escaneos solo tocan particiones relevantes
  • Los índices son más pequeños y rápidos
  • Eliminar datos antiguos es instantáneo (elimina la partición)
  • Ejecución de consulta paralela entre particiones

Ejecución de Consulta Paralela

-- PostgreSQL puede paralelizar escaneos grandes automáticamente
SET max_parallel_workers_per_gather = 4;
SET work_mem = '256MB';

-- Ejecuta una agregación costosa
EXPLAIN ANALYZE
SELECT
  DATE_TRUNC('day', created_at) as day,
  SUM(amount) as daily_revenue,
  COUNT(*) as order_count,
  AVG(amount) as avg_order
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day DESC;

-- El planificador dividirá esto entre 4 workers
-- Cada worker procesa una porción de los datos
-- Los resultados se fusionan al final

Parte 10: Monitoreo y Mantenimiento

Las consultas rápidas requieren mantenimiento continuo.

Mantenimiento Regular

-- Actualiza estadísticas de tabla para que el planificador tome buenas decisiones
ANALYZE orders;

-- Recupera espacio de filas eliminadas
VACUUM FULL orders;

-- Reconstruye índices para eliminar bloat
REINDEX TABLE orders;

-- Crea una rutina de mantenimiento
CREATE OR REPLACE FUNCTION maintenance_routine()
RETURNS void AS $$
BEGIN
  ANALYZE orders;
  ANALYZE users;
  ANALYZE order_items;

  REINDEX TABLE CONCURRENTLY orders;
  REINDEX TABLE CONCURRENTLY order_items;
END;
$$ LANGUAGE plpgsql;

-- Ejecuta en un horario
-- SELECT cron.schedule('maintenance', '0 2 * * *', 'SELECT maintenance_routine()');

Monitoreando Consultas Lentas

-- Habilita logging de consultas
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log consultas > 1 segundo
ALTER SYSTEM SET log_statement = 'all';

-- O usa la extensión pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- Encuentra tus consultas más lentas
SELECT
  query,
  calls,
  mean_exec_time,
  total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Resetea estadísticas
SELECT pg_stat_statements_reset();

El Arte del SQL Analítico

El SQL analítico no es sobre conocer cada función. Es sobre entender tus datos lo suficientemente bien para hacer buenas preguntas.

La diferencia entre rápido y lento es:

  • Pensar en conjuntos, no en filas: Nunca hagas bucles en código de aplicación. Deja que SQL maneje agregaciones.
  • Usar la estructura de datos correcta: No cada columna necesita un índice. Las columnas correctas sí.
  • Entender costo: Sabe qué EXPLAIN ANALYZE te dice. Confía en él más que en intuición.
  • Pensamiento incremental: Analiza qué cambió, no todo. Materializa cálculos costosos.
  • Ventanas de tiempo: Nunca proceses todo. Procesa datos recientes, archiva datos antiguos.

La mayoría de problemas de rendimiento no son problemas de base de datos. Son problemas de diseño de consulta.

El secreto para analytics rápido no es una base de datos más rápida. Es hacer las preguntas correctas de la manera correcta. PostgreSQL responderá correctamente y rápidamente si preguntas adecuadamente. Aprende a preguntar.

Tags

#postgresql #sql #data-extraction #analytics #query-optimization #performance #materialized-views #json #full-text-search #cte #window-functions #advanced #enterprise

Artículos relacionados

Arquitectura de Software: De 0 a Arquitecto de Sistemas Empresariales

Arquitectura de Software: De 0 a Arquitecto de Sistemas Empresariales

Guía completa sobre arquitectura de software empresarial. Patrones, C4, microservicios, B2B, multi-tenant, casos reales, antipatrones y mejores prácticas. Enfocado en negocio y decisiones estratégicas.

• Arquitectura
Gestor de Notas Seguro en Go 1.25: Arquitectura Hexagonal desde Cero

Gestor de Notas Seguro en Go 1.25: Arquitectura Hexagonal desde Cero

La Guía Definitiva paso a paso para construir un gestor de notas empresarial con Go 1.25, Arquitectura Hexagonal pura, JWT, roles de usuario y permisos granulares. Desde la configuración de CachyOS hasta la inyección de dependencias. Diseñado para novatos y expertos.

• Backend
Gestor de Notas Seguro con Arquitectura Hexagonal en Go 1.25: Guía Completa Paso a Paso

Gestor de Notas Seguro con Arquitectura Hexagonal en Go 1.25: Guía Completa Paso a Paso

Una guía exhaustiva y profesional para construir un gestor de notas seguro desde cero usando Go 1.25: arquitectura hexagonal pura, autenticación JWT, roles de usuario, compartir notas con permisos, testing con httpie, Docker, MongoDB y PostgreSQL. Paso a paso real, código limpio, tips de Neovim y terminal.

• Backend
← Volver al blog