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)     |           |          | 
    "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     |           |          | 
    "region_pkey" PRIMARY KEY, btree (r_regionkey)

I am using smallint and text in order to save space, but weirdly this is the result:

select pg_size_pretty(pg_table_size('region'))

returns 8192 bytes while

select pg_size_pretty(pg_table_size('region2'))

returns 48 kB.

Why is region2 taking more space, even though I am using smallint instead of integer and text instead of char(n)?

  • 1
    See here or here May 13, 2022 at 21:00
  • Assuming that they contain the same visible rows, it must be because it is bloated. Run VACUUM (FULL) on both tables and compare again. Avoiding char is good, but always use bigint for artificial primary keys. May 13, 2022 at 22:13
  • 1
    @LaurenzAlbe - if there are never going to be more than ~ 16k regions, the surely SMALLINT is superior to BIGINT as a PK?
    – Vérace
    May 14, 2022 at 11:42
  • @Vérace In a purist sense, and as far as storage space is concerned, yes. But for a small table, the difference is irrelevant, and I prefer to have a simple rule. May 14, 2022 at 13:07
  • 1
    @LaurenzAlbe: For a small table the difference is irrelevant indeed. But those are often lookup tables referenced in big tables, with one or more indexes on them. Then the difference can matter, especially when multicolumn indexes get 50 % bigger with bigint ... So, integer typically makes sense. (smallint much less commonly.) May 15, 2022 at 1:30

1 Answer 1


After running VACUUM FULL public.region; and VACUUM FULL public.region2;, test again with:

SELECT pg_relation_size('public.region');

Three possible issues:

  1. The obvious reason: table bloat from updates or deletes. Removed by VACUUM FULL.

  2. 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.

  3. 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 pg_relation_size().

The manual:

pg_table_size ( regclass ) → bigint

Computes the disk space used by the specified table, excluding indexes (but including its TOAST table if any, free space map, and visibility map).


pg_relation_size ( relation regclass [, fork text ] ) → bigint

Computes the disk space used by one “fork” of the specified relation. (Note that for most purposes it is more convenient to use the higher-level functions pg_total_relation_size or pg_table_size, 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:

  • main returns the size of the main data fork of the relation.

  • fsm returns the size of the Free Space Map (see Section 70.3) associated with the relation.

  • vm returns the size of the Visibility Map (see Section 70.4) associated with the relation.

  • init returns the size of the initialization fork, if any, associated with the relation.

Since your example has no TOAST table, only fsm and 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 VACUUM FULL.

Test with more rows (thousands).


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.