Postgresql pg_toast tables, are they mandatory?

Question

I have a small schema in PostgreSQL v13 with only schema structure and still no data.

What I have seen, that I got some pg_toast tables for normal tables although there is no data inside. I have a simple table with 5 columns, a couple of them are varchar=200 length and inte=19 length.

Why am I getting those pg_toast tables when I did not insert any data into the table. I can see the relation of the pg_toast table in pg_class with the reltoastrelid column.

These tables have 0 kbytes data according to:

pg_relation_size(reltoastrelid) AS toast_size

The only ones that have pg_toast data are: pg_rewrite and pg_statistic.

Answer 1

PostgreSQL internally does not store a column in a tuple, when the column size is larger than the TOAST threshold (2 kb). For example, size of an integer value is 4 bytes, which obviously fits the page size. However the page is not always enough for some data types such as varchar or text. To handle this limit, PostgreSQL uses a storage technique which is called TOAST (The Oversized-Attribute Storage Technique). PostgreSQL basically creates a new table, which is called toast table, and stores values in it as compressed chunks. That's why you have pg_toast* tables in the pg_class catalog.

Are they mandatory? Yes (sort of) - they are internal relations (tables), about which you shouldn't care in most situations.

Answer 2

Why do you care?

If a table definition is capable of holding rows of more than about 2kb, then it creates a toast table for it at the time the table is created (or when it was altered to expand the max size). It doesn't matter what rows are actually present the in the table, it is the definition that matters.

For example, if I create this table, no toast table is created because the max size of a row is fixed at much less than 2k.

create table t  (t varchar(200), u varchar(200), v varchar(200), g varchar(200), h varchar(200));