Purushottam Nawale's questions

我有一个 PostgreSQL 16 数据库设置，其配置如下：

-- DB Version: 16
-- OS Type: linux
-- DB Type: oltp
-- Total Memory (RAM): 64 GB
-- CPUs num: 32
-- Connections num: 200
-- Data Storage: ssd

show effective_cache_size; --48 GB
show effective_io_concurrency; --200
show huge_pages; --try
show maintenance_work_mem; --4GB
show max_connections; --200
show max_parallel_maintenance_workers; --4
show max_parallel_workers; --32
show max_parallel_workers_per_gather; --4
show max_wal_size; --8GB
show max_worker_processes; --32
show min_wal_size; --2GB
show parallel_setup_cost; --0.1
show random_page_cost; --1.1
show shared_buffers; --16GB
show wal_buffers; --16MB
show work_mem; --256MB

我正在运行一个CREATE TABLE包含多个连接的查询。有时查询会并行运行，但有时不会。我想确保查询并行运行，或者至少增加它在该特定事务会话中并行运行的机会。如果分配的并行工作器比平时少，那也没关系，因为如果没有并行工作器，查询会非常慢。

下面的配置在一段时间内对我来说效果最好，但它导致了类似于此问题的错误。

ALTER SYSTEM SET maintenance_work_mem = '8GB';
ALTER SYSTEM SET work_mem = '2GB';
ALTER SYSTEM SET effective_io_concurrency = '400';
ALTER SYSTEM SET max_parallel_workers_per_gather = '16';
ALTER SYSTEM SET wal_buffers = '1GB';

我附加了一个选择查询计划以供参考：查询计划。

我想最大化 CPU 利用率（目前低于 10%）和 RAM 利用率（目前低于 30%）。

我知道这force_parallel_mode已被弃用。如何在 PostgreSQL 中实现查询的一致并行执行？

我正在使用 PostgreSQL 数据库，需要一些关于何时VACUUM ANALYZE在我的表上运行的建议。

1. 我将一个非常大的表tmp_details（5000 万条记录）作为分区附加到现有表details（5 亿条记录）中。该details表经常用于报告，但我没有对其执行任何其他写入操作。鉴于 PostgreSQL 文档（https://www.postgresql.org/docs/current/populate.htmlVACUUM ANALYZE ）建议在加载大量数据后运行：

   • 贴上去之后还需要在桌子VACUUM ANALYZE上跑吗？detailstmp_details
   • 如果是的话，我应该清理整个details桌子还是只清理新连接的分区？

2. 我还notreconciled从中创建一个新表（1000 万条记录）tmp_details。该notreconciled表稍后将用于报告和分区。创建表后我应该VACUUM ANALYZE在该表上运行吗？notreconciled

任何见解或最佳实践都将不胜感激！

我有一张source_aepsfinswitch_tmp_details包含非常大数据集的表。我的目标是根据列将此表划分为两个分区reconciliation_date_time：

1. 如果reconciliation_date_time是NULL，则该行应该进入一个分区。
2. 如果reconciliation_date_time是NOT NULL，则该行应该转到另一个分区。

由于数据量很大，我想避免使用INSERT INTO语句手动移动数据。相反，我尝试了以下方法：

BEGIN;
CREATE TABLE IF NOT EXISTS public.source_aepsfinswitch_tmp_details_new (
    LIKE public.source_aepsfinswitch_tmp_details EXCLUDING CONSTRAINTS,
    PRIMARY KEY (uniqueid, reconciliation_date_time)
) PARTITION BY RANGE (reconciliation_date_time);

ALTER TABLE public.source_aepsfinswitch_tmp_details RENAME TO source_aepsfinswitch_tmp_details_old;
ALTER TABLE public.source_aepsfinswitch_tmp_details_new RENAME TO source_aepsfinswitch_tmp_details;
ALTER TABLE public.source_aepsfinswitch_tmp_details ATTACH PARTITION public.source_aepsfinswitch_tmp_details_old DEFAULT;
COMMIT;

但是，当我运行它时，出现以下错误：

SQL Error [42804]: ERROR: column "reconciliation_date_time" in child table must be marked NOT NULL

我怎样才能在不遇到此错误的情况下实现我的目标？我尝试了 LIST 和 RANGE，但都没有用。有没有办法按照描述对表进行分区而不需要列？有没有办法在不reconciliation_date_time使用NOT NULL的情况下将表拆分为分区INSERT INTO？

感谢Laurenz Albe 的建议，我能够附加分区。但是，如何基于父表创建两个表？执行以下查询时出现以下错误：

CREATE TABLE IF NOT EXISTS public.source_aepsfinswitch_tmp_details_new (LIKE public.source_aepsfinswitch_tmp_details EXCLUDING CONSTRAINTS) PARTITION BY LIST (reconciliation_date_time);    
ALTER TABLE public.source_aepsfinswitch_tmp_details RENAME TO source_aepsfinswitch_tmp_details_old;
ALTER TABLE public.source_aepsfinswitch_tmp_details_new RENAME TO source_aepsfinswitch_tmp_details;
ALTER TABLE public.source_aepsfinswitch_tmp_details ATTACH PARTITION public.source_aepsfinswitch_tmp_details_old DEFAULT;
CREATE TABLE IF NOT EXISTS public.source_aepsfinswitch_tmp_details_null PARTITION OF public.source_aepsfinswitch_tmp_details FOR VALUES IN (NULL);

SQL Error [23514]: ERROR: updated partition constraint for default partition "source_aepsfinswitch_tmp_details_old" would be violated by some row

我认为这个问题是由主键引起的。在 PostgreSQL 中创建与其它分区相同的事务的新分区可能会导致上述错误。有办法解决这个问题吗？

我正在使用 INSERT 查询分块将表中的数据插入tmp_details到details表中。插入数据时，我还在修改数据。查询执行需要花费大量时间。我的读取 IOPS 达到 2500，写入 IOPS 接近 100。我的 I/O 利用率为 100%，而 CPU 利用率不到 10%。我的 RAM 利用率不到 40%。我正在使用ctid2,000,000 行的块插入。我可以做些什么来提高查询性能并降低 I/O 利用率？我想将 CPU 利用率提高到 80% 以上。

服务器规格：

• PostgreSQL 版本：15.6
• 内存：32 GB
• 核心数： 16
• 磁盘空间：SSD 500 GB
• 操作系统：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

表格详细信息：

uniquekey列、、、和上有索引。我必须使用 DISTINCT ON 子句，因为由于 JOIN 我得到了重复的行。我尝试使用 插入 1,000,000 行，key_priority_radcs但执行仍然需要很长时间，很可能是因为它必须在每次迭代中扫描整个表 C 和 D。因此，我将数据作为整体插入了 6000 万行，然后在最后执行了提交。我的目标是从后端应用服务器并行运行这些类似的表 C 和 D 插入查询，但如果我的 I/O 利用率为 100%，这将毫无意义。key_priority_rtdpsis_processedkey_priority_ratrsctid

插入查询：

EXPLAIN
INSERT
    INTO
    cbsupi.source_cbsupi_details (codglacct,
    refusrno,
    key_priority_radcs,
    recon_created_date,
    dattxnposting,
    status,
    uniquekey,
    coddrcr,
    cbsacqiss,
    codacctno,
    amttxnlcy,
    acnotrim,
    priority_no,
    rrn,
    recon_updated_date,
    recon_date_1_to_2,
    recon_date_1_to_3,
    reconciliation_date_time ) (
    SELECT
        DISTINCT ON
        (A.uniquekey) A.codglacct,
        A.refusrno,
        A.key_priority_radcs,
        A.recon_created_date,
        A.dattxnposting,
        A.status,
        A.uniquekey,
        A.coddrcr,
        A.cbsacqiss,
        A.codacctno,
        A.amttxnlcy,
        A.acnotrim,
        A.priority_no,
        A.rrn,
        '2025-01-07 19:50:41' AS recon_updated_date,
        CASE
            WHEN C.key_priority_rtdps IS NOT NULL THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS recon_date_1_to_2,
        CASE
            WHEN D.key_priority_ratrs IS NOT NULL THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS recon_date_1_to_3,
        CASE
            WHEN (C.key_priority_rtdps IS NOT NULL
                AND D.key_priority_ratrs IS NOT NULL) THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS reconciliation_date_time
    FROM
        cbsupi.source_cbsupi_tmp_details A
    LEFT JOIN switchupi.source_switchupi_tmp_details C ON
        (A.key_priority_radcs = C.key_priority_rtdps)
    LEFT JOIN npciupi.source_npciupi_tmp_details D ON
        (A.key_priority_radcs = D.key_priority_ratrs)
    WHERE
        A.is_processed IS NULL ) ON
    CONFLICT (uniquekey) DO
UPDATE
SET
    recon_updated_date = EXCLUDED.recon_updated_date,
    recon_date_1_to_3 = EXCLUDED.recon_date_1_to_3,
    key_priority_radcs = EXCLUDED.key_priority_radcs,
    status = EXCLUDED.status,
    reconciliation_date_time = EXCLUDED.reconciliation_date_time,
    codacctno = EXCLUDED.codacctno,
    amttxnlcy = EXCLUDED.amttxnlcy,
    recon_date_1_to_2 = EXCLUDED.recon_date_1_to_2,
    rrn = EXCLUDED.rrn,
    codglacct = EXCLUDED.codglacct,
    refusrno = EXCLUDED.refusrno,
    dattxnposting = EXCLUDED.dattxnposting,
    coddrcr = EXCLUDED.coddrcr,
    cbsacqiss = EXCLUDED.cbsacqiss,
    acnotrim = EXCLUDED.acnotrim,
    priority_no = EXCLUDED.priority_no;

解释结果

"QUERY PLAN"
Insert on source_cbsupi_details  (cost=72270111.44..73213761.44 rows=0 width=0)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: source_cbsupi_details_pkey
"  ->  Subquery Scan on ""*SELECT*""  (cost=72270111.44..73213761.44 rows=62910000 width=811)"
        ->  Unique  (cost=72270111.44..72584661.44 rows=62910000 width=823)
              ->  Sort  (cost=72270111.44..72427386.44 rows=62910000 width=823)
                    Sort Key: a.uniquekey
                    ->  Hash Left Join  (cost=10739152.00..50771187.50 rows=62910000 width=823)
                          Hash Cond: (a.key_priority_radcs = d.key_priority_ratrs)
                          ->  Hash Left Join  (cost=5337191.00..25537830.00 rows=62910000 width=800)
                                Hash Cond: (a.key_priority_radcs = c.key_priority_rtdps)
                                ->  Seq Scan on source_cbsupi_tmp_details a  (cost=0.00..2092124.00 rows=62910000 width=767)
                                      Filter: (is_processed IS NULL)
                                ->  Hash  (cost=4118441.00..4118441.00 rows=60000000 width=33)
                                      ->  Seq Scan on source_switchupi_tmp_details c  (cost=0.00..4118441.00 rows=60000000 width=33)
                          ->  Hash  (cost=4124101.00..4124101.00 rows=62910000 width=33)
                                ->  Seq Scan on source_npciupi_tmp_details d  (cost=0.00..4124101.00 rows=62910000 width=33)
JIT:
  Functions: 24
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"

问题：

1. 如何提高查询的性能并降低I/O利用率？
2. 有没有办法从应用程序并行运行这些类似的插入查询而不会达到 I/O 利用率限制？
3. 分块插入数据对我有好处吗，还是一次性插入全部数据更好？因为，从我的观察来看，分块插入数据比一次性插入全部数据花费的时间更多。

编辑：

我附上了select 语句的查询计划。显然，Insert 查询花费了一个多小时，而 Select 语句仅花费了 265 秒。我认为我的问题在于最后的单个提交，可能是由于过多的日志生成造成的。如果我保持自动提交，它会起作用吗？有没有办法分块插入而不循环遍历整个表？

我曾经使用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. 当前方法的局限性是什么？我能做些什么来避免这些局限性？

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