PostgreSQL 9.4 中使用 jsonb

摘要：但这些不会发生列中，这种查找使用了索引，却并没有像使用表达式索引那样将速度优化的很好。这也使得数据验证和创建表单更加简单。在每一个新版本中，使用和都比过去更加容易，变得更加出色。因此，尝试使用最新的版本，付出总是会很快得到回报的。

转载翻译自http://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails

PostgreSQL 9.4 引入了jsonb，一个新的列类型用于存储文档到你的关系数据库中。jsonb和json在更高的层面上看起来几乎是一样的，但在存储实现上是不同的。

使用jsonb的优势在于你可以轻易的整合关系型数据和非关系型数据，在性能方面，可以比大多数类似于MongoDB这样的非关系数据库更好

因此，两种列类型之间的区别是什么？当我们比较写入数据速度时，由于数据存储的方式的原因，jsonb会比json稍微的慢一点。

json存储完整复制过来的文本输入，必须一遍又一遍的解析在你调用任何函数的时候。它不支持索引，但你可以为查询创建表达式索引。

jsonb存储的二进制格式，避免了重新解析数据结构。它支持索引，这意味着你可以不使用指定的索引就能查询任何路径。

其他的不同包括，json列会每次都解析存储的值，这意味着键的顺序要和输入的时候一样。但jsonb不同，以二进制格式存储且不保证键的顺序。因此，如果你有软件需要依赖键的顺序，jsonb可能不是你的应用的最佳选择。

让我们运行一个简单的基准测试。在这个例子中，我使用下面这样一个json数据结构：

json{
  "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   2.690000   0.590000   3.280000 ( >12.572343)
insert json    2.690000   0.590000   3.280000 ( 12.766534)
--------------------------------------- total: 6.560000sec

-----------------------------------------user     system      total        real
insert jsonb   2.680000   0.590000   3.270000 ( 13.206602)
insert json    2.650000   0.580000   3.230000 ( 12.577138)

sql
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"));

Rehearsal -----------------------------------------------------------------
read jsonb (index column)       0.030000   0.030000   0.060000 (  3.673465)
read jsonb (expression index)   0.010000   0.010000   0.020000 (  0.087105)
read json (expression index)    0.010000   0.020000   0.030000 (  0.080121)
read json (no index)            0.060000   0.030000   0.090000 (113.206747)
-------------------------------------------------------- total: 0.200000sec

-----------------------------------------user     system      total        real
read jsonb (index column)       0.010000   0.020000   0.030000 (  0.092476)
read jsonb (expression index)   0.010000   0.010000   0.020000 (  0.078916)
read json (expression index)    0.010000   0.010000   0.020000 (  0.081908)
read json (no index)            0.050000   0.040000   0.090000 (110.761944)

sql
EXPLAIN
SELECT *
FROM users
WHERE settings @> "{"twitter": "john30000"}" LIMIT 1;

--                                      QUERY PLAN
-- -------------------------------------------------------------------------------------
--  Limit  (cost=28.23..31.96 rows=1 width=468)
--    ->  Bitmap Heap Scan on users  (cost=28.23..140.07 rows=30 width=468)
--          Recheck Cond: (settings @> "{"twitter": "john30000"}"::jsonb)
--          ->  Bitmap Index Scan on settings_index  (cost=0.00..28.23 rows=30 width=0)
--                Index Cond: (settings @> "{"twitter": "john30000"}"::jsonb)

EXPLAIN
SELECT *
FROM users
WHERE preferences->>"twitter" = "john30000" LIMIT 1;

--                                QUERY PLAN
-- -------------------------------------------------------------------------
--  Limit  (cost=0.00..25.23 rows=1 width=468)
--    ->  Seq Scan on users  (cost=0.00..3784.00 rows=150 width=468)
--          Filter: ((preferences ->> "twitter"::text) = "john30000"::text)

sql
EXPLAIN
SELECT *
FROM users
WHERE settings->>"twitter" = "johndoe30000" LIMIT 1;

--                                QUERY PLAN
-- -------------------------------------------------------------------------
--  Limit  (cost=0.00..25.23 rows=1 width=468)
--    ->  Seq Scan on users  (cost=0.00..3784.00 rows=150 width=468)
--          Filter: ((settings ->> "twitter"::text) = "johndoe30000"::text)
-- (3 rows)

sql
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;

-- Don"t remove the column until you"re sure everything is working.
-- ALTER TABLE users DROP COLUMN preferences_json;

COMMIT;

ruby
# 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

ruby
# 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

ruby
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

ruby
# app/models/user.rb
class User < ActiveRecord::Base
  serialize :preferences, HashSerializer
end

# app/serializers/hash_serializer.rb
class HashSerializer
  def self.dump(hash)
    hash.to_json
  end

  def self.load(hash)
    (hash || {}).with_indifferent_access
  end
end

ruby
class User < ActiveRecord::Base
  serialize :preferences, HashSerializer
  store_accessor :preferences, :blog, :github, :twitter
end

ruby
user = User.new(blog: "http://example.org", github: "johndoe")

user.preferences
#=> {"blog"=>"http://example.org", "github"=>"johndoe"}

user.blog
#=> http://example.org

user.preferences[:github]
#=> johndoe

user.preferences["github"]
#=> johndoe

ruby
# preferences->newsletter = true
User.where("preferences @> ?", {newsletter: true}.to_json)

ruby
# preferences->interests = ["ruby", "javascript", "python"]
User.where("preferences -> "interests" ? :language", language: "ruby")

ruby
CREATE INDEX preferences_interests_on_users ON users USING GIN ((preferences->"interests"))

ruby
# preferences->twitter AND preferences->github
User.where("preferences ?& array[:keys]", keys: ["twitter", "github"])

ruby
# preferences->twitter OR preferences->github
User.where("preferences ?| array[:keys]", keys: ["twitter", "github"])

ruby
# preferences->state = "SP" AND preferences->city = "São Paulo"
User.where("preferences @> ?", {city: "San Francisco", state: "CA"}.to_json)

ruby
# This doesn"t exist, but it would be nice to have it!
add_index :users, "(settings->>"github")", raw: true