TOAST is the abbreviation of "The Oversize-Attribute Storage Technology", which is mainly used to store the value of a large field. To understand TOAST, we need to understand the concept of page (BLOCK). In PG, page is the basic unit of data in file storage. Its size is fixed and can only be specified at compile time. It can not be modified later. The default size is 8KB. At the same time, PG does not allow one row of data to be stored across pages, so for very long row data, PG will start TOAST, specifically by compression and slicing. If slicing is enabled, the actual data is stored in multiple rows of another system table, which is called the TOAST table. This storage method is called out-of-line storage.

Before going into details, we need to understand that there are four TOAST strategies for each table field in PG:

• PLAIN: Avoid compression and out-of-line storage. Only those data types that can be stored without TOAST policy are allowed to be selected (for example, int type), and for text s that require more than page size, this policy is not allowed.

• EXTENDED: Compression and out-of-line storage are allowed. Generally, it will be compressed first, and if it is still too large, it will be stored out of line.

• EXTERNA: Off-line storage is allowed, but compression is not allowed. A field like a string that operates on a part of the data may achieve better performance by using this strategy because it does not need to read out the entire line of data and decompress it.

• MAIN: Compression is allowed, but off-line storage is not allowed. However, in fact, in order to ensure the storage of excessive data, out-of-line storage will be started as a last resort when other methods (such as compression) can not meet the demand. Therefore, it is more appropriate to avoid out-of-line storage as far as possible.
  Now let's study the details of TOAST through practical operation:

First create a blog table:

postgres=# create table blog(id int, title text, content text);
CREATE TABLE
postgres=# \d+ blog;
                          Table "public.blog"
 Column  |  Type   | Modifiers | Storage  | Stats target | Description 
---------+---------+-----------+----------+--------------+-------------
 id      | integer |           | plain    |              | 
 title   | text    |           | extended |              | 
 content | text    |           | extended |              | 

As you can see, interger defaults the TOAST policy to plain and text to extended. PG data tells us that if there are fields in the table that need TOAST, then the system will automatically create a TOAST table for out-of-line storage, then where is the table?

postgres=# select relname,relfilenode,reltoastrelid from pg_class where relname='blog';
 relname | relfilenode | reltoastrelid 
---------+-------------+---------------
 blog    |       16441 |         16444
(1 row)

Through the appeal statement, we find that the oid of the blog table is 16441, and that of the corresponding TOAST table is 16444. (For the concepts of oid and pg_class, please refer to PG Official Documents Then its corresponding TOAST table name is: pg_toast.pg_toast_16441 (notice here is the oid of the blog table). Let's look at its definition:

postgres=# \d+ pg_toast.pg_toast_16441;
TOAST table "pg_toast.pg_toast_16441"
   Column   |  Type   | Storage 
------------+---------+---------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain

The TOAST table has three fields:

• Chunk_id: The OID used to represent a specific TOAST value can be understood as a row of data in the TOAST field of the original table (blog here) that consists of all rows with the same chunk_id value.

• chunk_seq: Used to indicate the location of the row data in the entire data

• chunk_data: Actual stored data.
  Now let's actually verify that:

postgres=# insert into blog values(1, 'title', '0123456789');
INSERT 0 1
postgres=# select * from blog;
 id | title |  content   
----+-------+------------
  1 | title | 0123456789
(1 row)

postgres=# select * from pg_toast.pg_toast_16441;
 chunk_id | chunk_seq | chunk_data 
----------+-----------+------------
(0 rows)

You can see that because content has only 10 characters, there is no compression and no out-of-line storage. Then we use the following SQL statements to increase the length of content, doubling each time, and observe the length of content to see what happens.

postgres=# update blog set content=content||content where id=1;
UPDATE 1
postgres=# select id,title,length(content) from blog;
 id | title | length 
----+-------+--------
  1 | title |     20
(1 row)
postgres=# select * from pg_toast.pg_toast_16441;
 chunk_id | chunk_seq | chunk_data 
----------+-----------+------------
(0 rows)

Repeatedly execute the above procedure until data is available in the pg_toast_16441 table:

postgres=# select id,title,length(content) from blog;
 id | title | length 
----+-------+--------
  1 | title | 327680
(1 row)

postgres=# select chunk_id,chunk_seq,length(chunk_data) from pg_toast.pg_toast_16441;
 chunk_id | chunk_seq | length 
----------+-----------+--------
    16439 |         0 |   1996
    16439 |         1 |   1773
(2 rows)

As you can see, until the length of content is 327680 (much larger than the page size of 8K), there are only two rows of data in the corresponding TOAST table, and the length is slightly less than 2K. This is because compression is enabled first and then out-of-line storage is used under the extended strategy.

Next, we change the content TOAST policy to EXTERNA to prohibit compression.

postgres=# alter table blog alter content set storage external;
ALTER TABLE
postgres=# \d+ blog;
                          Table "public.blog"
 Column  |  Type   | Modifiers | Storage  | Stats target | Description 
---------+---------+-----------+----------+--------------+-------------
 id      | integer |           | plain    |              | 
 title   | text    |           | extended |              | 
 content | text    |           | external |              | 

Then we insert another data:

postgres=# insert into blog values(2, 'title', '0123456789');
INSERT 0 1
postgres=# select id,title,length(content) from blog;
 id | title | length 
----+-------+--------
  1 | title | 327680
  2 | title |     10
(2 rows)

Then repeat the above steps until a new row is generated in the TOAST table:

postgres=# update blog set content=content||content where id=2;
UPDATE 1
postgres=# select id,title,length(content) from blog;
 id | title | length 
----+-------+--------
  2 | title |   2560
  1 | title | 327680
(2 rows)

postgres=# select chunk_id,chunk_seq,length(chunk_data) from pg_toast.pg_toast_16441;
 chunk_id | chunk_seq | length 
----------+-----------+--------
    16447 |         0 |   1996
    16447 |         1 |   1773
    16448 |         0 |   1996
    16448 |         1 |    564
(4 rows)

This time we see that when the content length reaches 2560 (according to official documents, it should be more than 2KB), two new lines of chunk_id 16448 are generated in the TOAST table, and the sum of the lengths of the two lines of chunk_data is exactly equal to 2560. The following conclusions are drawn from the above operations:

• If compression is allowed by policy, TOAST prefers compression
• Whether compressed or not, when the data exceeds about 2KB, out-of-line storage is enabled
• Modifying TOAST policy will not affect the way existing data is stored