Consider that you are moving to a new apartment. There is a policy in the new apartment complex: every item you have must be put inside one standard storage box. It can be books, clothing, cooking utensils, and the like; they will easily fit in the box. However, how do you store something bulky, such as a bicycle, sofa, or whatever? You have a special storage room in the basement where all those bulky items can be kept, and a small piece of paper with the description "sofa - basement, shelf 4" goes into the box. Whenever you want the sofa, you go to the place written on the paper. From the outside perspective, your box remains the same; only there is a little piece of paper there. This scenario closely resembles PostgreSQL's mechanism for dealing with oversized data called TOAST (The Oversized-Attribute Storage Technique).
The Hard Limit No One Mentions
PostgreSQL uses 8-kilobyte pages to store its data. Every single row that goes into every single table has to fit into such a page. It is a simple principle that defines the workings of the storage engine, and it is not often mentioned as there is seldom an issue with this limit until you begin storing complex HTML descriptions, deeply nested JSON structures, and binary objects.
All of those can easily take up more than 8 kilobytes of space. A single blog post, a PDF certificate, or even a deeply nested REST API answer can do it effortlessly. PostgreSQL is not shocked by this fact. There is a special solution to that problem, and it has been around for a while.
Basement Storage Room
The technique is called TOAST — The Oversized-Attribute Storage Technique. With each table creation in PostgreSQL that is supposed to contain oversized values, another table is created behind your back. You do not see it in your queries. It does not come in your \dt listing. It is simply created and waits until the oversized value appears.
When you insert the row that contains oversized values, PostgreSQL tries to compress the value at first. In most cases, the text data is easily compressed, and when the compressed value becomes small enough to fit into the regular row, there is no need to use the basement storage — everything remains in the row. Otherwise, the oversized value gets divided into pieces approximately 2 kilobytes in size and gets stored in the companion table. Instead of the value, the main row stores a tiny 18-byte pointer that, in essence, says: "the real value is in the basement".
When you query the column, PostgreSQL automatically follows the pointer to get the data from the basement, rebuilds it, decompresses it, and returns you the value. No special code needs to be written for this.
You Can Actually Control This
Every column has a storage method that dictates to PostgreSQL. How hard should it try to compress and then move out-of-line? By default, a storage method for large types will attempt to compress before out-of-lining. However, you can configure it to avoid compressing altogether; it may be useful for fast access to large values, where decompression time is important. Or you can just instruct PostgreSQL to try its best not to move the value away from the row, but to do so only when all else fails.
These options are rarely used in practice by most applications. However, knowing about them means having a tool at hand when it's needed.
Where It Quietly Costs You
TOAST only becomes apparent when it does not. Here are a few patterns to feel its impact.
If your query performs a SELECT * on a table where at least some of the columns contain large TOASTed values, then PostgreSQL retrieves data from two tables for every row it needs – the table itself and its companion table. By simply choosing to get the ID and timestamp, you incurred a penalty for taking the elevator down to the basement without any need to do so. Choosing only the columns you actually need is a good practice in general, but on a TOAST-heavy table, it means the difference between an efficient query and a much slower one.
Update operations can be more stealthy. In PostgreSQL, when you update an existing row, the entire row receives a new version, which is what MVCC does. In case the row you are updating has a big TOASTed field that you haven’t updated, the chunks associated with that field will get new versions in the companion table as well. These chunks will be considered useless garbage that needs to be deleted by vacuum later. Updating workloads in tables with large text fields creates bloat in the companion table as quickly as in the table itself.
For many developers, it can take years before they encounter TOAST in PostgreSQL, even though the system works for decades in a row. This speaks volumes about how well it does its job. But when the table with big JSON documents suddenly starts misbehaving – selecting becomes slow, disks fill up, updates cost a fortune – knowing the underground basement gives a chance to turn a difficult situation into an easily solved problem.