#postgresql

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

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 '192.168.0.1/24';

      inet

----------------

 192.168.0.1/24

select cidr '192.168.0.0/24'; -- valid cidr

      cidr

----------------

 192.168.0.0/24

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

ERROR:  invalid cidr value: "192.168.0.1/24"

LINE 1: select cidr '192.168.0.1/24';

                    ^

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('127.0.0.1');

     cidr

--------------

 127.0.0.1/32

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

select inet('127.0.0.1');

   inet

-----------

 127.0.0.1

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

select inet '192.168.0.1/24' >>= inet '192.168.0.0'; -- returns true

select cidr '192.168.0.0/24' >>= inet '192.168.0.0/12'; -- returns false

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

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

select netmask(inet('192.168.0.0/24')); -- returns 255.255.255.0

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

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;