PostgreSQL

Sintaxe no PostgreSQL: endereço de rede

Bom, chega um ponto em que estou falando mais sobre tipos de dados do que sobre sintaxe propriamente dito. O PostgreSQL é um SGDB que nasceu com uma proposta ousada, não apenas substituir o Ingres, mas ter uma arquitetura orientada a objetos e ser muito extensível. Um dos impactos disso é a capacidade de se criar vários tipos de dados diferentes. É realmente simples de fazer isso. Quando vimos o ENUM, vimos que você tem o CREATE TYPE, mas é só a ponta do iceberg: você tem o CREATE OPERATOR, CREATE AGGREGATE, CREATE CAST, CREATE ACCESS METHOD, CREATE COLLATION e por aí vai. Você nem precisa conhecer a linguagem C para fazer muita coisa interessante.

Uma das coisas interessantes que o PostgreSQL faz é criar tipos de dados novos. É quase um vício. Quase todo ano aparece um tipo de dados novo. O capítulo “Data Types” da documentação está dividido em 20 sessões na versão 10 do PostgreSQL. Na versão 7.1 eram apenas 8. Cada sessão apresenta um conjunto de tipos de dados específico. É claro que isso começa a parecer jabuticaba (uma daquelas coisas que só tem no Brasil…). Na verdade é mesmo. São extensões ao padrão SQL. Ninguém mais tem. Mas se considerarmos que a linguagem SQL é fortemente tipada, acho que os demais SGDBs deveriam ser um pouco mais desta forma também. Todos os SGDBs correram para criar uma implementação de XML e JSON nos seus bancos de dados. Mas existem muitas outras situações em que criar um tipo de dados específico para uma situação ajuda muito.

Vamos ver o caso específico de redes aqui. Este é um caso emblemático, pois é bem antigo no PostgreSQL e exemplifica bem como um tipo que poderia ser apenas um VARCHAR, pode ser muito mais que isso. O Postgres possui atualmente 4 tipos que armazenam endereços de rede:

  • inet: Armazena endereços IPv4 ou IPv6 e opcionalmente a subrede ou máscara (ok, é mais comum ouvir em inglês netmask).
  • cidr: Armazena a rede ou netmask da rede;
  • macaddr: Armazena  endereços MAC ou MAC addresses;
  • macaddr8: Semelhandte ao macaddr, mas utilizando o formato EUI-64 com 6 ou 8  bytes.

inet

A sintaxe para fazer a coerção para o tipo correto é a mesma que demonstramos no primeiro episódio da nossa saga. Vejamos o uso do inet:

Cada um dos tipos possui uma sintaxe específica que é aceita e convertida internamente antes de armazenar. A primeira vantagem óbvia é a validação. Se você tentar inserir um valor inválido, o Postgres vai acusar um erro imediatamente, o que não aconteceria se você utilizar um simples VARCHAR:

Claro que você pode criar uma função que faz esse tipo de validação na sua aplicação. Mas aqui você já tem isso pronto. Veja que o formato inet admite vários formatos diferentes de entrada, considerando sempre a máscara também. Vejamos alguns exemplos:

Veja que um com máscara 32 o IP é exibido sem uma máscara uma vez que /32 é a mesma coisa não utilizar uma subrede. Já quando omitimos um dos últimos dígitos e utilizamos uma Classe C por exemplo, o PostgreSQL subentende que o último dígito é zero.

Podemos trabalhar com IPv6 também:

cidr

Aqui temos algo semelhante, mas pensando apenas na máscara:

Se você omitir o tamanho da máscara, o PostgreSQL vai deduzir o tamanho pela faixa de IPs que você fornecer:

É claro que a quantidade de dígitos que você fornecer também afeta a interpretação:

macaddr

A sintaxe para o macaddr admite o uso dos separadores ‘.’, ‘:’, ‘-‘ ou nenhum separador, dependendo da forma como você agrupar ou não os dígitos:

Apesar de aceitar vários formatos, o padrão IEE especifica o uso do segundo exemplo como correto.

macaddr8

Da mesma forma, o MAC com 8 bytes ao invés de 6 pode ser utilizado em diferentes formatos:

Note que se você inserir um MAC de 6 bytes num macaddr8 ele vai preencher 2 bytes com ff:fe no lugar dos bytes faltantes.

Armazenamento

É claro que os tipos de rede são mais eficientes em termos de armazenamento que utilizar simplesmente um VARCHAR. Cada dígito aqui é um dígito hexadecimal de 0 a F e não um byte completo de 00 a FF. Portanto, há uma economia de 50% do espaço ocupado. Em termos de espaço temos:

  • inet: 7 bytes com IPv4 e 19 bytes com IPv6
  • cdir: 7 bytes com IPv4 e 19 bytes com IPv6
  • macaddr: 6 bytes
  • macaddr8: 8 bytes

Operadores

A brincadeira começa a ficar interessante aqui. Se você olhar o capítulo “Funções e Operadores” na documentação, verá uma sessão específica só para tipos de rede. Antes de mais nada, vou criar uma tabela com alguns registros aleatórios para poder brincar aqui:

Alguns operadores não são novidade como <, <=, =, >=, > e <>. Como esperado temos:

Mas o PostgreSQL traz outros operadores mais interessantes:

  • << está contido
  • <<= está contido ou igual
  • >> contém
  • >> contém ou igual
  • && contém ou está contido
  • NOT bit a bit
  • AND bit a bit
  • OR bit a bit
  • +

Vejamos como ficam alguns exemplos:

Agora a cereja do bolo. Primeiro vamos aumentar o volume de dados na tabela:

Agora vejamos o plano de execução de uma de nossas consultas:

Você não precisa entender muito sobre planos de execução. Apenas observe que fizemos um “Seq Scan” na tabela “net”, ou seja, varremos todas as linhas da tabela em busca de quais atendem aos requisitos da nossa cláusula WHERE. E claro: levou 18,66ms para rodar o comando.

Agora vamos criar um índice e rodar o mesmo plano de execução:

Agora o plano de execução passou a utilizar o índice “net_ip_idx” que criamos. E o resultado? Um tempo de execução de 0,062ms. Este é o grande pulo do gato com os operadores. Suas operações já são indexáveis por natureza. E isso você nunca vai conseguir imitar utilizando um VARCHAR.

Funções

Além dos operadores, temos também algumas funções nativas para trabalhar com tipos de dados para redes. Estas não são indexáveis por natureza, mas você pode criar índices incluindo estas funções se for realmente importante. Você pode e deve ver a lista das funções existentes na documentação oficial. Sempre. Não pretendemos aqui substituir a documentação, apenas dar uma visão mais clara sobre alguns pontos específicos. Então vá lá e faça uma visita.

Conclusões

Tipos de dados específicos como os tipos de rede são muito poderosos, eles nos ajudam de diversas formas:

  • Armazenamento otimizado, ocupando muito menos espaço;
  • Validação nativa bem feita;
  • Existe um arsenal operadores e funções prontas para utilizarem estes tipos de dados;
  • Operadores nativos utilizam índices automaticamente sem que você precise fazer nada além de criar um índice comum no campo.

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.