PostgreSQL: What is TOAST (The Oversized-Attribute Storage Technique)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Like SQL Server, PostgreSQL data page size is also 8KB.

This post is very similar to a few of the SQL Server published articles.

SQL Server: The Internal Structure of the Data Page

SQL Server: What happened, when Row Overflow and Data Exceeding 8KB?

PostgreSQL uses 8kb fixed size data page to store tuples of Table.

It does not allow tuples to span in multiple pages because not possible to store large field value directly into the 8kb page.
This is one type of limitation of PostgreSQL.

What is TOAST (The Oversized-Attribute Storage Technique)?

The TOAST is one type of Technique to store and manage oversized or overflow data pages.

This is a background process of PostgreSQL, and It also compresses the value and broken into multiple physical pages.

If row size is exceeding in the main table, It creates new a TOAST table to manage and store oversized rows.

The mechanism is accomplished by splitting up the large column entry into 2KB bytes and storing them as chunks in the TOAST tables.
It then stores the length and a pointer to the TOAST entry back where the column stored.

Because of how the pointer system implemented, most TOAST table column types limited to a max size of 1GB.

The advantage of TOAST table is, whenever data require fetching from TOAST table, only It needs to refer TOAST table otherwise, It does not call unnecessary.

How to find whether a TOAST table is associated with your main tables?

You can easily select reltoastrelid from pg_class. Like any other table ObjectID is also assigned to TOAST Table.

You can execute below script: