Andrei's questions

Estou trabalhando com um banco de dados PostgreSQL onde preciso armazenar um valor numérico associado a uma chave específica.

Com o tempo, adicionarei continuamente esse valor com base na chave.

Quero garantir que a tabela não fique inchada com várias versões de linhas ou tuplas inativas, especialmente porque essa operação de atualização será frequente (como 100 req/s).

1. Quais são as melhores práticas no PostgreSQL para fazer isso?

2. Devo usar INSERT ON CONFLICT, um gatilho ou outra abordagem?

3. Como posso garantir que minha tabela permaneça eficiente e não sofra com inchaço excessivo devido a atualizações frequentes?

Eu tenho dois servidores com as seguintes especificações:

• 8 vCPU, 32768 MB de RAM, SSD de 640 GB

O banco de dados mestre Postgres 13.3 (db1) é instalado no primeiro servidor (Ubuntu 16.04.7) com a seguinte configuração:

shared_buffers = 16GB 
work_mem = 128MB
maintenance_work_mem = 8GB
effective_cache_size = 16GB

effective_io_concurrency = 400
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

wal_level = logical
synchronous_commit = on
max_wal_size = 4GB
min_wal_size = 32MB
wal_keep_size = 16384
wal_sender_timeout = 60s
checkpoint_completion_target = 0.7

synchronous_standby_names = 'FIRST 1 (db2_slave)'
max_standby_archive_delay = 1800s
max_standby_streaming_delay = 1800s

O standby é um banco de dados Postgres 13.4 (db2) instalado no segundo servidor (Ubuntu 20.04.3) com a seguinte configuração:

shared_buffers = 24GB
work_mem = 128MB
maintenance_work_mem = 16GB
effective_cache_size = 24GB

effective_io_concurrency = 400
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

wal_level = logical
synchronous_commit = on
max_wal_size = 4GB
min_wal_size = 32MB
checkpoint_completion_target = 0.7

primary_conninfo = 'host=... port=5432 user=repluser passfile=''...'' application_name=db2_slave'
primary_slot_name = 'db2'
hot_standby = on
max_standby_archive_delay = 1800s
max_standby_streaming_delay = 1800s

Se eu executar iotop -u postgresql no modo de espera, vejo dois processos:

2229172 postgres: 13/main: walreceiver streaming DDFD/8E9FE9E0
2229138 postgres: 13/main: startup recovering 000000010000DDFD0000008E

Depois de executar a solicitação de leitura que leva alguns segundos no modo de espera ( SELECT COUNT(*) FROM big_table;), o streaming do walreceiver continua funcionando, mas a réplica para de sincronizar:

2229138 postgres: 13/main: startup recovering 000000010000DE0400000017 waiting

Eu executei esta consulta no mestre:

SELECT client_addr                                                       as client,
       usename                                                           as user,
       application_name                                                  as name,
       state,
       sync_state                                                        as mode,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))   as pending,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, write_lsn))              as write,
       pg_size_pretty(pg_wal_lsn_diff(write_lsn, flush_lsn))             as flush,
       pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn))            as replay,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as total_lag
FROM pg_stat_replication;

E a saída foi:

   client    |   user   |   name    |   state   | mode | pending |  write  |  flush  | replay | total_lag 
-------------+----------+-----------+-----------+------+---------+---------+---------+--------+-----------
 ...         | repluser | db2_slave | streaming | sync | 0 bytes | 0 bytes | 0 bytes | 21 MB  | 21 MB
(1 row)

Se eu executar esta requisição várias vezes, o replay e o lag total aumentam o tempo todo durante a execução desta query ( SELECT COUNT(*) FROM big_table). Por isso, quero saber as respostas para as perguntas:

1. Por que o atraso de repetição continua aumentando durante a execução de uma consulta analítica para réplica?
2. Por que o processo de recuperação está no estado "aguardando" assim que eu inicio uma solicitação para o modo de espera?

Eu tenho um livro- razão de tabela que representa o livro-razão.

            date            | user_id | usd_value | eur_value 
----------------------------+---------+-----------+-----------
 2020-01-13 19:00:10.877+03 |       1 |        10 |         0
 2020-01-13 19:10:15.4+03   |       1 |        30 |         0
 2020-01-13 19:44:40.187+03 |       1 |         0 |        40
 2020-01-13 19:45:06.935+03 |       2 |        15 |         0
 2020-01-13 19:46:22.38+03  |       1 |        40 |         0
 2020-01-13 19:50:43.176+03 |       2 |         0 |        15
 2020-01-13 20:08:58.47+03  |       1 |        55 |         0

Esta tabela é muito grande, então quero reduzir a contagem de linhas.

Estou tentando determinar a melhor maneira de realizar a tarefa: como posso modificar a tabela de origem (excluindo e atualizando linhas) para obter as seguintes linhas :

            date            | user_id | usd_value | eur_value 
----------------------------+---------+-----------+-----------
 2020-01-13 19:00:10.877+03 |       1 |        80 |        40
 2020-01-13 19:45:06.935+03 |       2 |        15 |        15
 2020-01-13 20:08:58.47+03  |       1 |        55 |         0

Esta tabela é resultado da consulta:

SELECT min(date) as date, user_id, sum(usd_value) as usd_value, sum(eur_value) as eur_value
from ledger
GROUP BY date_trunc('hour', date), user_id
ORDER BY date;