Bases de datos

Optimización del rendimiento en PostgreSQL: índices, particionamiento y EXPLAIN ANALYZE en la práctica

Ruslan Ismailov Publicado 14 min de lectura
O

Introducción: por qué optimizar PostgreSQL es una habilidad continua, no una tarea puntual

PostgreSQL es uno de los sistemas de gestión de bases de datos relacionales más potentes y maduros, pero su rendimiento en producción depende directamente de cuánto comprende el equipo los mecanismos internos de la base de datos. Optimizar PostgreSQL no es "agregar un índice y olvidarse", sino un proceso iterativo constante integrado en la cultura de desarrollo.

A medida que los datos crecen, las consultas que funcionaban al instante con 10.000 filas empiezan a "colgarse" con 10 millones. La distribución de datos cambia, aparecen nuevos patrones de consulta y la carga se transforma. Por eso, la optimización del rendimiento en PostgreSQL es una habilidad viva que debe aplicarse de forma regular.

En este artículo recorreremos todo el camino desde el diagnóstico de consultas lentas hasta el ajuste fino de la configuración, con ejemplos SQL reales y análisis de planes de ejecución.

Herramientas de diagnóstico: pg_stat_statements, auto_explain y EXPLAIN ANALYZE

pg_stat_statements: encontrar las consultas más costosas

El primer paso de la optimización es identificar qué está ralentizando el sistema. La extensión pg_stat_statements recopila estadísticas de todas las consultas ejecutadas y es el estándar de facto para el diagnóstico de rendimiento en PostgreSQL.

Activar la extensión:

-- postgresql.conf\nshared_preload_libraries = 'pg_stat_statements'\n\n-- En la base de datos\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Consulta para encontrar las consultas más lentas por tiempo total de ejecución:

SELECT\n  query,\n  calls,\n  round(total_exec_time::numeric, 2) AS total_ms,\n  round(mean_exec_time::numeric, 2) AS mean_ms,\n  round(stddev_exec_time::numeric, 2) AS stddev_ms,\n  rows\nFROM pg_stat_statements\nORDER BY total_exec_time DESC\nLIMIT 20;

Presta especial atención a las consultas con mean_exec_time alto y gran cantidad de calls: son las que ofrecen mayor ganancia al optimizarse.

auto_explain: registro automático de planes lentos

La extensión auto_explain registra automáticamente el plan de ejecución de las consultas que superan un umbral de tiempo:

-- postgresql.conf\nshared_preload_libraries = 'pg_stat_statements,auto_explain'\nauto_explain.log_min_duration = 1000  -- consultas de más de 1 segundo\nauto_explain.log_analyze = true\nauto_explain.log_buffers = true\nauto_explain.log_format = text

Esto resulta especialmente útil en producción, cuando reproducir manualmente una consulta lenta es complicado.

EXPLAIN ANALYZE: cómo leer el plan de consulta correctamente

EXPLAIN ANALYZE es la herramienta principal para analizar consultas en PostgreSQL. Veamos un ejemplo real:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT u.id, u.email, COUNT(o.id) AS order_count\nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE u.created_at > '2024-01-01'\nGROUP BY u.id, u.email\nHAVING COUNT(o.id) > 5\nORDER BY order_count DESC\nLIMIT 100;

Ejemplo de salida:

Limit  (cost=15234.56..15234.81 rows=100 width=52) (actual time=892.341..892.387 rows=100 loops=1)\n  ->  Sort  (cost=15234.56..15259.56 rows=10000 width=52) (actual time=892.338..892.352 rows=100 loops=1)\n        Sort Key: (count(o.id)) DESC\n        Sort Method: top-N heapsort  Memory: 33kB\n        ->  HashAggregate  (cost=14734.56..14884.56 rows=10000 width=52) (actual time=878.234..889.123 rows=8934 loops=1)\n              Group Key: u.id, u.email\n              ->  Hash Join  (cost=3456.78..13234.56 rows=200000 width=24) (actual time=45.234..756.123 rows=198432 loops=1)\n                    Hash Cond: (o.user_id = u.id)\n                    Buffers: shared hit=1234 read=8932\n                    ->  Seq Scan on orders o  (cost=0.00..8234.56 rows=200000 width=8) (actual time=0.012..312.456 rows=200000 loops=1)\n                    ->  Hash  (cost=2956.78..2956.78 rows=40000 width=24) (actual time=44.123..44.123 rows=39876 loops=1)\n                          ->  Seq Scan on users u  (cost=0.00..2956.78 rows=40000 width=24) (actual time=0.015..38.234 rows=39876 loops=1)\n                                Filter: (created_at > '2024-01-01'::timestamp)\n                                Rows Removed by Filter: 12456\nPlanning Time: 2.345 ms\nExecution Time: 892.567 ms

Lo que hay que observar aquí:

  • Seq Scan on orders — escaneo completo de la tabla de pedidos. 200.000 filas sin índice es una señal de alerta.
  • Buffers: shared read=8932 — la mayoría de los datos se leen desde disco, no desde la caché.
  • Rows Removed by Filter: 12456 — el filtro por created_at en users funciona, pero sin índice.

Regla clave: presta atención a la diferencia entre estimated rows y actual rows. Una gran discrepancia indica estadísticas desactualizadas — ejecuta ANALYZE.

Indexación en PostgreSQL: elegir el tipo correcto

B-tree: la opción universal

El índice B-tree se utiliza por defecto y es adecuado para la mayoría de los escenarios: igualdad, rangos, ordenación y LIKE con prefijo fijo.

-- Índice estándar\nCREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);\n\n-- Índice compuesto (¡el orden de las columnas importa!)\nCREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);\n\n-- Índice para ordenación y rangos\nCREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at DESC);

Importante: crea siempre los índices con CONCURRENTLY en producción para no bloquear la tabla.

GIN: para arrays, JSONB y búsqueda de texto completo

GIN (Generalized Inverted Index) es óptimo para tipos de datos que contienen múltiples valores.

-- Índice para JSONB\nCREATE INDEX CONCURRENTLY idx_products_attributes ON products USING GIN(attributes);\n\n-- Consulta que utiliza GIN\nSELECT * FROM products\nWHERE attributes @> '{\"color\": \"red\", \"size\": \"XL\"}';\n\n-- Búsqueda de texto completo\nCREATE INDEX CONCURRENTLY idx_articles_search\nON articles USING GIN(to_tsvector('spanish', title || ' ' || body));\n\nSELECT * FROM articles\nWHERE to_tsvector('spanish', title || ' ' || body) @@ to_tsquery('spanish', 'optimización & PostgreSQL');

GiST: geodatos y búsqueda difusa

Los índices GiST se utilizan con tipos de geodatos (PostGIS), rangos (tsrange, int4range) y la extensión pg_trgm para búsqueda difusa.

-- Índice para búsqueda difusa (requiere pg_trgm)\nCREATE EXTENSION IF NOT EXISTS pg_trgm;\nCREATE INDEX CONCURRENTLY idx_users_email_trgm ON users USING GiST(email gist_trgm_ops);\n\n-- LIKE sin prefijo fijo ahora usa el índice\nSELECT * FROM users WHERE email LIKE '%gmail%';

BRIN: para tablas grandes con ordenación natural

BRIN (Block Range INdex) es un índice compacto para tablas con datos físicamente ordenados (series temporales, logs, eventos).

-- Para una tabla de eventos con timestamp creciente de forma monótona\nCREATE INDEX CONCURRENTLY idx_events_occurred_at\nON events USING BRIN(occurred_at) WITH (pages_per_range = 128);\n\n-- BRIN ocupa miles de veces menos espacio que B-tree\n-- Ideal para consultas por rangos de fechas en tablas muy grandes

Índices parciales y funcionales

Índices parciales: indexar solo las filas necesarias

Un índice parcial contiene únicamente las filas que satisfacen la condición WHERE. Esto reduce significativamente el tamaño del índice y acelera las consultas.

-- Indexamos solo los pedidos activos (no completados)\nCREATE INDEX CONCURRENTLY idx_orders_active\nON orders(created_at)\nWHERE status NOT IN ('completed', 'cancelled');\n\n-- Índice para notificaciones no leídas\nCREATE INDEX CONCURRENTLY idx_notifications_unread\nON notifications(user_id, created_at)\nWHERE read_at IS NULL;\n\n-- La consulta usa automáticamente el índice parcial\nSELECT * FROM notifications\nWHERE user_id = 42 AND read_at IS NULL\nORDER BY created_at DESC;

Índices funcionales: indexar el resultado de una expresión

-- Búsqueda case-insensitive por email\nCREATE INDEX CONCURRENTLY idx_users_email_lower\nON users(LOWER(email));\n\n-- La consulta ahora usa el índice\nSELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com');\n\n-- Índice por parte de la fecha\nCREATE INDEX CONCURRENTLY idx_orders_date\nON orders(DATE(created_at));\n\nSELECT COUNT(*) FROM orders WHERE DATE(created_at) = '2024-12-01';

Particionamiento de tablas en PostgreSQL 16+

El particionamiento permite dividir una tabla grande en partes lógicas (particiones), lo que acelera las consultas gracias al partition pruning: PostgreSQL omite las particiones que no pueden contener los datos buscados.

Particionamiento por rango: para datos temporales

-- Creamos la tabla particionada\nCREATE TABLE events (\n  id BIGSERIAL,\n  user_id BIGINT NOT NULL,\n  event_type VARCHAR(50) NOT NULL,\n  payload JSONB,\n  occurred_at TIMESTAMP NOT NULL\n) PARTITION BY RANGE (occurred_at);\n\n-- Creamos particiones por mes\nCREATE TABLE events_2024_11 PARTITION OF events\n  FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');\n\nCREATE TABLE events_2024_12 PARTITION OF events\n  FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');\n\nCREATE TABLE events_2025_01 PARTITION OF events\n  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');\n\n-- Los índices se crean en cada partición\nCREATE INDEX ON events_2024_12(user_id);\nCREATE INDEX ON events_2025_01(user_id);\n\n-- Particionamiento + creación automática de particiones mediante pg_partman

Verificamos que el partition pruning funciona:

EXPLAIN SELECT * FROM events\nWHERE occurred_at BETWEEN '2025-01-01' AND '2025-01-31';\n\n-- La salida mostrará Append con una sola partición events_2025_01\n-- El resto de las particiones se omiten por completo

Particionamiento por lista: para categorías y regiones

CREATE TABLE orders (\n  id BIGSERIAL,\n  user_id BIGINT NOT NULL,\n  region VARCHAR(20) NOT NULL,\n  total NUMERIC(12,2),\n  created_at TIMESTAMP DEFAULT NOW()\n) PARTITION BY LIST (region);\n\nCREATE TABLE orders_eu PARTITION OF orders\n  FOR VALUES IN ('DE', 'FR', 'NL', 'IT', 'ES');\n\nCREATE TABLE orders_us PARTITION OF orders\n  FOR VALUES IN ('NY', 'CA', 'TX', 'FL');\n\nCREATE TABLE orders_default PARTITION OF orders DEFAULT;

Particionamiento por hash: distribución uniforme

CREATE TABLE user_events (\n  id BIGSERIAL,\n  user_id BIGINT NOT NULL,\n  data JSONB,\n  created_at TIMESTAMP DEFAULT NOW()\n) PARTITION BY HASH (user_id);\n\n-- 4 particiones para una distribución uniforme\nCREATE TABLE user_events_0 PARTITION OF user_events\n  FOR VALUES WITH (MODULUS 4, REMAINDER 0);\nCREATE TABLE user_events_1 PARTITION OF user_events\n  FOR VALUES WITH (MODULUS 4, REMAINDER 1);\nCREATE TABLE user_events_2 PARTITION OF user_events\n  FOR VALUES WITH (MODULUS 4, REMAINDER 2);\nCREATE TABLE user_events_3 PARTITION OF user_events\n  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Ajuste de la configuración de PostgreSQL

Una configuración adecuada de PostgreSQL mejora el rendimiento sin necesidad de cambiar el esquema ni las consultas.

Parámetros clave

-- Tamaño de shared buffers: 25% de la RAM para un servidor dedicado\nshared_buffers = 8GB\n\n-- Estimación de la memoria disponible para la caché del SO (influye en el planificador)\neffective_cache_size = 24GB\n\n-- Memoria para ordenación y hash joins (¡por cada consulta/conexión!)\nwork_mem = 64MB\n\n-- Memoria para operaciones de mantenimiento (VACUUM, CREATE INDEX)\nmaintenance_work_mem = 2GB\n\n-- Procesos paralelos de trabajo\nmax_parallel_workers_per_gather = 4\nmax_parallel_workers = 8\nmax_worker_processes = 16\n\n-- Coste de I/O aleatorio (reducir para SSD)\nrandom_page_cost = 1.1  -- SSD\n# random_page_cost = 4.0  -- HDD (valor por defecto)\n\n-- Activar JIT para consultas analíticas\njit = on\njit_above_cost = 100000

Importante: work_mem se multiplica por el número de conexiones y operaciones de ordenación dentro de una sola consulta. Con 100 conexiones y work_mem = 256MB, teóricamente se pueden consumir 25 GB de RAM solo en ordenación.

Optimización de consultas JOIN y subconsultas

Usa los CTE con criterio

En PostgreSQL hasta la versión 12, los CTE eran "barreras de optimización": el planificador no podía "ver" dentro de ellos. A partir de PostgreSQL 12 este comportamiento cambió, aunque a veces es necesario controlarlo de forma explícita:

-- CTE materializado (forzamos la materialización)\nWITH expensive_cte AS MATERIALIZED (\n  SELECT user_id, SUM(amount) as total\n  FROM orders\n  WHERE created_at > NOW() - INTERVAL '30 days'\n  GROUP BY user_id\n)\nSELECT u.email, c.total\nFROM users u\nJOIN expensive_cte c ON c.user_id = u.id\nWHERE c.total > 1000;\n\n-- NOT MATERIALIZED — permitimos que el planificador optimice\nWITH recent_users AS NOT MATERIALIZED (\n  SELECT id FROM users WHERE created_at > '2024-01-01'\n)\nSELECT * FROM orders WHERE user_id IN (SELECT id FROM recent_users);

EXISTS vs IN vs JOIN

-- Lento para subconsultas grandes\nSELECT * FROM users\nWHERE id IN (SELECT user_id FROM orders WHERE total > 1000);\n\n-- Más rápido: EXISTS con subconsulta correlacionada\nSELECT * FROM users u\nWHERE EXISTS (\n  SELECT 1 FROM orders o\n  WHERE o.user_id = u.id AND o.total > 1000\n);\n\n-- O JOIN con DISTINCT\nSELECT DISTINCT u.*\nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE o.total > 1000;

Control de la estrategia de JOIN

-- Si el planificador elige un tipo de JOIN incorrecto\nSET enable_hashjoin = off;  -- Desactivar hash join\nSET enable_nestloop = off;  -- Desactivar nested loop\n\n-- Usa pg_hint_plan para un control más preciso en producción\n-- SELECT /*+ HashJoin(u o) */ u.*, o.total FROM users u JOIN orders o ON ...\n\n-- No olvides restablecer la configuración al finalizar\nRESET enable_hashjoin;\nRESET enable_nestloop;

Vacuum y bloat: monitorear el estado de las tablas

PostgreSQL usa MVCC: al ejecutar UPDATE y DELETE, las versiones antiguas de las filas no se eliminan de inmediato. La acumulación de filas "muertas" se denomina bloat y provoca una degradación del rendimiento.

Monitoreo de bloat y estado del vacuum

-- Estadísticas de vacuum por tabla\nSELECT\n  schemaname,\n  relname AS table_name,\n  n_dead_tup AS dead_tuples,\n  n_live_tup AS live_tuples,\n  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,\n  last_autovacuum,\n  last_autoanalyze\nFROM pg_stat_user_tables\nORDER BY dead_ratio_pct DESC NULLS LAST\nLIMIT 20;\n\n-- Tamaño del bloat por índices\nSELECT\n  indexrelname,\n  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,\n  idx_scan,\n  idx_tup_read,\n  idx_tup_fetch\nFROM pg_stat_user_indexes\nORDER BY pg_relation_size(indexrelid) DESC\nLIMIT 20;

Configuración de autovacuum para tablas de alta carga

-- Autovacuum agresivo para una tabla específica\nALTER TABLE orders SET (\n  autovacuum_vacuum_scale_factor = 0.01,   -- 1% de filas muertas activa el vacuum\n  autovacuum_analyze_scale_factor = 0.005, -- 0.5% para ANALYZE\n  autovacuum_vacuum_cost_delay = 2,        -- ms de pausa entre páginas\n  autovacuum_vacuum_threshold = 100\n);\n\n-- VACUUM ANALYZE manual cuando sea necesario\nVACUUM (ANALYZE, VERBOSE) orders;\n\n-- Para eliminar un bloat severo — VACUUM FULL (¡bloquea la tabla!)\n-- En producción es preferible usar pg_repack\n-- pg_repack --table orders mydb

Detección de índices no utilizados

-- Índices que nunca han sido escaneados\nSELECT\n  schemaname,\n  tablename,\n  indexname,\n  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,\n  idx_scan AS times_used\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\n  AND indexrelname NOT LIKE 'pg_%'\nORDER BY pg_relation_size(indexrelid) DESC;

Los índices no utilizados no son solo espacio desperdiciado: ralentizan INSERT, UPDATE y DELETE, ya que PostgreSQL debe actualizar cada índice cuando los datos cambian.

Conclusión: la auditoría regular como parte de la cultura de desarrollo

La optimización del rendimiento en PostgreSQL es una disciplina, no una acción puntual. El ciclo mínimo recomendado para sistemas en producción es:

  • A diario: monitoreo de consultas lentas mediante pg_stat_statements y alertas ante degradación del tiempo de respuesta.
  • Semanalmente: verificación del estado del autovacuum, análisis de nuevos índices e índices no utilizados.
  • Mensualmente: auditoría completa de los planes de ejecución de las 20 consultas más cargadas y revisión del bloat.
  • Ante cambios de esquema: ejecutar EXPLAIN ANALYZE en todas las consultas que afecten a las tablas modificadas.

La inversión en comprender el funcionamiento interno de PostgreSQL se amortiza con creces. Un índice bien elegido puede transformar una consulta de 30 segundos en una de 5 milisegundos. El particionamiento permite trabajar con tablas de cientos de gigabytes con la misma agilidad que con tablas pequeñas. Y el monitoreo constante previene incidentes nocturnos.

PostgreSQL pone todas las herramientas necesarias a disposición del desarrollador; solo hay que aprender a utilizarlas.

Tecnologías

Etiquetas

Ruslan Ismailov

Desarrollador Senior Web / Backend. Desarrollador senior web/backend con 9 años de experiencia. Stack: PHP, Laravel, PostgreSQL, Redis, Docker, Kubernetes, REST, microservicios, CI/CD. Más sobre mí →