No Postgres existe a opção de salvar dados como JSON – o que pode ser muito útil especialmente quando temos muitas incertezas quanto aos requisitos de negócio que ajudariam na modelagem das tabelas.

A flexibilidade de poder armazenar os dados sem se preocupar com a estrutura das tabelas parece interessante, porém qual o impacto disso na performance?

A verdade é o que os dados do tipo JSON são armazenados da maneira como foram inseridos – isto é, em texto – o que deixa as consultas um pouco mais lentas já que os dados precisam ser parseados novamente. Porém, existe uma outra variação: o JSONB. Esse formato processa o texto no momento da inserção e o armazena em formato binário. Isso faz com que a inserção demore um pouco mais, em troca de consultas mais rápidas. Ah, e o JSONB também suporta índices!

Nesse artigo você vai encontrar uma comparação dos tipos de índices disponíveis para JSONB no Postgres, um benchmark e como usá-los em aplicações Rails.

Setup

Para testar a performance de índices, é importante ter um banco populado com uma quantidade razoável de registros. Abaixo você encontrará as etapas necessárias para criar o banco de dados utilizado nesse benchmark.
Para acompanhar as etapas à seguir, precisaremos do Postgres devidamente instalado e rodando.

Iremos importar cerca de 600 mil registros na estrutura abaixo:

{

        "customer_id": "ATVPDKIKX0DER",
        "product": {
            "category": "Arts & Photography",
            "group": "Book",
            "id": "1854103040",
            "sales_rank": 72019,
            "similar_ids": [
                "1854102664",
                "0893815381",
                "0893816493",
                "3037664959",
                "089381296X"
            ],
            "subcategory": "Art",
            "title": "The Age of Innocence"
        },
        "review": {
            "date": "1995-08-10",
            "helpful_votes": 5,
            "rating": 5,
            "votes": 12
        }
   }

Para importar esses dados, faça o download do arquivo e siga as instruções abaixo:

1. Criar banco de dados

❯ psql

CREATE DATABASE store;

2. Criar tabela

Vamos criar a tabela reviews com um único campo (content) para armazenar o conteúdo de cada registro:

❯ psql store

CREATE TABLE reviews(content jsonb);

3. Importar os dados

Por fim, vamos importar os dados do JSON que acabamos de baixar utilizando o comando copy dentro do console do Postgres:

copy reviews (content) FROM 'customer_reviews_nested_1998.json'

B-tree

O índice do formato B-tree funciona somente com uma chave do documento e para um operador específico: isto é, o operador usado na definição do índice deverá ser o mesmo utilizado na consulta:

CREATE INDEX ON reviews((content #>> '{product,category}'));

Para que o índice seja utilizado, devemos fazer a consulta com o mesmo operador:

SELECT COUNT(*) FROM reviews WHERE content #>> '{product,category}' = 'Arts & Photography';

Se utilizarmos outros operadores o índice não será aplicado, e consequentemente a busca ficará mais lenta:

SELECT COUNT(*) FROM reviews WHERE content->'product'->>'category' = 'Arts & Photography';

O índice para a consulta acima seria o seguinte:

CREATE INDEX ON reviews((content->'product'->>'category'));

Se for necessário responder perguntas como “quais as reviews tem rating 4 ou mais” ou “quais reviews não são de livros” – operadores >, =, <, >=, <= e != – esse é o índice mais indicado.

Porém fica uma observação aqui: nos testes feitos nesse benchmark, os índices para esses operadores acabaram não valendo a pena, o tempo de execução foi o mesmo. Mas pode ser que esse não seja sempre o caso, por isso é importante fazer benchmarks no seu ambiente para ter uma escolha mais assertiva.

Prós:

• Melhor performance na consulta;
• Mais rápido para gerar;
• Utiliza menos espaço em disco;
• Suporta os operadores >, =, <, >=, <= e !=.

Contras:

• Somente um operador de consulta;
• Se for necessário buscar por várias chaves do documento, será preciso criar um índice para cada uma.

Hash

Apenas uma menção honrosa. Sua funcionalidade é muito similar ao B-tree, porém o próprio banco já lança um warning ao criá-lo. O warning informa que o Hash não suport algo chamado WAL e, portanto, seu uso é desencorajado:

CREATE INDEX ON reviews USING HASH((content #>> '{product,category}'));
WARNING:  hash indexes are not WAL-logged and their use is discouraged

O Write-Ahead Logging (WAL) é um padrão que garante a integridade dos dados. De forma resumida, ele garante que as alterações nos arquivos do banco de dados só sejam salvas depois que essas mudanças forem salvas em um log. Isso permite que, se houver um crash no banco, seja possível aplicar as mudanças perdidas com base no log.
Caso queira saber mais sobre WAL, recomendo ler a documentação de Postgres.

Por não suportar o WAL, o Hash acaba sendo um pouco mais rápido que o B-tree, porém ele não foi incluído no benchmark por conta de seu uso ser desencorajado.

Atualização: a partir do Postgres 10, os índices de tipo Hash passaram a suportar o WAL. Portanto, pode valer a pena considerar essa opção na hora de escolher qual índice utilizar.

GIN (General inverted Index)

A partir do Postgres 9.4, podemos utilizar esse novo formato de índice para os documentos. Esse índice pode ser usado em todo o documento (com qualquer chave).

CREATE INDEX on reviews USING GIN(content);

Nesse caso podemos realizar a consulta dessa forma:

SELECT COUNT(*) FROM reviews WHERE content @> '{"product": {"category": "Arts & Photography"}}';

Também é possível utilizar os operadores ?, ?| e ?& – que são usados para saber se o documento contém alguma chave.

GIN (jsonb_path_ops)

Normalmente o operador mais usado é o @> , para saber se o documento “contém o pedaço” que for passado como argumento.
Se na sua aplicação for possível utilizar apenas esse operador, é interessante usar a variação jsonb_path_ops:

CREATE INDEX ON reviews USING GIN(content jsonb_path_ops);

Por suportar somente esse operador, o índice fica um pouco menor e as consultas mais performáticas.

Prós:

• Funciona com qualquer chave do documento;
• Um índice serve para consultas por qualquer chave.

Contras:

• Ocupa mais espaço em disco;
• Demora mais tempo para ser gerado;
• Consulta é mais lenta;
• Não permite utilizar os operadores >, =, <, >=, <= e !=.

Benchmarks

Os testes foram realizados em um MacBook Pro 13’ Early 2015 com 8GB de RAM, Core i5 2.7GHz e 120GB SSD.
O banco possuí 589.859 registros.

Criação do índice

Consulta

Com o resultado do benchmark, podemos concluir que: se a ideia for utilizar operadores de comparação simples (>, =, <, >=, <= e !=) ou apenas um chave do documento, a melhor opção é o B-tree.
Se as consultas necessitarem de maior flexibilidade – buscar por várias chaves do documento – a melhor opção é o GIN. E se você utilizar somente o operador @>, vale a pena utilizar a variação jsonb_path_ops do GIN.

Uso em aplicações Rails

Os índices acima contém expressões de SQL, então como podemos criar esses tipos de índice no Rails? Bom, se sua aplicação estiver usando Rails 4 ou anterior, o jeito é usar o método execute que aceita um SQL literal como argumento:

class AddIndex < ActiveRecord::Migration
  def up
    execute 'CREATE INDEX index_reviews_on_content ON reviews USING GIN(content jsonb_path_ops)'
  end

  def down
    remove_index :reviews, name: 'index_reviews_on_content'
  end
end

Por conta disso também é preciso especificar como a migration pode ser revertida – ou seja, implementar o método down.

A boa notícia é que, a partir do Rails 5 podemos utilizar expressões SQL na criação dos índices:

class AddIndex < ActiveRecord::Migration[5.0]
  def up
    add_index :reviews, 'content jsonb_path_ops', using: :gin, name: 'index_reviews_on_content'
  end

  def down
    remove_index :reviews, name: 'index_reviews_on_content'
  end
end

Em um próximo post, vamos mostrar alguns exemplos de como fazer queries e updates utilizando Rails e JSONB. Fique ligado!

Links de referência

https://www.postgresql.org/docs/9.5/static/functions-json.html

https://www.postgresql.org/docs/9.5/static/datatype-json.html

https://blog.heapanalytics.com/when-to-avoid-jsonb-in-a-postgresql-schema/

http://schinckel.net/2014/05/25/querying-json-in-postgres/

PostgreSQL internals: JSONB type and its indexes

https://blog.2ndquadrant.com/jsonb-type-performance-postgresql-9-4/

https://stackoverflow.com/questions/36075918/postgresql-index-on-json

http://blog.bigbinary.com/2016/07/20/rails-5-adds-support-for-expression-indexes-for-postgresql.html