Entrevista sobre PostgreSQL: ¿Cómo funcionan juntos MVCC y VACUUM?

Enunciado y contexto aplicable

Una tabla orders de PostgreSQL 18 tiene 500 millones de filas activas y actualizaciones constantes. El equipo observa cuatro hechos:

• n_dead_tup sigue aumentando;
• los workers de autovacuum aparecen periódicamente;
• pg_relation_size('orders') no disminuye tras un vacuum ordinario;
• una sesión de la aplicación ha estado idle in transaction durante seis horas y expone un backend_xmin antiguo.

Explique la cadena completa desde la visibilidad de MVCC hasta la limpieza física. Diagnostique el incidente, elija una secuencia de recuperación segura y detalle la evidencia requerida antes de declararlo resuelto. Los números son datos de ejercicio, no umbrales operativos universales.

Esto se aplica a entrevistas de backend, bases de datos, plataforma y SRE en las que el candidato debe conectar la semántica de concurrencia con el comportamiento del almacenamiento en producción. SQL es solo el lenguaje de inspección.

Qué está evaluando el entrevistador

La primera prueba es si el candidato comprende que UPDATE es creación de versiones. PostgreSQL almacena metadatos de tuplas como el ID de la transacción que inserta (xmin) y el ID de la transacción que elimina o reemplaza (xmax). Una instantánea combina los límites de transacción y el estado de confirmación para decidir qué versión es visible. La regla es más precisa que “elegir la fila con el mayor xmin”.

La segunda prueba es si el candidato puede conectar la visibilidad con la limpieza. Una versión antigua no se puede eliminar mientras una instantánea activa aún pueda necesitarla. Una transacción abierta prolongada, una transacción preparada o un replication slot pueden retener el horizonte de limpieza. Autovacuum puede ejecutarse con éxito y, aun así, reportar tuplas que están muertas pero no son eliminables.

La tercera prueba es la precisión operativa. Un VACUUM simple normalmente hace que el espacio muerto sea reutilizable dentro de la relación; por lo general, no reduce el archivo de la relación. VACUUM FULL reescribe la relación, necesita espacio temporal adicional en disco y toma un bloqueo ACCESS EXCLUSIVE. Es una operación de mantenimiento excepcional, no la primera respuesta ante un aumento en la estimación de tuplas muertas.

Por último, el candidato debe separar cuatro señales: conteos estimados de tuplas, espacio recuperable, tamaño de la relación y rendimiento visible para el usuario. Estos conceptos están relacionados pero no son intercambiables. Una disminución en n_dead_tup no demuestra que el archivo del sistema operativo se haya reducido, y un tamaño de archivo inalterado no demuestra que el vacuum haya fallado.

Preguntas para aclarar antes de responder

• ¿Qué niveles de aislamiento se están utilizando? Bajo Read Committed, cada sentencia normalmente obtiene una nueva instantánea; Repeatable Read y Serializable mantienen una instantánea a nivel de transacción. Una transacción inactiva puede retener su horizonte de instantánea incluso sin realizar ningún trabajo.
• ¿Es el backend_xmin antiguo el bloqueador global? Establezca correlación en lugar de asumirla. Las transacciones preparadas, los slots de replicación lógica o física y otras sesiones pueden exponer un horizonte más antiguo.
• ¿Están las estadísticas lo suficientemente actualizadas para guiar el incidente? n_dead_tup y n_live_tup son estimaciones. Léalas junto con las marcas de tiempo del último vacuum, el progreso, los registros, los tamaños de las relaciones y el comportamiento de la carga de trabajo.
• ¿El objetivo es la reutilización del disco o la reducción inmediata del archivo? El vacuum rutinario apunta a la reutilización en estado estable. Devolver una gran cantidad de espacio al sistema operativo requiere una reescritura o un plan adecuado de reconstrucción en línea.
• ¿Puede la aplicación terminar de forma segura la transacción de seis horas? Identifique primero al propietario y la operación de negocio. Cancelarla o terminarla revierte su trabajo abierto y puede afectar el flujo de un usuario.
• ¿Qué cambió en la carga de trabajo? La tasa de actualizaciones, las columnas indexadas, el ancho de fila, la configuración de autovacuum, la saturación de workers y la duración de las transacciones afectan el recambio de versiones y la capacidad de limpieza.
• ¿Cuánto impacto en bloqueos y E/S está permitido? Un plan de recuperación debe preservar la latencia, la replicación, el margen de disco y la disponibilidad, no solo terminar el mantenimiento rápidamente.

Estructura de respuesta de 30 segundos

“El MVCC de PostgreSQL permite que cada sentencia lea una instantánea consistente mientras las actualizaciones crean nuevas versiones de tuplas. La versión antigua permanece hasta que ninguna instantánea activa pueda verla. En este caso, la transacción abierta de seis horas puede retener backend_xmin, por lo que autovacuum puede escanear la tabla pero no puede eliminar las versiones que siguen siendo potencialmente visibles.

Primero confirmaría los horizontes más antiguos entre sesiones, transacciones preparadas y slots de replicación; los correlacionaría con las estadísticas de la tabla, el progreso de vacuum, los registros y los tamaños; luego finalizaría el bloqueador verificado a través de la aplicación propietaria. Ejecutaría o dejaría que el vacuum ordinario se ponga al día bajo límites medidos de E/S y verificaría que las estimaciones de tuplas muertas y el comportamiento de reutilización se estabilicen. Un VACUUM simple hace que el espacio sea reutilizable y normalmente no reduce el archivo. VACUUM FULL reescribe y bloquea exclusivamente la tabla, por lo que requiere una decisión de mantenimiento independiente. Finalmente, limitaría la duración de las transacciones, ajustaría las tablas calientes por relación, monitorearía la antigüedad de XID y mantendría el congelamiento para que los XID antiguos nunca crucen el horizonte de wraparound.”

Análisis detallado paso a paso

Paso 1: Rastrear una actualización a través de MVCC

Supongamos que la transacción 100 inserta una versión de orden. El encabezado de su tupla registra un XID de inserción en xmin. Más tarde, la transacción 220 actualiza la orden. PostgreSQL crea una tupla sucesora y marca la versión anterior como reemplazada utilizando metadatos de transacción que incluyen xmax; no sobrescribe los bytes anteriores in situ como sugeriría el modelo lógico.

Un lector consulta su instantánea y el estado de confirmación de la transacción para decidir qué versión es visible. En términos simplificados, descarta las versiones insertadas por transacciones que no estaban confirmadas o que estaban en el futuro de la instantánea, y puede conservar una versión cuya transacción de eliminación aún no era visible. Las reglas de visibilidad reales también manejan la transacción actual, las transacciones abortadas, los identificadores de comandos (command IDs) y los hint bits, por lo que comparar los valores numéricos de xmin y xmax por sí solos no es una implementación correcta.

Este modelo reduce el conflicto de bloqueos entre lectura y escritura: los lectores ordinarios no bloquean a los escritores y los escritores no bloquean a los lectores ordinarios. No significa que los escritores nunca se bloqueen entre sí. Dos transacciones que actualizan la misma fila lógica aún pueden esperar o entrar en conflicto, y los niveles de aislamiento más altos pueden abortar transacciones para preservar sus garantías.

Paso 2: Derivar el horizonte de limpieza

Después de que la transacción 220 se confirma, la tupla antigua queda obsoleta para nuevas instantáneas. No se puede eliminar de inmediato si una instantánea que comenzó antes todavía puede verla. Vacuum elige un punto de corte basado en el horizonte relevante más antiguo. Las versiones más nuevas que ese límite de seguridad pueden considerarse “recientemente muertas”: lógicamente obsoletas para el trabajo actual pero aún no seguras para su eliminación.

La sesión idle in transaction de seis horas es peligrosa porque el cliente ha dejado una transacción abierta. Su backend_xmin puede preservar una instantánea antigua incluso aunque el servidor esté esperando el siguiente comando del cliente. La misma investigación debe incluir:

• pg_prepared_xacts, porque una transacción preparada puede retener un XID antiguo;
• pg_replication_slots, porque xmin o catalog_xmin pueden retener filas o catálogos requeridos;
• otras filas de pg_stat_activity con backend_xid o backend_xmin antiguos;
• la retroalimentación de réplicas y la configuración de decodificación lógica, porque los requisitos de replicación pueden afectar la limpieza.

Por lo tanto, la cadena causal es: horizonte de larga duración → las versiones antiguas permanecen potencialmente visibles → vacuum no puede recuperarlas → el trabajo en heap e índices se acumula → la eficiencia de la caché y el costo de escaneo pueden deteriorarse. Esa cadena debe demostrarse con marcas de tiempo y horizontes alineados, no inferirse del nombre de una sola sesión inactiva.

Paso 3: Diagnosticar por separado con estimaciones, progreso y tamaño

Comience con una instantánea de solo lectura de la actividad y las estadísticas de la relación:

SELECT pid,
       usename,
       application_name,
       state,
       xact_start,
       age(backend_xid) AS xid_age,
       age(backend_xmin) AS xmin_age,
       wait_event_type,
       wait_event,
       left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY GREATEST(
  COALESCE(age(backend_xid), 0),
  COALESCE(age(backend_xmin), 0)
) DESC;

SELECT relid::regclass AS relation,
       n_live_tup,
       n_dead_tup,
       n_tup_upd,
       n_tup_hot_upd,
       last_vacuum,
       last_autovacuum,
       vacuum_count,
       autovacuum_count
FROM pg_stat_user_tables
WHERE relid = 'orders'::regclass;

SELECT pg_size_pretty(pg_relation_size('orders')) AS heap_size,
       pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

n_dead_tup es una estimación, no una medición exacta de bloat. last_autovacuum demuestra que un worker terminó, no que eliminó todas las versiones obsoletas. Una relación grande puede estar en buen estado si las páginas liberadas se reutilizan al mismo ritmo que llegan las nuevas versiones. Por el contrario, un tamaño de relación estable puede ocultar un aumento en la latencia o el recambio de índices.

Mientras vacuum esté activo, inspeccione pg_stat_progress_vacuum para conocer su fase y los bloques del heap escaneados. Utilice los registros de autovacuum o la salida de VACUUM (VERBOSE) para saber cuántas tuplas se eliminaron, cuántas permanecieron no eliminables y si el congelamiento avanzó. Verifique pg_stat_all_tables, el historial del tamaño de la relación, la latencia de las consultas, la presión sobre búferes y E/S, la tasa de generación de WAL, el retraso de replicación y el margen de disco en la misma línea de tiempo.

Paso 4: Recuperar en el orden más seguro

Primero identifique al propietario y el propósito de la transacción más antigua. Si está abandonada, ciérrela a través de la aplicación o del propietario de la conexión. Si es trabajo activo de negocio, decida si la reversión es aceptable antes de cancelarla. PostgreSQL expone pg_cancel_backend y pg_terminate_backend, pero el acceso a una función no equivale a una autorización para interrumpir la producción.

A continuación, resuelva cualquier transacción preparada más antigua o slot de replicación obsoleto a través de su sistema propietario. Eliminar un slot activo puede requerir la reconstrucción de una réplica o la pérdida de una posición de decodificación esperada, por lo que esta es una decisión explícita de recuperación.

Una vez que el horizonte avance, permita que autovacuum se ponga al día o ejecute un VACUUM (VERBOSE, ANALYZE) orders simple dirigido durante una ventana medida. Monitoree la latencia, la E/S, WAL, el retraso de replicación, el progreso de vacuum y el margen restante en disco. No inicie múltiples tareas de mantenimiento que compitan entre sí simplemente porque la primera tarde tiempo.

Luego verifique los resultados:

• el backend_xmin relevante más antiguo o el horizonte del slot avanzó;
• vacuum reporta que las versiones muertas previamente retenidas son eliminables y se han eliminado;
• n_dead_tup muestra una tendencia a la baja tras la actualización de estadísticas;
• las nuevas actualizaciones reutilizan el espacio disponible y el crecimiento de la relación regresa a un estado estable esperado;
• la latencia de las solicitudes, el costo de escaneo de índices, WAL y el retraso de las réplicas se mantienen dentro de sus límites acordados;
• age(relfrozenxid) y la antigüedad del XID de la base de datos tienen un margen seguro.

Solo después de esta evidencia debe el equipo evaluar la compactación física. VACUUM FULL orders crea una nueva copia compacta, necesita disco adicional durante la reescritura y mantiene un bloqueo ACCESS EXCLUSIVE. Una tabla de 500 millones de filas puede requerir en su lugar una estrategia de reconstrucción en línea o un reemplazo planificado de particiones. La elección correcta depende del tiempo de inactividad, el disco libre, la replicación, las claves foráneas, la convergencia de escritura y la reversión, no del deseo de reducir una métrica de tamaño.

Paso 5: Explicar autovacuum sin magia

Autovacuum reacciona a estadísticas acumulativas. Para actualizaciones y eliminaciones, PostgreSQL 18 utiliza un activador de la forma:

vacuum threshold = min(
  autovacuum_vacuum_max_threshold,
  autovacuum_vacuum_threshold
    + autovacuum_vacuum_scale_factor * pg_class.reltuples
)

El vacuum impulsado por inserciones tiene un umbral independiente basado en las tuplas insertadas y la fracción de páginas no congeladas. El vacuum anti-wraparound también se fuerza por la antigüedad del XID incluso cuando el autovacuum ordinario se ha deshabilitado para una tabla.

En una relación muy grande y con alta tasa de actualizaciones, un factor de escala global puede esperar demasiados cambios o crear trabajo en ráfagas. Ajuste los parámetros de almacenamiento de la tabla a partir de la producción medida de versiones y la capacidad de vacuum. Inspeccione también la disponibilidad de workers y el retraso por costo: un activador correcto no garantiza que un worker comience de inmediato o termine más rápido de lo que la carga de trabajo genera basura.

La prevención también pertenece a la ruta de escritura. Mantenga las transacciones cortas y evite llamadas de red o tiempo de espera del usuario dentro de ellas. Aplique idle_in_transaction_session_timeout de forma selectiva a roles de aplicación adecuados, ya que los grupos de conexiones y los trabajos legítimos prolongados necesitan un manejo compatible. Actualice únicamente las columnas necesarias. Cuando ninguna columna indexada cambia y la nueva tupla cabe en la misma página de heap, una actualización HOT puede evitar nuevas entradas de índice; fillfactor puede mejorar esa oportunidad a costa de dejar más espacio de página sin usar inicialmente.

Paso 6: Conectar el congelamiento con el wraparound de XID

Los IDs de transacción normales son de 32 bits y se comparan en un espacio circular. Un XID normal tiene aproximadamente dos mil millones de IDs considerados más antiguos y dos mil millones considerados más nuevos. Si una tupla mantuviera un XID de inserción ordinario indefinidamente, con el tiempo un valor muy antiguo podría parecer estar en el futuro.

Vacuum evita esto congelando las versiones de tuplas confirmadas que son suficientemente antiguas. El PostgreSQL moderno representa el congelamiento mediante el estado de la tupla mientras preserva el xmin original para visibilidad forense; la versión congelada se trata como más antigua que cualquier transacción normal. Los marcadores de XID congelado de la tabla y de la base de datos registran hasta qué punto ha avanzado este trabajo.

Este es un requisito de corrección, no una limpieza opcional de bloat. Monitoree age(pg_class.relfrozenxid) y age(pg_database.datfrozenxid), investigue los vacuums anti-wraparound y conserve suficiente capacidad para que se completen. Aumentar los límites de congelamiento simplemente pospone el trabajo y reduce la ventana de seguridad; no elimina la restricción circular de XID.

Respuesta de ejemplo sólida

“Modelaría el incidente como producción de versiones versus recuperación segura. El MVCC de PostgreSQL le otorga a cada sentencia o transacción una instantánea. Una actualización crea una tupla sucesora y marca la versión anterior a través de los metadatos de la transacción. Un lector evalúa xmin, xmax, el estado de confirmación y su instantánea para seleccionar una versión visible. Esto permite que las lecturas y escrituras ordinarias continúen sin entrar en conflicto con bloqueos de lectura, mientras que los escritores en la misma fila aún pueden bloquearse o abortar.

La versión antigua no se puede eliminar hasta que ninguna instantánea relevante pueda verla. Inspeccionaría todas las sesiones en busca de backend_xid y backend_xmin antiguos, luego las transacciones preparadas y los slots de replicación. La transacción inactiva de seis horas es una fuerte sospechosa porque una transacción abierta puede retener su horizonte de instantánea, pero demostraría que es el bloqueador más antiguo antes de terminarla.

Correlacionaría ese horizonte con pg_stat_user_tables, pg_stat_progress_vacuum, los registros de autovacuum, los tamaños de heap e índices, el crecimiento de la relación, la latencia, la E/S, WAL y el retraso de replicación. n_dead_tup es una estimación, y una marca de tiempo de autovacuum solo demuestra que se ejecutó una pasada. Si el propietario confirma que la transacción está abandonada, la cerraría, resolvería cualquier horizonte más antiguo y dejaría que un vacuum simple dirigido se ponga al día bajo una carga medida.

Un VACUUM simple elimina las versiones que son seguras de eliminar y hace que su espacio sea reutilizable. Por lo general, deja el archivo de la relación con el mismo tamaño. VACUUM FULL reescribe la relación, necesita disco temporal y bloquea exclusivamente la tabla, por lo que lo consideraría únicamente bajo una decisión de compactación planificada con una estrategia de tiempo de inactividad o reconstrucción en línea.

Para la prevención, limitaría la duración de las transacciones, configuraría tiempos de espera de transacciones inactivas apropiados para cada rol, monitorearía los horizontes más antiguos y la antigüedad de XID, ajustaría autovacuum por tabla caliente según el recambio medido y fomentaría las actualizaciones HOT donde el esquema y la carga de trabajo lo permitan. Vacuum también congela las versiones confirmadas suficientemente antiguas para que sus XIDs se traten siempre como pasados, evitando el wraparound. El éxito significa que el horizonte bloqueador avanza, las tuplas eliminables se limpian, la reutilización de espacio estabiliza el crecimiento, los SLO del servicio se mantienen saludables y la antigüedad de XID congelado retiene un margen seguro.”

Errores comunes y mejoras

• Decir que UPDATE modifica una fila in situ → PostgreSQL normalmente crea una nueva versión de tupla en el heap → rastree el predecesor y el sucesor a través de los metadatos de MVCC.
• Reducir la visibilidad a xmin < current_xid → el estado de confirmación, los límites de la instantánea, las transacciones activas, xmax y las reglas de comandos importan → describa la decisión de la instantánea sin inventar un atajo numérico.
• Afirmar que lectores y escritores nunca se bloquean → MVCC elimina el conflicto ordinario de bloqueos lectura/escritura, mientras que los escritores sobre la misma fila y los bloqueos explícitos aún entran en conflicto → formule la garantía más precisa.
• Asumir que un autovacuum completado eliminó todas las tuplas muertas → los horizontes antiguos pueden dejar versiones como no eliminables → inspeccione las tuplas retenidas, los horizontes bloqueadores, los registros y el progreso.
• Tratar n_dead_tup como bytes exactos de bloat → es un conteo estimado de filas → mida el heap, los índices, el crecimiento, la reutilización y el rendimiento por separado.
• Llamar a un tamaño de archivo inalterado un fallo de vacuum → el vacuum simple normalmente mantiene el espacio liberado dentro de la relación para su reutilización → evalúe la reutilización en estado estable antes de exigir compactación.
• Ejecutar VACUUM FULL de inmediato → la reescritura requiere disco adicional y un bloqueo exclusivo → elimine los bloqueadores y póngase al día con el vacuum ordinario antes de elaborar un plan de compactación.
• Ajustar únicamente el factor de escala global → las tablas calientes y la capacidad de los workers difieren → utilice configuraciones por tabla respaldadas por la tasa de versiones, el tiempo de finalización y la evidencia de SLO.
• Terminar el PID más antiguo sin verificaciones de propiedad → su transacción se revierte y un flujo de cliente puede fallar → confirme primero el propósito, el impacto y la ruta de recuperación.
• Tratar el congelamiento como una optimización de almacenamiento → el congelamiento protege la corrección en la comparación circular de XID → monitoree la antigüedad de XID congelado y el trabajo anti-wraparound como un control de seguridad.

Preguntas de seguimiento

Pregunta de seguimiento 1: ¿Por qué la tabla puede mantener el mismo tamaño después de un VACUUM exitoso?

El vacuum simple marca el espacio de las tuplas muertas como reutilizable dentro de la misma relación. Puede devolver páginas completamente libres al final físico en circunstancias limitadas, pero el comportamiento habitual es la reutilización interna. Reducir el espacio libre arbitrario requiere reescribir o reorganizar la relación. Por lo tanto, un tamaño estable junto con una latencia estable y una reutilización continua puede ser un estado saludable.

Pregunta de seguimiento 2: ¿Por qué autovacuum se ejecutó pero dejó muchas versiones muertas?

Es posible que aún sean visibles para una instantánea antigua, estén retenidas por una transacción preparada o un horizonte de replicación, o se generen más rápido de lo que los workers pueden limpiarlas. El worker también puede retrasarse o interrumpirse por la carga de trabajo y los bloqueos. Utilice registros detallados, el progreso, los horizontes más antiguos, la saturación de workers y la tasa de producción de versiones para distinguir estos casos.

Pregunta de seguimiento 3: ¿Cuál es la diferencia entre VACUUM y ANALYZE?

Vacuum recupera espacio reutilizable, mantiene los índices y el mapa de visibilidad, y congela los metadatos de transacciones antiguas. Analyze muestrea datos para actualizar las estadísticas del planificador. VACUUM (ANALYZE) realiza ambas cosas, pero una no sustituye a la otra: las estadísticas precisas no eliminan tuplas muertas y el espacio recuperado no garantiza un modelo de distribución de datos preciso.

Pregunta de seguimiento 4: ¿Cómo reducen las actualizaciones HOT la presión sobre vacuum?

Cuando una actualización no cambia ninguna columna indexada y el sucesor cabe en la misma página de heap, PostgreSQL puede evitar agregar nuevas entradas de índice. Las versiones intermedias en una cadena HOT también pueden podarse durante el acceso normal a la página. HOT no elimina los requisitos de MVCC ni de vacuum, pero reduce el recambio de índices y el trabajo de limpieza. Monitoree n_tup_hot_upd frente a las actualizaciones totales y pruebe cualquier cambio de fillfactor frente a los costos de espacio y caché.

Pregunta de seguimiento 5: ¿Se puede deshabilitar autovacuum y ejecutar una tarea nocturna en su lugar?

Eso es riesgoso para cargas de trabajo variables y no deshabilita el mantenimiento anti-wraparound. Un pico durante el día puede generar más versiones obsoletas de las que una ventana nocturna puede recuperar, mientras que las tablas estáticas aún necesitan congelamiento eventualmente. Mantenga autovacuum habilitado, ajústelo a partir del recambio observado en las tablas y compleméntelo con mantenimiento controlado solo cuando la carga de trabajo justifique esa elección.

Pregunta de seguimiento 6: ¿Qué salvaguarda ayuda con las transacciones inactivas?

idle_in_transaction_session_timeout puede terminar una sesión que espera demasiado tiempo dentro de una transacción abierta. Aplíquelo a roles compatibles y pruebe el comportamiento del pool, los reintentos y los trabajos legítimos. Corrija también el límite en la aplicación: inicie la transacción poco antes del trabajo en la base de datos, confirme o revierta de inmediato y nunca espere la entrada del usuario o un servicio remoto mientras la mantiene abierta.