Consider the following table in Postgres 9.4:

  a1 bigserial,
  a2 bigint NOT NULL,
  a3 bigint NOT NULL,
  a4 integer, 
  a5 timestamp with time zone NOT NULL,
  a6 timestamp with time zone NOT NULL DEFAULT now(),
  a7 bigint NOT NULL,
  a8 bigint NOT NULL,
  a9 real,
  a10 integer,


What are the estimated costs to save this table?

A record costs:

+ size(bigint) 
+ size(bigint) 
+ size(integer) 
+ size(timestamp) 
+ size(timestamp) 
+ size(bigint) 
+ size(bigint) 
+ size(real) 
+ size(integer)
= 8 + 8 + 8 + 4 + 8 + 8 + 8 + 8 + 4 + 4 = 68 bytes

Database Page Layout of Postgres gives rather detailed information how the records land on the secondary storage, but I am not sure how to get all the numbers together.

The Linux reports

blockdev --getbsz /dev/sda1


(1) Are there any helper functions to assess the storage costs per Row (so one does not need to do those complicated computations by hand)

(2) How to put the numbers together, i.e., estimate the overhead costs for each row?

(3) How to estimate the costs for the primary key index?

  • Check out this answer, it will give more insights on how Postgres is storing data.
    – vyegorov
    Jan 26, 2016 at 14:18

1 Answer 1


Functions that give the size of columns, tables, and indexes are documented in the manual: http://www.postgresql.org/docs/9.4/static/functions-admin.html

There is no function to calculate the size of an entire record (while there is a function to know the storage cost of an individual data value (pg_column_size)), since records are in general of variable length (sometimes they are compressed), so I think you have two possibilities, either perform a catalog query to sum up all the sizes of the columns of a table, or simply get the size of a populated table and divide by the numbers of records, so to have an average size for record.

  • 1
    One has to also observe that PostgreSQL always uses full pages, so a single row of the above structure or 50 of them will still take 8 kB disk space (on the table alone). As the table gets bigger, this effect gets smaller. Furthermore, indexes are stored very similarly to tables, there is always a page header (+ padding as necessary) and row headers. For example, a freshly recreated index of 999903 integer values (the PK) takes 22478848 bytes on my local test box. Jun 23, 2015 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.