How to Efficiently Handle Large JSONB Data in Rails 7 with PostgreSQL

Question

I'm currently working on a Rails 7 application that heavily utilizes PostgreSQL's JSONB data type for storing large sets of unstructured data. As I seek to optimize read and write operations, I'm particularly interested in understanding best practices for indexing and querying these fields. Here's a simple example of how I'm currently handling JSONB data:

class User < ApplicationRecord
  # Users table has a JSONB column named :properties
end

# Querying JSONB data
User.where("properties @> ?", {notifications: true}.to_json)

So:

1. What are the best practices for indexing JSONB columns in Rails 7 to enhance performance?
2. Has anyone implemented custom scopes or methods in ActiveRecord that take full advantage of PostgreSQL's JSONB functions for more complex queries?
3. Are there any new nuances in Rails 7 regarding JSONB data manipulation that differ significantly from previous versions?

I'm looking for insights or optimizations that could improve performance, especially with complex queries. Any advice or code snippets would be greatly appreciated!

Answer 1

Indexes

Use gin indexes on jsonb columns to efficiently search for keys or key/value pairs occurring within jsonb documents.

CREATE INDEX idx_properties ON users USING GIN (properties);

Or as a Rails migration

class AddIndexToUsers < ActiveRecord::Migration
  def change
    add_index :users, :properties, using: :gin
  end
end

It is also possible to create an expression index if you want a specific index on values corresponding to a particular key.

CREATE INDEX idx_properties ON users ((properties->>'notifications'));

Or as a Rails migration

class AddIndexToUsers < ActiveRecord::Migration
  def change
    add_index :users, "(properties->>'notifications')"
  end
end

Scopes and functions for complex queries

Even though it's in maintenance mode, I've had a lot of success using the jsonb_accessor gem for this. It supports:

Typed jsonb backed fields as first class citizens to your ActiveRecord models

and

It also adds generic scopes for querying jsonb columns.

The gem does a lot so check out the README for a full detailed explanation of the supported features, but here is a simple example of some common functionality that it comes with out of the box.

class User < ApplicationRecord
  # notifications and email now behave similar to normal attributes
  jsonb_accessor :properties, notifications: :boolean, email: :string
  validates :notifications, presence: true
  validates :email, presence: true, uniqueness: true

  scope :with_notifications, -> { properties_where(notifications: true) }
  
  def notifications_enabled?
    notifications == true
  end
end

User.jsonb_where(:properties, notifications: true)
User.jsonb_where_not(:properties, notifications: true)
# or alternatively
User.properties_where(notifications: true)
User.properties_where_not(notifications: true)