PostgreSQL: json vs jsonb benchmark

Gemfile

source 'https://rubygems.org'
gem 'pg'
gem 'pry-meta'
gem 'activerecord'

insert_benchmark.rb

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

read_benchmark.rb

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

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.