I have these two identical tables:
Table "public.region" Column | Type | Collation | Nullable | Default -------------+----------+-----------+----------+--------- r_regionkey | integer | | not null | r_name | char(25) | | | r_comment | char(152) | | | Indexes: "region_pkey" PRIMARY KEY, btree (r_regionkey)
Table "public.region2" Column | Type | Collation | Nullable | Default -------------+----------+-----------+----------+--------- r_regionkey | smallint | | not null | r_name | text | | | r_comment | text | | | Indexes: "region_pkey" PRIMARY KEY, btree (r_regionkey)
I am using
text in order to save space, but weirdly this is the result:
8192 bytes while
region2 taking more space, even though I am using
smallint instead of
text instead of
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
VACUUM FULL public.region; and
VACUUM FULL public.region2;, test again with:
Three possible issues:
The obvious reason: table bloat from updates or deletes. Removed by
Schema-qualified names (‘public.region’ instead of just ‘region’) makes sure you don’t measure the wrong table in a different schema by accident. Probably not the case.
pg_table_size()includes auxiliary relation forks (files), which may be filled for one table, but not for the other. For your purpose, the more accurate test is with
Computes the disk space used by the specified table, excluding indexes
(but including its TOAST table if any, free space map, and visibility
text] ) →
Computes the disk space used by one “fork” of the specified relation.
(Note that for most purposes it is more convenient to use the
which sum the sizes of all forks.) With one argument, this returns the
size of the main data fork of the relation. The second argument can be
provided to specify which fork to examine:
mainreturns the size of the main data fork of the relation.
fsmreturns the size of the Free Space Map (see Section 70.3) associated with the relation.
vmreturns the size of the Visibility Map (see Section 70.4) associated with the relation.
initreturns the size of the initialization fork, if any, associated with the relation.
Since your example has no TOAST table, only
vm make a difference. Those are typically negligible in size for bigger tables, but relevant for your minimal test. Both may go down to “0 bytes” after
Test with more rows (thousands).
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0