2

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.

2
  • Your description of your table doesn't make any sense, what is an inte=19 and how many of them are there? Please describe your table precisely, by showing us an actual CREATE statement.
    – jjanes
    Dec 1, 2021 at 4:37
  • Hi, inte=19 was a typo, I meant int=19 Dec 1, 2021 at 13:27

2 Answers 2

5

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.

2
  • 1
    Page sizes (the default) is 8kB for PostgreSQL! And it's something like record size > 2kB for TOAST to kick in - see here.
    – Vérace
    Nov 30, 2021 at 21:25
  • @Vérace-getVACCINATEDNOW updated the answer. Thanks for correction.
    – Sahap Asci
    Nov 30, 2021 at 22:16
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));
2
  • wouldn't that create a pg_toast table? You have 5 columns of max 200 chars. 5x200=1000 characters. A character can be more than 1 byte though, right? Dec 1, 2021 at 8:05
  • Hi, the table was created with a ddl like this: global_id_pk int8 NOT NULL, lo_id varchar(191), frnt varchar(200), tyame varchar(200),CONSTRAINT j_id_pk PRIMARY KEY (g_id_pk) ); CREATE INDEX jvfocal_id_idx ON table2.l_id (ll_id); CREATE INDEX jownd_fk_idx ON tableva.l_id (ow_fk); or_id_fk int8, Should not this be without a toast table? Dec 1, 2021 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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