Escalabilidade do Postgres para tabela com grande número de colunas indexadas

Uma sessão individual do banco de dados PostgreSQL costumava ser de thread único, pois há um único processo de back-end que processa as instruções SQL para a conexão. O PostgreSQL 9.6 introduziu a consulta paralela, que permite ao processo backend iniciar processos adicionais durante uma instrução. Mas mesmo sem isso, você pode ter muitas sessões de banco de dados simultâneas, cada uma delas com um processo de back-end, portanto pode haver bastante simultaneidade. A comunicação entre esses processos acontece por meio de técnicas de comunicação entre processos como memória compartilhada, sinais e semáforos.

Suas suposições são em sua maioria verdadeiras, exceto que não há fusão de modificações de índice por escritores simultâneos. Solicitações simultâneas de modificação de dados são serializadas em virtude de várias técnicas de bloqueio (semáforos, mutexes e spinlocks).

Não há como configurar o PostgreSQL para obter melhor desempenho às custas da integridade e consistência dos dados. O PostgreSQL é bastante implacável quando se trata disso. Suspeito que sua pergunta seja teórica e não baseada em problemas que você já encontrou. Com uma tabela ampla e com muitos índices, eu esperaria que não seja a simultaneidade o seu grande problema, mas a lentidão da própria modificação dos dados. Sugiro que você altere as especificações da sua aplicação; veja esta pergunta para ver meus pensamentos sobre isso.

Ser single-thread não importa. É multiprocesso com memória compartilhada, e a maneira como os processos gerenciam a simultaneidade não é significativamente diferente da maneira como os threads o fazem.

Existem dois tipos de bloqueios: os bloqueios pesados ​​duram a duração de uma transação (normalmente), enquanto os bloqueios leves e os bloqueios giratórios duram apenas um tempo muito breve.

Os gravadores que atualizam linhas individuais não bloqueiam os leitores, a menos que o leitor esteja executando uma consulta que produza um conjunto de resultados que inclua a linha que está sendo atualizada.

Os escritores bloqueiam os leitores usando travas leves ou travas giratórias na medida necessária para que um processo não altere os dados enquanto o outro os inspeciona. Isso geralmente acontece no nível da página, não no nível da linha. Assim, enquanto um escritor escreve em uma página, os leitores não podem inspecioná-la. Mas assim que o escritor termina (uma questão de microssegundos ou menos, geralmente), eles podem. Se a linha que eles desejam ver foi atualizada, eles apenas extrairão o valor antigo em vez do novo valor.

Os escritores só bloqueiam uns aos outros se estiverem tentando atualizar a mesma linha ao mesmo tempo.

Os escritores bloqueiam outros escritores no nível da página por períodos muito breves, assim como fazem com os leitores. Se dois escritores quiserem atualizar a mesma linha, um bloqueará indefinidamente em um bloqueio pesado, aguardando que o outro confirme ou reverta.

Atualizações simultâneas de índices baseados em btree de vários gravadores são mescladas de acordo com um conjunto de regras que geralmente faz a coisa certa (portanto, atualizar os mesmos índices ao mesmo tempo não causa o bloqueio dos gravadores, a menos que estejam atualizando a mesma linha).

Se eles estiverem atualizando a mesma linha, isso será resolvido antes de chegarem ao índice. Portanto, os índices não impõem novos problemas de bloqueio de “peso pesado”. Eles impõem um travamento mais leve, mas apenas proporcional à quantidade de trabalho em geral que impõem.

Estou bem com a falta de consistência em nome de uma maior simultaneidade.

É difícil acreditar nisso, a menos que você queira dizer falta de consistência em algum sentido especializado. Sem consistência, você obterá resultados errados. Se você não se importa se os resultados estão errados, não há necessidade de índices, basta adicionar WHERE/AND 1=0todas as suas consultas e elas deverão ser rápidas sem índices.

os detalhes do aplicativo realmente não importam para a pergunta

Se você está fazendo uma pergunta, significa que não sabe a resposta. Neste caso é um pouco presunçoso ocultar informações porque acha que não são relevantes: para saber se a informação é relevante, você precisaria saber a resposta, o que não acontece, porque está fazendo a pergunta; )

se você quer saber, é um aplicativo de namoro com informações de perfil muito extensas, muitos campos dos quais podem ser adicionados aos critérios de pesquisa

Uma excelente solução para um grande número de colunas de baixa cardinalidade é um índice de filtro Bloom . Você tem que carregar a extensão:

CREATE EXTENSION bloom

Infelizmente ele suporta apenas até 32 colunas, então se você tiver mais colunas você precisará de vários índices. Ainda para 100 colunas... 4 índices provavelmente usarão menos recursos que 100 índices.

Outra opção é fornecer a cada par (nome_atributo, valor) um número, armazená-lo em uma matriz inteira e colocar um índice essencial nele. É um pouco complicado, por exemplo "cabelo=loiro" talvez correspondesse a "há o número 123 na matriz".

Fiz um pequeno benchmark com 1 milhão de linhas e o índice de floração venceu por uma grande margem.

Portanto, recomendo que você experimente e compare com suas consultas de pesquisa mais comuns e também ajuste os parâmetros de flores, como o comprimento da assinatura. Devido ao limite de 32 colunas, a forma como você divide as colunas em índices provavelmente também será importante.

Observe que seu problema é idêntico à pesquisa de texto completo. Encontrar linhas com "hair=blonde e status=single" é exatamente o mesmo que codificar os atributos em palavras-chave e fazer uma pesquisa de texto completo em "hair_blonde status_single".

Portanto, outra opção é usar apenas um mecanismo de texto completo rápido. Mas a integração do banco de dados provavelmente será uma droga. Eu não recomendaria usar o mecanismo de texto completo do postgres, pois ele é baseado em índices essenciais, o que significa que você obteria melhor desempenho usando índices essenciais diretamente.

--

Script de geração de dados para benchmark

SELECT * FROM profiles_bloom WHERE a01=1 AND a02=1 AND a03=1 AND a10=1 AND a11=1 AND a12=1

Seq scan: 44ms
Bloom: 12ms
Btree: 26ms
gist (using integer array contains operator): 63ms
gin (same): 45ms

As linhas são muito pequenas, o que torna a verificação do índice de bitmap menos eficiente. Com linhas maiores, cada página sinalizada pela varredura de índice de bitmap contém menos linhas para filtrar, portanto deve ser mais rápido.

Infelizmente, o índice do filtro Bloom não suporta bools, então usei colunas inteiras.

Como parece que sua principal curiosidade está nas compensações de consistência para melhorar a simultaneidade, o tópico sobre o qual você provavelmente está procurando aprender se chama Níveis de isolamento de transação . Esta é uma implementação no PostgreSQL (e na maioria dos sistemas de banco de dados) baseada no padrão SQL que controla essa compensação:

O padrão SQL define quatro níveis de isolamento de transação. O mais rigoroso é o Serializable, que é definido pelo padrão em um parágrafo que diz que qualquer execução simultânea de um conjunto de transações Serializable produzirá o mesmo efeito que executá-las uma de cada vez em alguma ordem. Os outros três níveis são definidos em termos de fenómenos, resultantes da interação entre transações simultâneas, que não devem ocorrer em cada nível. A norma observa que, devido à definição de Serializable, nenhum desses fenômenos é possível nesse nível.

Estes são os fenômenos mencionados acima que podem ocorrer em vários graus dependendo do nível de isolamento:

leitura suja

• Uma transação lê dados gravados por uma transação simultânea não confirmada.

leitura não repetível

• Uma transação relê os dados que leu anteriormente e descobre que os dados foram modificados por outra transação (aquela confirmada desde a leitura inicial).

leitura fantasma

• Uma transação reexecuta uma consulta retornando um conjunto de linhas que satisfazem uma condição de pesquisa e descobre que o conjunto de linhas que satisfazem a condição foi alterado devido a outra transação confirmada recentemente.

anomalia de serialização

• O resultado da confirmação bem-sucedida de um grupo de transações é inconsistente com todas as ordens possíveis de execução dessas transações, uma de cada vez.

Aqui está a tabela dos níveis de isolamento oferecidos pelo PostgreSQL e seus possíveis fenômenos:

O nível de isolamento padrão no PostgreSQL é Read Committedbasicamente significa que os leitores bloqueiam os escritores e os escritores bloqueiam os leitores. Em um sistema de banco de dados diferente, você pode estar interessado no Read Uncommittednível de isolamento, que permite a leitura de dados que estão sendo gravados simultaneamente, mas o PostgreSQL na verdade não implementa esse nível de isolamento dessa maneira - o que é uma coisa boa, porque é perigoso aquele com riscos para a maioria dos casos de uso.

Em vez disso, o PostgreSQL possui controle de simultaneidade multiversão integrado, o que permite simultaneidade otimista. Esse recurso permite manter estados anteriores dos dados à medida que os dados são alterados (sendo gravados) simultaneamente para permitir implicitamente que leitores simultâneos também possam ler esses dados. Esta breve resposta do DBA.StackExchange discute isso um pouco mais detalhadamente.

Além de tudo isso, consulte meu comentário em sua postagem sobre como melhorar geralmente o design do seu banco de dados para aumentar o desempenho e a simultaneidade.