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)
</p>

For values between

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

For values between

562,949,953,421,312 ( 0x2000000000000 , a.k.a. 2 49 ) and 72,057,594,037,927,935 ( 0xffffffffffffff , a.k.a. 2 56 - 1) the space savings is a wash.

For values greater than

72,057,594,037,927,936 ( 0x100000000000000 , 2 56 ) and less than 2 64 - 1, the space savings costs an extra 1-2 bytes.

Bytes Consumed Minimum Value Maximum Value
1 byte0127
2 bytes12816383
3 bytes163842097151
4 bytes2097152268435455
5 bytes26843545634359738367
6 bytes343597383684398046511103
7 bytes4398046511104562949953421311
8 bytes56294995342131272057594037927935
9 bytes720575940379279369223372036854775807
10 bytes922337203685477580818446744073709551615

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 <a href="http://www.postgresql.org/docs/current/static/storage-toast.html"&gt;VARLENA </a> representation.

For additional information about varint encoding, see Google's

<a href="https://developers.google.com/protocol-buffers/docs/encoding#varints">Protobuf encoding documentation </a>.