I became a Site Reliability Engineer because I wanted to expose myself to large-scale, complex database systems. The challenges of scaling databases, ensuring high availability, and understanding the internals of how these systems work have always fascinated me.
In this pursuit, I’ve come across many ingenious mechanisms in PostgreSQL, one of which is TOAST — an essential feature for managing large rows in PostgreSQL. In this blog, I’ll dive into what TOAST is, why it was invented, and why it’s critical for handling large rows in PostgreSQL efficiently.
TL;DR: TOAST, short for The Oversized-Attribute Storage Technique, is PostgreSQL’s solution for managing rows with very large data.
PostgreSQL has a fixed page size of 8KB (block_size default value, 8192 bytes), which serves as the basic unit of data storage. This size limitation means that rows exceeding 8KB cannot be stored directly within a single page.
TOAST addresses this challenge by enabling the database to handle oversized rows through two key mechanisms: compression and/or out-of-line storage. Compression reduces the size of large attributes to fit within the page, and when that’s not sufficient, TOAST stores the data as separate chunks in a dedicated TOAST table, with the main table maintaining a reference to these chunks. This approach ensures efficient storage and retrieval of large data while adhering to PostgreSQL’s architectural constraints.
Notably, TOAST supports specific data types, such as text, bytea, and jsonb, which are variable-length and dynamic. However, certain data types or configurations might bypass TOAST, depending on use cases.
Here’s a simplified representation of how TOAST operates:
Main Table TOAST Table
+-------+-------+ +----------+-------------+
| id | data | | chunk_id | data_chunk |
+-------+-------+ +----------+------------+
| 1 | Ref ->|------------> | 1.1 | "..." |
| 2 | Small | | 1.2 | "..." |
+-------+-------+ +----------+------------+
If the data column exceeds the 8KB page size, it’s compressed or split into chunks and stored in a separate TOAST table. The main table holds a reference to these chunks, allowing PostgreSQL to retrieve the data efficiently.
TOAST compresses oversized attributes before storage, significantly reducing disk usage. Only the necessary chunks are retrieved when queried, minimizing unnecessary I/O.
By storing oversized attributes separately, TOAST ensures that queries focusing on smaller columns don’t have to load large, irrelevant data. This reduces the amount of data transferred and improves overall query efficiency.
Applications often store rich data types like JSON, images, or logs in databases. TOAST enables PostgreSQL to handle these use cases without requiring major architectural changes.
TOAST is a testament to PostgreSQL’s engineering brilliance, providing a robust mechanism to handle large rows while maintaining performance and efficiency. Understanding TOAST is crucial for database administrators and engineers working with PostgreSQL at scale. Whether you’re optimizing for performance or troubleshooting storage issues, knowing how TOAST operates can make all the difference. Happy exploring!