EM0's questions

Estou tentando descobrir o que uma transação específica está bloqueando que impede outras consultas de executar. Eu consultei pg_locks assim:

select c.relname, l.*
from pg_locks l
join pg_database d on l.database = d.oid
left join pg_class c on l.relation = c.oid
where d.datname = 'MYDB' and pid = MYPID

Isso retornou um monte de resultados como este

relationera NULL para a maioria (não todos) dos resultados. Para estes, classide objideram não nulos (como no exemplo acima). Como resolvo isso para identificadores de objeto úteis? De acordo com a documentação do pg_locks, os bloqueios são "identificados por OID de classe e OID de objeto, da mesma forma que em pg_description". Quando consulto pg_description , no entanto, não consigo encontrar esses oids. Os valores da coluna objoid estão todos abaixo de 20000 - não há nada como 805681436.

Edição: consulta atualizada com base na resposta de Laurenz Albe (obrigado!)

select l.granted, l.locktype, l.mode, l.classid, l.objid, l.classid::regclass
    , c.relname, cns.conname -- relation
    , cns.conrelid::regclass as constraint_table, cns.contype -- constraint
    , trg.tgrelid::regclass as trigger_table, trg.tgname -- trigger
from pg_locks l
join pg_database d on l.database = d.oid
left join pg_class c on l.relation = c.oid
left join pg_constraint cns on l.objid = cns.oid and l.classid::regclass = 'pg_constraint'::regclass
left join pg_trigger trg on l.objid = trg.oid and l.classid::regclass = 'pg_trigger'::regclass
where d.datname = 'MYDB' and pid = MYPID

PostgreSQL 15.2. Eu tenho uma tabela particionada assim:

create table event
(
    dataset_id integer not null,
    event_id integer not null,
    second_offset integer not null,
    -- other columns
) partition by list (dataset_id);

As partições desta tabela têm um PK ativado event_ide um índice BRIN ativado second_offset:

create index event_DATASET_ID_ix_second_offset on event_DATASET_ID using brin (second_offset);

Alguns deles têm centenas de milhões de linhas. second_offseté a hora do evento e a linha é inserida logo, então segue de perto (mas não perfeitamente) a ordem física das linhas. As linhas nunca são atualizadas ou excluídas nessas tabelas, apenas inseridas e lidas.

Eu executo consultas como esta (simplificadas):

set enable_seqscan = off; 

select *
from event
where dataset_id = 365
    and second_offset <= timestamp_to_second_offset('2023-05-10') -- function that returns int
    and second_offset >= timestamp_to_second_offset('2023-05-09')

Eles usam o índice, mas ainda são lentos. EXPLICAR ANALISAR mostra:

Bitmap Heap Scan on event_365 event  (cost=453.13..2689210.37 rows=1322 width=54) (actual time=40651.983..40651.984 rows=0 loops=1)
  Recheck Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
  Rows Removed by Index Recheck: 238676609
  Filter: (dataset_id = 365)
  Heap Blocks: lossy=1762985
  ->  Bitmap Index Scan on event_365_ix_second_offset  (cost=0.00..452.80 rows=52893390 width=0) (actual time=73.633..73.634 rows=17629850 loops=1)
        Index Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
Planning Time: 0.673 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.672 ms, Inlining 4.802 ms, Optimization 19.712 ms, Emission 9.971 ms, Total 36.157 ms
Execution Time: 40653.748 ms

... até executar reindex index behavior.event_365_ix_second_offsetou, alternativamente, vacuum behavior.event_366(tentei isso em duas partições diferentes). Então a consulta se torna muito rápida! EXPLAIN ANALYZE então mostra:

Bitmap Heap Scan on event_365 event  (cost=596.29..5940945.52 rows=5967 width=54) (actual time=5.012..5.013 rows=0 loops=1)
  Recheck Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
  Filter: (dataset_id = 365)
  ->  Bitmap Index Scan on event_365_ix_second_offset  (cost=0.00..594.80 rows=238696656 width=0) (actual time=5.008..5.008 rows=0 loops=1)
        Index Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
Planning Time: 0.771 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.642 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 1.642 ms
Execution Time: 6.940 ms

Portanto, minha pergunta é como garantir que as consultas sejam sempre executadas rapidamente. Devo adicionar um cron job que reindexe todos eles todas as noites? Parece um pouco hacky e algo como PG deveria fazer automaticamente, não é?

pg_stat_user_tablesmostra que o autovacuum nunca é executado na maioria dessas partições - não sei por quê. Portanto, não sei se devo tentar forçar o autovacuum a funcionar de alguma forma.

Semelhante a Quais são os possíveis significados de NULL e zero para autovacuum_count, last_autovacuum e autoanalyze_count no PostgreSQL? exceto no meu caso, é PostgreSQL 15.1 e não há exclusões - apenas os dados são inseridos.

Eu tenho um banco de dados com algumas tabelas particionadas e algumas não particionadas também. Algumas das partições têm centenas de milhões de linhas, mas n_live_tup é 0 para elas e vacuum_count, autovacuum_count, analyze_count, autoanalyze_count são todos 0.

Apenas as 2 últimas tabelas criadas (ambas as partições) têm autoanalyze_count = 1 e 4, respectivamente, e têm n_live_tup > 0. Existem, no entanto, algumas outras tabelas com n_live_tup > 0 (apesar de nenhuma análise ter sido executada nelas).

        select relname, n_live_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count
        from pg_catalog.pg_stat_user_tables
        where schemaname = 'myschema'

Observe que n_live_tup para event_59 está totalmente errado aqui e a tabela realmente tem> 1,1 bilhão de linhas (essa é a maior).

Tentei configurar log_autovacuum_min_duration = 0conforme sugerido na pergunta vinculada, mas parece não ter registrado nada até agora.

Por que minhas tabelas não estão sendo analisadas?

Editar 1: agora até as estatísticas dessas tabelas foram redefinidas:

O servidor não travou desde então - pg_postmaster_start_time()retorna 2023-02-13 17:02:32.365095+00(foi quando eu o reiniciei). Como você pode ver na primeira captura de tela, a análise automática foi executada logo depois disso. Os logs mostram que ele foi executado novamente às 20:10:18, mas, apesar disso, todas as contagens de análise/vácuo são 0 e n_live_tup diminuiu.

Corri manualmente analyze event_241e n_live_tup tornou-se preciso. Reiniciei o PG depois e isso não redefiniu a contagem, então não sei por que foi redefinido durante a noite.

Editar 2: Acontece que o servidor está travando todas as noites quando os backups do pg_dump são executados, mas aparentemente sem que o processo do postmaster seja reiniciado, porque pg_postmaster_start_time()continua retornando o mesmo valor. Posso reproduzir facilmente a falha pg_dumping de um banco de dados específico (não aquele sobre o qual esta postagem é).

2023-02-17 09:04:16.607 UTC [27083] postgres@problematic_database LOG:  connection authorized: user=postgres database=problematic_database application_name=pg_dump
2023-02-17 09:04:16.609 UTC [27083] postgres@problematic_database PANIC:  could not open critical system index 2662
2023-02-17 09:04:16.610 UTC [11922] LOG:  server process (PID 27083) was terminated by signal 6: Aborted
2023-02-17 09:04:16.610 UTC [11922] LOG:  terminating any other active server processes
2023-02-17 09:04:16.645 UTC [11922] LOG:  all server processes terminated; reinitializing
2023-02-17 09:04:17.176 UTC [27945] LOG:  database system was interrupted; last known up at 2023-02-17 09:03:24 UTC

Corrigir essa falha é um tópico separado, mas, para esta pergunta, estou me perguntando:

1. Qual é a melhor maneira de monitorar se isso está acontecendo? Poderíamos verificar os logs em busca de palavras-chave, mas parece melhor verificar diretamente se as estatísticas estão sendo redefinidas, pois isso detectará isso e outros erros. É apenas uma questão de executar manualmente a análise em pelo menos uma tabela e, em seguida, pesquisar regularmente analyze_count = 0ou existe uma maneira melhor?
2. Se detectarmos que as estatísticas foram redefinidas (devido a uma falha), parece uma boa ideia preencher analyzemanualmente as estatísticas novamente, certo? Mas então por que o PG não faz isso automaticamente após se recuperar de uma falha?
3. Ou talvez seja uma boa ideia apenas executar o ANALYZE regularmente, digamos, semanalmente ou mesmo todas as noites? (Atualmente, leva ~ 200 segundos neste banco de dados - muito menos tempo do que o backup!)

Eu tenho uma tabela particionada simples no PostgreSQL 14, assim:

create table entity
(
    dataset_id integer not null references dataset (id),
    ...
) partition by list (dataset_id);

create table entity_1
(
    like entity including constraints including defaults,
    constraint entity_1_pkey primary key (entity_id),
);

alter table entity attach partition entity_1 for values in (1);

A razão para criar uma partição uma partição "desanexada" primeiro e depois anexá-la em uma instrução separada é evitar um bloqueio exclusivo na tabela pai ( entity), o que bloquearia a criação de uma partição enquanto um cliente estivesse lendo de qualquer outra partição. Esta foi a solução sugerida por Laurenz Albe em https://stackoverflow.com/a/67016755/1536933 e funciona muito bem para criar partições.

Infelizmente, tenho o mesmo problema ao descartar partições: enquanto um cliente estiver lendo dados de qualquer partição, não posso descartar nenhuma outra partição: não apenas está drop table entity_1bloqueada, mas até alter table entity detach partition entity_1 concurrentlyestá bloqueada! Não consigo ver nenhum bloqueio relevante pg_lockspara a instrução "detach simultaneamente" (nada com granted=false), mas o painel pgAdmin mostra Wait event: Lock: virtualxideBlocking PIDs: (pid of the reading process)

O que posso fazer para descartar partições enquanto os dados estão sendo lidos de outras partições? Certamente isso deve ser possível?

Eu tenho uma tabela com apenas colunas integer e smallint, somando até 20 bytes. pg_column_sizerelatórios 44. Isso é esperado devido à sobrecarga de 24 bytes por linha, conforme explicado em Diferença entre pg_column_size(table.*) e pg_column_size(table.col1) + pg_column_size (table.col2)

No entanto, se eu dividir pg_table_sizepelo número de linhas, obtenho ~ 53 bytes por linha, mesmo com centenas de milhões de linhas. De onde vêm esses 9 bytes extras?

Eu posso reproduzir isso mesmo com uma coluna inteira:

drop table if exists anint;

create table anint
as
select generate_series
from generate_series(1, 10000000);

select pg_column_size(e.*) -- 28
from anint e
limit 1;

select n_live_tup as row_count_estimate, -- 10000000
    pg_size_pretty(pg_table_size(relid)) as table_size, -- 346 MB
    case when n_live_tup = 0 then null else pg_table_size(relid) / cast(n_live_tup as float) end as table_bytes_per_row -- 36.2561536
from pg_catalog.pg_statio_user_tables io
join pg_catalog.pg_stat_user_tables s using (relid)
where io.schemaname = 'public' and io.relname = 'anint'

Isso retorna ~36 bytes/linha, não 28. (Testado no PostgreSQL 14.1.)