#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;