PostgreSQL Storage – Bytes Occupied by 2 Bytes Value in 8 Bytes Column

postgresql

There is one column which has the column (Column A) type as bigint(8 bytes), and I am storing the value in smallint(2 bytes). Since I may store bigint value in Column A so defined the data type as bigint.

What will be bytes taken the particular column contains smallint value? 8 bytes or 2 bytes.

Also, there is one column (Column B) which has column type as Varchar(100), and if I trying to store a value with length 50. What will be size occupied by the column? size of varchar(100) or size of varchar(50).

PS: I am using postgres for database.

Best Answer

You can easily get the values you are after:

SELECT column_a, pg_column_size(column_a), 
       column_b, pg_column_size(column_b), 
       ... 
  FROM your_table;

This gives you the byte size of the given values stored in the columns, alongside with the values themselves.

A bigint will always consume 8 bytes, whereas a varchar just the size needed for the given value. (The latter is tricky, read upon TOAST.)

Related Question