Início / dba / Perguntas / 334904
Accepted
James Hay
Asked: 2024-01-16 19:39:32 +0800 CST

Existe uma penalidade de desempenho para violações de restrições de chave exclusivas no Postgres?

  • 772

Na minha API, o usuário pode enviar uma solicitação que tenta criar uma nova linha quando existe uma linha com essa chave exclusiva.

Atualmente, estou detectando o erro de chave exclusiva e retornando uma mensagem informando que X já existe. Mas é mais eficiente pesquisar a linha primeiro (na mesma conexão) e executar a instrução INSERT apenas se essa linha não existir?

Minha intuição diz que ler o erro do Postgres deveria ser mais eficiente, mas gostaria de ter certeza de que estou fazendo as coisas de maneira idiomática.

Postgres é a versão 12

postgresql

3 respostas

  1. Mas é mais eficiente pesquisar a linha primeiro (na mesma conexão) e executar a instrução INSERT apenas se essa linha não existir?

    Independentemente de ter melhor desempenho ou não, não há garantia de precisão sem bloquear a tabela inteira desde o momento da pesquisa até o momento do INSERT. Sem bloquear a tabela, alguém pode, teoricamente, usar INSERTa mesma chave de dados entre o momento da verificação e o momento da verificação INSERT(mesmo que eles estejam separados por nanossegundos). Nesse ritmo, provavelmente terá menos desempenho para todo o sistema, de uma perspectiva holística, do que confiar apenas na restrição de chave única.

    • 7
  2. Best Answer

    é mais eficiente pesquisar a linha primeiro (na mesma conexão) e executar a instrução INSERT apenas se essa linha não existir?

    Se alguém inserir uma duplicata simultaneamente, não há problema: o select não a verá, mas a restrição exclusiva ainda a capturará. Você ainda precisa duplicar seu código de tratamento de erros. No entanto, se alguém excluir a duplicata depois que o select a viu, ela não será inserida.

    Executei um benchmark python, o código fonte está disponível em pastebin . Este é um exemplo simples usando uma tabela com apenas uma chave primária e uma coluna de texto fictícia. Para cada id no intervalo 0..99, ele insere 100 vezes. Somente a primeira vez funcionará, o restante será rejeitado pela restrição única.

    Os candidatos são:

    • insert_only: envia a inserção, então funciona ou falha na restrição exclusiva.

    • select_then_insert: seleciona para verificar e depois insere.

    • insert_select combina as duas consultas anteriores em uma, o que também remove a condição de corrida:

      INSERT INTO testins (id,t) SELECT %s,'olá, mundo' WHERE NOT EXISTS( SELECT FROM testins WHERE id=%s ) RETURNING id

    • on_conflict usa o recurso upsert:

      INSERT INTO testins (id,t) VALUES (%s,'hello, world') ON CONFLICT (id) NÃO FAÇA NADA RETURNING id

    "RETURNING id" simplesmente retorna o id se a linha foi inserida, então você sabe que foi. Se a consulta não retornar nada, significa que houve uma duplicata.

    Resultados (com tamanho de tabela):

    on_conflict: 0.683s 100 rows      8.000 kB
insert_only: 0.850s 100 rows    512.000 kB
select_then_insert: 0.736s 100 rows      8.000 kB
insert_select: 0.615s 100 rows      8.000 kB

    Este teste possui 99 duplicatas para cada inserção, então vamos tentar uma quantidade mais razoável de 1 duplicata por inserção:

    on_conflict: 0.743s 5000 rows    256.000 kB
insert_only: 0.782s 5000 rows    512.000 kB
select_then_insert: 0.943s 5000 rows    256.000 kB
insert_select: 0.668s 5000 rows    256.000 kB

    Sem duplicatas:

    on_conflict: 0.816s 10000 rows    512.000 kB
insert_only: 0.691s 10000 rows    512.000 kB
select_then_insert: 1.184s 10000 rows    512.000 kB
insert_select: 0.777s 10000 rows    512.000 kB

    Em todos os casos, a maior parte do tempo é gasta em viagens de ida e volta na conexão e na confirmação de transações.

    Conclusão:

    O problema com o INSERT direto é o fato de que ele ainda grava a linha na tabela, depois tenta escrevê-la no índice e falha em uma duplicata, depois reverte a transação. Isso resulta em gravações em disco (tabela e WAL) e incha a tabela com linhas mortas que precisarão de VACUUMing. Fazer tudo isso explica a pequena penalidade de desempenho.

    As outras soluções não inserem a linha se houver duplicata, o que evita gravações inúteis e inchaço da tabela.

    O mais idiomático para o postgres seria ON CONFLICT.

    Portanto, se você espera ter muitas duplicatas, ou seja, na maioria das vezes o INSERT falhará e o tráfego nesta consulta for alto, seria vantajoso usar ON CONFLICT.

    Se você espera poucas duplicatas, ou seja, na maioria das vezes o INSERT funcionará, então você pode simplesmente deixar que ele gere o erro.

    Se isso fizer parte de uma transação maior que você prefere não falhar, reverter e fazer todo o trabalho novamente, ON CONFLICT pode ajudar, pois não gerará um erro em caso de duplicata.

    • 5

  3. No PostgreSQL, restrições exclusivas são implementadas inserindo primeiro o registro e, em seguida, revertendo-o se violar a restrição.

    Se sua restrição for adiada, a entrada B-Tree duplicada também será inserida por enquanto e um gatilho interno chamado unique_key_recheckserá executado para verificar se os registros recém-inseridos não violam a restrição.

    Se parece com isso:

    test=# CREATE TABLE mytable (id INT NOT NULL, value TEXT NOT NULL, CONSTRAINT ux_mytable_id UNIQUE (id) DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE
test=# INSERT INTO mytable VALUES (1, 'test');
INSERT 0 1
test=# INSERT INTO mytable VALUES (1, 'test2');
ERROR:  duplicate key value violates unique constraint "ux_mytable_id"
DETAIL:  Key (id)=(1) already exists.
test=# SELECT * FROM heap_page_items(get_raw_page('mytable', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------
  1 |   8152 |        1 |     33 |    759 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x010000000b74657374
  2 |   8112 |        1 |     34 |    760 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x010000000d7465737432
(2 rows)

test=# SELECT * FROM bt_page_items('ux_mytable_id', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           | dead | htid  | tids
------------+-------+---------+-------+------+-------------------------+------+-------+------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,1) |
          2 | (0,2) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,2) |
(2 rows)

    Os segundos registros nesses conjuntos de resultados são registros inativos cujo backup foi revertido quando sua restrição falhou. Esses registros sobrecarregam o espaço de tabela e o WAL.

    Então, respondendo diretamente à sua pergunta: sim, há coisas que afetam o desempenho geral que acontecem quando você viola a restrição e não acontecem quando você não o faz.

    Minha intuição diz que ler o erro do Postgres deveria ser mais eficiente

    Isso depende da frequência com que essas violações de restrições acontecem.

    Ler o registro com antecedência requer percorrer a árvore B duas vezes, e se sua taxa de erro for muito baixa (como deveria ser), pode valer a pena sofrer o impacto de desempenho de entrada morta de vez em quando, em vez de fazer a verificação em cada inserção.

    Observe que em um sistema projetado adequadamente, a restrição exclusiva deve existir independentemente de você verificá-la antecipadamente ou não.

    • 3

relate perguntas