优化 PostgreSQL 中大型表更新的性能

我曾经使用UPDATE查询来更新一个非常大的表，但执行时间太长。为了提高性能，我改用这种CREATE TABLE方法并添加索引来更新表。这种方法大大提高了我的查询执行速度，但我想了解它的可扩展性和局限性。

服务器规格：

• PostgreSQL 版本：15.6
• 内存：32 GB
• 核心数： 16
• 磁盘空间：SSD 250 GB（50% 可用）
• 操作系统：Linux Ubuntu 22.04

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

表格详细信息：

询问：

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;

唯一约束和索引：

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

问题：

1. 目前，我正在对 1.8 亿行（60M + 60M + 60M）运行上述查询。将来，我可能需要对 10 亿行运行此查询。这种方法是否可以扩展到 10 亿行？我们可以根据需要增加服务器规格，但这种方法是否可行？本质上，如果我要为 3 亿行甚至 10 亿行重新创建表，这是否可行？
2. 我的团队建议以 100 万行为单位更新数据。这种方法比当前方法更好吗？
3. 查询目前大约需要 20 分钟，这是可以接受的。如果数据量增加，我应该注意哪些瓶颈（例如 I/O 瓶颈）以确保查询时间按比例扩展而不会卡住？
4. 当前方法的局限性是什么？我能做些什么来避免这些局限性？

任何见解或优化都将不胜感激。谢谢！