Like SQL Server, PostgreSQL data page size is also 8KB.
This post is very similar to a few of the SQL Server published articles.
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:
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 dbrnd.com, 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.