Mejores prácticas de almacenamiento de OCI Database with PostgreSQL

El almacenamiento compartido de escala automática gestiona los datos de la base de datos y los archivos log de escritura anticipada (WAL). Este tipo de almacenamiento proporciona alta disponibilidad mediante la replicación entre dominios de disponibilidad.

Estas son las características clave del almacenamiento compartido de escala automática:

• Escala dinámica: el almacenamiento se escala automáticamente a medida que crea y borra objetos de base de datos. No se requiere aprovisionamiento previo y no se produce tiempo de inactividad durante la ampliación.
• Arquitectura desacoplada: los recursos informáticos y el almacenamiento están separados, lo que permite que cada uno se amplíe de forma independiente en función de sus necesidades.
• Alta disponibilidad: los datos se replican en los dominios de disponibilidad, lo que permite un failover rápido sin pérdida de datos.
• Límite estricto: la capacidad máxima es de 32 TB por sistema de base de datos.

El almacenamiento compartido de escala automática almacena los siguientes tipos de contenido:

• Datos de base de datos, como tablas, índices y otros objetos de base de datos.
• Archivos WAL para durabilidad y replicación de transacciones.

El almacenamiento temporal se asigna por nodo y maneja archivos operativos que no requieren persistencia a largo plazo. Este almacenamiento no se escala automáticamente y su tamaño se basa en la unidad de computación. La fórmula para este tamaño de almacenamiento se basa en Max(50 GB, 2 GB + numOCPU × 32 GB).

Estas son las características clave del almacenamiento temporal:

• No se escala automáticamente: para aumentar la capacidad, debe actualizar a una unidad de computación más grande.
• Aislado de datos críticos: si se llena el almacenamiento temporal, los datos de la base de datos y el WAL no se verán afectados.

El almacenamiento temporal almacena los siguientes tipos de contenido:

• Archivos temporales/derrames de pgsql_tmp/pg_tempdir y pg_replslot, que se crean cuando las consultas superan la memoria disponible.
• Archivos log PostgreSQL para supervisión y depuración.
• Archivos de desbordamiento de ranuras de replicación (para replicación lógica).

El resto de este tema aborda las mejores prácticas para gestionar los problemas de agotamiento del almacenamiento para estos tipos de almacenamiento.

Utilice la siguiente consulta para identificar la retención de WAL por ranuras de replicación:

SELECT 
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained,
    pg_size_pretty(safe_wal_size) AS safe_wal_remaining
FROM pg_replication_slots;

En este ejemplo de un retorno, se retienen 15 GB de WAL y solo se pueden retener 5 GB más antes de que la ranura se invalide (si se configura max_slot_wal_keep_size). Si max_slot_wal_keep_size = -1, safe_wal_remaining muestra NULL.

 slot_name | active | wal_retained | safe_wal_remaining
-----------+--------+--------------+--------------------
 replica_1 | t      | 15 GB        | 5 GB

Para obtener instrucciones sobre la configuración de alarmas personalizadas, consulte Creación de alarmas y paneles de control de supervisión de OCI personalizados de PostgreSQL mediante Container Instances

Le recomendamos que configure alertas si se produce alguna de las siguientes condiciones:

• wal_retained supera el 50 % de la capacidad disponible.
• Cualquier espacio tiene active = false durante más de una hora.

Utilice las siguientes medidas de mitigación según sea necesario:

• Borrar ranuras abandonadas: si un suscriptor ya no es necesario o se puede volver a crear, borre la ranura de replicación:

  SELECT pg_drop_replication_slot('slot_name');

• Definir límite de retención finito: configure max_slot_wal_keep_size en un valor que equilibre la estabilidad de la replicación con la protección principal.

PostgreSQL proporciona amplios parámetros de registro útiles para la depuración. Sin embargo, la configuración de registro agresiva puede generar una E/S de disco excesiva durante el tráfico máximo, llenando rápidamente el volumen de almacenamiento temporal.

Estas son las configuraciones problemáticas comunes:

• log_statement = 'all': registra cada sentencia SQL, generando volúmenes de log masivos en sistemas ocupados.
• log_min_duration_statement = 0: registra cada sentencia con su duración.
• log_connections/log_disconnections = on: en sistemas de alto tráfico, genera entradas para cada conexión.
• Registro de nivel de depuración activado: los parámetros como log_executor_stats, log_parser_stats, log_planner_stats generan una salida detallada.

Los síntomas del registro excesivo de PostgreSQL incluyen:

• El uso de almacenamiento temporal aumenta rápidamente.
• E/S de disco alto en el volumen de registro.

PostgreSQL utiliza memoria adicional más allá de shared_buffers para el procesamiento de consultas. Mientras que shared_buffers maneja el almacenamiento en caché de datos y las operaciones DML en memoria, work_mem se utiliza para operaciones de consulta como la ordenación, la unión y la agregación.

Cuando una consulta implica operaciones JOIN, AGGREGATE o SORT, PostgreSQL primero comprueba si las columnas relevantes tienen índices preordenados. Si los índices no están disponibles, PostgreSQL intenta realizar estas operaciones en la memoria.

Si el valor work_mem no es suficiente para los datos que se están procesando, PostgreSQL genera archivos temporales y utiliza disco en lugar de memoria.

A continuación, se muestran escenarios comunes en los que se puede producir una configuración de memoria inadecuada:

En el siguiente ejemplo, se muestra una entrada de log de ejemplo cuando el disco se llena:

2025-12-03 10:55:25.650 UTC [249610] ERROR: could not write block 196800198 of temporary file: No space left on device

Los síntomas de una configuración de memoria inadecuada excesiva incluyen:

• El almacenamiento temporal se completa durante la ejecución de consultas complejas.
• Rendimiento de consultas lento en operaciones JOIN, SORT o AGGREGATE.
• Aumento de E/S de disco durante el procesamiento de consultas.

La replicación lógica PostgreSQL utiliza la descodificación lógica para transformar los registros WAL raw en cambios significativos y estructurados a nivel de fila. A diferencia de la replicación física que copia bloques de disco de byte a byte, la descodificación lógica interpreta los datos transaccionales y los convierte en cambios lógicos (INSERT, UPDATE, DELETE) con valores de columna reales.

Cada ranura de replicación lógica tiene un proceso de remitente WAL asociado responsable de los cambios de transmisión en los suscriptores de destino. Las transacciones se envían en orden de confirmación, y el suscriptor recibe, descodifica y aplica estos cambios al sistema de destino.

PostgreSQL soporta la replicación lógica nativa donde el suscriptor es otra instancia PostgreSQL. A partir de PostgreSQL 14, los suscriptores pueden gestionar grandes transacciones en curso de forma eficaz con el modo streaming=on activado:

• Las transacciones en curso (todavía no confirmadas) se transmiten de forma incremental al suscriptor.
• El suscriptor aplica los cambios a un estado temporal/pendiente inmediatamente.
• Una vez que la transacción se confirma en el editor, el suscriptor confirma esos cambios.
• Si se realiza un rollback de la transacción, el suscriptor descarta los cambios pendientes.

Este enfoque evita que el editor acumule grandes cantidades de datos descodificados en la memoria o derrame archivos mientras espera que se completen las transacciones de larga ejecución.

Los suscriptores federados o de terceros también pueden consumir de ranuras de replicación lógicas PostgreSQL mediante el protocolo de replicación PostgreSQL. Sin embargo, a diferencia de los suscriptores PostgreSQL nativos, los suscriptores federados normalmente no soportan el modo streaming=on porque no pueden controlar cómo se comporta el sistema de destino subyacente.

Cuando un suscriptor no admite streaming=on, el remitente de WAL de PostgreSQL mantiene los cambios descodificados para las transacciones en curso en los archivos de vertido en:

pg_replslot/<slot_name>/xid-<xid>-lsn-<lsn>.spill

Estos archivos de derrame pueden crecer debido a la estricta garantía de orden de confirmación de PostgreSQL. Por ejemplo:

• La transacción TRX1 permanece abierta durante mucho tiempo (inactivo en la transacción o realizando operaciones masivas).
• Otras transacciones independientes (TRX2, TRX3, TRX4, etc.) continúan confirmándose.
• El remitente de WAL acumula datos de transacciones confirmadas en los archivos de derrames.
• Estas transacciones solo se envían al suscriptor después de que TRX1 emita COMMIT o ROLLBACK.

Este es el comportamiento esperado al trabajar con suscriptores federados que no soportan el modo streaming=on.

En el siguiente ejemplo, se muestran los detalles del archivo de derrames (registrados en el nivel DEBUG2) en el registro:

2025-12-03 10:28:14.650 UTC [249610] DEBUG: spill 4096 changes in XID 750 to disk

Si el disco se llena, las entradas del log cambian a lo siguiente:

2025-12-03 10:32:15.550 UTC [249610] ERROR: could not write to data file for XID <xid>: No space left on device
2025-12-03 10:32:15.554 UTC [249610] ERROR: could not read from reorderbuffer spill file: No space left on device

Los síntomas de los archivos de desbordamiento de ranura de replicación incluyen:

• El uso de almacenamiento temporal aumenta rápidamente.
• Muchos archivos .spill están presentes en la carpeta pg_replslot.