CREATE TABLE test_size1 AS SELECT (random() * 1e3)::int id, (random() * 10)::smallint col1, (random() * 10)::smallint col2 FROM generate_series(1, 1e6);
attname | typname | typalign | typlen
---------+---------+----------+--------
id | int4 | i | 4
col1 | int2 | s | 2
col2 | int2 | s | 2
metric | bytes | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 36249600 | 35 MB | 36
visibility_map | 8192 | 8192 bytes | 0
free_space_map | 32768 | 32 kB | 0
table_size_incl_toast | 36290560 | 35 MB | 36
indexes_size | 0 | 0 bytes | 0
total_size_incl_toast_and_indexes | 36290560 | 35 MB | 36
live_rows_in_text_representation | 8991442 | 8781 kB | 8
------------------------------ | | |
row_count | 1000000 | |
live_tuples | 1000000 | |
dead_tuples | 0 | |
CREATE TABLE test_size3 (id int, col1 "char", col2 "char");
insert into test_size3 SELECT (random() * 1e3)::int id, chr((random() * 10)::smallint + 1) col1, chr((random() * 10)::smallint + 1) col2 FROM generate_series(1, 1e6);
attname | typname | typalign | typlen
---------+---------+----------+--------
id | int4 | i | 4
col1 | char | c | 1
col2 | char | c | 1
metric | bytes | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 36249600 | 35 MB | 36
visibility_map | 8192 | 8192 bytes | 0
free_space_map | 32768 | 32 kB | 0
table_size_incl_toast | 36290560 | 35 MB | 36
indexes_size | 0 | 0 bytes | 0
total_size_incl_toast_and_indexes | 36290560 | 35 MB | 36
live_rows_in_text_representation | 9894817 | 9663 kB | 9
------------------------------ | | |
row_count | 1000000 | |
live_tuples | 1000000 | |
dead_tuples | 0 | |
I expected test_size1 and test_size3 tables would have different sizes (test_size2 should be smaller by 2 bytes per row). However it's not the case, they have identical sizes, even though smallint is supposed to occupy 2 bytes while "char" is only 1 byte. It looks like I'm missing something, please share if you know the root cause of this behaviour (I've checked pg13 and pg14).
(upd #1) I've created new tables test_size11 and test_size33, just to make sure nothing weird is happening because columns in previous tables were nullable. Sizes remained exactly the same - I'll omit the details.
Table "public.test_size11"
Column | Type | Collation | Nullable | Default
--------+----------+-----------+----------+---------
id | integer | | not null |
col1 | smallint | | not null |
col2 | smallint | | not null |
Table "public.test_size33"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
col1 | "char" | | not null |
col2 | "char" | | not null |
Alignment padding shouldn't be the issue, given the order and type of columns.
