Usando PostgreSQL e JSONB no Rails
Leia em 7 minutos
Com o novo formato JSONB, introduzido no PostgreSQSL 9.4, temos todo o poder de bancos de dados não-relacionais no próprio PostgreSQL. Com ele é possível realizar buscas mais rápidas e simples. Também é possível criar índices em toda a estrutura ou em um caminho específico do JSON.
Isso sem falar em funções que permitem extrair e manipular os dados do JSON, permitindo a integração de forma simples entre dados relacionais e não relacionais. Tudo isso com performance que se iguala ou ultrapassa a maioria dos bancos de dados não-relacionais, como MongoDB.
Entendendo as diferenças entre JSON e JSONB
A maior diferença entre os tipos JSON e JSONB está na performance. O JSONB é muito mais rápido que seu antecessor para fazer leituras, já que você pode indexar o conteúdo do campo.
Em termos de velocidade de escrita, os tempos são bem próximos, mas o JSON é um pouco mais rápido. Isso acontece porque o JSONB precisa transformar os dados em uma estrutura nativa do PostgreSQL. Em um benchmark que fiz, inserir 30.000 registros com a estrutura abaixo tiveram tempos praticamente iguais.
{
"twitter": "johndoe1",
"github": "johndoe1",
"bio": "Lorem ipsum dolor sit amet, consectetur adipisicing elit. Labore impedit aliquam sapiente dolore magni aliquid ipsa ad, enim, esse ut reprehenderit quaerat deleniti fugit eaque. Vero eligendi voluptatibus atque, asperiores.",
"blog": "http://johndoe1.example.com",
"interests": [
"music",
"movies",
"programming"
],
"age": 42,
"newsletter": true
}
Rehearsal ------------------------------------------------
insert jsonb 1.110000 0.550000 1.660000 ( 10.818088)
insert json 1.060000 0.520000 1.580000 ( 9.547106)
--------------------------------------- total: 3.240000sec
user system total real
insert jsonb 1.070000 0.530000 1.600000 ( 10.327861)
insert json 1.120000 0.560000 1.680000 ( 10.641324)
Mas a história muda um pouco quando você precisa fazer buscas no campo JSONB. Isso porque ele aceita índices do tipo GIN. Você pode criar índices em toda a estrutura de dados ou em apenas em um/mais campos.
No benchmark foi usada a seguinte tabela, com um índice no campo JSONB:
CREATE TABLE users (
id serial NOT NULL,
preferences json,
settings jsonb
);
CREATE INDEX github_handle ON users USING GIN ((settings->>'github'));
Após inserir 30.000 registros, uma busca pelo username ‘johndoe30000’ retornou os seguintes tempos para 1.000 iterações:
Rehearsal ----------------------------------------------
read jsonb 0.030000 0.020000 0.050000 ( 0.121338)
read json 0.050000 0.030000 0.080000 (135.412013)
------------------------------------- total: 0.130000sec
user system total real
read jsonb 0.020000 0.020000 0.040000 ( 0.135295)
read json 0.050000 0.030000 0.080000 (138.217214)
Como você pode ver, a diferença é brutal. Vamos se o EXPLAIN pode nos dar alguma dica do que está acontecendo. Por que será que a diferença foi tão grande?
EXPLAIN
SELECT (preferences->>'github')::text AS github_handle
FROM users
WHERE (preferences->>'github')::text = 'johndoe30000' LIMIT 1;
-- QUERY PLAN
-- ---------------------------------------------------------------------------
-- Limit (cost=0.00..25.23 rows=1 width=32)
-- -> Seq Scan on users (cost=0.00..3784.38 rows=150 width=32)
-- Filter: ((preferences ->> 'github'::text) = 'johndoe30000'::text)
-- (3 rows)
EXPLAIN
SELECT settings->>'github' AS github_handle
FROM users
WHERE settings->>'github' = 'johndoe30000' LIMIT 1;
-- QUERY PLAN
-- -----------------------------------------------------------------------------------
-- Limit (cost=0.29..8.31 rows=1 width=432)
-- -> Index Scan using github_handle on users (cost=0.29..8.31 rows=1 width=432)
-- Index Cond: ((settings ->> 'github'::text) = 'johndoe30000'::text)
-- (3 rows)
O que é importante perceber é que o campo JSON fez uma busca sequencial (Seq Scan); isso significa que o PostgreSQL foi procurando registro por registro, sequencialmente, até que a condição fosse satisfeita.
No caso do campo JSONB, a busca foi feita à partir do índice (Index Scan), permitindo que a consulta fosse realizada muito mais rapidamente.
Uma coisa que é importante ter em mente é que o JSONB também fará uma busca sequencial caso você busque por um caminho que não foi indexado. Isso acontece devido ao modo como o operador ->> funciona.
EXPLAIN
SELECT settings->>'github' AS github_handle
FROM users
WHERE settings->>'twitter' = 'johndoe30000' LIMIT 1;
-- QUERY PLAN
-- -------------------------------------------------------------------------
-- Limit (cost=0.00..25.23 rows=1 width=432)
-- -> Seq Scan on users (cost=0.00..3784.38 rows=150 width=432)
-- Filter: ((settings ->> 'twitter'::text) = 'johndoe30000'::text)
-- (3 rows)
Uma maneira mais eficiente de fazer buscas por qualquer propriedade do JSON é indexar todo o campo, definindo um índice que suporta apenas o operador @>, ou seja, que faz uma busca partial como no exemplo abaixo.
CREATE INDEX users_settings_gin_index ON users USING GIN (settings jsonb_path_ops);
EXPLAIN
SELECT settings->>'github' AS github_handle
FROM users
WHERE settings @> '{"github":"johndoe30000"}' LIMIT 1;
-- QUERY PLAN
-- -------------------------------------------------------------------------------------
-- Limit (cost=28.23..31.96 rows=1 width=432)
-- -> Bitmap Heap Scan on users (cost=28.23..140.15 rows=30 width=432)
-- Recheck Cond: (settings @> '{"github": "johndoe30000"}'::jsonb)
-- -> Bitmap Index Scan on settings_index (cost=0.00..28.23 rows=30 width=0)
-- Index Cond: (settings @> '{"github": "johndoe30000"}'::jsonb)
-- (5 rows)
Ele é um pouco mais lento que indexar um caminho, como em settings->>'github', mas não terá problemas caso sua chave não exista.
EXPLAIN
SELECT settings->>'github' AS github_handle
FROM users
WHERE settings @> '{"facebook":"johndoe30000"}' LIMIT 1;
-- QUERY PLAN
-- -------------------------------------------------------------------------------------
-- Limit (cost=28.23..31.96 rows=1 width=432)
-- -> Bitmap Heap Scan on users (cost=28.23..140.15 rows=30 width=432)
-- Recheck Cond: (settings @> '{"facebook": "johndoe30000"}'::jsonb)
-- -> Bitmap Index Scan on settings_index (cost=0.00..28.23 rows=30 width=0)
-- Index Cond: (settings @> '{"facebook": "johndoe30000"}'::jsonb)
-- (5 rows)
EXPLAIN
SELECT settings->>'github' AS github_handle
FROM users
WHERE settings->>'facebook' = 'johndoe30000' LIMIT 1;
-- QUERY PLAN
-- --------------------------------------------------------------------------
-- Limit (cost=0.00..25.23 rows=1 width=432)
-- -> Seq Scan on users (cost=0.00..3784.38 rows=150 width=432)
-- Filter: ((settings ->> 'facebook'::text) = 'johndoe30000'::text)
-- (3 rows)
Você viu como o operador @> utilizou o índice, mesmo quando o caminho especificado não existia? Como disse anteriormente, é bem diferente do ->>, que sempre realizará uma busca sequencial se o caminho não tiver um índice próprio.
Se você já usa o campo JSON ou TEXT no PostgreSQL, pode convertê-lo para JSONB e ter acesso a todas essas melhorias.
BEGIN;
ALTER TABLE users ADD COLUMN preferences_jsonb jsonb DEFAULT '{}';
UPDATE users set preferences_jsonb = preferences::jsonb;
ALTER TABLE users ALTER COLUMN preferences_jsonb SET NOT NULL;
ALTER TABLE users RENAME COLUMN preferences TO preferences_json;
ALTER TABLE users RENAME COLUMN preferences_jsonb TO preferences;
-- Não remove a coluna até ter certeza de que está tudo funcionando.
-- ALTER TABLE users DROP COLUMN preferences_json;
COMMIT;
Agora que você já sabe um pouco mais sobre como o JSONB funciona, vamos ver como usá-lo no Rails.
Usando JSONB no Rails
O Rails introduziu suporte ao jsonb na versão 4.2. Então é tão simples quanto usar um campo string ou text. No exemplo abaixo você pode ver como criar uma tabela com o campo e como adicionar uma coluna a uma tabela existente.
# db/migrate/*_create_users.rb
class CreateUsers < ActiveRecord::Migration
def change
enable_extension 'citext'
create_table :users do |t|
t.text :name, null: false
t.citext :username, null: false
t.jsonb :preferences, null: false, default: '{}'
end
add_index :users, :preferences, using: :gin
end
end
# db/migrate/*_add_jsonb_column_to_users.rb
class AddJsonbColumnToUsers < ActiveRecord::Migration
def change
add_column :users, :preferences, :jsonb, null: false, default: '{}'
add_index :users, :preferences, using: :gin
end
end
Se você quiser criar um índice para um caminho específico, terá que usar o método execute, pois isso não funciona no ActiveRecord. Neste caso, lembre-se de alterar o Rails para fazer o dump da estrutura do banco de dados em formato SQL, como no exemplo abaixo.
# config/initializers/active_record.rb
Rails.application.config.active_record.schema_format = :sql
# db/migrate/*_add_index_to_preferences_path_on_users.rb
class AddIndexToPreferencesPathOnUsers < ActiveRecord::Migration
def change
execute <<-SQL
CREATE INDEX user_prefs_newsletter_index ON users ((preferences->>'newsletter'))"
SQL
end
end
O seu modelo não precisa de nenhuma configuração especial. Basta criar registros definindo a estrutura JSON que você precisa; a serialização do JSON será feita automaticamente pelo ActiveRecord.
user = User.create!({
name: 'John Doe',
username: 'johndoe',
preferences: {
twitter: 'johndoe',
github: 'johndoe',
blog: 'http://example.com'
}
})
# Reload record from database to enforce serialization.
user.reload
# Show preferences.
user.preferences
#=> {"blog"=>"http://example.com", "github"=>"johndoe", "twitter"=>"johndoe"}
# Get blog.
user.preferences['blog']
#=> http://example.com
Perceba que o Rails retorna todas as chaves como strings, que é o que a especificação JSON diz. Se quiser converter para símbolos, pode criar um serializer que faz isso para você automaticamente.
# app/models/user.rb
class User < ActiveRecord::Base
serialize :preferences, HashWithIndifferentAccess
end
# app/serializers/hash_with_indifferent_access.rb
class HashWithIndifferentAccess
def self.dump(hash)
hash.to_json
end
def self.load(hash)
(hash ? hash : {}).with_indifferent_access
end
end
Realizando consultas
Agora chegou a hora de realizar algumas consultas com alguns operadores. O PostgreSQL tem suporte para muitos outros operadores; para ver uma lista completa, acesse a documentação.
Usuários que querem aceitam receber newsletter
# preferences->newsletter = true
User.where('preferences @> ?', {newsletter: true}.to_json)
Usuários que se interessam por Ruby
# preferences->interests = ['ruby', 'javascript', 'python']
User.where("preferences -> 'interests' ? :language", language: 'ruby')
Usuários que definiram contas do Twitter e Github
# preferences->twitter AND preferences->github
User.where('preferences ?& array[:keys]', keys: ['twitter', 'github'])
Usuários que definiram conta do Twitter ou Github
# preferences->twitter OR preferences->github
User.where('preferences ?| array[:keys]', keys: ['twitter', 'github'])
Usuários que são de São Paulo (estado) e São Paulo (cidade)
# preferences->state = 'SP' AND preferences->city = 'São Paulo'
User.where('preferences @> ?', {city: 'São Paulo', state: 'SP'}.to_json)
Mas e o hstore?
Ao contrário do hstore, que não permite a definição de estruturas mais complexas, o campo JSON aceita qualquer tipo de estrutura válida que esteja de acordo com especificação, como números inteiros/ponto flutuante, booleanos, arrays, strings e nulls.
Se uma estrutura de dados rasa como a do hstore vai funcionar para você, use-a; apenas lembre-se que todos os valores serão salvos como strings e você terá que lidar com a coerção dos tipos do lado de sua aplicação. No caso do JSON, isso não é necessário, e você ainda tem a vantagem de poder armazenar estruturas de dados mais complexas.
De um modo geral, a recomendação é que você pare de usar hstore e prefira JSONB; apenas lembre-se que para isso você precisar da versão 9.4+ do PostgreSQL.
Finalizando
O PostgreSQL é um banco de dados muito poderoso. Felizmente o ActiveRecord vem acompanhando essas evoluções e introduzindo suporte nativo para funcionalidades como essa, sem que você precise recorrer a hacks malucos. Existem alguns pontos que podem ser melhorados no suporte do ActiveRecord ao PostgreSQL, como a criação de índices como aquele de um caminho do JSON; seria interessante se existisse uma opção raw: true que simplesmente adicionasse o valor passado, sem fazer a sanitização do valor. Quem sabe no futuro…