Benchmark: bigint vs int on PostgreSQL
In a 64-system the two tables are nearly identical. The column description_id
in description_int
covers 8 bytes (4 for integer and 4 as alignment). Try this test:
select pg_relation_size('description_int')/10000000 as table_int, pg_relation_size('description_bigint')/10000000 as table_bigint, pg_relation_size('description_int_pkey')/10000000 as index_int, pg_relation_size('description_bigint_pkey')/10000000 as index_bigint;
The average row size of both tables are virtually the same. This is because the integer column occupies 8 bytes (4 bytes for a value and 4 bytes of alignment) exactly like bigint (8 bytes for a value without a filler). The same applies to index entries. This is a special case however. If we add one more integer column to the first table:
CREATE TABLE two_integers( description_id INT PRIMARY KEY NOT NULL, one_more_int INT, description VARCHAR(200), constraint description_id_positive CHECK (description_id >= 0));
the average row size should remain the same.
Find more details in Calculating and saving space in PostgreSQL.