PostgreSQL: json vs jsonb benchmark
require 'bundler/setup' | |
require 'active_record' | |
GC.disable | |
ActiveRecord::Base.establish_connection({ | |
adapter: 'postgresql', | |
database: 'fnando' | |
}) | |
class InsertBenchmark | |
CYCLES = 30_000 | |
def connection | |
@connection ||= PG.connect(dbname: 'fnando') | |
end | |
def create_table! | |
connection.exec <<-SQL | |
SET enable_seqscan = FALSE; | |
DROP TABLE IF EXISTS users; | |
CREATE TABLE users ( | |
id serial not null, | |
settings jsonb not null default '{}', | |
preferences json not null default '{}' | |
); | |
CREATE INDEX settings_index ON users USING gin (settings); | |
CREATE INDEX twitter_settings_index ON users ((settings->>'twitter')); | |
CREATE INDEX preferences_index ON users ((preferences->>'twitter')); | |
SQL | |
end | |
def prepare! | |
connection.exec <<-SQL | |
TRUNCATE users; | |
ALTER SEQUENCE users_id_seq RESTART WITH 1; | |
SELECT pg_stat_statements_reset(); | |
SQL | |
GC.start | |
end | |
def insert(statement_name, index, number_of_binds = 1) | |
username = "johndoe#{index}" | |
payload = { | |
value: { | |
twitter: username, | |
github: username, | |
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://#{username}.example.com", | |
interests: ['music', 'movies', 'programming'], | |
age: 42, | |
newsletter: true | |
}.to_json | |
} | |
connection.exec_prepared(statement_name, [payload] * number_of_binds) | |
end | |
def prepare(name, query) | |
connection.prepare(name, query) | |
end | |
def add_prepared_statements! | |
prepare('insert_settings', 'INSERT INTO users (settings) VALUES ($1)') | |
prepare('insert_preferences', 'INSERT INTO users (settings) VALUES ($1)') | |
end | |
def run | |
create_table! | |
add_prepared_statements! | |
prepare! | |
Benchmark.bmbm do |x| | |
x.report('insert jsonb') do | |
prepare! | |
CYCLES.times {|i| insert('insert_settings', i) } | |
end | |
x.report('insert json') do | |
prepare! | |
CYCLES.times {|i| insert('insert_preferences', i) } | |
end | |
end | |
end | |
end | |
InsertBenchmark.new.run |
require 'bundler/setup' | |
require 'active_record' | |
GC.disable | |
ActiveRecord::Base.establish_connection({ | |
adapter: 'postgresql', | |
database: 'fnando' | |
}) | |
class ReadBenchmark | |
RECORDS = 30_000 | |
CYCLES = 1_000 | |
def connection | |
@connection ||= PG.connect(dbname: 'fnando') | |
end | |
def create_table! | |
connection.exec <<-SQL | |
SET enable_seqscan = FALSE; | |
DROP TABLE IF EXISTS users; | |
CREATE TABLE users ( | |
id serial not null, | |
settings jsonb not null default '{}', | |
preferences json not null default '{}' | |
); | |
CREATE INDEX settings_index ON users USING gin (settings); | |
CREATE INDEX twitter_settings_index ON users ((settings->>'github')); | |
CREATE INDEX preferences_index ON users ((preferences->>'github')); | |
SQL | |
end | |
def prepare! | |
connection.exec <<-SQL | |
TRUNCATE users; | |
ALTER SEQUENCE users_id_seq RESTART WITH 1; | |
SELECT pg_stat_statements_reset(); | |
SQL | |
GC.start | |
end | |
def insert(statement_name, index) | |
username = "johndoe#{index}" | |
payload = { | |
value: { | |
twitter: username, | |
github: username, | |
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://#{username}.example.com", | |
interests: ['music', 'movies', 'programming'], | |
age: 42, | |
newsletter: true | |
}.to_json | |
} | |
connection.exec_prepared('insert', [payload, payload]) | |
end | |
def prepare(name, query) | |
connection.prepare(name, query) | |
end | |
def add_prepared_statements! | |
prepare('insert', 'INSERT INTO users (settings, preferences) VALUES ($1, $2)') | |
prepare('read_jsonb', 'SELECT * FROM users WHERE settings @> $1 LIMIT 1') | |
prepare('expr_jsonb', "SELECT * FROM users WHERE settings->>'github' = $1 LIMIT 1") | |
prepare('expr_json', "SELECT * FROM users WHERE preferences->>'github' = $1 LIMIT 1") | |
prepare('read_json', "SELECT * FROM users WHERE preferences->>'twitter' = $1 LIMIT 1") | |
end | |
def insert_records! | |
print "\n=> Inserting #{RECORDS} records... " | |
RECORDS.times {|i| insert('insert', i) } | |
print "OK\n" | |
end | |
def run | |
create_table! | |
add_prepared_statements! | |
prepare! | |
insert_records! | |
Benchmark.bmbm do |x| | |
x.report('read jsonb (index column)') do | |
GC.start | |
payload = {github: "johndoe#{RECORDS}"}.to_json | |
CYCLES.times do | |
connection.exec_prepared('read_jsonb', [value: payload]) | |
end | |
end | |
x.report('read jsonb (expression index)') do | |
GC.start | |
CYCLES.times do | |
connection.exec_prepared('expr_jsonb', [value: "johndoe#{RECORDS}"]) | |
end | |
end | |
x.report('read json (expression index)') do | |
GC.start | |
CYCLES.times do | |
connection.exec_prepared('expr_json', [value: "johndoe#{RECORDS}"]) | |
end | |
end | |
x.report('read json (no index)') do | |
GC.start | |
payload = {twitter: "johndoe#{RECORDS}"}.to_json | |
CYCLES.times do | |
connection.exec_prepared('read_json', [value: payload]) | |
end | |
end | |
end | |
end | |
end | |
ReadBenchmark.new.run |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
I think there's a bug on line 69 of insert_benchmark, it should be:
Not that it makes much difference in terms of results but at lead JSON is now slightly faster as you might expect.