MySQL vs PostgreSQL: por qué la misma consulta es rápida en uno y brutal en otro

Guía de diagnóstico rápido

Cuando una consulta es rápida en MySQL y brutal en PostgreSQL (o al revés), resiste la tentación de empezar a “ajustar” perillas. Primero encuentra en cuál de tres cubos estás: plan malo, bloqueos, o limitado por IO. Entonces podrás arreglar el problema real en lugar de reorganizar el feng shui del servidor.

Primero: demuestra que es un problema de plan (o no)

1. Captura el plan:
   • Postgres: EXPLAIN (ANALYZE, BUFFERS)
   • MySQL: EXPLAIN ANALYZE (8.0+) y SHOW WARNINGS si es necesario
2. Busca mentiras de cardinalidad: filas estimadas vs filas reales. Si está mal por 10x, ya tienes problemas. Si está mal por 100x, estás en respuesta a incidentes.
3. Identifica el nodo dominante: el paso con el mayor tiempo real (Postgres) o el mayor “actual time”/loops (MySQL 8.0 analyze). Ahí es donde te enfocas.

Segundo: elimina bloqueos y transacciones largas

1. Postgres: revisa pg_stat_activity por wait_event y transacciones largas.
2. MySQL: revisa SHOW PROCESSLIST y esperas de locks de InnoDB.
3. En ambos: si hay un escritor reteniendo locks o una transacción larga impidiendo limpieza, puedes optimizar para siempre y aún así perder.

Tercero: decide si estás limitado por IO, CPU o memoria

1. Si el plan muestra muchas fallas de buffer / lecturas, estás limitado por IO.
2. Si la CPU está al tope y el plan muestra grandes sorts/hashes, estás limitado por CPU/memoria.
3. Si los picos de latencia se correlacionan con checkpointing, flushing o presión de fsync, estás limitado por almacenamiento.

Idea parafraseada de Werner Vogels: “Diseña para el fallo como condición normal.” Aplica eso al rendimiento de consultas también: diseña y opera asumiendo que los planes ocasionalmente fallarán.

Por qué “la misma consulta” rara vez es la misma

En producción, “misma consulta” generalmente significa “misma cadena SQL”. A las bases de datos les importan el resto: detalles del esquema, reglas de colación, tipos de datos, índices, estadísticas, configuración y concurrencia. Una consulta puede ser lógicamente equivalente y físicamente muy diferente.

Tipos de datos y conversiones implícitas: los asesinos silenciosos de planes

PostgreSQL es estricto con los tipos y con gusto introduce una conversión implícita que impide el uso del índice. MySQL es más permisivo y a veces “ayuda” convirtiendo valores; a veces esa “ayuda” se convierte en un escaneo completo con sonrisa.

Ejemplo clásico: comparar una columna uuid con un parámetro de texto en Postgres puede forzar un casteo sobre la columna, haciendo que el índice sea inutilizable. MySQL podría comparar cadenas y usar un índice si la colación y las reglas de prefijo coinciden. O puede que no. La consistencia es una característica que hay que ganarse.

Colaciones y comparaciones de texto

Las colaciones de MySQL (especialmente las insensibles a mayúsculas por defecto) pueden cambiar la indexabilidad y las comparaciones. El comportamiento de colación en PostgreSQL depende de libc/ICU y puede afectar el orden de ordenación, las clases de operadores de índice y el rendimiento. Si migraste datos y no migraste intencionalmente las colaciones, no migraste la carga de trabajo: la relocaste y esperaste que se comporte.

Parametrización y caché de planes

PostgreSQL puede elegir un plan genérico para sentencias preparadas que es “aceptable en promedio” pero malo para un valor de parámetro común. El comportamiento de MySQL difiere según el conector y si usas prepares en el servidor. Si tu app cambió cómo enlaza parámetros, tu plan puede cambiar drásticamente aunque la cadena SQL parezca idéntica.

Broma #1: Los optimizadores de consultas son como niños pequeños: pueden ser brillantes, pero si los sorprendes con una nueva distribución de datos, tiran el plan al suelo.

Contexto histórico: por qué existen estas diferencias

• Hecho 1: PostgreSQL desciende del proyecto POSTGRES en UC Berkeley (finales de los 80), que trató la planificación de consultas y la extensibilidad como objetivos de diseño de primera clase.
• Hecho 2: MySQL comenzó a mediados de los 90 con foco en velocidad y simplicidad para cargas web; las versiones tempranas tenían soporte limitado para subconsultas y un optimizador más simple.
• Hecho 3: InnoDB se convirtió en el motor por defecto de MySQL en 5.5; antes de eso, muchas implementaciones usaban MyISAM con bloqueo y características de durabilidad diferentes.
• Hecho 4: MVCC de PostgreSQL ha sido central, pero requiere vacuum para recuperar espacio: el rendimiento puede degradarse si ignoras ese ciclo de vida.
• Hecho 5: MySQL 8.0 introdujo un diccionario de datos más capaz y mejoras del optimizador; los consejos de blogs de la era 5.6 pueden ser activamente dañinos.
• Hecho 6: PostgreSQL 12+ mejoró particionamiento y comportamiento del planner para particiones; versiones antiguas podían planear consultas particionadas pobremente.
• Hecho 7: La replicación de MySQL ha sido históricamente basada en sentencias o en filas con distintos trade-offs; las réplicas de lectura pueden comportarse distinto bajo carga por lag de aplicación y bloqueo de metadatos.
• Hecho 8: JIT de PostgreSQL (LLVM) puede acelerar algunas consultas pero también añadir overhead; depende de la carga y puede sorprender en sistemas sensibles a latencia.

Comportamiento del optimizador que lo cambia todo

Estimación de cardinalidad: la base de datos está adivinando, y adivina distinto

La mayoría de las historias “rápido en DB A, lento en DB B” se reducen a la estimación de conteo de filas. El optimizador elige orden de joins y algoritmos de join según las filas estimadas. Si se equivoca, elige el arma equivocada.

PostgreSQL: usa estadísticas de ANALYZE: estadísticas por columna, valores más comunes, histogramas, correlación y (opcionalmente) estadísticas extendidas entre columnas. Es bueno, pero no es psíquico. Si tus predicados involucran múltiples columnas correlacionadas, las estadísticas por defecto pueden subestimar gravemente.

MySQL: usa estadísticas del motor; las estadísticas de InnoDB pueden ser persistentes o recalculadas; existen histogramas y ayudan en 8.0. Sin histogramas, InnoDB puede asumir distribución uniforme y tomar decisiones trágicas cuando los datos están sesgados.

Algoritmos de join y cuándo los prefiere cada motor

PostgreSQL tiene una caja de herramientas potente: nested loop, hash join, merge join. También puede hacer bitmap index scans para combinar múltiples índices. MySQL históricamente se inclinó por nested loops (incluido block nested loop) y ha mejorado, pero su enfoque es distinto.

Esto importa porque “un join grande” puede ser rápido si el planner elige hash join sobre conjuntos grandes, o catastrófico si elige nested loop con un resultado interno mal estimado.

CTEs, subconsultas y cercos de optimización

Versiones antiguas de PostgreSQL trataban los CTEs (WITH) como cercos de optimización: se materializaban y prevenía pushdown de predicados. Versiones más nuevas pueden inlinear CTEs, pero no siempre. MySQL trata las tablas derivadas de forma diferente y tiene sus propias reglas de materialización.

Si tu “misma SQL” usa CTEs, la interpretación del planner puede ser la diferencia entre milisegundos y “cancelado por el on-call”.

Ordenaciones, hashing y perillas de memoria

Postgres usa work_mem por operación de sort/hash, por nodo de consulta, por trabajador paralelo. Una consulta puede generar múltiples sorts; lo fijas demasiado alto y tendrás picos de memoria. Lo fijas demasiado bajo y derramas a disco y lloras.

MySQL usa estructuras de memoria diferentes (join buffer, sort buffer, tmp tables). Creará tablas temporales en disco dependiendo del tamaño y tipos de datos. Ambos motores pueden parecer “lentos por CPU” cuando en realidad están “derramando a disco lentamente”.

Índices: misma idea, filos diferentes

B-tree no es una promesa, es un contrato con letra pequeña

Ambos motores usan índices B-tree para la mayoría de cosas. Pero cómo los usan difiere: index-only scans, índices cubrientes, visibility maps, comportamiento tipo clustered y si el motor puede aplicar condiciones en la capa de índice.

PostgreSQL: index-only scans dependen de la visibilidad

Postgres puede hacer index-only scan si puede probar que las tuplas son visibles sin tocar el heap. Eso depende del visibility map, que depende del vacuum. Si tu tabla churnea y el autovacuum no puede seguir el ritmo, tu “índice cubriente” no cubre en tiempo de ejecución. El plan puede seguir diciendo index-only, pero los fetches al heap revelarán la verdad.

MySQL/InnoDB: la clave primaria es la tabla

InnoDB almacena filas agrupadas por la clave primaria. Los índices secundarios apuntan a la clave primaria, no directamente a la ubicación de la fila. Eso significa que búsquedas por índice secundario pueden requerir saltos extra. También significa que la elección de la clave primaria afecta localidad, splits de página y comportamiento de caché. Una clave primaria UUID puede funcionar con el formato y estrategia de inserción adecuados, pero no es gratis.

Índices multi-columna: el orden importa más de lo que la gente admite

MySQL y Postgres ambos se preocupan por las columnas líderes. Pero sus optimizadores difieren en cómo combinan índices (bitmap scans en Postgres pueden ser muy efectivos; MySQL tiene index merge pero no es un sustituto universal). Si una consulta es rápida en Postgres porque hace bitmap-AND de dos índices de una columna, el mismo esquema en MySQL puede necesitar un índice compuesto para evitar escaneos.

Índices funcionales y soporte de expresiones

Postgres es cómodo con índices por expresión (como lower(email)) e índices parciales (como WHERE deleted_at IS NULL). MySQL tiene índices funcionales (a través de columnas generadas) y puede hacer cosas similares, pero la ergonomía difiere. Si tu consulta depende del patrón “natural” de un motor, portarla puede ser engañosamente lenta hasta que re-expreses la estrategia de índices.

MVCC, vacuum y el impuesto oculto de las filas muertas

Ambos, MySQL (InnoDB) y PostgreSQL, usan MVCC. Pagan la cuenta de forma distinta.

Inflación en PostgreSQL: cuando “eliminado” no ha desaparecido

En Postgres, las actualizaciones crean nuevas versiones de fila. Las eliminaciones marcan filas como muertas. El espacio se recupera después. Si autovacuum no puede seguir el ritmo—por transacciones de larga duración, configuraciones malas o demasiado churn—tu tabla e índices se inflan. Los planes que antes eran amigables con índices se vuelven limitados por IO. La deuda de vacuum es como deuda de tarjeta: los pagos mínimos se sienten bien hasta que no lo son.

Purgado en MySQL y longitud de la lista de historial

InnoDB también mantiene versiones antiguas en logs de undo y los purga cuando es seguro. Transacciones largas pueden aumentar la longitud de la lista de historial, creando trabajo extra para el purge e incrementando la sobrecarga de lectura para algunas cargas. Es una forma distinta de dolor, pero sigue siendo dolor.

Visibilidad y “¿por qué está leyendo tanto?”

Si una consulta de Postgres comienza a hacer muchos fetches al heap, a menudo tienes un problema de vacuum/visibilidad. Si una carga InnoDB empieza a detenerse en purge o muestra presión creciente de undo, tienes problemas en el ciclo de vida de transacciones. Ninguno se arregla añadiendo otro índice “por si acaso”.

Bloqueos e aislamiento: cuando la corrección cuesta latencia

Diferentes valores por defecto, diferentes sorpresas

El nivel de aislamiento por defecto de PostgreSQL es READ COMMITTED. El de MySQL (a menudo) es REPEATABLE READ para InnoDB. Eso puede cambiar lo que las lecturas ven y cuánto tiempo se mantienen ciertos locks de metadata y gap locks. El resultado: la misma “consulta de lectura” puede bloquearse de forma distinta según escrituras concurrentes y límites de transacción.

Locks de metadatos y DDL

El locking de metadatos de MySQL puede causar paradas sorprendentes cuando corre DDL. PostgreSQL tiene sus propios comportamientos de bloqueo alrededor de cambios de esquema. La regla operacional es la misma: trata el DDL como tratas los despliegues—planificados, probados y cronometrados. Si ejecutas un “ALTER rápido” al mediodía, estás audicionando para la rotación de on-call.

Broma #2: Nada te envejece más rápido que un “cambio de esquema en línea” que resulta ser “en línea, es decir: todo el mundo está en línea mirándolo bloquear”.

Caché e IO: su almacenamiento es parte de la base de datos

A escala, el tiempo de consulta a menudo es tiempo de almacenamiento con mejor marca.

Buffer pools vs shared buffers: distinta aritmética, misma física

MySQL/InnoDB tiene el buffer pool, típicamente dimensionado grande (60–80% de la RAM en máquinas dedicadas, ajustado según la carga y otras necesidades de memoria). PostgreSQL tiene shared_buffers más el caché de páginas del SO haciendo el trabajo pesado. Esto afecta comportamiento en caliente vs frío. Una consulta “rápida” en un buffer pool MySQL caliente puede ser “brutal” en una instancia Postgres que depende más del caché del SO y compite con otros procesos o tiene ajuste de memoria distinto.

Checkpoints, fsync y amplificación de escritura

Ambos motores son durables por defecto, pero sus patrones de escritura difieren. Los checkpoints de PostgreSQL pueden producir ráfagas de IO a menos que se ajusten; el redo log y doublewrite buffer de MySQL tienen su propia huella de IO. Si tu almacenamiento tiene IOPS limitadas o alta variación de latencia, la base de datos convertirá fielmente eso en miseria hacia el usuario.

Trampas de almacenamiento que los SREs realmente encuentran

• Almacenamiento en bloque cloud con créditos de rafaga: “rápido hasta que no lo es”.
• Volúmenes en red con vecinos ruidosos: geniales para backups, cuestionables para datos calientes.
• Ajustes RAID / sistema de archivos mal alineados: puedes benchmarkear hasta autoengañarte.
• Compresión y cifrado: excelentes características, no gratuitas.

Tareas prácticas: comandos, salidas y decisiones (12+)

Estas son las cosas que haces a las 02:00 cuando el panel está rojo y la teoría se tomó un descanso. Cada tarea incluye un comando, un fragmento realista de salida, lo que significa y qué decisión tomas después.

Task 1: PostgreSQL — obtén el plan real con tiempos y buffers

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > now()-interval '7 days';"
Hash Join  (cost=1250.11..9821.44 rows=42000 width=8) (actual time=38.221..412.903 rows=39812 loops=1)
  Output: o.id
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=10231 read=821
  ->  Seq Scan on public.orders o  (cost=0.00..7260.00 rows=500000 width=16) (actual time=0.020..220.111 rows=480321 loops=1)
        Output: o.id, o.customer_id
        Filter: (o.created_at > (now() - '7 days'::interval))
        Rows Removed by Filter: 120034
        Buffers: shared hit=8121 read=701
  ->  Hash  (cost=1025.00..1025.00 rows=18000 width=8) (actual time=37.902..37.904 rows=18210 loops=1)
        Buffers: shared hit=2110 read=120
Planning Time: 1.102 ms
Execution Time: 418.771 ms

Qué significa: El escaneo secuencial en orders domina y lee 701 buffers desde disco. Tu filtro en created_at no está usando un índice (o el planner decidió no usarlo).

Decisión: Verifica si existe un índice en (created_at) o (created_at, customer_id) y si es selectivo. También revisa las estadísticas y si los últimos 7 días representan una gran fracción de la tabla.

Task 2: MySQL — obtén el plan y tiempos reales

cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > NOW() - INTERVAL 7 DAY\G"
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=12453.20 rows=40000) (actual time=0.112..52.883 rows=39812 loops=1)
    -> Index lookup on c using idx_segment (segment='enterprise')  (cost=210.12 rows=18000) (actual time=0.041..6.233 rows=18210 loops=1)
    -> Index lookup on o using idx_customer_created (customer_id=c.id, created_at>(now() - interval 7 day))  (cost=0.62 rows=3) (actual time=0.002..0.002 rows=2 loops=18210)

Qué significa: MySQL está impulsando desde customers usando idx_segment y luego sondeando orders vía un índice compuesto. Por eso es rápido.

Decisión: Si Postgres está escaneando orders, probablemente necesites el índice compuesto (o mejores estadísticas) para incentivar un plan impulsado por índices.

Task 3: PostgreSQL — comprueba índices existentes en una tabla

cr0x@server:~$ psql -d appdb -c "\d+ orders"
Table "public.orders"
Column       | Type                     | Collation | Nullable | Default | Storage | Stats target | Description
-------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
id           | bigint                   |           | not null |         | plain   |              |
customer_id  | bigint                   |           | not null |         | plain   |              |
created_at   | timestamp with time zone |           | not null | now()   | plain   |              |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "idx_orders_customer_id" btree (customer_id)

Qué significa: No hay índice en created_at ni compuesto en (customer_id, created_at).

Decisión: Añade un índice compuesto que coincida con tu patrón de join + filtro, o un índice parcial si solo consultas datos recientes.

Task 4: PostgreSQL — crea un índice compuesto sin tumbar el mundo

cr0x@server:~$ psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at);"
CREATE INDEX

Qué significa: La construcción del índice se completó sin bloquear lecturas/escrituras (aún consume tiempo y IO).

Decisión: Vuelve a ejecutar EXPLAIN (ANALYZE, BUFFERS). Si el plan cambia y la latencia mejora, publícalo. Si no, probablemente tengas un problema de estadísticas/selectividad o un cuello de botella distinto.

Task 5: PostgreSQL — refrescar estadísticas ahora (cuando sea seguro)

cr0x@server:~$ psql -d appdb -c "ANALYZE orders; ANALYZE customers;"
ANALYZE
ANALYZE

Qué significa: Se actualizaron las estadísticas del planner. Si cargaste datos recientemente o cambió la distribución, esto puede arreglar planes malos.

Decisión: Si las estimaciones estaban terriblemente equivocadas, vuelve a revisar el plan. Si existe correlación entre columnas, considera estadísticas extendidas.

Task 6: PostgreSQL — encuentra rápidamente la discrepancia estimada vs real

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE) SELECT * FROM orders WHERE created_at > now()-interval '7 days';"
Seq Scan on orders  (cost=0.00..7260.00 rows=5000 width=64) (actual time=0.012..189.442 rows=480321 loops=1)
Planning Time: 0.211 ms
Execution Time: 205.118 ms

Qué significa: La estimación (5,000 filas) está lejos de la real (480,321 filas) por ~96x. Ese es el arma humeante.

Decisión: Aumenta el stats target para created_at, añade estadísticas extendidas o rediseña el predicado/indexación (por ejemplo, particiona por tiempo).

Task 7: PostgreSQL — aumenta stats target para una columna sesgada

cr0x@server:~$ psql -d appdb -c "ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 1000; ANALYZE orders;"
ALTER TABLE
ANALYZE

Qué significa: Postgres guardará estadísticas más ricas para created_at. La planificación será más lenta, la ejecución a menudo más rápida (vale la pena para consultas calientes).

Decisión: Si la consulta es de alta frecuencia y sensible, es un intercambio razonable. Si es analítica ad-hoc, quizá no.

Task 8: MySQL — confirma uso de índices y tipo de acceso

cr0x@server:~$ mysql -e "EXPLAIN SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > NOW() - INTERVAL 7 DAY;"
id	select_type	table	type	possible_keys	key	key_len	rows	Extra
1	SIMPLE	c	ref	idx_segment	idx_segment	1022	18210	Using where
1	SIMPLE	o	range	idx_customer_created	idx_customer_created	16	39812	Using where; Using index

Qué significa: type=ref/range y una clave específica indican acceso por índice. “Using index” sugiere un índice cubriente para las columnas seleccionadas.

Decisión: Si ves type=ALL inesperadamente, estás escaneando. Entonces inspeccionas definiciones de índices y la sargabilidad de los predicados.

Task 9: MySQL — verifica comportamiento del hit rate del buffer pool de InnoDB

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';"
Variable_name	Value
Innodb_buffer_pool_reads	1842231
Variable_name	Value
Innodb_buffer_pool_read_requests	987654321

Qué significa: reads son lecturas físicas; read_requests son lógicas. Si las lecturas físicas aumentan rápidamente durante la ventana de consultas lentas, vas al disco.

Decisión: Si el hit rate es malo, aumenta buffer pool (si la RAM lo permite), arregla el working set (índices, consulta) o atiende la latencia de IO.

Task 10: PostgreSQL — busca bloqueos y transacciones largas

cr0x@server:~$ psql -d appdb -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-xact_start AS xact_age, query FROM pg_stat_activity WHERE datname='appdb' ORDER BY xact_age DESC LIMIT 5;"
 pid  | usename | state  | wait_event_type |  wait_event   | xact_age |                   query
------+--------+--------+-----------------+---------------+----------+--------------------------------------------
 9123 | app    | active | Lock            | transactionid | 00:41:22 | SELECT ... FROM orders ...
 8877 | app    | idle in transaction | Client | ClientRead | 02:13:10 | UPDATE customers SET ...

Qué significa: Una sesión “idle in transaction” ha estado abierta por horas. Eso puede bloquear vacuum y causar bloat, y puede arruinar indirectamente el rendimiento de lecturas.

Decisión: Arregla el manejo de transacciones en la app. A corto plazo, termina la sesión si es seguro y entiendes el radio de blast.

Task 11: MySQL — encuentra esperas de locks y quién está bloqueando

cr0x@server:~$ mysql -e "SHOW PROCESSLIST;"
Id	User	Host	db	Command	Time	State	Info
221	app	10.0.1.10:42218	appdb	Query	28	Waiting for table metadata lock	ALTER TABLE orders ADD COLUMN foo INT
305	app	10.0.1.11:53122	appdb	Query	3	Sending data	SELECT ... FROM orders ...

Qué significa: Un DDL está esperando un metadata lock; también puede causar que otras sesiones se acumulen según el timing y orden de locks.

Decisión: Pausa/mata el DDL si está impactando producción y replanéalo. Para el futuro, usa métodos de cambio de esquema en línea apropiados para tu versión.

Task 12: PostgreSQL — verifica indicadores de bloat en tablas e índices

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
   relname   | n_live_tup | n_dead_tup |     last_autovacuum
-------------+------------+------------+----------------------------
 orders      |    5200000 |    2100000 | 2025-12-29 01:12:03+00
 events      |   22000000 |    9800000 | 

Qué significa: Muchas tuplas muertas, y una tabla no ha sido autovacuumada recientemente (o nunca). Eso puede inflar escaneos y degradar index-only scans.

Decisión: Investiga configuraciones de autovacuum y transacciones largas; considera un VACUUM (ANALYZE) manual durante una ventana de mantenimiento y arregla la causa raíz.

Task 13: Linux — confirma si ahora mismo estás limitado por latencia IO

cr0x@server:~$ iostat -xz 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    5.11   24.33    0.00   52.34

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await  aqu-sz  %util
nvme0n1         820.0  65536.0     0.0   0.00   18.40    79.90   210.0  18432.0   25.12   12.33   98.70

Qué significa: Alto %util y alto r_await/w_await con notoria %iowait. El almacenamiento está saturado o lento.

Decisión: Deja de culpar al optimizador por la física. Reduce lecturas (mejores índices, evita escaneos), añade caché o mejora la clase de almacenamiento / IOPS provisionadas.

Task 14: PostgreSQL — ve si sorts/hashes derraman a disco

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000;"
Sort  (cost=980000.00..1030000.00 rows=5200000 width=64) (actual time=2100.551..2320.112 rows=100000 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 512000kB
  Buffers: shared hit=12000 read=88000
Execution Time: 2355.991 ms

Qué significa: “external merge Disk” significa que derramó. Esto suele ser la razón por la que una consulta es brutal en un motor: los ajustes de memoria y el working set no coinciden.

Decisión: Arregla añadiendo un índice que coincida con el orden ((created_at DESC)), reduciendo el conjunto de resultados o incrementando cuidadosamente work_mem para esa carga.

Tres mini-historias corporativas desde las trincheras

1) Incidente causado por una suposición equivocada: “Un índice es un índice”

La empresa quería mover un servicio de solo lectura de MySQL a PostgreSQL para simplificar operaciones. La capa SQL era “portable”. El esquema era “el mismo”. El plan de rollout era escribir en doble y cambiar lecturas.

En staging se veía bien. En producción, un endpoint empezó a agotar tiempo. Misma consulta. Mismo WHERE. Diferente realidad. PostgreSQL escogió un seq scan y un hash join que parecían razonables en papel. Las filas reales no se acercaban a la estimación.

La causa raíz fue mundana: en MySQL, el equipo tenía un índice compuesto que coincidía con la forma de la consulta. En PostgreSQL, la migración creó índices de columna única pero se perdió el compuesto. El index merge y el patrón nested-loop de MySQL estaban enmascarando el error; PostgreSQL quería un índice que soportara tanto la clave de join como el filtro temporal. El planner de Postgres también subestimó la selectividad del predicado temporal porque la distribución tenía un precipicio (un job de retención había rellenado una semana de datos recientemente).

La solución no fue un ajuste heroico. Fue construir el índice compuesto concurrentemente, ejecutar ANALYZE y añadir una regla: cualquier consulta que haga join y filtre debe tener revisión de índices. También añadieron estadísticas extendidas para columnas correlacionadas. Después de eso, los planes se estabilizaron y el endpoint dejó de hacer de vector de denegación de servicio.

2) Optimización que salió mal: “Forcemos al planner”

Un equipo distinto tenía una consulta analítica lenta en PostgreSQL. Alguien descubrió que deshabilitar nested loops hacía la consulta más rápida en su portátil. Lo pusieron SET enable_nestloop=off en la configuración de sesión de la aplicación. Pareció ingenioso. También fue una trampa.

Durante una semana pareció un triunfo. Luego cambió el tráfico: el dataset de un cliente era mucho más pequeño que el promedio, y nested loops eran en realidad la elección correcta. Con nested loops deshabilitado, Postgres eligió hash joins y grandes ordenaciones, y el uso de memoria se disparó. La base de datos empezó a derramar a disco, la latencia de IO subió y de pronto consultas no relacionadas se volvieron lentas. La “solución” convirtió la preferencia de rendimiento de una consulta en una política global para ese pool de conexiones.

Revirtieron el cambio y hicieron el trabajo aburrido: mejoraron estadísticas, añadieron un índice y reescribieron la consulta para ser más selectiva temprano. La lección quedó: las pistas de plan (o pseudo-pistas vía settings de sesión) son el último recurso. Envejecen mal. Los datos cambian. Las cargas cambian. Tu ingenio no sobrevivirá al próximo trimestre.

3) Práctica aburrida pero correcta que salvó el día: “Mide, luego cambia una cosa”

Un sistema de pagos corría MySQL y PostgreSQL para distintos servicios. Tras un despliegue, la latencia de MySQL subió para un conjunto de consultas que normalmente estaban por debajo de 50ms. La reacción inmediata fue ajustar buffer pool y culpar a la red.

El on-call siguió un runbook: confirma bloqueos, confirma saturación de IO, captura planes, compara antes/después. Extrajeron el slow query log y encontraron la misma consulta haciendo “Using temporary; Using filesort”. Luego comprobaron diff de esquema: una migración había eliminado un índice compuesto y lo había sustituido por dos índices de columna única, bajo la creencia de que “el optimizador puede combinarlos”. A veces puede. Esta vez no.

Restauraron el índice compuesto, confirmaron con EXPLAIN que el plan volvió y vieron la latencia recuperarse. Sin heroísmos dramáticos. Sin ruleta de parámetros. Solo evidencia, un cambio y un plan de reversión. No es glamoroso, pero tampoco lo es no pagar nómina porque tu base de datos está ocupada ordenando.

Errores comunes: síntoma → causa raíz → solución

• Síntoma: Postgres usa Seq Scan, MySQL usa un índice y es rápido.
  Causa raíz: Falta un índice compuesto en Postgres, o el predicado no es sargable debido a cast/funciones.
  Solución: Crea el índice compuesto que coincida con join + filtro; asegura que los predicados comparen mismos tipos; considera un índice por expresión si se necesita.
• Síntoma: Postgres estima 5k filas, en realidad lee 500k; el plan es horrible.
  Causa raíz: Estadísticas obsoletas/insuficientes; columnas correlacionadas; distribución sesgada.
  Solución: ANALYZE; aumenta stats target; añade estadísticas extendidas; considera particionar por tiempo o por tenant.
• Síntoma: “Index-only scan” pero aún lento; los fetches al heap son enormes.
  Causa raíz: Visibility map no está marcado por lag de vacuum; actualizaciones/eliminaciones frecuentes.
  Solución: Arregla autovacuum y la duración de transacciones; ejecuta vacuum de mantenimiento; reduce churn o rediseña actualizaciones.
• Síntoma: El plan de MySQL alterna entre bueno y malo aleatoriamente.
  Causa raíz: Diferencias en recálculo/persistencia de estadísticas de InnoDB; sensibilidad a parámetros; falta de histograma.
  Solución: Habilita estadísticas persistentes; añade histogramas para columnas sesgadas; estabiliza con índices compuestos correctos.
• Síntoma: La consulta es lenta solo a veces; por lo demás está bien.
  Causa raíz: Ráfagas de IO durante checkpoint/flush; expulsión de caché; latencia por vecino ruidoso en almacenamiento.
  Solución: Suaviza IO de escritura (tuning de checkpoints, tamaño de redo), mejora la clase de almacenamiento, reduce el working set, añade caché.
• Síntoma: Lecturas bloqueadas o con timeouts durante deploy.
  Causa raíz: Bloqueo por DDL / metadata locks; transacciones largas reteniendo locks.
  Solución: Planifica ventanas de DDL; usa técnicas de esquema online; configura timeouts; arregla transacciones de la app.
• Síntoma: Postgres se vuelve más lento a lo largo de semanas sin cambios de código.
  Causa raíz: Bloat de tabla/índice y deuda de vacuum; transacciones de larga duración previenen limpieza.
  Solución: Audita autovacuum, gestión de transacciones; reindex periódico donde sea necesario; elimina “idle in transaction.”
• Síntoma: MySQL picos de CPU con “Sending data”, baja actividad de disco.
  Causa raíz: Uso grande de join buffer, orden de joins ineficiente, falta de índice cubriente o búsquedas fila a fila.
  Solución: Añade índices cubrientes/compuestos; reescribe la consulta; verifica orden de joins con EXPLAIN ANALYZE; evita funciones en columnas indexadas.

Listas de verificación / plan paso a paso

Cuando una consulta es rápida en MySQL y lenta en PostgreSQL

1. Obtén el plan con ejecución real: Postgres EXPLAIN (ANALYZE, BUFFERS). Identifica el nodo dominante.
2. Compara estimaciones vs real: si está fuera por >10x, arregla estadísticas antes de cualquier otra cosa.
3. Revisa índices y su forma: verifica que existan índices compuestos para join + filtro. Postgres frecuentemente quiere el compuesto donde MySQL se apañó con index merge.
4. Confirma sargabilidad del predicado: sin casts en columnas indexadas, sin WHERE date(created_at)=... sin índices por expresión.
5. Investiga vacuum/visibilidad: tuplas muertas, fetches al heap, transacciones largas.
6. Comprueba derrames de memoria: sorts/hashes que derraman a disco; ajusta indexación o diseño de consulta antes de subir memoria.
7. Sólo entonces ajusta perillas: work_mem, paralelismo, umbrales de autovacuum—basado en cuellos de botella medidos.

Cuando una consulta es rápida en PostgreSQL y lenta en MySQL

1. EXPLAIN en MySQL: busca type=ALL, “Using temporary”, “Using filesort”.
2. Confirma índices compuestos: los bitmap scans de Postgres pueden combinar índices bien; MySQL frecuentemente necesita un índice compuesto único para evitar tablas temporales.
3. Revisa colaciones y tipos de datos: comparaciones de cadenas y conversiones implícitas pueden deshabilitar índices en MySQL también.
4. Inspecciona estadísticas e histogramas de InnoDB: columnas sesgadas sin histogramas pueden engañar al optimizador.
5. Valida salud del buffer pool: si las lecturas del buffer pool suben durante la lentitud, es IO-bound, no “SQL malo”.
6. Revisa metadata locks: DDL o transacciones largas pueden detener consultas inocentes.

Cambia planes de forma segura

1. Cambia una cosa a la vez (índice, estadísticas, reescritura de consulta o config).
2. Mide antes/después con los mismos parámetros y datos representativos.
3. Tener rollback: eliminar índice concurrentemente (Postgres) o eliminar índice (MySQL) tras verificar alternativas.
4. Documenta la razón en la migración. El tú del futuro merece evidencia, no sensaciones.

Preguntas frecuentes

1) ¿PostgreSQL es más lento que MySQL?

No. Ambos pueden ser extremadamente rápidos. PostgreSQL suele ganar en consultas complejas y opciones ricas de indexación; MySQL suele brillar en patrones OLTP sencillos. El factor decisivo es si el planner tiene los índices y estadísticas correctos para la distribución de tus datos.

2) ¿Por qué PostgreSQL hace un seq scan cuando existe un índice?

Porque cree que el escaneo es más barato. Razones comunes: el predicado no es selectivo, el índice no está alineado con el filtro, las estadísticas son incorrectas o la consulta aplica una función/cast que impide el uso del índice.

3) ¿Por qué MySQL elige un plan terrible incluso con índices?

Las estadísticas de InnoDB pueden ser engañosas en datos sesgados sin histogramas; algunas formas de consulta empujan a MySQL a tablas temporales y filesorts; y a veces el “mejor” índice es un compuesto que coincide con WHERE + JOIN, no dos índices de columna única.

4) ¿Debería usar hints para forzar planes?

Evítalo a menos que estés acorralado. Los planes forzados envejecen mal conforme cambian los datos. Arregla índices, estadísticas y forma de la consulta primero. Si debes usar hints, aíslos, documenta y añade monitorización para saber cuándo dejan de ser válidos.

5) ¿PostgreSQL necesita más mantenimiento que MySQL?

Distinto mantenimiento. PostgreSQL necesita disciplina de vacuum y atención a transacciones largas. MySQL necesita atención al comportamiento de undo/purge de InnoDB, dimensionamiento del buffer pool y prácticas operacionales alrededor de DDL y metadata locks. Ninguno es “configura y olvida” en producción.

6) ¿Por qué las sentencias preparadas cambian el rendimiento en PostgreSQL?

Postgres puede usar un plan genérico para prepared statements que no se adapta a valores de parámetros. Si un valor de parámetro es común y muy selectivo (o no), un plan genérico puede estar consistentemente equivocado. A veces usar planes custom (o evitar prepares prematuros) importa.

7) ¿Por qué la misma consulta se comporta distinto en una réplica?

Lag de replicación, diferente calidez de caché, distinta contención de IO y overhead de replay/apply pueden cambiar la latencia. Además, las réplicas suelen tener configuraciones distintas (intencional o accidentalmente), especialmente en durabilidad y memoria.

8) ¿Debería particionar tablas para arreglar consultas lentas?

El particionado puede ayudar si filtras por la clave de partición y tu motor puede podar particiones eficazmente. También añade complejidad operativa. Si tu problema son índices faltantes o estadísticas malas, particionar es una forma elegante de evitar hacer lo básico.

9) ¿Qué base de datos es más predecible para rendimiento de consultas?

La predictibilidad viene de la disciplina: esquemas estables, índices correctos, estadísticas refrescadas, duraciones de transacción controladas y configuración consistente. Cualquiera de los dos motores puede ser predecible si lo operas con intención.

Conclusión: qué hacer la próxima semana

Si estás lidiando con un choque de rendimiento entre motores, haz tres cosas con intención:

1. Haz visibles los planes: captura EXPLAIN ANALYZE (y buffers en Postgres) para los casos lentos, no para el camino feliz.
2. Arregla la forma, no la superstición: añade el índice compuesto que coincida con join + filtro; elimina casts implícitos; refresca estadísticas; atiende deuda de vacuum/transacciones.
3. Opera la base de datos como un sistema: revisa latencia de IO, salud de caché, bloqueos y mantenimiento. El optimizador no es tu enemigo; es tu empleado. Dale datos precisos y herramientas adecuadas.

Luego haz lo aburrido que mantiene sistemas vivos: escribe lo que pasó, por qué pasó y qué guardarraíles lo previenen la próxima vez. La producción no premia la memoria. Premia la repetibilidad.