Purushottam Nawale's questions

Tenho um banco de dados PostgreSQL 16 configurado com a seguinte configuração:

-- 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

Estou executando uma CREATE TABLEconsulta com múltiplas junções. Às vezes, a consulta é executada em paralelo, mas outras vezes não. Quero garantir que a consulta seja executada em paralelo ou, pelo menos, aumentar as chances de ela ser executada em paralelo para aquela sessão de transação específica. Também é bom se os trabalhadores paralelos atribuídos forem menos do que o normal, porque sem trabalhadores paralelos a consulta é muito lenta.

A configuração abaixo funcionou melhor para mim por algum tempo, mas causou um erro semelhante a este problema .

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';

Estou anexando um plano de consulta selecionado para referência: plano de consulta .

Quero maximizar a utilização da CPU (atualmente menos de 10%) e a utilização da RAM (atualmente menos de 30%).

Estou ciente de que force_parallel_modefoi descontinuado. Como posso obter execução paralela consistente para minhas consultas no PostgreSQL?

Estou trabalhando com um banco de dados PostgreSQL e preciso de alguns conselhos sobre quando executar VACUUM ANALYZEem minhas tabelas.

1. Estou anexando uma tabela muito grande tmp_details(50 milhões de registros) como uma partição para uma tabela existente details(500 milhões de registros). A detailstabela é usada ativamente para relatórios, mas não estou realizando nenhuma outra operação de gravação nela. Dada a documentação do PostgreSQL ( https://www.postgresql.org/docs/current/populate.html ), sugere-se executar VACUUM ANALYZEapós carregar grandes quantidades de dados:

   • É necessário correr VACUUM ANALYZEna detailsmesa depois de acoplar tmp_detailsnela?
   • Se sim, devo aspirar a detailsmesa inteira ou apenas a divisória recém-fixada?

2. Também estou criando uma nova tabela notreconciled(10 milhões de registros) de tmp_details. A notreconciledtabela será usada tanto para relatórios quanto como uma partição mais tarde. Devo executar VACUUM ANALYZEna notreconciledtabela após criá-la?

Quaisquer insights ou melhores práticas seriam muito apreciados!

Tenho uma tabela source_aepsfinswitch_tmp_detailscontendo um conjunto de dados muito grande. Meu objetivo é particionar essa tabela em duas partições com base na reconciliation_date_timecoluna:

1. Se reconciliation_date_timefor NULL, a linha deve ir para uma partição.
2. Se reconciliation_date_timefor NOT NULL, a linha deve ir para outra partição.

Devido ao tamanho dos dados, quero evitar usar INSERT INTOinstruções para mover os dados manualmente. Em vez disso, tentei a seguinte abordagem:

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;

Entretanto, quando executo isso, recebo o seguinte erro:

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

Como posso atingir meu objetivo sem encontrar esse erro? Tentei LIST e RANGE, mas nenhum funcionou. Existe uma maneira de particionar a tabela conforme descrito sem exigir que a coluna reconciliation_date_timeseja NOT NULL? Existe alguma maneira de dividir a tabela em partições sem usar INSERT INTO?

Graças à sugestão de Laurenz Albe , consegui anexar a partição. No entanto, como posso criar duas tabelas com base na tabela pai? Estou recebendo o seguinte erro ao executar a consulta abaixo:

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

Acho que esse problema é causado pela chave primária. Criar uma nova partição das mesmas transações que em outras partições no PostgreSQL pode estar causando o erro mencionado acima. Existe uma maneira de lidar com isso?

Estou inserindo dados da tmp_detailstabela na detailstabela usando uma consulta INSERT em blocos. Também estou modificando os dados enquanto os insiro. A consulta está demorando muito para ser executada. Meu IOPS de leitura está atingindo 2500s, e meu IOPS de gravação está perto de 100. Minha utilização de E/S é 100%, e minha utilização de CPU é menor que 10%. Minha utilização de RAM é menor que 40%. Estou usando ctidpara inserções de blocos de 2.000.000 de linhas. O que posso fazer para melhorar o desempenho das minhas consultas e reduzir a utilização de E/S? Quero aumentar a utilização da CPU para mais de 80%.

Especificações do servidor:

• Versão do PostgreSQL: 15.6
• RAM: 32 GB
• Núcleos: 16
• Espaço em disco: SSD 500 GB
• 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:

Há índices nas colunas uniquekey, key_priority_radcs, key_priority_rtdps, is_processed, e key_priority_ratrs. Tive que usar a cláusula DISTINCT ON porque estava obtendo linhas duplicadas devido ao JOIN. Tentei inserir em blocos de 1.000.000 de linhas usando ctid, mas ainda está demorando muito para executar, provavelmente porque ele tem que escanear toda a tabela C e D para cada iteração. Então, inseri os dados como um todo, 60 milhões de linhas e então executei um commit no final. Meu objetivo é executar essas consultas de inserção semelhantes para a tabela C e D em paralelo a partir do servidor de aplicativo de backend, mas será inútil se minha Utilização de E/S for 100%.

Inserir consulta:

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;

Explique os resultados

"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"

Questões:

1. Como posso melhorar o desempenho da consulta e reduzir a utilização de E/S?
2. Existe uma maneira de executar essas consultas de inserção semelhantes em paralelo a partir do aplicativo sem atingir o limite de utilização de E/S?
3. Inserir dados em blocos me beneficiará ou é melhor inserir todos os dados de uma vez? Porque, pelo que observei, inserir dados em blocos está levando mais tempo do que inserir todos os dados de uma vez.

EDITAR:

Estou anexando o plano de consulta para a instrução select. Aparentemente, a consulta Insert está levando mais de uma hora, enquanto a instrução Select está levando apenas 265 segundos. Acho que meu problema está no commit único no final, possivelmente devido à geração excessiva de log. Funcionaria se eu mantivesse o auto-commit ativado? Existe uma maneira de inserir em pedaços sem fazer um loop pela tabela inteira?

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!