Sou novo no PostgreSQL e um pouco novo em bancos de dados em geral. Existe uma maneira estabelecida de como devemos indexar valores UUID no Postgres? Estou dividido entre usar hash e usar um trie, a menos que já haja algo embutido que ele use automaticamente. O que quer que eu use vai lidar com grandes quantidades de dados.
A família de operadores SP-GiST "text_ops" indexa usando um trie. Como os UUIDs são bastante longos e muito diferentes, eles parecem atraentes, embora eu só faça pesquisas de correspondência completa.
Há também uma opção de hash. Hashing é O(1), e não precisarei fazer nenhuma comparação além da igualdade, é claro, mas como os UUIDs são bastante longos, temo que gerar hashes a partir deles desperdiçaria muito tempo.
Ou isso é algo que depende muito das especificidades do sistema e do uso?
Eu prefiro usar bigserial na maioria dos casos, mas me disseram para usar uuid para isso. Precisamos de uuid porque podemos ter vários servidores usando bancos de dados diferentes, portanto, não há garantia de que teremos bigints exclusivos. Poderíamos usar uma sequência (e semente) diferente para cada servidor, mas ainda não é tão flexível quanto os UUIDs. Por exemplo, não poderíamos migrar entradas de banco de dados de um servidor para outro sem converter os IDs e suas referências em todos os lugares.
uuidUse o tipo de dados interno do PostgreSQL e crie um índice regular de b-tree nele.Não há necessidade de fazer nada de especial. Isso resultará em um índice ideal e também armazenará o
uuidcampo em um formato tão compacto quanto possível atualmente.(Os índices de hash no PostgreSQL anteriores à versão 10 não eram seguros contra falhas e eram realmente uma relíquia histórica que tendia a não ter um desempenho melhor do que uma árvore b. Evite-os. No PostgreSQL 10 eles foram feitos à prova de falhas e tinham alguns melhorias de desempenho feitas para que você possa considerá-las.)
Se por algum motivo você não puder usar o
uuidtipo, você geralmente criaria uma b-tree na representação de texto ou, de preferência, umabytearepresentação do uuid.Índice BRIN? Se você usar UUIDs baseados em tempo (versão 1), eles serão gerados para que seu valor aumente. Nesse caso, o BRIN é adequado.
https://www.postgresql.org/docs/9.5/brin-intro.html :
Perfeito para dados enormes e "principalmente" ordenados.
Veja este post para alguns benchmarks:
https://www.percona.com/blog/2019/07/16/brin-index-for-postgresql-dont-forget-the-benefits/
Eles geraram uma tabela de 1,3 GB de dados naturalmente ordenados (timestamps cimentados). Em seguida, eles geraram um índice BRIN (com pages_per_range = 32) e um índice B-Tree neste banco de dados. Em seguida, eles compararam o tempo de execução do SELECT e o tamanho dos índices. O que eles conseguiram:
B-Árvore:
BRIN:
enquanto isso, sem índice, seria:
Só para dar uma noção das ordens.
O que é importante discutir ainda é a complexidade da atualização do índice após o INSERT dos dois. Enquanto para BRIN é O(1), uma vez que você escreve sequencialmente no próximo espaço livre na memória e, consequentemente, cria novas entradas BRIN, no entanto, para B-Tree, como bem sabemos, é O(logN) ( B-Trees ) ( mais alto a árvore leva mais tempo).
Índices de hash estão ausentes em ação no PostgreSQL. O PostgreSQL sabe que precisa de índices de hash e que seu código para índices de hash é antigo e mofado, mas eles não o removem porque estão esperando que alguém apareça e revise a indexação de hash. Veja este tópico:
http://www.postgresql.org/message-id/[email protected]
Use o tipo de índice padrão (ou seja, B-Tree).
Embora não haja muito nele,
hashnão foi mais rápido.hashtambém não suportaunique(se você precisar disso).Resultados obtidos dados:
uuidmais cerca de 150 bytes de outras colunasanalyze mytableselect * from mytable where myuuid = '<some uuid>'::uuid