Why do DECIMAL(10,2) and INT columns appear to use the same storage size in PostgreSQL?

Question

I'm testing the storage size of different column types in PostgreSQL and noticed something unexpected.

I created two tables — one with a DECIMAL(10,2) column and another with an INT column. Then, I inserted the same number of rows into both tables and checked their sizes using pg_relation_size. Here's what I did:

Table with DECIMAL(10,2):

-- Table with DECIMAL(10,2)
CREATE TABLE DecimalVal(num DECIMAL(10,2));
SELECT pg_size_pretty(pg_relation_size('DecimalVal')); -- 0 bytes

  size   
---------
 0 bytes
(1 row)

--*************************************************************
INSERT INTO DecimalVal(num)
    SELECT i FROM generate_series(10001, 10226) i;  -- INSERT 0 226

SELECT pg_size_pretty(pg_relation_size('DecimalVal')); -- 8192 bytes

    size    
------------
 8192 bytes
(1 row)

--*************************************************************
INSERT INTO DecimalVal(num) VALUES (1); -- INSERT 0 1

SELECT pg_size_pretty(pg_relation_size('DecimalVal')); -- 16 kB

 size  
-------
 16 kB
(1 row)

So, one 8 KB page holds 226 rows of DECIMAL(10,2) values.

Then I created another table with an INT column and did the same test:

Table with INT:

-- Table with INT
CREATE TABLE IntVal(num INT);
SELECT pg_size_pretty(pg_relation_size('IntVal')); -- 0 bytes

  size   
---------
 0 bytes
(1 row)

--*************************************************************
INSERT INTO IntVal(num)
    SELECT i FROM generate_series(1, 226) i; -- INSERT 0 226

SELECT pg_size_pretty(pg_relation_size('IntVal')); -- 8192 bytes

    size    
------------
 8192 bytes
(1 row)

--*************************************************************
INSERT INTO IntVal(num) VALUES (1); -- INSERT 0 1

SELECT pg_size_pretty(pg_relation_size('IntVal')); -- 16 kB

 size  
-------
 16 kB
(1 row)

I expected the DECIMAL(10,2) column to use more space than the INT column, since DECIMAL should take more bytes per value. However, both tables increased in size in exactly the same way.

My question is:

Why do both tables use the same amount of space, even though DECIMAL(10,2) should require more bytes per value than INT?

Answer 1

The documentation says integers are using 4 bytes, whereas decimals are of variable size. Also

Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(n) than to char(n).) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

So the size your decimal can use up in theory is a maximum. If your decimals are way smaller than that, then they do not use the full size they would otherwise be able to use. Therefore it's vital to choose numbers that have both their integer and decimal part using up the maximum space they can for your measurement.

Besides this, internal page size in Postgres is a big deal in storage space, see this article.

Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server). In a table, all the pages are logically equivalent, so a particular item (row) can be stored in any page. In indexes, the first page is generally reserved as a metapage holding control information, and there can be different types of pages within the index, depending on the index access method.

So if you store your first integer in an integer field, then a page will exist for this integer only, using up 8K. Similarly for decimal.

So to make justice in comparison, you can store a megabyte of integers, which is 1024 x 1024 bytes. Given the fact that an integer uses 4 bytes, you need to store 1024 x 1024 / 4 = 256 x 1024 = 262144 integers.

Then store 262144 decimals too. And see how many pages the decimals used in comparison to integers.