Tema 22: Mantenimiento, vacuum y optimización — la chachapali del DBA

#PF-022 | XP: 280 | Insignia: Tlacaelel del Mantenimiento | Tier: Senior | Tiempo: 4 h

Objetivo

Aprenderás las tareas de mantenimiento periódico que mantienen una base de datos sana: VACUUM y ANALYZE en PostgreSQL, OPTIMIZE TABLE en MySQL, gestión de índices, particionamiento de tablas grandes, y la disciplina de hacer una "chachapali" (revisión anual) de tu base.

Mapa del tema

Mantenimiento en PostgreSQL: VACUUM, ANALYZE, AUTOVACUUM Mantenimiento en MySQL: OPTIMIZE TABLE, reorganización de índices Particionamiento de tablas grandes Gestión de índices: cuándo crear, cuándo borrar La chachapali: revisión anual de salud LAB: configurar autovacuum y particionar una tabla

Visión mexica — Por Citlalli

Tlacaelel fue el cihuacoatl más importante de Tenochtitlan durante 50 años. Cada final del xiuhpohualli (ciclo de 52 años), reorganizaba los registros del imperio: reasignaba tributos entre los calpulli, ajustaba los padrones después de las hambrunas, consolidaba los archivos del calmécac. No era un trabajo creativo: era la chachapali (renovación) del sistema, una limpieza periódica que permitía que el altépetl siguiera funcionando otra vuelta del calendario.

En bases de datos, la chachapali del DBA es el mantenimiento periódico: VACUUM, ANALYZE, OPTIMIZE, revisión de índices, particionamiento, archival de datos viejos. No es trabajo visible para el usuario, pero sin él, la base se hincha, se ralentiza, y eventualmente muere.

Bloque 1 — Mantenimiento en PostgreSQL: VACUUM, ANALYZE, AUTOVACUUM

¿Por qué PostgreSQL necesita VACUUM?

PostgreSQL usa MVCC (Multi-Version Concurrency Control): cada UPDATE crea una nueva versión de la fila, y la versión anterior se marca como "muerta". Sin VACUUM, esas filas muertas se acumulan:

-- Insertar y actualizar muchas veces
INSERT INTO pedidos (...) VALUES (...);  -- Crea fila viva
UPDATE pedidos SET estado = '...' WHERE pedido_id = 1;  -- Crea nueva fila viva, vieja se marca muerta
UPDATE pedidos SET estado = '...' WHERE pedido_id = 1;  -- Otra vez
-- Después de 1000 UPDATEs, tienes 1000 filas muertas para esa fila

Sin VACUUM:

• Tabla crece aunque los datos no cambien.
• `SELECT` se vuelve más lento (más páginas que leer).
• `pg_stat_user_tables.n_dead_tup` se dispara.

VACUUM manual

-- VACUUM básico: reclama espacio pero no devuelve al SO
VACUUM (VERBOSE, ANALYZE) pedidos;
-- VACUUM FULL: devuelve espacio al SO pero bloquea la tabla
VACUUM FULL pedidos;

Regla: `VACUUM` regular está bien en producción. `VACUUM FULL` requiere ventana de mantenimiento porque bloquea.

ANALYZE: actualizar estadísticas para el planner

ANALYZE pedidos;
-- El planner usa estas estadísticas para elegir el plan óptimo
-- Sin ANALYZE reciente, el planner puede elegir planes lentos

AUTOVACUUM: el demonio del mantenimiento

`postgresql.conf`:

# Activar autovacuum (default: on)
autovacuum = on
Cada cuántos segundos se despierta el demonio
autovacuum_naptime = 60
Umbral: si hay más de 50 tuplas muertas + 5% del total, VACUUM esa tabla
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
ANALYZE cuando hay 50 tuplas modificadas + 10% del total
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

Para tablas muy grandes, umbrales más agresivos:

ALTER TABLE pedidos SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- 1% en vez de 5%
  autovacuum_analyze_scale_factor = 0.02  -- 2% en vez de 10%
);

Monitorear el autovacuum

SELECT
  schemaname || '.' || relname AS tabla,
  n_live_tup AS vivas,
  n_dead_tup AS muertas,
  round(100.0  n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS pct_muerte,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Si `pct_muerte > 20%`, necesitas VACUUM manual o ajustar autovacuum.

Bloque 2 — Mantenimiento en MySQL: OPTIMIZE TABLE

OPTIMIZE TABLE: desfragmentar

OPTIMIZE TABLE pedidos;

Hace dos cosas:

• InnoDB: reconstruye la tabla para desfragmentar. En MySQL 8.0+, también reorganiza los índices.
• MyISAM: desfragmenta y recupera espacio.

pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --execute D=tienda_tlalli,t=pedidos \
  h=localhost

-- Ver espacio por tabla
SELECT
  table_name,
  round(data_length / 1024 / 1024, 2) AS data_mb,
  round(index_length / 1024 / 1024, 2) AS index_mb,
  round((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'tienda_tlalli'
ORDER BY data_length DESC;

# 1. mysqldump completo
mysqldump -u root -p --all-databases > all.sql
2. Detener MySQL
sudo systemctl stop mysql
3. Borrar /var/lib/mysql/ibdata1 y /var/lib/mysql/iblog
4. Activar innodb_file_per_table
echo "innodb_file_per_table = ON" >> /etc/mysql/my.cnf
5. Reiniciar e importar
sudo systemctl start mysql
mysql -u root -p < all.sql

En MySQL 8.0+, `innodb_file_per_table = ON` es el default, así que este problema ya no existe para instalaciones nuevas.

Bloque 3 — Particionamiento de tablas grandes

Cuándo particionar

• Tabla con > 100 millones de filas.
• Queries que filtran por una columna (fecha, región, status).
• Necesidad de borrar datos viejos rápido (`DROP PARTITION` es instantáneo, `DELETE` masivo no).

Particionamiento por rango en PostgreSQL

-- Crear tabla particionada por fecha
CREATE TABLE pedidos (
  pedido_id BIGSERIAL,
  cliente_id INT NOT NULL,
  fecha_pedido DATE NOT NULL,
  total NUMERIC(10,2),
  estado VARCHAR(20),
  PRIMARY KEY (pedido_id, fecha_pedido)  -- La clave de partición debe estar en la PK
) PARTITION BY RANGE (fecha_pedido);
-- Crear particiones mensuales
CREATE TABLE pedidos_2026_01 PARTITION OF pedidos
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE pedidos_2026_02 PARTITION OF pedidos
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- ... una por mes
CREATE TABLE pedidos_2026_12 PARTITION OF pedidos
  FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
-- Crear una partición por defecto (por si llega una fecha fuera de rango)
CREATE TABLE pedidos_default PARTITION OF pedidos DEFAULT;

Beneficios

-- Borrar datos de enero 2026: instantáneo
DROP TABLE pedidos_2026_01;
-- vs DELETE FROM pedidos WHERE fecha_pedido < '2026-02-01' (lento, genera dead tuples)

Particionamiento por hash (distribución uniforme)

CREATE TABLE pedidos (
  pedido_id BIGSERIAL PRIMARY KEY,
  cliente_id INT NOT NULL,
  fecha_pedido DATE,
  total NUMERIC(10,2),
  estado VARCHAR(20)
) PARTITION BY HASH (pedido_id);
CREATE TABLE pedidos_p0 PARTITION OF pedidos FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE pedidos_p1 PARTITION OF pedidos FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE pedidos_p2 PARTITION OF pedidos FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE pedidos_p3 PARTITION OF pedidos FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Particionamiento en MySQL 8

MySQL 8 soporta particionamiento nativo:

CREATE TABLE pedidos (
  pedido_id BIGINT AUTO_INCREMENT,
  cliente_id INT NOT NULL,
  fecha_pedido DATE NOT NULL,
  total DECIMAL(10,2),
  estado VARCHAR(20),
  PRIMARY KEY (pedido_id, fecha_pedido)
) PARTITION BY RANGE (YEAR(fecha_pedido)  100 + MONTH(fecha_pedido)) (
  PARTITION p_2026_01 VALUES LESS THAN (202602),
  PARTITION p_2026_02 VALUES LESS THAN (202603),
  PARTITION p_2026_03 VALUES LESS THAN (202604),
  -- ...
  PARTITION p_2026_12 VALUES LESS THAN (202701),
  PARTITION p_max VALUES LESS THAN MAXVALUE
);

Bloque 4 — Gestión de índices: cuándo crear, cuándo borrar

Cuándo crear un índice

• La query filtra por una columna específica (`WHERE cliente_id = ?`).
• La query ordena por una columna (`ORDER BY fecha_pedido DESC`).
• La query hace JOIN por una columna.
• La query agrupa por una columna.

-- Índice (cliente_id, fecha_pedido) sirve para:
SELECT  FROM pedidos WHERE cliente_id = 1;
SELECT  FROM pedidos WHERE cliente_id = 1 AND fecha_pedido >= '2026-01-01';
-- PERO NO para:
SELECT  FROM pedidos WHERE fecha_pedido >= '2026-01-01';
-- (porque cliente_id es la primera columna)

Regla: columna más selectiva primero. Si filtras mucho por `cliente_id` y poco por `fecha_pedido`, índice `(cliente_id, fecha_pedido)`. Si es al revés, índice `(fecha_pedido, cliente_id)`.

Índices redundantes

Si tienes `idx_pedidos_cliente (cliente_id)` y `idx_pedidos_cliente_fecha (cliente_id, fecha_pedido)`, el segundo cubre al primero. El primero es redundante y puede borrarse.

MySQL 8 tiene un sys schema que detecta esto:

SELECT  FROM sys.schema_redundant_indexes;

Índices no usados

-- PostgreSQL: requiere extension pg_stat_statements + pg_stat_user_indexes
SELECT
  schemaname || '.' || relname AS tabla,
  indexrelname AS indice,
  idx_scan AS veces_usado
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Si idx_scan = 0, el índice no se usa. Considera borrarlo.

MySQL:

SELECT  FROM sys.schema_unused_indexes;

Borrar índice con cuidado

-- PostgreSQL
DROP INDEX idx_pedidos_cliente;
-- InnoDB soporta DROP INDEX online (sin lock)
-- MySQL
ALTER TABLE pedidos DROP INDEX idx_pedidos_cliente;
-- Online en InnoDB 8.0+

`EXPLAIN` siempre

Antes de añadir o borrar un índice:

EXPLAIN ANALYZE
SELECT  FROM pedidos WHERE cliente_id = 1 ORDER BY fecha_pedido DESC LIMIT 10;
-- Si dice Seq Scan, necesitas índice.
-- Si dice Index Scan, ya lo tienes.

Bloque 5 — La chachapali: revisión anual de salud

Una vez al año, dedica medio día a hacer la chachapali (revisión profunda) de tu base de datos. Es la versión DBA del xiuhpohualli mexica.

Checklist chachapali

Crecimiento y capacidad

• [ ] ¿Cuánto creció la base en el último año?
• [ ] ¿Cuál es la proyección de tamaño a 1, 3, 5 años?
• [ ] ¿Hay que particionar alguna tabla?
• [ ] ¿Hay que archivar datos viejos a una base histórica?

Tengo tienda_tlalli en MySQL 8 (50 MB, 6 tablas, 50 clientes, 110 pedidos).
Genera una checklist chachapali (revisión anual) personalizada,
con:
Tareas específicas para el tamaño actual de la base.
Umbrales numéricos (cuándo preocuparse, cuándo no).
Comandos que puedo correr ahora mismo para cada tarea.
Estimación de tiempo total.

SHOW autovacuum;  -- PostgreSQL
SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%';

ALTER TABLE pedidos SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 200
);

-- Generar carga
UPDATE pedidos SET estado = 'entregado' WHERE pedido_id < 50;
-- Esperar 60 segundos
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'pedidos';
-- n_dead_tup debe haber bajado

-- Renombrar la tabla original
ALTER TABLE pedidos RENAME TO pedidos_old;
-- Crear tabla particionada
CREATE TABLE pedidos (
  pedido_id BIGSERIAL,
  cliente_id INT NOT NULL,
  fecha_pedido DATE NOT NULL,
  total NUMERIC(10,2),
  estado VARCHAR(20),
  PRIMARY KEY (pedido_id, fecha_pedido)
) PARTITION BY RANGE (fecha_pedido);
-- Crear particiones por trimestre
CREATE TABLE pedidos_2025_q4 PARTITION OF pedidos
  FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');
CREATE TABLE pedidos_2026_q1 PARTITION OF pedidos
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE pedidos_2026_q2 PARTITION OF pedidos
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE pedidos_2026_q3 PARTITION OF pedidos
  FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
CREATE TABLE pedidos_default PARTITION OF pedidos DEFAULT;
-- Copiar datos
INSERT INTO pedidos SELECT  FROM pedidos_old;
-- Verificar
SELECT
  tableoid::regclass AS particion,
  count(*)
FROM pedidos
GROUP BY tableoid;
-- Esperado: datos distribuidos en particiones según fecha

Paso 5 — Probar el borrado instantáneo

-- Borrar todas las pedidos de octubre-diciembre 2025
DROP TABLE pedidos_2025_q4;
-- Instantáneo, sin DELETE masivo

Paso 6 — Documentar la chachapali

Genera un documento `CHACHAPALI_2026.md` con los hallazgos del LAB.

Entregables del LAB

• [ ] Autovacuum configurado y verificado.
• [ ] Tabla `pedidos` particionada con 4-5 particiones.
• [ ] Datos migrados sin pérdida.
• [ ] `DROP TABLE` de una partición ejecutado y medido (instantáneo).
• [ ] Documento `CHACHAPALI_2026.md` con la nueva arquitectura.

Quest

Preguntas de opción múltiple (10)

1. ¿Qué hace VACUUM en PostgreSQL?

A. Borra filas permanentemente. B. Marca filas muertas como reutilizables y actualiza estadísticas. C. Crea respaldos. D. Cambia la contraseña.

2. ¿Cuál es la diferencia entre VACUUM y VACUUM FULL?

A. Son sinónimos. B. VACUUM es no-bloqueante, VACUUM FULL bloquea la tabla. C. VACUUM FULL es no-bloqueante. D. VACUUM es para MySQL, VACUUM FULL para PostgreSQL.

3. ¿Qué hace ANALYZE?

A. Borra datos. B. Actualiza las estadísticas que el planner usa para elegir planes. C. Comprime la tabla. D. Activa la réplica.

4. ¿Qué es el particionamiento por rango?

A. Distribuir filas均匀 entre N particiones usando hash. B. Dividir la tabla por rangos de una columna (e.g., fechas). C. Crear índices. D. Cambiar el motor de almacenamiento.

5. ¿Cuándo conviene particionar una tabla?

A. Con menos de 1000 filas. B. Con > 100 millones de filas y queries por la columna de partición. C. Solo en bases en producción. D. Nunca.

6. ¿Por qué es importante el orden de columnas en un índice compuesto?

A. El orden no importa. B. El índice puede usarse para queries que filtran por las primeras columnas en orden. C. Hace el índice más pequeño. D. Aumenta la velocidad de inserción.

7. ¿Cómo detectar índices no usados en PostgreSQL?

A. `pg_stat_user_indexes.idx_scan` B. `EXPLAIN` C. `SHOW INDEXES` D. `VACUUM`

8. ¿Qué herramienta de MySQL detecta índices redundantes?

A. `mysqldump` B. `sys.schema_redundant_indexes` C. `OPTIMIZE TABLE` D. `SHOW STATUS`

9. ¿Con qué frecuencia se recomienda hacer la chachapali?

A. Diariamente. B. Mensualmente. C. Anualmente. D. Cada 5 años.

10. ¿Qué pasa si olvidas ANALYZE después de una carga masiva?

A. La base se cae. B. El planner usa estadísticas viejas y puede elegir planes muy lentos. C. Los datos se corrompen. D. La réplica se desconecta.

Preguntas abiertas (3)

A. Explica por qué PostgreSQL necesita VACUUM y MySQL no. ¿Qué hace cada motor para manejar las filas eliminadas/actualizadas?

B. Tu tabla `pedidos` tiene 500 millones de filas. ¿La particionarías? Si sí, ¿por qué columna? Si no, ¿por qué no?

C. Diseña una checklist chachapali para una base de producción con 50 GB de datos, RPO 5 min, RTO 30 min, 10 tablas principales, 30 índices.

Respuestas modelo

1. B — VACUUM marca filas muertas como reutilizables. La A es `DELETE`. La C es `pg_dump`. La D es `ALTER USER`.

2. B — VACUUM es no-bloqueante y devuelve espacio solo dentro del mismo archivo. VACUUM FULL bloquea y devuelve al SO. La A es falsa. La C es al revés. La D es falsa.

3. B — ANALYZE actualiza estadísticas. La A es `DELETE`. La C es `VACUUM FULL`. La D es `START SLAVE`.

4. B — Por rangos de una columna. La A es por hash. La C es otra cosa. La D es motor.

5. B — Tablas grandes con queries por la columna. La A no aplica. La C no es criterio. La D es absurdo.

6. B — El índice funciona para queries por las primeras columnas en orden. La A es falsa. La C no siempre. La D no es eso.

7. A — `pg_stat_user_indexes.idx_scan`. La B es de queries. La C es MySQL. La D es mantenimiento.

8. B — `sys.schema_redundant_indexes`. La A es respaldo. La C es mantenimiento. La D es estado.

9. C — Anualmente. La A es operacional. La B es muy frecuente. La D es muy raro.

10. B — El planner usa estadísticas viejas. La A es falso. La C no es automático. La D no es eso.

Respuestas a las preguntas abiertas

A. PostgreSQL usa MVCC: cada UPDATE crea una nueva fila y la vieja se marca muerta. Sin VACUUM, las filas muertas se acumulan. MySQL con InnoDB también usa MVCC pero las filas muertas se purgan en el undo log por una tarea interna, y el espacio se reutiliza automáticamente. MySQL no tiene un comando VACUUM porque su mecanismo es diferente.

B. 500 millones de filas: sí, particionar por `fecha_pedido` (rango mensual o trimestral). Las queries típicas filtran por fecha, las particiones antiguas se pueden archivar con `DROP PARTITION` instantáneo. Si la mayoría de queries no filtran por fecha, particionar por hash sobre `cliente_id` o `pedido_id` también funciona.

C. Checklist chachapali:

Crecimiento: tamaño actual, proyección, tablas candidatas a particionar. Rendimiento: top 10 queries lentas, top 10 frecuentes, índices no usados. Seguridad: usuarios inactivos, GRANT ALL, certificados TLS por vencer. Resiliencia: último failover de prueba, última restauración de prueba, RPO/RTO cumplidos. Mantenimiento: versión del motor, autovacuum funcionando, logs rotando. Documentación: runbook actualizado, diagrama de arquitectura vigente.

Tras las huellas — Por Citlalli

Al final del xiuhpohualli mexica, el tlatoani realizaba el fuego nuevo: se encendía una llama nueva en el Templo Mayor, y si la llama se mantenía viva durante la noche siguiente, significaba que el sol regresaría y el ciclo continuaría. Si la llama se apagaba, el mundo se acababa (al menos simbólicamente) y había que empezar de nuevo.

Tu chachapali anual es tu fuego nuevo: revisas que el sistema esté sano, documentas los hallazgos, y si la llama sigue ardiendo, el ciclo continúa 52 años más. No importa si la base tiene 5 MB o 500 GB: el principio es el mismo.

Sabiduría mexica: el mantenimiento no es glamour. Es la diferencia entre un sistema que sobrevive 52 años y uno que se apaga al primer eclipse.

Cierre del cuaderno

Diferencia entre VACUUM y VACUUM FULL. Cuándo particionar una tabla y por qué columna. Cómo detectar índices no usados. ¿Cuándo fue mi última chachapali? ____________. Tarea de mantenimiento para esta semana: ____________.