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

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 8kb page.
This is one type of limitation of PostgreSQL.

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

The TOAST is a 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 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 is normally stored.

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

The advantage of TOAST table is, whenever data require to fetch from TOAST table, only It requires 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:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of