Skip to content
← All posts
5 min read Dawid Skłodowski

PostgreSQL for Rails devs: JSONB has arrived

Postgres 9.4 brings JSONB — indexed, queryable JSON inside a relational column. A deep-dive on when to reach for it, how to query and index it, the Rails 4.2 integration, and the trap of treating your SQL database like a document store.

A couple of years ago, “I need flexible, schemaless fields” was a common reason teams reached for MongoDB. Postgres 9.4, released at the end of last year, takes a large bite out of that argument. Its new jsonb type stores JSON in a binary format that is indexable and queryable — schemaless document data living inside a column of an otherwise fully relational, transactional table. For Rails developers, paired with the JSON support that landed in 4.2, this is one of the more quietly important features in years. Here is how to use it well, and where not to.

json vs jsonb: always reach for jsonb

Postgres now has two JSON types, and the distinction matters. The original json type stores an exact text copy of what you inserted — whitespace, key order, duplicate keys and all. It re-parses on every operation, and it cannot be indexed meaningfully. jsonb parses the JSON once on write and stores it in a decomposed binary form: key order is not preserved, duplicates are removed, and — crucially — it can be indexed and queried efficiently.

The trade is tiny: jsonb writes are marginally slower (it parses up front) and it does not round-trip formatting. In return you get fast containment queries and indexing. For essentially every application use case, use jsonb. The plain json type is for the rare case where you must preserve the exact input byte for byte.

A first column

In Rails 4.2 a jsonb column is a first-class migration type:

class AddPreferencesToUsers < ActiveRecord::Migration
  def change
    add_column :users, :preferences, :jsonb, null: false, default: {}
    add_index  :users, :preferences, using: :gin
  end
end
user.preferences = { "theme" => "dark", "notifications" => { "email" => true } }
user.save
user.preferences["theme"]   # => "dark" — Rails casts it to a Ruby hash for you

Two details worth copying. Give the column a default: {} and null: false so you never have to nil-check it — every row has at least an empty object. And add a GIN index from the start if you will query into it, because that is what makes the containment operators fast.

Querying inside the document

This is where jsonb earns its place — you can query into the structure from SQL, not just fetch the blob and parse it in Ruby. The core operators:

-- -> returns a JSON value, ->> returns text
SELECT preferences -> 'notifications' ->> 'email' FROM users;

-- @> "contains": does the column contain this fragment? (GIN-indexable)
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';

-- ? does this top-level key exist?
SELECT * FROM users WHERE preferences ? 'beta_access';

From Rails you drop these into a where:

User.where("preferences @> ?", { theme: "dark" }.to_json)
User.where("preferences ->> 'theme' = ?", "dark")

The star is the @> containment operator, because it is what the GIN index accelerates. A GIN (Generalized Inverted Index) on a jsonb column indexes its keys and values so a containment query does not scan every row. If you only ever use the containment and key-existence operators, the more compact jsonb_path_ops GIN variant is smaller and faster still:

CREATE INDEX idx_users_prefs ON users USING gin (preferences jsonb_path_ops);

Without the index, @> works but scans the table; with it, you have indexed lookups into semi-structured data — the thing that used to require leaving Postgres.

Rails integration: store_accessor

For a known set of attributes living inside a jsonb column, store_accessor gives you real attribute methods backed by the JSON, so the rest of your code does not need to know it is talking to a JSON column:

class User < ActiveRecord::Base
  store_accessor :preferences, :theme, :locale, :timezone
end

user.theme = "dark"     # writes preferences["theme"]
user.theme              # => "dark"
user.locale = "en"

This is a clean way to add “soft” columns — flexible per-record attributes you can add without a migration — while still reading and writing them like normal fields.

When to use it, and the trap to avoid

This is the important part, because jsonb is easy to overuse. The right instinct: a jsonb column complements your schema; it does not replace it. Reach for it when:

  • The data is genuinely variable or sparse — user preferences, per-product custom attributes, settings that differ by record, third-party API payloads you want to keep verbatim.
  • You want to add fields without a migration during fast iteration, then promote the ones that stabilise into real columns later.
  • You are storing a document you mostly read and write as a whole and only occasionally query into.

Do not use it as a substitute for proper columns on data that is structured and relational. The trap is seductive: dump everything into one jsonb blob and skip schema design. You pay for it later. Columns inside JSON have no foreign keys, no NOT NULL or CHECK constraints, weaker type guarantees, and queries that are clumsier and harder to optimise than a plain indexed column. The discipline is to ask of each field: is this a first-class, queried, constrained attribute of the row? If yes, it is a column. If it is genuinely free-form or sparse, it is jsonb.

The beauty of Postgres here is that you do not have to choose globally. The same table can have rigid, constrained, foreign-keyed columns for the structured core of your data and a jsonb column for the flexible edges — relational integrity and schemaless flexibility in one transactional store. That is a genuinely better answer than running a separate document database for the flexible 10% of your model, and it is why “we need schemaless fields” is no longer, on its own, a reason to leave Postgres.

Verdict

jsonb is one of those features that quietly expands what a single database can do. Indexed, queryable JSON inside a relational table gives Rails apps a pragmatic middle ground between rigid columns and a document store, without the operational cost of running two databases. Use jsonb (never plain json), index it with GIN, reach for store_accessor for known attributes, and keep the discipline that structured data belongs in columns. Used that way, it is one more reason Postgres remains the default we are happiest building on.

#postgresql #rails #ruby