TOAST stands for The Oversized-Attribute Storage Technique.
PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. Read More
Toast is an operation in PostgreSQL to handle large chunks of data to fit in page buffer. When the data surpass TOAST_TUPLE_THRESHOLD (2KB default), Postgres will squeeze the data, trying to fit in 2KB buffer size. If the compressing of the large column data does not lead to smaller block (<2KB), it will be split into smaller chunks.
Because of how the pointer system is implemented, most TOAST table column types limited to a max size of 1GB.
Almost every table you create has its own related (unique) TOAST table, which may or may not ever end up being utilized, depending on the size of rows you insert. A table with only fixed-width columns like integers may not have an associated toast table.
All of this is transparent to the user and enabled by default.
The benefit of TOAST table is that whenever data requires retrieving from TOAST table, it only needs to refer to TOAST table; otherwise, it does not refer to it unnecessarily.
How to find whether a TOAST table is correlated with your main tables?
You can easily select reltoastrelid from pg_class. Like any other table ObjectID is also assigned to TOAST Table.
SELECT relname,reltoastrelid
FROM pg_class
WHERE relname='Table_Name';
Cheers!
Leave a Reply