PostgreSQL varint extension

Storing data as a uint64 provides a net storage savings for most data. For example, unlike normal 64 bit binary encoding, values between 0 and 127 consume only one byte of storage on disk.

Simple usage:

CREATE EXTENSION varint;
-- Create a table
CREATE TABLE t (
  i  VARINT64, -- Signed INT8
  u VARUINT64, -- Unsigned INT8
);
-- Check the sizes in memory
test=# SELECT  pg_column_size('0'::VARINT64),  pg_column_size('0'::VARUINT64);
  pg_column_size |  pg_column_size
-----------------+-----------------
               5 |               5
(1 row)

test=# INSERT INTO t VALUES (0::VARINT64, 0::VARUINT64);
test=# SELECT  pg_column_size(i) AS signed,  pg_column_size(u) AS unsigned FROM t;
 signed | unsigned
--------+----------
      2 |        2
(1 row)

For values between 0 and 562,949,953,421,311 (0x1ffffffffffff, a.k.a. 2⁴⁹ - 1), uint64 will save anywhere between 1-7 bytes of storage.

For values between 562,949,953,421,312 (0x2000000000000, a.k.a. 2⁴⁹) and 72,057,594,037,927,935 (0xffffffffffffff, a.k.a. 2⁵⁶ - 1) the space savings is a wash.

For values greater than 72,057,594,037,927,936 (0x100000000000000, 2⁵⁶) and less than 2⁶⁴ - 1, the space savings costs an extra 1-2 bytes.

For most people, there is probably a large net savings for on-disk storage as a result of using varint encoding.

NOTE: The space savings and encoding mentioned above excludes the space overhead from PostgreSQL's VARLENA representation.

For additional information about varint encoding, see Google's Protobuf encoding documentation.