In this lesson, you will learn how PostgreSQL manages data that is simply too large to fit into a standard database page.
The Problem: The 8KB Limit
In PostgreSQL, a data page is typically 8KB. If you try to insert a single row that exceeds this size—like a massive text document or a high-resolution image—the database engine encounters a physical constraint. It cannot split a single row across two pages because each page must remain a self-contained unit for reliability.
The Solution: TOAST
To solve this, PostgreSQL uses a mechanism called TOAST (The Oversized-Attribute Storage Technique). When you insert a row, the engine checks if the row size exceeds the threshold (usually 2KB). If it does, PostgreSQL automatically compresses the data and, if it still doesn't fit, pushes the oversized column values out of the main "heap" table and into a separate, hidden TOAST table.
The main table then only holds a small "pointer" to the data stored in this secondary location. This keeps the main heap table efficient, ensuring that standard queries aren't slowed down by processing massive blobs of data they don't necessarily need.
-- Even if you insert a massive string,
-- PostgreSQL handles the off-page storage invisibly:
INSERT INTO documents (content)
VALUES (repeat('Very large text...', 10000));
Large Objects (lo)
While TOAST is automatic, PostgreSQL also supports Large Objects (the lo interface). Unlike TOAST, which is managed transparently by the engine as part of a row, Large Objects are treated as independent entities stored in their own system tables. You access these via specific functions (lo_create, lo_import). They are useful for files that are far too large for a standard table row and need to be accessed via stream-like patterns rather than standard SQL SELECT statements.
In summary, TOAST handles the "hidden" heavy lifting of your everyday wide rows, while Large Objects are specialized tools for handling massive binary blobs outside of your standard relational schema.
Key Takeaway: TOAST transparently moves oversized columns to external storage to keep your main heap pages small and performant, while Large Objects provide a manual way to store and stream massive binary files.