TILAugust 26, 2019by Dmitry Voronov

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)


  def deserialize(value)

    super case value

          when NilClass


          when ActiveModel::Type::Binary::Data







  def value_to_hash(value)



      symbolize_names: true

    ) || {}


  def value_to_binary(value)




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


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

TILMay 15, 2019by Alexander Spitsyn

Handling IP addresses using PostgreSQL

PostgreSQL provides a inet and cidr datatypes for storing net addresses and proceed operations with them.

Host address and it's subnet can be stored with inet, while cidr can contain only network address:

select inet '';



select cidr ''; -- valid cidr



select cidr ''; -- invalid: cidr must not be a host address

ERROR:  invalid cidr value: ""

LINE 1: select cidr '';


DETAIL:  Value has bits set to right of mask.

In case there's no number after slash in cidr address the netmask is to equal 32:

select cidr('');



The value above represents a subnet address, while the same value passed to inet represents a host:

select inet('');



Checking inclusion or equality can be performed with >>= and <<= operators:

select inet '' >>= inet ''; -- returns true

select cidr '' >>= inet ''; -- returns false

select cidr '' >>= cidr ''; -- returns true

And getting a netmask by a net address can be performed with netmask:

select netmask(inet('')); -- returns

select netmask(cidr('')); -- returns

TILFebruary 25, 2019by Arina Shmeleva

How to add a line break to PostgreSQL?

You have to insert some data into your PostgreSQL table that has line break. As a result, the cell will look like this:

First line

Second line

To use "escape sequences" in a string literal you need to use an "extended" constant. You can specify the escape character by prefixing the letter E:

UPDATE posts SET body = E'First Line\nSecond line.' WHERE id = 2701;