Otimizando o desempenho para grandes atualizações de tabelas no PostgreSQL

Eu costumava atualizar uma tabela muito grande usando UPDATEconsultas, mas elas estavam demorando muito para executar. Para melhorar o desempenho, mudei para usar a CREATE TABLEabordagem e adicionar índices para atualizar a tabela. Essa abordagem aumentou significativamente minha velocidade de execução de consultas, mas quero entender sua escalabilidade e limitações.

Especificações do servidor:

• Versão do PostgreSQL: 15.6
• RAM: 32 GB
• Núcleos: 16
• Espaço em disco: SSD 250 GB (50% grátis)
• SO: Linux Ubuntu 22.04

Configuração do PostgreSQL:

max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

Detalhes da tabela:

Consulta:

BEGIN;
ALTER TABLE source_switchdata_tmp_details RENAME TO source_switchdata_tmp_details_og;
CREATE TABLE source_switchdata_tmp_details AS
SELECT DISTINCT ON (A.uniqueid) A.transactiondate,
        A.cycles,
        A.transactionamount,
        A.bcid,
        A.bcname,
        A.username,
        A.terminalid,
        A.uidauthcode,
        A.itc,
        A.transactiondetails,
        A.deststan,
        A.sourcestan,
        A.hostresponsecode,
        A.institutionid,
        A.acquirer,
        A.bcrefid,
        A.cardno,
        A.rrn,
        A.transactiontype,
        A.filename,
        A.cardnotrim,
        A.uniqueid,
        A.transactiondatetime,
        A.transactionstatus,
        A.overall_probable_status,
        A.recon_created_date,
        A.priority_no,
        A.recon_key_priority_1_1_to_2,
        A.recon_key_priority_1_1_to_3,
        A.recon_key_priority_2_1_to_2,
        A.recon_key_priority_2_1_to_3,
        A.process_status,
        A.reconciliation_date_time,
        CURRENT_TIMESTAMP AS recon_updated_date,
        CASE
                WHEN C.recon_key_priority_1_2_to_1 IS NOT NULL THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS recon_status_1_to_2,
        CASE
                WHEN D.recon_key_priority_1_3_to_1 IS NOT NULL THEN 'Reconciled'
                WHEN D.recon_key_priority_2_3_to_1 IS NOT NULL THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS recon_status_1_to_3,
        CASE
                WHEN (C.recon_key_priority_1_2_to_1 IS NOT NULL AND D.recon_key_priority_1_3_to_1 IS NOT NULL) THEN 'Reconciled'
                WHEN (D.recon_key_priority_2_3_to_1 IS NOT NULL) THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS overall_recon_status
FROM source_switchdata_tmp_details_og A
        LEFT JOIN source_aepscbsdata_tmp_details C ON (A.recon_key_priority_1_1_to_2 = C.recon_key_priority_1_2_to_1)
        LEFT JOIN source_npcidata_tmp_details D 
        ON (A.recon_key_priority_1_1_to_3 = D.recon_key_priority_1_3_to_1) 
        OR (A.recon_key_priority_2_1_to_3 = D.recon_key_priority_2_3_to_1);
DROP TABLE source_switchdata_tmp_details_og;
COMMIT;

Restrições e índices exclusivos:

A.uniqueid = Primary key and Index
A.recon_key_priority_1_1_to_3 = Index
A.recon_key_priority_1_1_to_2 = Index
D.recon_key_priority_1_3_to_1 = Index
A.recon_key_priority_2_1_to_3 = Index
D.recon_key_priority_2_3_to_1 = Index

Questões:

1. Atualmente, estou executando a consulta acima para 180 milhões de linhas (60M + 60M + 60M). No futuro, posso precisar executar essa consulta para 1 bilhão de linhas. Essa abordagem será escalável para 1 bilhão de linhas? Podemos aumentar as especificações do servidor se necessário, mas essa abordagem será viável? Essencialmente, se eu fosse recriar a tabela para 300 milhões de linhas ou mesmo 1 bilhão de linhas, isso seria prático?
2. Minha equipe sugere atualizar os dados em blocos de 1 milhão de linhas. Essa abordagem é melhor do que a atual?
3. A consulta atualmente leva cerca de 20 minutos, o que é aceitável. Se o tamanho dos dados aumentar, de quais gargalos, como gargalos de E/S, devo estar ciente para garantir que o tempo da consulta seja dimensionado proporcionalmente sem ficar preso?
4. Quais são as limitações da abordagem atual? E o que posso fazer para evitar tais limitações?

Quaisquer insights ou otimizações seriam muito apreciados. Obrigado!