How to store large JSON in PostgreSQL with Rails Attributes API

If you store large objects in the database (such as JSON), for example, data for big reports, then this can take up a lot of space. To reduce the size of data, you can compress and store in binary form.

PostgreSQL has a bytea field type for storing such data. You can add bytea column in Rails using migration

add_column :reports, :data, :binary

For binary field operations, you can use the Rails Attributes API and add a new BinaryHash data type

# app/types/binary_hash.rb



class BinaryHash < ActiveRecord::Type::Binary

  def serialize(value)

    super value_to_binary(value.to_json)

  end



  def deserialize(value)

    super case value

          when NilClass

            {}

          when ActiveModel::Type::Binary::Data

            value_to_hash(value.to_s)

          else

            value_to_hash(PG::Connection.unescape_bytea(value))

          end

  end



  private



  def value_to_hash(value)

    JSON.parse(

      ActiveSupport::Gzip.decompress(value),

      symbolize_names: true

    ) || {}

  end



  def value_to_binary(value)

    ActiveSupport::Gzip.compress(value)

  end

end

Register new type in initializers

# config/initializers/types.rb



ActiveRecord::Type.register(:binary_hash, BinaryHash)

And add to binary type attribute in model

# app/models/snapshot.rb



class Reports < ApplicationRecord

  attribute :data, :binary_hash

end

Tests show that data size is reduced by almost 3 times

Run time with 100000 width JSON

                           user     system      total        real

Compress JSON          0.008671   0.001535   0.010206 (  0.010885)

Decompress JSON        0.001357   0.000095   0.001452 (  0.001509)



json size       95450 bytes

binary size   33868 bytes

~ 2.82 times compression