Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Evitare problemi di prestazioni con REPLICA IDENTITY FULL in PostgreSQL RDS per PostgreSQL

La replica logica PostgreSQL richiede che ogni tabella pubblicata abbia un'identità di replica in modo che il sottoscrittore possa individuare la riga corretta da aggiornare o eliminare. Per impostazione predefinita, la chiave primaria funge da identità di replica. Quando una tabella non ha una chiave primaria o un indice univoco adatto, puoi impostare l'identità della replica suFULL, il che fa sì che PostgreSQL utilizzi l'intera riga come chiave.

Oltre a REPLICA IDENTITY FULL risolvere il problema immediato della replica delle tabelle senza chiavi primarie, può comportare gravi problemi di prestazioni sia per l'editore che per il sottoscrittore. Comprendere questi impatti è importante per chiunque utilizzi la replica logica con PostgreSQL RDS per PostgreSQL, comprese le funzionalità che si basano sulla replica logica interna, come le implementazioni. blue/green

Perché REPLICA IDENTITY FULL causa problemi

Aumento del volume WAL dell'editore

L'REPLICA IDENTITYimpostazione controlla quali informazioni PostgreSQL scrive nel log write-ahead (WAL) per identificare le righe che vengono aggiornate o eliminate. Con l'identità di replica predefinita (chiave primaria), solo le colonne chiave vengono registrate come identità di riga precedente. ConFULL, PostgreSQL registra i vecchi valori di ogni colonna per ogni e. UPDATE DELETE Ciò ha diverse conseguenze:

Ricerche lente nelle righe sull'abbonato

Quando il sottoscrittore riceve un record UPDATE o un record di DELETE registro, deve trovare la riga corrispondente nella copia locale della tabella. ConREPLICA IDENTITY FULL, l'abbonato cerca una riga che corrisponda a tutti i valori di colonna della vecchia immagine di riga.

Il modo in cui PostgreSQL esegue questa ricerca varia a seconda della versione principale di PostgreSQL:

In che modo REPLICA IDENTITY FULL causa il ritardo nella replica

I due problemi sopra descritti, ovvero un WAL più ampio sull'editore e una ricerca più lenta delle righe sul sottoscrittore, si combinano per causare ritardi nella replica.

Per impostazione predefinita, la replica logica di PostgreSQL utilizza un processo di lavoro di applicazione singola per sottoscrizione per ricevere le modifiche dall'editore e applicarle alle tabelle del sottoscrittore. I processi di lavoro di applicazione vengono modificati in modo seriale, una riga alla volta, in ordine di commit. Ciò significa che il throughput dell'abbonato è limitato dalla velocità con cui può applicare ogni singola modifica.

Quando REPLICA IDENTITY FULL viene impostato su una tabella senza un indice appropriato, ogni UPDATE e DELETE richiede una scansione sequenziale dell'intera tabella per trovare la riga corrispondente. Se la tabella contiene milioni di righe, ognuna di queste operazioni può richiedere secondi o più. Il risultato è un problema a cascata:

Questo problema è particolarmente grave per le tabelle che ricevono DELETE operazioni UPDATE OR frequenti. INSERTle operazioni non sono influenzate perché non richiedono una ricerca di riga sul sottoscrittore.

Impatto sulle implementazioni blue/green

Blue/green le distribuzioni in Amazon Amazon RDS utilizzano la replica logica internamente per mantenere l'ambiente verde sincronizzato con l'ambiente blu configurando un singolo abbonamento per database. Il processo di applicazione della replica logica nell’ambiente verde è a thread singolo. Un singolo processo di lavoro di applicazione riceve tutte le modifiche dall'ambiente blu e le applica una alla volta, in ordine di commit. Non esiste alcuna applicazione parallela nel percorso di blue/green replica.

Grazie a questo design a thread singolo, la capacità dell'ambiente verde di tenere il passo con l'ambiente blu dipende interamente dalla velocità con cui un applicatore riesce a elaborare ogni singola modifica. Quando le tabelle vengono utilizzate REPLICA IDENTITY FULL senza una chiave primaria o un indice adatto, l'impatto sull'apply worker dipende dalla versione di PostgreSQL. Nelle versioni precedenti alla 16, ogni singola UPDATE tabella DELETE impone all'apply worker di eseguire una scansione sequenziale dell'intera tabella per trovare la riga corrispondente. Nelle versioni 16 e successive, PostgreSQL utilizzerà un indice adatto, se disponibile, ma se non esiste un indice idoneo, l'apply worker ricorre comunque a una scansione sequenziale. Mentre l'application worker esegue la scansione di una tabella di grandi dimensioni per una riga, tutte le altre modifiche in sospeso su tutte le tabelle vengono messe in coda e attendono.

Le conseguenze per le blue/green implementazioni sono significative:

Per evitare questi problemi, assicurati che tutte le tabelle abbiano una chiave primaria o un indice univoco adatto configurato esplicitamente come identità di replica utilizzata ALTER TABLE ... REPLICA IDENTITY USING INDEX prima di creare una blue/green distribuzione. Non fare affidamento sulla selezione REPLICA IDENTITY FULL implicita dell'indice in PostgreSQL 16+, perché l'abbonato potrebbe scegliere un indice poco selettivo o ricorrere alle scansioni sequenziali. Testa la distribuzione con un carico di lavoro di scrittura rappresentativo per verificare che l'ambiente ecologico sia in grado di tenere il passo.

Per ulteriori informazioni sui limiti di blue/green distribuzione, consultaLimitazioni e considerazioni per le distribuzioni di Amazon RDS Amazon blue/green. Per le best practice, consulta Best practice di RDS per PostgreSQL per le distribuzioni blue/green.

Come identificare le tabelle utilizzando REPLICA IDENTITY FULL

Esegui la seguente query per trovare tutte le tabelle conREPLICA IDENTITY FULL:

SELECT n.nspname AS schema, c.relname AS table_name, c.relreplident
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relreplident = 'f'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;

I valori relreplident delle colonne sono:

Soluzioni alternative e best practice

Aggiungi una chiave primaria laddove possibile

La soluzione più efficace consiste nell'aggiungere una chiave primaria alle tabelle che ne sono prive. Quando esiste una chiave primaria, PostgreSQL la utilizza come identità di replica predefinita, che fornisce ricerche efficienti nelle righe sul sottoscrittore e riduce al minimo il sovraccarico WAL per l'editore.

ALTER TABLE my_table ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

Se l'aggiunta di una chiave primaria non è possibile a causa di vincoli applicativi, è consigliabile aggiungere un indice univoco su un set di colonne e impostarlo come identità di NOT NULL replica:

CREATE UNIQUE INDEX my_table_replica_idx ON my_table (col1, col2);
ALTER TABLE my_table REPLICA IDENTITY USING INDEX my_table_replica_idx;

Non fare affidamento sulla selezione implicita dell'indice (PostgreSQL 16+)

A partire da PostgreSQL 16, l'apply worker dell'abbonato può utilizzare gli indici btree o hash per le ricerche di riga quando l'identità di replica è impostata su, anche se tali indici non sono configurati esplicitamente come identità FULL di replica. Sebbene ciò impedisca in alcuni casi le scansioni sequenziali, fare affidamento su questo comportamento implicito è contrario allo schema per i seguenti motivi:

Configura invece in modo esplicito l'identità della replica per ogni tabella replicata:

REPLICA IDENTITY FULLRiservate solo alle tabelle in cui nessuna delle due opzioni è fattibile e tenete presente che le prestazioni dipendono da fattori al di fuori del vostro controllo diretto.

Monitora il ritardo di replica

Durante l'utilizzoREPLICA IDENTITY FULL, monitora attentamente il ritardo di replica per rilevare i rallentamenti di applicazione degli abbonati prima che diventino critici.

Per quanto riguarda l'editore, controlla il ritardo tra la posizione corrente del WAL e ciò che l'abbonato ha confermato:

SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(),
       (pg_current_wal_lsn() - confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

Un lag_bytes valore in costante crescita indica che l'abbonato è in ritardo. La pg_stat_replication_slots vista fornisce statistiche aggiuntive sull'utilizzo di ogni slot di replica.

Sul sottoscrittore, la pg_stat_subscription visualizzazione mostra lo stato di ciascun worker candidato, inclusa l'ultima posizione WAL ricevuta e segnalata:

SELECT subname, received_lsn, latest_end_lsn,
       last_msg_send_time, last_msg_receipt_time
FROM pg_stat_subscription;

Un ampio divario tra «received_lsne»latest_end_lsn, o un timestamp obsoletolast_msg_send_time, può indicare che l'application worker ha difficoltà a tenere il passo. La pg_stat_subscription_stats visualizzazione tiene traccia anche degli errori di applicazione e dei conflitti che potrebbero contribuire al ritardo.

Per , puoi anche monitorare la metrica, che tiene traccia del ritardo in byte dello OldestReplicationSlotLag CloudWatch slot di replica più indietro. Un valore crescente è un segnale di allarme precoce del ritardo nella replica.

Verifica di quali tabelle potrebbero utilizzare un indice non ottimale durante l'applicazione

Sul sottoscrittore, è possibile identificare le tabelle in cui l'application worker esegue letture eccessive dell'heap, il che può indicare che la tabella non dispone di un indice efficiente per le ricerche di riga durante l'applicazione. Eseguite la seguente query sul sottoscrittore:

SELECT relname, heap_blks_read, heap_blks_hit,
       idx_blks_read, idx_blks_hit,
       heap_blks_read + heap_blks_hit AS total_heap_access
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 10;

Una tabella con un heap_blks_read valore elevato relativo a idx_blks_read può indicare che l'application worker non utilizza un indice efficiente per individuare le righe UPDATE e le DELETE operazioni. Questa è una fonte comune di ritardo nella replica quando REPLICA IDENTITY FULL è in uso.

Valuta se è necessaria REPLICA IDENTITY FULL

Prima dell'impostazioneREPLICA IDENTITY FULL, valuta se ne hai veramente bisogno. I motivi più comuni per utilizzarlo includono:

Se l'unica ragione è la mancanza di una chiave primaria, aggiungerne una è quasi sempre la soluzione migliore. Se hai bisogno di immagini precedenti complete per CDC, valuta se l'utente del CDC può ricostruire intere righe mantenendone lo stato dall'esterno, in modo da evitare il sovraccarico del WAL e degli abbonati di. REPLICA IDENTITY FULL

Riepilogo delle raccomandazioni