Indexación en MariaDB vs PostgreSQL: por qué las «mejores prácticas» fallan en cargas reales

Por qué las «mejores prácticas» fallan en producción

La mayoría de los consejos de indexación están escritos para una base de datos ficticia: esquema estable, consultas predecibles, concurrencia moderada y una capa de almacenamiento que se comporta como promete el folleto. Tu base de datos no es esa base de datos.

«Añade un índice para cada cláusula WHERE» es un clásico. También es la forma de convertir un sistema OLTP que funciona en un montón de compost amplificado por escrituras. Cada índice adicional es otra estructura que actualizar, otro conjunto de páginas que ensuciar, otra razón para que exista lag en la replicación y otra oportunidad para que el optimizador elija el camino equivocado.

La indexación en producción trata sobre restricciones. ¿Cuántas escrituras te puedes permitir? ¿Cuánta memoria se reserva para caché de páginas de índice? ¿Con qué frecuencia cambia la forma de tus datos (claves calientes, sesgos, picos)? ¿Cuál es tu tolerancia a ventanas de mantenimiento? PostgreSQL y MariaDB responden a estas preguntas de forma distinta porque sus internos y herramientas operativas difieren.

Además: la «mejor práctica» a menudo asume que el planificador tiene estadísticas precisas. Cuando las estadísticas derivan—or tu distribución de datos cambia—tu índice perfecto se vuelve una trampa. No es que la base de datos sea tonta. Es que cambiaste las reglas mientras ella jugaba.

Una cita que debería pegarse al monitor: La esperanza no es una estrategia. —Gene Kranz. Indexar sin medir es esperanza con pasos extra.

Broma #1: Añadir índices sin medir es como comprar más estanterías porque no encuentras nada—eventualmente tienes una biblioteca y aún pierdes las llaves.

Hechos e historia que aún importan

Algunos puntos de contexto que parecen académicos hasta que explican tu incidente:

1. PostgreSQL heredó MVCC temprano (control de concurrencia multiversión), lo que significa que las tuplas muertas y vacuum son parte de la historia de indexación, no una nota al pie.
2. InnoDB se convirtió en el motor predeterminado de MySQL hace mucho, y MariaDB en gran medida sigue la misma línea de InnoDB—por lo que el diseño de índices secundarios hereda la realidad de «clave primaria clusterizada».
3. Los índices GIN y GiST de PostgreSQL se diseñaron para tipos de datos complejos y patrones de búsqueda; son potentes, pero introducen dinámicas de mantenimiento y bloat que la multitud de B-tree suele olvidar.
4. MariaDB/MySQL históricamente se apoyaron en «index condition pushdown» y heurísticas del optimizador; funcionan bien hasta que no, y entonces la única verdad es el plan real.
5. PostgreSQL ganó columnas INCLUDE para índices B-tree (comportamiento similar a índices cubridores) relativamente tarde respecto a algunos motores; cambió cómo la gente debería construir índices «cubriendo».
6. Los índices secundarios de InnoDB almacenan la clave primaria en las páginas leaf. Ese detalle explica una cantidad sorprendente de crecimiento en espacio y de churn en caché cuando tu clave primaria es ancha.
7. PostgreSQL usa mapas de visibilidad para hacer posibles los index-only scans; si vacuum no da abasto, tu «index-only» scan se convierte en «índice más tabla» de todos modos.
8. MySQL/MariaDB han soportado desde hace tiempo «índices invisibles» (en MySQL) y MariaDB tiene capacidad similar vía switches del optimizador y hints; poder probar la remoción de índices de forma segura es oro operativo.

Lo que los índices realmente cuestan (y quién paga)

1) Amplificación de escrituras: cada inserción/actualización paga renta

En OLTP, la factura se paga en las escrituras. Las inserciones tocan la tabla más cada índice secundario. Las actualizaciones pueden ser peores: actualizas una columna que aparece en varios índices y multiplicaste el trabajo. El borrado tampoco es gratis—ambos motores deben registrar la eliminación a su manera, y ambos pueden dejar la limpieza para después.

MariaDB (InnoDB) actualiza estructuras B-tree y escribe logs redo/undo. PostgreSQL escribe nuevas versiones de tupla (MVCC) y actualiza índices para la nueva tupla; las versiones antiguas permanecen hasta que vacuum las limpia. Mecanismos distintos, misma moraleja: más índices significan más churn.

2) Presión de caché: los índices compiten con lo que realmente necesitas

Los índices no son «lecturas gratis». Son estructuras de datos que viven en memoria y en disco. Si tu buffer pool (MariaDB) o shared_buffers + cache del SO (PostgreSQL) no pueden contener el working set, obtienes churn de IO. El churn de IO convierte un «índice mejor práctica» en «¿por qué tenemos 4000 IOPS en reposo?».

3) Riesgo de planificación: el optimizador puede elegir el «buen» plan equivocado

Cuantos más índices tengas, más opciones tiene el planificador. Suena bien hasta que las estimaciones de cardinalidad están mal. Entonces elige con confianza un plan que parece barato en su modelo y es caro en la realidad.

4) Mantenimiento: bloat, fragmentación y la mentira de «configúralo y olvídalo»

El bloat en PostgreSQL suele venir de tuplas muertas y entradas de índice que no se reclaman inmediatamente. Vacuum lo mitiga, pero vacuum necesita recursos y configuración. MariaDB/InnoDB puede sufrir fragmentación y problemas de split de páginas también, y «OPTIMIZE TABLE» no es algo que quieras ejecutar a la ligera a escala.

Broma #2: Vacuum es el único conserje al que la gente le grita por hacer su trabajo demasiado lento y también por hacerlo.

Dónde difieren MariaDB y PostgreSQL en comportamiento de índices

Clave primaria clusterizada vs realidad de tablas heap

InnoDB (MariaDB) almacena los datos de la tabla clusterizados por la clave primaria. Las entradas leaf de índices secundarios incluyen la clave primaria, usada como «puntero de fila». Eso hace que el ancho y la aleatoriedad de la PK importen mucho. Una PK ancha hincha todos los índices secundarios. Una PK aleatoria aumenta los page splits y reduce la localidad.

PostgreSQL usa tablas heap; los índices apuntan a ubicaciones de tuplas (TIDs). Tu clave primaria no está físicamente clusterizada a menos que explícitamente uses CLUSTER (y aceptes sus implicaciones de mantenimiento). Esto debilita algunos argumentos de «localidad de PK» en Postgres, pero introduce otros temas como HOT updates y fragmentación del heap.

Índices cubridores: «INCLUDE» vs «simplemente añadir columnas»

En MariaDB, un «índice cubridor» se logra típicamente colocando las columnas necesarias en la clave del índice. Eso aumenta el tamaño del índice y puede empeorar el coste de escritura. En PostgreSQL, puedes usar INCLUDE para añadir columnas no clave, permitiendo index-only scans mientras mantienes el orden del índice definido por menos claves. No es magia; las columnas incluidas siguen ocupando espacio y requieren mantenimiento, pero tienes un control más preciso.

Los index-only scans son condicionales, no garantizados

Los index-only scans de PostgreSQL requieren que los bits del mapa de visibilidad estén establecidos, lo que depende de vacuum. Si tu tabla se actualiza frecuentemente, el mapa de visibilidad puede quedarse atrás, y Postgres igualmente consultará el heap. En MariaDB, «cubrir» significa que el motor puede evitar búsquedas en la tabla si todas las columnas requeridas están en el índice, pero aún pagas con índices más grandes y mayor presión de caché.

Los modos de fallo por estimación y estadísticas se ven diferentes

Ambos motores pueden equivocarse en estimaciones. PostgreSQL expone más perillas (default_statistics_target, objetivos de estadísticas por columna, estadísticas extendidas). MariaDB se apoya en estadísticas persistentes e histogramas en versiones recientes, pero el camino para «arreglar estimaciones» a menudo parece «ANALYZE y rezar», más diseño cuidadoso de índices y moldeado de consultas.

Diferencias en concurrencia y ritmos de mantenimiento

En Postgres, vacuum es parte de las operaciones de estado estable. En InnoDB, a menudo te enfocas más en dimensionar el buffer pool, el redo log y evitar churn patológico de páginas. Ambos requieren mantenimiento rutinario; simplemente fallan de manera diferente cuando los descuidas.

Patrones de carga que hacen que el consejo salga mal

Patrón A: sistemas «orientados a lectura» que en secreto son orientados a escritura

Telemetría, logs de auditoría, streams de eventos, tablas «append-only»: se sienten orientadas a lectura porque las consultas del panel son lo que notas. Pero la base de datos pasa la mayor parte del tiempo ingestando. Añadir índices para cada filtro del panel puede multiplicar los costes de escritura y provocar lag en la replicación.

Patrón B: columnas de alta cardinalidad vs baja cardinalidad

Indexar un booleano o un enum pequeño puede ser útil en Postgres con índices parciales, pero en MariaDB un índice de baja cardinalidad puede ser ignorado o causar trabajo innecesario. El consejo de «indexa todo en WHERE» ignora la selectividad.

Patrón C: el mito del orden en índices compuestos

Sí, el orden en un índice compuesto importa. No, no existe un orden universal. «Pon la columna más selectiva primero» suele ser erróneo cuando tu consulta usa condiciones de rango, ORDER BY, o necesita cobertura de índice. En MariaDB, las reglas del prefijo izquierdo son un gran asunto. En Postgres, el planificador es flexible, pero aún está limitado por cómo se puede usar el índice.

Patrón D: sobre-indexar para cubrir ORMs

Los ORMs generan consultas con predicados inconsistentes y muchos filtros opcionales. Los equipos responden añadiendo índices «de apoyo» para cada permutación. Así terminas con 25 índices en una tabla de 10 columnas.

Patrón E: ordenación y paginación que te castigan

Paginación con OFFSET/LIMIT más ORDER BY en columnas no indexadas es un desastre a cámara lenta. «Añadir un índice en la columna de ordenación» ayuda hasta que añades una cláusula WHERE que cambia el mejor índice. Entonces necesitas un índice compuesto que coincida filtro + orden, o necesitas paginación por cursor (keyset). La «mejor práctica» aquí no es «añadir un índice»; es «cambiar la consulta».

Guía rápida de diagnóstico

Cuando la latencia sube y la gente actualiza Slack como si fuera una página de estado, este es el orden que suele encontrar el culpable más rápido.

Primero: confirma si estás limitado por CPU, IO o bloqueos

• Limitado por CPU: CPU alta, poco IO wait, consultas que consumen ciclos; los planes suelen mostrar joins, sorts o funciones caras.
• Limitado por IO: IOPS de lectura/escritura altos, await alto; misses en caché de búfer; el working set de índice/tabla no cabe.
• Limitado por bloqueos: hilos esperando; grafos de bloqueo; transacciones largas; autovacuum bloqueado (Postgres) o metadata locks (MariaDB).

Segundo: identifica las 1–3 consultas principales por tiempo total, no solo por tiempo por llamada

La «consulta más lenta» por tiempo por llamada suele ser un señuelo. La consulta principal por tiempo total es la que paga tu hipoteca.

Tercero: valida los planes contra la realidad

En Postgres, compara EXPLAIN (ANALYZE, BUFFERS) con las estimaciones. En MariaDB, inspecciona EXPLAIN, conteos handler, y uso de índices. Busca escaneos inesperados, filesorts, tablas temporales y bucles anidados que deberían haber sido hash joins (Postgres) o que deberían haberse limitado antes.

Cuarto: revisa la salud y el estado de mantenimiento de los índices

En Postgres: actividad de vacuum, tuplas muertas, indicadores de bloat, mapa de visibilidad. En MariaDB: tasa de aciertos del buffer pool, comportamiento del change buffer, crecimiento del tamaño de índices y si la elección de tu PK está hinchando cada índice secundario.

Quinto: solo entonces crea o elimina índices

Si creas índices antes de saber tu clase de cuello de botella, solo estás añadiendo peso a un barco que no has inspeccionado en busca de agujeros.

Práctica: 14 tareas con comandos, salidas y decisiones

Estas son tareas reales de producción: ejecuta un comando, interpreta la salida y toma una decisión concreta. Los comandos se muestran como si estuvieras en una máquina con las herramientas cliente instaladas.

Task 1 (PostgreSQL): Encuentra los mayores consumidores de tiempo por tiempo total

cr0x@server:~$ psql -d appdb -c "SELECT query, calls, total_exec_time::numeric(12,1) AS total_ms, mean_exec_time::numeric(10,2) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
                               query                                | calls |  total_ms  | mean_ms
--------------------------------------------------------------------+-------+------------+---------
 SELECT ... FROM orders WHERE account_id=$1 AND status=$2 ORDER BY... | 98234 |  8543210.5 |   86.97
 UPDATE inventory SET qty=qty-$1 WHERE sku=$2                         | 45012 |  3011220.2 |   66.90
 SELECT ... FROM events WHERE created_at >= $1 AND tenant_id=$2        | 12033 |  2210098.7 |  183.67

Qué significa: La primera consulta está consumiendo la mayor parte del tiempo de reloj global; incluso una latencia media «moderada» suma mucho con alto volumen de llamadas.

Decisión: Optimiza esa consulta principal primero. No persigas el informe raro de 30 segundos a menos que esté impactando el SLA.

Task 2 (PostgreSQL): Compara estimaciones vs realidad con buffers

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50;"
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..102.55 rows=50 width=128) (actual time=1.212..12.844 rows=50 loops=1)
   Buffers: shared hit=120 read=480
   ->  Index Scan Backward using idx_orders_account_created on orders  (cost=0.42..10523.31 rows=5200 width=128) (actual time=1.210..12.832 rows=50 loops=1)
         Index Cond: (account_id = 42)
         Filter: (status = 'open'::text)
         Rows Removed by Filter: 940
         Buffers: shared hit=120 read=480
 Planning Time: 0.290 ms
 Execution Time: 12.930 ms

Qué significa: El índice ayuda al ORDER BY/LIMIT, pero el filtro en status está eliminando muchas filas. Los buffers muestran lecturas reales: estás tocando muchas páginas.

Decisión: Considera un índice compuesto en (account_id, status, created_at DESC) o un índice parcial en status «open» si eso es estable. También verifica si «open» es una fracción pequeña; los índices parciales destacan aquí.

Task 3 (PostgreSQL): Identificar índices no usados (con precaución)

cr0x@server:~$ psql -d appdb -c "SELECT relname AS table, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes JOIN pg_class ON pg_class.oid=relid WHERE idx_scan=0 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;"
  table   |         index          | idx_scan |  size
----------+------------------------+----------+--------
 events   | idx_events_payload_gin |        0 | 2048 MB
 orders   | idx_orders_status      |        0 |  512 MB

Qué significa: Estos índices no se han escaneado desde que se reiniciaron las estadísticas. Eso no garantiza que sean inútiles; podrían usarse rara vez, solo para constraints, o las estadísticas fueron reiniciadas recientemente.

Decisión: Valida con logs de consultas, patrones de la app y una ventana de prueba segura. En Postgres, considera eliminar índices verdaderamente no usados para reducir la sobrecarga de escritura y vacuum.

Task 4 (PostgreSQL): Encontrar señales de bloat vía tuplas muertas

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, (n_dead_tup::numeric/(n_live_tup+1))*100 AS dead_pct FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
  relname  | n_live_tup | n_dead_tup | dead_pct
-----------+------------+------------+----------
 events    |   80000000 |   22000000 |  27.50
 orders    |    1200000 |     210000 |  17.50

Qué significa: Muchas tuplas muertas significan presión de vacuum y posible bloat de índices. Esto puede arruinar los index-only scans e inflar el IO.

Decisión: Ajusta autovacuum para estas tablas (configuración por tabla), y considera particionar o cambiar el patrón de escritura si es crónico.

Task 5 (PostgreSQL): Verifica actividad de autovacuum y bloqueos

cr0x@server:~$ psql -d appdb -c "SELECT pid, now()-xact_start AS xact_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state<>'idle' ORDER BY xact_start NULLS LAST LIMIT 8;"
 pid  |  xact_age  | wait_event_type |     wait_event     |                       query
------+------------+-----------------+--------------------+---------------------------------------------------
 9123 | 02:41:10   | Lock            | relation           | VACUUM (ANALYZE) events
 7331 | 02:42:55   |                 |                    | BEGIN; SELECT ... FROM events FOR UPDATE;

Qué significa: Una transacción larga que mantiene bloqueos puede impedir el progreso del vacuum. El vacuum está esperando; las tuplas muertas se acumulan; los índices se hinchan; el rendimiento declina.

Decisión: Arregla el patrón de transacciones de la aplicación. Añade timeouts de sentencia, acorta transacciones y evita sesiones idle-in-transaction.

Task 6 (PostgreSQL): Confirma si los index-only scans realmente ocurren

cr0x@server:~$ psql -d appdb -c "SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes JOIN pg_class ON pg_class.oid=relid WHERE relname='orders' ORDER BY idx_scan DESC;"
 relname | idx_scan | idx_tup_read | idx_tup_fetch
---------+----------+--------------+--------------
 orders  |  1200000 |     98000000 |     97000000

Qué significa: idx_tup_fetch casi iguala a idx_tup_read, lo que implica muchas fetches al heap. Los index-only scans no te están ahorrando mucho.

Decisión: Mejora la eficacia del vacuum y considera si el índice puede hacerse cubridor con INCLUDE (Postgres) o si la forma de la consulta debe cambiar.

Task 7 (MariaDB): Encuentra las consultas principales por tiempo total (Performance Schema)

cr0x@server:~$ mariadb -e "SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1000000000000,2) AS total_s, ROUND(AVG_TIMER_WAIT/1000000000000,4) AS avg_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 3\G"
*************************** 1. row ***************************
DIGEST_TEXT: SELECT * FROM orders WHERE account_id = ? AND status = ? ORDER BY created_at DESC LIMIT ?
COUNT_STAR: 98234
total_s: 8543.21
avg_s: 0.0869

Qué significa: Mismo principio: el tiempo total identifica la consulta que realmente consume recursos.

Decisión: Centra la indexación y los cambios de consulta en estas pocas sentencias primero. No indexes para una consulta que se ejecuta dos veces al día salvo que bloquee todo.

Task 8 (MariaDB): Explica una consulta y observa filesort/temp

cr0x@server:~$ mariadb -e "EXPLAIN SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50\G"
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: idx_orders_account_created,idx_orders_status
key: idx_orders_account_created
key_len: 8
ref: const
rows: 5200
Extra: Using where; Using filesort

Qué significa: «Using filesort» significa que el motor espera ordenar en lugar de leer las filas en el orden deseado desde el índice. A menudo implica que tu índice no coincide con el ORDER BY después del filtrado.

Decisión: Considera un índice que coincida tanto con el filtro como con el orden: (account_id, status, created_at). Valida la cardinalidad; no apiles índices a ciegas.

Task 9 (MariaDB): Mide uso de índices con los contadores Handler (prueba rápida)

cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Handler_read%';"
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Handler_read_first    | 182349    |
| Handler_read_key      | 982341234 |
| Handler_read_next     | 774123998 |
| Handler_read_rnd_next | 889120044 |
+-----------------------+-----------+

Qué significa: Un Handler_read_rnd_next alto suele indicar muchos escaneos de tabla (o índices que se comportan como escaneos). Un Handler_read_next alto sugiere escaneos por rango. Es crudo, pero rápido.

Decisión: Si los escaneos se disparan, identifica las consultas y los índices faltantes/no usados—o acepta que estás limitado por IO y necesitas cambiar consultas o particionar.

Task 10 (MariaDB): Ver tamaños de índices y detectar daño colateral por PK demasiado ancha

cr0x@server:~$ mariadb -e "SELECT table_name, index_name, ROUND(stat_value*@@innodb_page_size/1024/1024,1) AS index_mb FROM mysql.innodb_index_stats WHERE database_name='appdb' AND stat_name='size' AND table_name='orders' ORDER BY index_mb DESC;"
+------------+---------------------------+----------+
| table_name | index_name                | index_mb |
+------------+---------------------------+----------+
| orders     | PRIMARY                   |  820.0   |
| orders     | idx_orders_account_status |  740.0   |
| orders     | idx_orders_created        |  610.0   |
+------------+---------------------------+----------+

Qué significa: Los índices secundarios pueden ser casi tan grandes como PRIMARY porque llevan la PK en sus entradas leaf. Si tu PK es un UUID de cadena grande, lo estás pagando en todas partes.

Decisión: Considera PKs compactas (claves sustitutas enteras) o almacenar UUIDs en formato binario cuando proceda. Revisa si todos los índices secundarios son necesarios.

Task 11 (PostgreSQL): Probar un índice nuevo sin bloquear escrituras (concurrently)

cr0x@server:~$ psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_orders_account_status_created ON orders (account_id, status, created_at DESC) INCLUDE (total_amount);"
CREATE INDEX

Qué significa: CONCURRENTLY evita bloqueos largos en escritura, pero tarda más y puede fallar si hay operaciones conflictivas.

Decisión: Usa concurrently en producción a menos que estés en una ventana de mantenimiento controlada y aceptes el bloqueo. Luego vuelve a ejecutar EXPLAIN (ANALYZE) para confirmar cambios en el plan.

Task 12 (MariaDB): Añadir un índice online (InnoDB) y validar el algoritmo

cr0x@server:~$ mariadb -e "ALTER TABLE orders ADD INDEX idx_orders_account_status_created (account_id, status, created_at), ALGORITHM=INPLACE, LOCK=NONE;"
Query OK, 0 rows affected (12 min 31.44 sec)

Qué significa: In-place, lock-none generalmente implica DDL menos disruptivo, pero la operación aún consume IO y puede perjudicar la latencia mientras se construye.

Decisión: Programa durante tráfico bajo, monitoriza el lag de replicación y confirma con EXPLAIN que realmente se usa. Si no se usa, acabas de comprar sobrecarga de escritura por nada.

Task 13 (PostgreSQL): Encontrar índices duplicados o redundantes

cr0x@server:~$ psql -d appdb -c "SELECT i1.relname AS index1, i2.relname AS index2, pg_get_indexdef(i1.oid) AS def1, pg_get_indexdef(i2.oid) AS def2 FROM pg_class i1 JOIN pg_index ix1 ON ix1.indexrelid=i1.oid JOIN pg_class i2 ON i2.relkind='i' JOIN pg_index ix2 ON ix2.indexrelid=i2.oid WHERE ix1.indrelid=ix2.indrelid AND i1.oid<>i2.oid AND ix1.indkey=ix2.indkey LIMIT 3;"
  index1              | index2                 | def1                                   | def2
----------------------+------------------------+----------------------------------------+----------------------------------------
 idx_orders_account   | idx_orders_account_v2  | CREATE INDEX ... (account_id)          | CREATE INDEX ... (account_id)

Qué significa: Dos índices con claves idénticas suelen ser redundantes salvo que uno sea parcial, tenga distinto opclass o diferente predicado.

Decisión: Elimina el redundante después de validar dependencias y uso. Menos índices reduce coste de escritura y trabajo de vacuum.

Task 14 (MariaDB): Verificar que el optimizador realmente elige tu índice previsto

cr0x@server:~$ mariadb -e "EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50\G"
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "orders",
      "access_type": "range",
      "possible_keys": ["idx_orders_account_status_created"],
      "key": "idx_orders_account_status_created",
      "rows_examined_per_scan": 60,
      "filtered": 100,
      "using_filesort": false
    }
  }
}

Qué significa: El motor eligió el índice compuesto, espera pocas filas por escaneo y sin filesort. Eso es lo que querías.

Decisión: Despliega, luego vigila latencia de escritura, lag de replicación y misses en el buffer pool. Una «victoria de lectura» que causa una «pérdida de escritura» sigue siendo una pérdida.

Tres mini-historias corporativas desde las trincheras de indexación

Mini-historia 1: El incidente causado por una suposición errónea (la falacia «la PK UUID no importa»)

La compañía ejecutaba un SaaS multi-tenant en MariaDB con InnoDB. Se desplegó un nuevo servicio con un diseño de tabla que usaba un UUID en cadena como clave primaria. La lógica era estándar: IDs globalmente únicos simplifican merges y evitan coordinación. Habían leído los posts de blog. Tenían las pegatinas.

En una semana, la latencia de escritura empezó a subir. No un pico, una pendiente. El on-call vio IO creciente y una tasa de aciertos en buffer pool que parecía que poco a poco estaba perdiendo la batalla. Apareció lag en replicación durante picos de tráfico, y luego se quedó después de que los picos terminaran.

La suposición errónea: «La elección de la clave primaria afecta mayormente la tabla, no los índices». En InnoDB, cada índice secundario leaf almacena la clave primaria. Su PK «inofensiva» se convirtió en un impuesto sobre cada otro índice. Además habían añadido varios índices secundarios para soportar filtros analíticos, multiplicando el impuesto.

Lo arreglaron de forma aburrida: introdujeron una PK entera surrogate y mantuvieron el UUID como clave secundaria única para referencias externas. La migración no fue divertida, pero el sistema dejó de sangrar. El equipo aprendió una regla que debería imprimirse en la documentación de onboarding: en InnoDB, tu clave primaria es una decisión de almacenamiento, no solo de identidad.

Mini-historia 2: La optimización que salió mal (manía de índices cubridores)

Otra organización usaba PostgreSQL para una plataforma de ingestión de eventos. Un ingeniero senior emprendió una campaña de index-only scans. La lógica era sólida: reducir fetches al heap haciendo que los índices «cubrieran» consultas comunes. Añadieron varios índices B-tree multi-columna con columnas INCLUDE, y la latencia de consultas mejoró en staging.

En producción, las cosas fueron bien durante un mes. Luego aumentó el volumen de ingestión, las actualizaciones se hicieron más frecuentes (cambios de estado y enriquecimiento), y autovacuum empezó a quedarse atrás. Los bits del mapa de visibilidad no se estaban estableciendo con la rapidez necesaria, y los index-only scans se convirtieron silenciosamente en scans de índice más fetches al heap. Mientras tanto, los índices más grandes aumentaron la presión sobre vacuum y checkpoints.

Los síntomas aparecieron como picos periódicos de latencia y tormentas de IO. La ironía fue dolorosa: el proyecto «index-only» aumentó la cantidad de datos que había que vacuumear y cachear, lo que dificultó que la condición previa de index-only scans (visibilidad) siguiera siendo verdadera.

La solución no fue «añadir más índices». Revirtieron las INCLUDE más anchas, separaron cargas de trabajo particionando la tabla de eventos más caliente y ajustaron thresholds de autovacuum por partición. Los index-only scans volvieron donde tenía sentido, y el sistema dejó de oscilar entre «rápido» y «en llamas».

Mini-historia 3: La práctica aburrida pero correcta que salvó el día (cambios de índices como despliegues reversibles)

Un equipo fintech ejecutaba tanto MariaDB como PostgreSQL en diferentes servicios. Tenían una costumbre que parecía tediosa: cada cambio de esquema/índice tenía un plan de rollback, y trataban el despliegue de índices como un despliegue de aplicación. El DBA no intentaba ser difícil; intentaba mantener los fines de semana intactos.

Cuando una regresión de consulta surgió tras un release menor, la causa raíz fue un flip del planificador: Postgres empezó a preferir un índice nuevo que parecía más barato en estimaciones pero era peor en la realidad debido al sesgo. Como el equipo había desplegado el índice en una ventana de cambio separada y lo etiquetó, pudieron aislar el problema rápidamente.

No se pusieron a reescribir consultas bajo presión. Simplemente deshabilitaron la ruta eliminando el índice concurrentemente (o, en otros casos, evitando hints del planificador; preferían soluciones estructurales). Luego recopilaron estadísticas reales y rediseñaron el índice para que coincidiera con el patrón de acceso dominante.

La práctica «aburrida» no fue heroica. Fue higiene de cambios: desplegar un cambio que afecta rendimiento a la vez, medir y mantener barato el rollback. Esa práctica no es tendencia en redes sociales, pero evita puentes de incidentes.

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

1) Las escrituras se ralentizan repentinamente después de «optimizar lecturas»

• Síntoma: Latencia de insert/update sube, lag de replicación aumenta, CPU y IO ambos suben.
• Causa raíz: Demasiados índices secundarios, o un índice compuesto/cubridor ancho nuevo añadido a una tabla caliente.
• Solución: Elimina índices redundantes; conserva solo los que soportan las consultas top. En Postgres, prefiere índices dirigidos y parciales; en MariaDB, mantén la PK compacta y evita indexar «todo».

2) «El índice existe» pero la consulta aún escanea o ordena

• Síntoma: EXPLAIN de MariaDB muestra «Using filesort» o «Using temporary»; el plan de Postgres muestra Seq Scan o Sort.
• Causa raíz: El índice no coincide con el orden del predicado (orden compuesto incorrecto), o el predicado usa funciones/casts que impiden usar el índice, o baja selectividad hace más barato un scan.
• Solución: Alinea el índice con filtro + orden; reescribe predicados para que sean sargables (evita envolver la columna en una función); considera índice parcial (Postgres) o columnas calculadas (MariaDB) donde aplique.

3) El planificador elige un plan terrible tras crecer los datos

• Síntoma: Una consulta que estaba bien la semana pasada ahora hace picos; EXPLAIN muestra nested loop en muchos registros, orden de join equivocado o índice equivocado.
• Causa raíz: Estadísticas obsoletas, distribución sesgada, planes sensibles a parámetros o falta de estadísticas extendidas (Postgres).
• Solución: Ejecuta ANALYZE; aumenta objetivos de estadísticas para columnas sesgadas; usa estadísticas extendidas en Postgres; en MariaDB, refresca estadísticas persistentes/histogramas cuando estén disponibles y valida con JSON EXPLAIN.

4) Los index-only scans en Postgres no permanecen index-only

• Síntoma: Los planes muestran Index Only Scan pero los fetches al heap son altos; el rendimiento regresa durante ráfagas de escritura.
• Causa raíz: Mapa de visibilidad no establecido por lag de vacuum; las actualizaciones frecuentes invalidan los bits all-visible.
• Solución: Ajusta autovacuum (scale factors, cost delay) por tabla/partición caliente; reduce el churn de actualizaciones; reconsidera el bloat por INCLUDE; particiona datos calientes.

5) La tasa de aciertos del buffer pool en MariaDB colapsa «sin razón»

• Síntoma: Más lecturas de disco, mayor latencia, aunque la mezcla de consultas parezca similar.
• Causa raíz: El working set de índices creció (nuevos índices, PK ensanchada, o explosión de cardinalidad) y ya no cabe en memoria.
• Solución: Elimina índices no usados; mantén la PK compacta; ajusta el tamaño del buffer pool; asegúrate de que tus índices calientes quepan en memoria, no los fríos.

6) Los waits de bloqueo se disparan tras añadir/eliminar índices

• Síntoma: Timeouts de bloqueo, esperas de DDL, escritores bloqueados.
• Causa raíz: DDL no online, transacciones largas o cambios de esquema compitiendo con tráfico caliente.
• Solución: En Postgres usa CREATE INDEX CONCURRENTLY y DROP INDEX CONCURRENTLY; en MariaDB usa ALGORITHM=INPLACE, LOCK=NONE cuando sea soportado; arregla primero las transacciones largas.

Listas de verificación / plan paso a paso

Un flujo pragmático de indexación (funciona para ambos motores)

1. Elige el objetivo: selecciona las consultas top por tiempo total, no por «la más lenta una vez».
2. Obtén un plan real: Postgres: EXPLAIN (ANALYZE, BUFFERS). MariaDB: EXPLAIN FORMAT=JSON más contadores de estado.
3. Clasifica el dolor: CPU vs IO vs bloqueos. Si no sabes cuál, no estás listo para indexar.
4. Propón el índice más pequeño que ayude: no lo hagas cubridor «por si acaso».
5. Revisa el impacto en escrituras: ¿la tabla está caliente? ¿cuántas escrituras/segundo? ¿cuántos índices existentes?
6. Despliega de forma segura: Postgres concurrentemente; MariaDB in-place/online; vigila lag de replicación.
7. Verifica uso: confirma que el índice se elige y reduce filas/IO. Si no se usa, elimínalo.
8. Mide efectos secundarios: cache de búfer, vacuum, checkpoint/redo, IO wait, lock waits.
9. Reevalúa trimestralmente: los índices no son eternos; las cargas envejecen.

Lista de comprobación de diseño de índices: hacer y no hacer

• Hacer: coincide los índices compuestos con tu predicado + orden más comunes. En OLTP, optimiza la ruta principal.
• Hacer: en Postgres, usa índices parciales para patrones tipo «status=active» cuando el predicado sea estable y selectivo.
• Hacer: en InnoDB, mantén la clave primaria compacta y evita PKs aleatorias y anchas si te importa el tamaño de índices secundarios.
• No hacer: indexar columnas de baja selectividad solas y esperar milagros.
• No hacer: añadir columnas INCLUDE hasta que hayas probado que los fetches al heap son el cuello de botella y que vacuum puede seguir el ritmo.
• No hacer: «arreglar» una consulta mala añadiendo tres índices si reescribir la consulta elimina la necesidad de ordenar o escanear.

Cuándo elegir tácticas estilo MariaDB vs tácticas estilo Postgres

• Si la hinchazón de la PK te está matando: MariaDB/InnoDB suele beneficiarse de rediseño de PK; Postgres menos, pero aún se beneficia de disciplina en el ancho de índices.
• Si predicados sesgados confunden al planificador: Postgres te da más herramientas de estadísticas; úsalas antes de añadir índices redundantes.
• Si necesitas índices parciales y tipos de índice más ricos: Postgres suele ser la caja de herramientas mejor equipada.
• Si necesitas previsibilidad operativa para cargas B-tree simples: MariaDB puede ser directo—hasta que te sobre-indexes y el buffer pool se vuelva un campo de batalla.

Preguntas frecuentes

1) ¿Debería «indexar cada clave foránea» en MariaDB y PostgreSQL?

Usualmente sí para rendimiento de joins en OLTP, pero no de forma ciega. En MariaDB, índices faltantes en FKs pueden causar amplificación de locks y borrados/actualizaciones lentas. En Postgres, los joins pueden funcionar sin ellos, pero a menudo pagarás en escaneos. Verifica con las consultas top y planes reales.

2) ¿Un índice compuesto siempre es mejor que varios índices de columna única?

No. El compuesto es mejor cuando tu consulta usa esas columnas juntas de forma que el índice pueda explotarlas (filtro + orden, o prefijo selectivo). Varios índices de columna única aún pueden ser útiles, y Postgres a veces puede combinarlos (bitmap index scans), pero eso puede convertirse en trabajo extra al heap.

3) ¿Por qué añadir un índice a veces hace más lenta una consulta?

Porque el planificador lo elige y se equivoca, o porque el índice causa IO aleatorio que es peor que un escaneo secuencial. Además, un índice nuevo puede cambiar la forma del plan (orden de joins, nested loops) de maneras que parecen más baratas en estimaciones pero no lo son.

4) ¿Los índices cubridores siempre ayudan?

No. En MariaDB, hacer un índice «cubrir» añadiendo columnas aumenta el tamaño del índice y el coste de escritura. En Postgres, INCLUDE puede reducir fetches al heap, pero solo si vacuum mantiene sanos los mapas de visibilidad. Cubrir es una herramienta, no un estilo de vida.

5) ¿Cómo sé si mis estadísticas de Postgres están mintiendo?

Cuando las filas estimadas difieren enormemente de las reales en EXPLAIN (ANALYZE), o cuando el plan cambia tras pequeños cambios. Arregla con ANALYZE, objetivos de estadísticas por columna y estadísticas extendidas para columnas correlacionadas.

6) ¿Puedo forzar el uso de índices con hints?

En MariaDB puedes, y la gente lo hace. En Postgres no hay hints integrados (existen extensiones). Forzar planes es deuda operativa: te encierra en un plan que puede volverse incorrecto conforme cambian los datos. Prefiere hacer que el plan correcto sea naturalmente el más barato vía esquema, estadísticas y forma de consulta.

7) ¿Cuál es la forma más rápida y segura de eliminar un índice?

Postgres: DROP INDEX CONCURRENTLY para evitar bloquear escritores. MariaDB: eliminar índices suele ser rápido pero aún puede bloquear metadata; hazlo en periodos tranquilos y vigila lag de replicación o esperas de DDL.

8) ¿Por qué un índice booleano/status a veces ayuda en Postgres pero no en MariaDB?

Postgres puede aprovechar índices parciales como WHERE status='open' y evitar indexar el resto, lo que aumenta la selectividad. MariaDB no tiene la misma característica de índices parciales; un índice completo de baja cardinalidad a menudo no es suficientemente selectivo para vencer un scan.

9) ¿Es la partición una solución de indexación?

La partición es una solución de gestión de datos que puede hacer la indexación más barata al reducir el tamaño del índice por partición y el coste de mantenimiento/vacuum. También puede complicar consultas y planes. Hazla cuando el conjunto caliente y el frío de una tabla única tengan necesidades operativas distintas.

10) ¿Cuántos índices son «demasiados»?

Cuando la latencia de escritura y el coste de mantenimiento dominan, o cuando tu caché no puede contener el working set. El número no es universal. Cuenta índices por tabla caliente, mide la amplificación de escrituras y elimina sin piedad los que no pagan renta.

Próximos pasos que realmente puedes hacer

Si ejecutas MariaDB o PostgreSQL en producción y la indexación está derivando a folklore, haz esto en la próxima semana:

1. Extrae las consultas top por tiempo total (pg_stat_statements o digests de performance_schema). Haz una lista corta.
2. Para cada consulta, captura un plan real (Postgres: ANALYZE+BUFFERS; MariaDB: explain JSON). Guárdalo como artefacto.
3. Clasifica el cuello de botella (CPU/IO/bloqueos). Si no puedes, añade observabilidad antes de añadir índices.
4. Elimina un índice verdaderamente no usado o redundante en una tabla caliente, con plan de rollback. Mide latencia de escritura antes/después.
5. Añade un índice dirigido usando métodos online/concurrentes, luego demuestra que se usa y mejora la métrica correcta (no solo el benchmark).
6. Programa chequeos de saneamiento de mantenimiento: salud de vacuum en Postgres, crecimiento de buffer pool y tamaño de índices en MariaDB.

El punto no es volverse un filósofo de índices. Es mantener la latencia predecible, los costes acotados y tu rotación de on-call aburrida. Aburrido es el plan premium.