27 de outubro de 2020 Arthur Nascimento 2851 Views

Descubra as novidades do PostgreSQL 13: suporte a colações não determinísticas

Com o lançamento recente do PostgreSQL 13 e com a grande maturidade das versões anteriores, algumas das novidades dessas versões mais recentes se tornam cada vez mais disponíveis para uso em produção, como particionamento e replicação lógica.

Aqui vamos ver uma funcionalidade nova no PostgreSQL 12, que é o suporte a colações não determinísticas. Essa funcionalidade é pequena, se comparada com outras, além de também ser algo usável em casos muito mais específicos. Mas é uma ferramenta a mais no canivete suíço do profissional; e o caminho até o seu entendimento passa por diversos estudos e investigações que valem a pena por elas mesmas.

Vale lembrar que esse assunto só é realmente interessante com codificações Unicode (UTF-8, UTF-16…), que é o ideal como codificação de servidor. Se o seu PostgreSQL não está em Unicode, planeje uma conversão. Se a sua aplicação exige uma codificação diferente, ainda assim use Unicode no servidor e conecte ela usando um client_encoding adequado.

Colações

Uma colação é um conjunto de regras bem definidas sobre comparações de igualdade e de ordenação de texto. Essas regras dizem se letras maiúsculas e minúsculas são iguais entre si, se pontuações devem ser ignoradas, se letras maiúsculas vêm antes ou depois das minúsculas, se dígitos numéricos vêm antes ou depois de letras, se letras acentuadas são iguais a letras sem acentos, se sequências de dígitos numéricos são comparadas como números inteiros (1 < 2 < 10 < 20) ou se são comparados como dígitos numéricos independentes (1 < 10 < 2 < 20), assim como uma infinidade de outras.

A colação também é uma das muitas mudanças de comportamento trazidas pela configuração correta da internacionalização (i18n), já que cada língua, país ou região tem regras próprias sobre como seus textos devem ser ordenados oficialmente, da mesma forma como têm regras sobre separadores de casas de milhares e sobre formato de data e hora.

Mesmo na linha de comando, podemos ver os resultados diferentes produzidos pelas regras da colação (use colações disponíveis no seu sistema e listadas por `locale -a`):

$ echo -n 'a\nA' | LC_COLLATE=C.utf8 sort
A
a
$ echo -n 'a\nA' | LC_COLLATE=pt_BR.utf8 sort
a
A

Em resumo, quando comparamos dois textos, a colação é o que nos responde se eles são “iguais” ou se um é “menor” que o outro. No contexto do PostgreSQL, a “igualdade” vai ser usada para buscas por equivalência, restrições de unicidade e agrupamentos, enquanto que o resultado “menor” vai ser usado para buscas de fatias de dados, ordenações e funções de janela; ambos possivelmente apoiados por índices.

As colações disponíveis ao PostgreSQL são inicialmente descobertas em tempo de initdb e ficam disponíveis em pg_collation e pelo metacomando \dOS. Versões mais antigas tinham acesso apenas a colações disponibilizadas pela libc (biblioteca C do sistema operacional, mesma usada pelo `sort` anterior), que é bastante limitada; mas a partir do PostgreSQL 10, ele também pode usar a biblioteca ICU (International Components for Unicode). Com a ICU e com o comando CREATE COLLATION, podemos criar novas colações (tanto determinísticas quanto não determinísticas) que seguem regras mais adequadas aos nossos dados.

Caso de estudo: e-mail

Agora vamos escolher um exemplo próximo da realidade para investigar como as colações afetam os resultados. Muitos campos poderiam ser usados, como códigos (CEP, CPF, RG, RNE, ISBN, ISN, SKU, CNPJ, placa de carro…), nomes e apelidos, endereços e outros. Cada um teria um conjunto diferente de regras que deveriam ser usadas na colação. Vamos usar o campo de e-mail; e ainda assim, apenas um subconjunto dos requisitos sobre ele.

Sobre campos de e-mail, vamos usar três requisitos:

comparações sobre e-mail são insensíveis a caixa (maiúsculas e minúsculas são consideradas iguais);
comparações devem ignorar pontos (‘fulano.silva’ é o mesmo destinatário que ‘FulanoSilva’ em muitos servidores); e,
o texto que o usuário/aplicação informou deve ser armazenado e retornado sem transformação.

O ponto (1) é esperado e necessário para esse tipo de campo. O ponto (2) foi adicionado para evitar que o mesmo usuário crie duas contas no nosso sistema usando o mesmo e-mail, ou seja, para melhorar a unicidade do campo no nosso sistema, necessário para muitos servidores, como o gmail. O ponto (3) é requisito comum que evita que o banco de dados estrague dados que vão ser usados de forma desconhecida e possivelmente complexa por sistemas externos, então é melhor preservar o que foi inserido da forma mais fiel possível. Claro que esse levantamento de requisitos poderia ter sido feito muito diferente sobre o campo de e-mail, como adicionando o tratamento especial para o ‘+’ e usando uma colação diferente para o domínio sem o requisito (2), assim como removendo requisitos. Mas como estão, esses três requisitos servem como exemplo.

Colações determinísticas e normalizações

As colações mais simples são as determinísticas. Elas consideram que a representação binária do texto é representante direta do significado por trás dele. Então se tivermos dois textos de entrada com representações binárias diferentes, como ‘fulano.silva’ e ‘FulanoSilva’, então eles são obrigatoriamente diferentes para as colações determinísticas.

Isso significa que se quisermos que a comparação daqueles dois textos retorne a igualdade, precisaremos fazer uma etapa prévia de transformação tanto no valor armazenado quanto no valor comparado, tornando ambos uma representação binária única. Essa etapa é chamada de normalização e pode consistir de uma infinidade de transformações no dado, como normalização Unicode (NFC, NFD, NFKC e NFKD), de caixa (lower(), upper(), case mapping, case folding, remoção de acentos (unaccent()), remoção de caracteres no início e fim de textos (trim(), ltrim(), rtrim()), substituição de expressões regulares (regexp_replace()) e diversas outras.

Seguindo o nosso exemplo simples de e-mail, isso significa que toda comparação entre uma coluna e um parâmetro seria bem mais complexa:

WHERE regexp_replace(lower(coluna_email), '[.]', '', 'g') = regexp_replace(lower(parametro_email), '[.]', '', 'g')

Isso tem a vantagem:

Comparações sobre colações determinísticas são rápidas, às vezes muito mais rápidas do que imaginamos por causa de instruções vetoriais, chaves abreviadas e outras otimizações.

Mas tem várias pequenas desvantagens:

Toda consulta fica mais complexa;
Necessário um índice funcional sobre a expressão inteira;
Se a expressão da consulta não for exatamente aquela do índice, ela irá retornar dados errados e será muito mais lenta;
Algumas transformações podem causar problemas com caixas (lower e upper, especialmente);
Outras transformações podem não cobrir todos os casos (como unaccent pode não conhecer alguns acentos de outras línguas);
O unicode muda continuamente e pode ser necessário atualizar as funções de normalização implementadas
Necessário reindexar quando funções de normalização mudarem.

Alguns desses pontos podem ser combatidos parcialmente com atributos adicionais populados por gatilhos ou colunas geradas, mas ocupam mais espaço na tabela ou quebram o requisito (3).

Colações não determinísticas

Em contraste, uma colação não determinística permite que mais de uma representação binária tenha um mesmo significado. Uma colação não determinística, portanto, pode ver ‘fulano.silva’ e ‘FulanoSilva’ como o mesmo texto, sem precisar de normalizações ou etapas a mais.

Com isso, a comparação seria simplificada:

WHERE coluna_email = parametro_email

Isso traz diversas vantagens:

Toda consulta é mais simples e à prova de erros e de esquecimentos humanos;
Se já existia um índice sobre a coluna, não é necessário índice adicional;
Índices compostos também tomam proveito da colação;
O dado original é mantido (requisito (3));
Não é necessário campo adicional na tabela;
Não é necessário gatilho adicional na tabela;
Usa o motor da ICU, que acompanha o desenvolvimento do Unicode e cobre corretamente mais casos do que nós poderíamos imaginar.

E algumas desvantagens:

O desempenho pode ser inferior, já que parte da inteligência foi movida do pré-processamento dos textos para cada uma das comparações;
Não atende expressões regulares (por enquanto);
Pode ser necessário reindexar quando atualizando a ICU e o PostgreSQL.

Usando uma colação não determinística

O primeiro passo é criarmos a colação para o nosso campo de e-mail com o comando CREATE COLLATION e com as especificações da ICU. Como aquelas especificações são complexas, vamos direto a alguns casos mais úteis e ao comando que resolverá o nosso exemplo de e-mail:

und-u-ks-level2 / und@colStrength=secondary: insensível apenas a caixa
und-u-ks-level1 / und@colStrength=primary: insensível a caixa e a acentos
und-u-ks-level1-kc-true / und@colStrength=primary;caseLevel=on: insensível apenas a acentos
und-ka-shifted / und@colAlternate=shifted: ignora pontuação
und-u-ks-level4-ka-shifted / und@colStrength=quaternary;colAlternate=shifted: compara pontuações equivalentes, como … (U+002E U+002E U+002E) e … (U+2026)
pt-u-kn-true / pt@numericOrdering=on: colação português com ordenação numérica natural

Toda colação é criada a partir de outra. Para colações não determinísticas, usamos a colação base ‘und’ do provedor ‘icu’, informando que ela será não determinística. Em cima daquela colação base, aplicamos modificadores que causam o efeito que queremos. Para o exemplo de e-mail, queremos que ele seja insensível a caixa (ks-level2) e que ignore pontuação (ka-shifted).

CREATE COLLATION coll_email (
        PROVIDER = icu,
        LOCALE = 'und-u-ks-level2-ka-shifted',
        DETERMINISTIC = false);

Com isso, podemos testar a colação com uma comparação simples:

SELECT 'fulano.silva' = 'FulanoSilva' COLLATE coll_email AS "iguais?";
iguais?
 t

Com isso, podemos usar a colação onde ela for mais adequada, como em consultas diretas, como mencionado acima. Também podemos criar ou alterar a tabela com essa colação diretamente no campo, assim ela será usada em toda consulta (que não mencionar explicitamente outra colação). E também é possível criar índices sobre o campo e restrições de unicidade, alcançando os nossos requisitos de impedir que um usuário use um destinatário de e-mail que é reconhecido como único:

=# CREATE TABLE pessoa (
    -- ...
    email TEXT COLLATE coll_email UNIQUE,
    -- ...
);
CREATE TABLE

=# INSERT INTO pessoa(email) VALUES ('fulano.silva@example.com');
INSERT 0 1

=# INSERT INTO pessoa(email) VALUES ('FulanoSilva@example.com');
ERROR:  duplicate key value violates unique constraint "pessoa_email_key"
DETAIL:  Key (email)=(FulanoSilva@example.com) already exists.

=# SELECT email FROM pessoa WHERE email = 'FulanoSilva@example.com';
email
 fulano.silva@example.com

Conclusão

Colações são uma ferramenta extremamente poderosa. A partir do PostgreSQL 10, que tem acesso a colações da ICU, é possível fazer todo tipo de customizações sobre as colações determinísticas, mas é no PostgreSQL 12 em diante que temos acesso a colações não determinísticas que abrem um novo espaço de possibilidades para representarmos as comparações corretas sobre nossos campos mais complexos.

Ainda assim, é importante escolher a estratégia mais adequada para cada campo, já que: a maioria dos campos de códigos (CEP, CPF, ISBN…) têm formatos conhecidos que provavelmente deveriam ser inseridos limpos e apenas validados pelo banco de dados por um CHECK, tomando proveito das colações mais baratas possíveis, como C; campos de texto longo e de consumo humano provavelmente se beneficiarão mais de funcionalidades como FTS (Full Text Search) e buscas sobre trígramos; outros só precisam ser insensíveis a caixa, então o tipo citext é suficiente; e muitos outros simplesmente não têm requisitos como o (3), e podem ser transformados sempre que necessário.

Por fim, é provável que mais funcionalidades sejam agregadas ao PostgreSQL nessa direção ao longo dos próximos anos, tornando as colações um assunto cada vez mais parte do nosso dia a dia, à medida que se tornam também mais úteis e poderosas. Então agora é um bom momento para já começar a praticar e dominar elas.