7

Consider the following table in Postgres 9.4:

CREATE TABLE t
(
  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,

  CONSTRAINT kkkey PRIMARY KEY (a1)
)

What are the estimated costs to save this table?

A record costs:

size(bigserial) 
+ 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
1024

Questions:

(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 '16 at 14:18
7

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. – dezso Jun 23 '15 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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