Toasting Up Large Objects (BLOBs/CLOBs)

Eric McCormack November 27, 2018

Let’s talk TOAST!

TOAST stands for The Oversized-Attribute Storage Technique.

EDB Postgres and PostgreSQL use 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.

When a row is attempted to be stored that exceeds this size, TOAST basically breaks up the data of large columns into smaller "pieces" and stores them into a TOAST table.

Almost every table you create has its own associated (unique) TOAST table, which may or may not ever end up being used, 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. 

Continue reading on Postgres Rocks. >>

Eric McCormack

Eric McCormack is a Senior Architect at EnterpriseDB. He has more than 20 years experience in software and database architecture. As Vice President, Software Development at Rezolve Group, McCormack led the design and development of database services and architectures and of business intelligence and reporting capabilities for internal and external clients. He spent 11 years at Rezolve following four years at EMC as a Senior Solutions Architect, where he was a member of the Global Practice Team. He worked on site with companies in the Western Hemisphere to implement solutions on EMC’s Centera systems. Prior to that, McCormack spent more than seven years at Braintree Security Software as a Senior Software Engineer. He holds a B.A. in Computer Science from Bridgewater State College.