Andrey B. Panfilov's questions

Existem algumas fontes na internet que insistem que idle in transactionas conexões podem impedir que o vácuo limpe tuplas mortas. Abaixo estão alguns exemplos:

Guia do usuário para Aurora :

Uma transação no estado inativo em transação pode conter bloqueios que bloqueiam outras consultas. Ele também pode impedir que VACUUM (incluindo autovacuum) limpe linhas mortas, levando ao inchaço do índice ou da tabela ou ao wraparound do ID da transação .

Blog da Cybertec

Na verdade, uma transação longa não é um problema – o problema começa se for necessária uma transação longa e muitas pequenas alterações. Lembre-se: a transação longa pode fazer com que o VACUUM não limpe suas linhas mortas .

Na verdade, existem muitos deles, mas do meu ponto de vista isso parece absolutamente ridículo: na maioria dos casos, o nível de isolamento da transação é lido como confirmado, o que, por sua vez, significa que não há necessidade de manter tuplas mortas para tais transações, além disso, encontrei alternativas opinião sobre esse assunto :

Na verdade, não se trata de transações de longa duração, mas de instantâneos de longa duração . Certamente uma instrução select ou insert de longa duração fará isso. Para níveis de isolamento superiores aos de leitura confirmada, toda a transação reterá o instantâneo até que seja inativo ; portanto, se alguém abrir uma transação de leitura repetível e depois sair de férias sem confirmá-la, isso seria um problema. As transações preparadas suspensas também funcionarão (se você não sabe o que é uma transação preparada, provavelmente não as está usando).

ou o comentário de Pavel Luzanov na postagem do blog Cybertec :

Acredito que esse exemplo de transação longa seja verdadeiro apenas para o nível de isolamento de leitura repetível (ou serializável). Mas por padrão BEGIN usou Read Commited. Portanto, após o término de SELECT na primeira sessão, VACUUM removerá as linhas mortas em uma tabela após os comandos UPDATE, DELETE subsequentes na sessão 2.

o que foi confirmado por @ Bill Karwin em sua resposta (obrigado!)

A questão é: existem cenários “válidos” “não ficcionais” em que idle in transactionas conexões devem ser consideradas prejudiciais? (Não estou perguntando sobre transações com nível de isolamento superior a leitura confirmada, vazamentos de transação ou conexão, longos bloqueios de transação, etc.).

O aplicativo está armazenando dados em cache de forma agressiva na memória e, para oferecer suporte à consistência (evitando a persistência de dados obsoletos), está fazendo algo como:

-- typical table structure:
create table t1 (
    id            varchar(16) primary key,
    version_stamp int4,
    ....
)

-- typical update statement
update t1 set
   version_stamp = version_stamp + 1,
   col1 = ?,
   col2 = ?,
...
where id = ? and version_stamp = ?

Se a atualização mencionada acima informar que nenhuma linha foi atualizada, isso significa que o aplicativo tentou persistir dados obsoletos e uma exceção foi lançada, e a ideia principal é prevenir ou, pelo menos, minimizar tais casos. Para isso, a aplicação realiza as seguintes consultas (por requisição, transação ou chamada de método):

select version_stamp from t1
  where id = ?

Se nenhuma linha foi retornada, isso significa que a linha foi excluída, se retornada version_stampfor diferente da version_stampmantida na memória, isso significa que estamos lidando com dados obsoletos.

A questão é: vale a pena definir chaves primárias como:

create unique index on t1(id) include(version_stamp)

Ou não nesse caso. O RPS típico para essas consultas é de cerca de 10k por segundo.

O PostgreSQL parece falhar ao reconhecer que a classificação é realizada por constante e cada constante define inequivocamente a fonte de linha correspondente e, como resultado, observo que ela calcula todas as fontes de linha antes de "classificar":

create table t1 as
select generate_series(1, 10) as c1;


explain analyse
select c1
from (select 1, c1 from t1 where c1 = 1
      union all
      select 2, c1 from t1 where c1 = 2
      union all
      select 3, c1 from t1 where c1 = 3
      union all
      select 4, c1 from t1 where c1 = 4
      order by 1
      limit 1) q

Subquery Scan on q  (cost=169.84..169.85 rows=1 width=4) (actual time=0.501..0.685 rows=1 loops=1)
  ->  Limit  (cost=169.84..169.84 rows=1 width=8) (actual time=0.479..0.622 rows=1 loops=1)
        ->  Sort  (cost=169.84..169.97 rows=52 width=8) (actual time=0.458..0.570 rows=1 loops=1)
              Sort Key: (1)
              Sort Method: top-N heapsort  Memory: 25kB
              ->  Append  (cost=0.00..168.28 rows=52 width=8) (actual time=0.044..0.476 rows=4 loops=1)
                    ->  Seq Scan on t1  (cost=0.00..41.88 rows=13 width=8) (actual time=0.023..0.046 rows=1 loops=1)
                          Filter: (c1 = 1)
                          Rows Removed by Filter: 9
                    ->  Seq Scan on t1 t1_1  (cost=0.00..41.88 rows=13 width=8) (actual time=0.016..0.038 rows=1 loops=1)
                          Filter: (c1 = 2)
                          Rows Removed by Filter: 9
                    ->  Seq Scan on t1 t1_2  (cost=0.00..41.88 rows=13 width=8) (actual time=0.016..0.038 rows=1 loops=1)
                          Filter: (c1 = 3)
                          Rows Removed by Filter: 9
                    ->  Seq Scan on t1 t1_3  (cost=0.00..41.88 rows=13 width=8) (actual time=0.017..0.039 rows=1 loops=1)
                          Filter: (c1 = 4)
                          Rows Removed by Filter: 9

No entanto, após a remoção, order by 1estou obtendo exatamente o que desejo - never executedavisos no plano de consulta:

explain analyse
select c1
from (select 1, c1 from t1 where c1 = 1
      union all
      select 2, c1 from t1 where c1 = 2
      union all
      select 3, c1 from t1 where c1 = 3
      union all
      select 4, c1 from t1 where c1 = 4
      --order by 1
      limit 1) q

Subquery Scan on q  (cost=0.00..3.25 rows=1 width=4) (actual time=0.092..0.195 rows=1 loops=1)
  ->  Limit  (cost=0.00..3.24 rows=1 width=8) (actual time=0.069..0.131 rows=1 loops=1)
        ->  Append  (cost=0.00..168.28 rows=52 width=8) (actual time=0.047..0.078 rows=1 loops=1)
              ->  Seq Scan on t1  (cost=0.00..41.88 rows=13 width=8) (actual time=0.026..0.037 rows=1 loops=1)
                    Filter: (c1 = 1)
              ->  Seq Scan on t1 t1_1  (cost=0.00..41.88 rows=13 width=8) (never executed)
                    Filter: (c1 = 2)
              ->  Seq Scan on t1 t1_2  (cost=0.00..41.88 rows=13 width=8) (never executed)
                    Filter: (c1 = 3)
              ->  Seq Scan on t1 t1_3  (cost=0.00..41.88 rows=13 width=8) (never executed)
                    Filter: (c1 = 4)

A questão é: é seguro omitir order bynesses casos?