Database OCI con best practice di storage PostgreSQL

Lo storage condiviso su scala automatica gestisce i dati del database e i file WAL (Write-Ahead Log). Questo tipo di storage fornisce alta disponibilità tramite la replica tra i domini di disponibilità.

Di seguito sono riportate le caratteristiche chiave dello storage condiviso su scala automatica.

• Scalabilità dinamica: lo storage si ridimensiona automaticamente quando crei ed elimini gli oggetti di database. Non è richiesto alcun pre-provisioning e non si verificano tempi di inattività durante il ridimensionamento.
• Architettura disaccoppiata: computazione e storage sono separati, consentendo a ciascuno di ridimensionarsi in modo indipendente in base alle tue esigenze.
• Alta disponibilità: i dati vengono replicati tra i domini di disponibilità, consentendo un failover rapido senza perdita di dati.
• Limite rigido: la capacità massima è di 32 TB per sistema di database.

La memorizzazione condivisa su scala automatica memorizza i tipi di contenuto riportati di seguito.

• Dati di database quali tabelle, indici e altri oggetti di database.
• File WAL per durabilità e replica delle transazioni.

Lo storage temporaneo viene allocato per ogni nodo e gestisce i file operativi che non richiedono persistenza a lungo termine. Questo storage non viene ridimensionato automaticamente e viene dimensionato in base alla forma di computazione. La formula per questa dimensione di storage si basa su Max (50 GB, 2 GB + numOCPU × 32 GB).

Di seguito sono riportate le caratteristiche principali dello storage temporaneo.

• Non esegue la scala automatica: per aumentare la capacità, è necessario eseguire l'upgrade a una forma di computazione più grande.
• Isolato da dati critici: se lo storage temporaneo si riempie, i dati del database e WAL rimangono inalterati.

Lo storage temporaneo memorizza i seguenti tipi di contenuto:

• File temporanei/di versamento da pgsql_tmp/pg_tempdir e pg_replslot, creati quando le query superano la memoria disponibile.
• File di log PostgreSQL per il monitoraggio e il debug.
• File di distribuzione dello slot di replica (per la replica logica).

Il resto di questo argomento riguarda le best practice per la gestione dei problemi di esaurimento dello storage per questi tipi di storage.

Utilizzare la query seguente per identificare la conservazione WAL in base agli slot di replica:

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;

In questo esempio di restituzione, vengono conservati 15 GB di WAL e solo 5 GB in più possono essere conservati prima che lo slot venga invalidato (se è configurato max_slot_wal_keep_size). Se max_slot_wal_keep_size = -1, safe_wal_remaining mostra NULL.

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

Per istruzioni sull'impostazione di allarmi personalizzati, vedere Creare allarmi e dashboard di monitoraggio OCI personalizzati PostgreSQL utilizzando le istanze contenitore

Si consiglia di impostare avvisi se si verifica una delle seguenti condizioni:

• wal_retained supera il 50% della capacità disponibile.
• Qualsiasi slot contiene active = false per più di un'ora.

Utilizzare le seguenti misure di attenuazione in base alle esigenze:

• Eliminare gli slot abbandonati: se un sottoscrittore non è più necessario o può essere ricreato, eliminare lo slot di replica:

  SELECT pg_drop_replication_slot('slot_name');

• Impostare il limite di conservazione finito: configurare max_slot_wal_keep_size in un valore che bilancia la stabilità della replica con la protezione primaria.

PostgreSQL fornisce parametri di log estesi utili per il debug. Tuttavia, le impostazioni di registrazione aggressive possono generare un numero eccessivo di I/O su disco durante il traffico di picco e riempire rapidamente il volume di storage temporaneo.

Di seguito sono riportate le configurazioni problematiche comuni.

• log_statement = 'all': registra ogni istruzione SQL, generando enormi volumi di log sui sistemi occupati.
• log_min_duration_statement = 0: registra ogni istruzione con la relativa durata.
• log_connections/log_disconnections = on: nei sistemi con traffico elevato vengono generate voci per ogni connessione.
• Log a livello di debug abilitato a sinistra: parametri quali log_executor_stats, log_parser_stats, log_planner_stats generano output descrittivo.

I sintomi di log eccessivo di PostgreSQL includono:

• L'uso temporaneo dello stoccaggio aumenta rapidamente.
• I/O su disco elevato sul volume di log.

PostgreSQL utilizza memoria aggiuntiva oltre shared_buffers per l'elaborazione delle query. Mentre shared_buffers gestisce l'inserimento dei dati nella cache e le operazioni DML in memoria, work_mem viene utilizzato per le operazioni di query quali l'ordinamento, il join e l'aggregazione.

Quando una query coinvolge operazioni JOIN, AGGREGATE o SORT, PostgreSQL controlla prima se le colonne pertinenti hanno indici presortiti. Se gli indici non sono disponibili, PostgreSQL tenta di eseguire queste operazioni in memoria.

Se l'impostazione work_mem non è sufficiente per i dati elaborati, PostgreSQL genera file temporanei e utilizza il disco anziché la memoria.

Di seguito sono riportati gli scenari comuni in cui possono verificarsi impostazioni di memoria inadeguate.

L'esempio seguente mostra una voce di log di esempio quando il disco diventa pieno.

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

I sintomi di impostazioni di memoria inadeguate eccessive includono:

• Storage temporaneo durante l'esecuzione di query complesse.
• Prestazioni di query lente sulle operazioni JOIN, SORT o AGGREGATE.
• Aumento dell'I/O su disco durante l'elaborazione delle query.

La replica logica PostgreSQL utilizza la decodifica logica per trasformare i record WAL grezzi in modifiche significative e strutturate a livello di riga. A differenza della replica fisica che copia blocchi di dischi byte-for-byte, la decodifica logica interpreta i dati transazionali e li converte in modifiche logiche (INSERT, UPDATE, DELETE) con valori di colonna effettivi.

Ogni slot di replica logica dispone di un processo mittente WAL associato responsabile delle modifiche di streaming ai sottoscrittori di destinazione. Le transazioni vengono inviate in ordine di commit e l'abbonato riceve, decodifica e applica queste modifiche al sistema di destinazione.

PostgreSQL supporta la replica logica nativa in cui il sottoscrittore è un'altra istanza PostgreSQL. A partire da PostgreSQL 14, gli abbonati possono gestire grandi transazioni in corso in modo efficiente con la modalità streaming=on abilitata:

• Le transazioni in corso (non ancora sottoposte a commit) vengono trasmesse in modo incrementale al sottoscrittore.
• Il sottoscrittore applica immediatamente le modifiche a uno stato temporaneo/in sospeso.
• Dopo il commit della transazione sul publisher, il sottoscrittore esegue il commit di tali modifiche.
• Se viene eseguito il rollback della transazione, il sottoscrittore ignora le modifiche in sospeso.

Questo approccio impedisce al publisher di accumulare grandi quantità di dati decodificati nei file di memoria o di fuoriuscita in attesa del completamento di transazioni con tempi di esecuzione lunghi.

Gli iscritti federati o di terze parti possono utilizzare anche gli slot di replica logica PostgreSQL utilizzando il protocollo di replica PostgreSQL. Tuttavia, a differenza dei sottoscrittori nativi PostgreSQL, i sottoscrittori federati in genere non supportano la modalità streaming=on perché non possono controllare il funzionamento del sistema di destinazione di base.

Quando un sottoscrittore non supporta streaming=on, il mittente WAL PostgreSQL mantiene le modifiche decodificate per le transazioni in corso nei file di fuoriuscita all'indirizzo:

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

Questi file di fuoriuscita possono crescere a causa della rigorosa garanzia di commit-order di PostgreSQL. Ad esempio:

• La transazione TRX1 rimane aperta per molto tempo (inattivo nella transazione o durante l'esecuzione di operazioni di massa).
• Altre transazioni indipendenti (TRX2, TRX3, TRX4 e così via) continuano a essere impegnate.
• Il mittente WAL accumula i dati di transazione impegnati nei file di fuoriuscita.
• Queste transazioni vengono inviate all'abbonato solo dopo che TRX1 ha emesso COMMIT o ROLLBACK.

Questo comportamento è previsto quando si lavora con sottoscrittori federati che non supportano la modalità streaming=on.

L'esempio seguente mostra i dettagli del file di fuoriuscita (registrato a livello DEBUG2) nel log:

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

Se il disco diventa pieno, le voci di log vengono modificate come indicato di seguito.

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

I sintomi dei file di fuoriuscita degli slot di replica includono:

• L'utilizzo temporaneo dello storage aumenta rapidamente.
• Molti file .spill sono presenti nella cartella pg_replslot.