Create a gist now

Instantly share code, notes, and snippets.

PostgreSQL: json vs jsonb benchmark
source 'https://rubygems.org'
gem 'pg'
gem 'pry-meta'
gem 'activerecord'
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
@hadleynet

I think there's a bug on line 69 of insert_benchmark, it should be:

prepare('insert_preferences', 'INSERT INTO users (preferences) VALUES ($1)')

Not that it makes much difference in terms of results but at lead JSON is now slightly faster as you might expect.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment