Category: All posts
Feb 12, 2025
Posted by
Team TigerData
If you’re working with large databases in Postgres, this story will sound familiar. As your Postgres database keeps growing, your performance starts to decline, and you begin to worry about storage space—or, to be precise, how much you’ll pay for it. You love PostgreSQL, but there’s something you wish you had: a highly effective data compression mechanism.
PostgreSQL does have somewhat of a compression mechanism: TOAST 🍞. In this post, we’ll walk you through how Postgres TOAST works and the different TOASTing strategies.
As much as we enjoy a good TOAST, we’ll discuss why this is not the kind of compression feature you need for reducing the storage footprint of modern large databases—and how, as the PostgreSQL enthusiasts that we are here at Timescale, we decided to build a more suitable compression mechanism for PostgreSQL, inspired by the columnar design of NoSQL databases.
Even if it might reduce the size of datasets, TOAST (The Oversized Attribute Storage Technique) is not your traditional data compression mechanism. To understand TOAST, we have to start by understanding how PostgreSQL stores data.
Postgres’ storage units are called pages, and pages have a fixed size (8 kB by default). Having a fixed page size gives Postgres many advantages: data management simplicity, efficiency, and consistency. But there is a downside: some data values might not fit within that page.
This is where TOAST comes in. TOAST refers to the automatic mechanism that PostgreSQL uses to efficiently store and manage values in Postgres that do not fit within a page. To handle such values, Postgres TOAST will, by default, compress them using an internal algorithm. If, after compression, the values are still too large, Postgres will move them to a separate table (called the TOAST table), leaving pointers in the original table.
(As we’ll see later in this article, you can modify this strategy as a user, for example, by telling Postgres to avoid compressing data in a particular column.)
The data types subject to TOAST are primarily variable-length ones that have the potential to exceed the size limits of a standard PostgreSQL page. On the other hand, fixed-length data types, like integer, float, or timestamp, are not subjected to TOAST since they fit comfortably within a page.
Some examples of these data types are:
json and jsonbtext stringsvarchar and varchar(n) (If the length specified in varchar(n) is small enough, then values of that column might always stay below the TOAST threshold.)bytea storing binary datapath and polygon and PostGIS types like geometry or geographyUnderstanding TOAST relates not only to page size but also to another Postgres storage concept: tuples. Tuples are rows in a PostgreSQL table. Typically, the TOAST mechanism kicks in if all fields within a tuple have a total size of over 2 kB approx.
If you’ve been paying attention, you might wonder, “Wait, but the page size is around 8 kB—why is there overhead?” That’s because PostgreSQL likes to ensure it can store multiple tuples on a single page: if tuples are too large, fewer tuples fit on each page, leading to increased I/O operations and reduced performance.
Postgres also needs to keep free space to fit additional operational data: each page stores the tuple data and additional information for managing the data, such as item identifiers, headers, and transaction information.
So, when the combined size of all fields in a tuple exceeds approximately 2 kB (or the TOAST threshold parameter, as we’ll see later), PostgreSQL takes action to ensure that the data is stored efficiently. TOAST handles this in two primary ways:
(We’re simplifying things slightly for the purpose of this article—read the PostgreSQL documentation for a full detailed view.)
pglz We’ve mentioned that TOAST can compress large values in PostgreSQL. But which compression algorithm is PostgreSQL using, and how effective is it?
The pglz (PostgreSQL Lempel-Ziv) is the default internal compression algorithm used by PostgreSQL specifically tailored for TOAST. Here’s how it works in simple terms:
pglz tries to avoid repeated data. When it sees repeated data, instead of writing the same thing again, it just points back to where it wrote it before. This "avoiding repetition" helps in saving space.pglz reads through data, it remembers a bit of the recent data it has seen. This recent memory is the "sliding window."pglz checks if it has seen this data recently (within its sliding window). If yes, it writes a short reference instead of repeating the data.pglz just writes it down as it is.pglz uses its references to fetch the original data. This process is quite direct, as it looks up the referred data and places it where it belongs.pglz doesn't need separate storage for its memory (the sliding window); it builds it on the go while compressing and does the same when decompressing.This implementation balances compression efficiency and speed within the TOAST mechanism. The compression rate effectiveness of pglz will largely depend on the nature of the data.
For example, highly repetitive data will compress much better than high entropy data (like random data). You might see compression ratios in the range of 25 to 50 percent, but this is a very general estimate—results will vary widely based on the exact nature of the data.
By default, PostgreSQL will go through the TOAST mechanism according to the procedure explained earlier (compression first and out-of-line storage next, if compression is not enough). Still, there might be scenarios where you might want to fine-tune this behavior on a per-column basis. PostgreSQL allows you to do this using the TOAST strategies EXTENDED, EXTERNAL, MAIN, and PLAIN.
EXTENDED: This is the default strategy. Data will be stored out of line in a separate TOAST table if it’s too large for a regular table page. Data will be compressed to save space before being moved to the TOAST table.EXTERNAL: This strategy tells PostgreSQL to store the data for this column out of line if the data is too large to fit in a regular table page, and we’re asking PostgreSQL not to compress the data—the value will be moved to the TOAST table as-is.MAIN: This strategy is a middle ground. It tries to keep data in line in the main table through compression; if the data is definitely too large, it will move the data to the TOAST table to avoid an error, but PostgreSQL won't move the compressed data. Instead, it will store the value in the TOAST table in its original form. PLAIN: Using PLAIN in a column tells PostgreSQL to always store the column's data in line in the main table, ensuring it isn't moved to an out-of-line TOAST table. Take into account that if the data grows beyond the page size, the INSERT will fail because the data won’t fit. If you want to inspect the current strategies of a particular table, you can run the following:
\d+ your_table_name
You'll get an output like this:
=> \d+ example_table
Table "public.example_table"
Column | Data Type | Modifiers | Storage | Stats target | Description
---------+------------------+-----------+----------+--------------+-------------
bar | varchar(100000) | | extended | |
```If you wish to modify the storage setting, you can do so using the following command:
-- Sets EXTENDED as the TOAST strategy for bar_column
ALTER TABLE example_blob ALTER COLUMN bar_column SET STORAGE EXTENDED;
Apart from the strategies above, these two parameters are also important to control TOAST behavior:
TOAST_TUPLE_THRESHOLD
This is the parameter that sets the size threshold for when TOASTing operations (compression and out-of-line storage) are considered for oversized tuples.
As previously mentioned, TOAST_TUPLE_THRESHOLD is set to approximately 2 kB by default.
TOAST_COMPRESSION_THRESHOLD
This parameter specifies the minimum size of a value before Postgres considers compressing it during the TOASTing process.
If a value surpasses this threshold, PostgreSQL will attempt to compress it. However, just because a value is above the compression threshold, it doesn't automatically mean it will be compressed: the TOAST strategies will guide PostgreSQL on how to handle the data based on whether it was compressed and its resultant size relative to the tuple and page limits, as we’ll see in the next section.
TOAST_TUPLE_THRESHOLD is the trigger point. When the size of a tuple's data fields combined exceeds this threshold, PostgreSQL will evaluate how to manage it based on the set TOAST strategy for its columns, considering compression and out-of-line storage. The exact actions taken will also depend on whether column data surpasses the TOAST_COMPRESSION_THRESHOLD.
Strategy | Compress if tuple > TOAST_COMPRESSION_THRESHOLD | Store out-of-line if tuple > TOAST_TUPLE_THRESHOLD | Description |
EXTENDED | Yes | Yes | Default strategy. Compresses first, then checks if out-of-line storage is needed. |
MAIN | Yes | Only in uncompressed form | Compresses first, and if still oversized, moves to TOAST table without compression. |
EXTERNAL | No | Yes | Always moves to TOAST if oversized, without compression. |
PLAIN | No | No | Data always stays in the main table. If a tuple exceeds the page size, an error occurs. |
By now, you’ll probably understand why TOAST is not the data compression mechanism you wish you had in PostgreSQL. Modern applications imply large volumes of data ingested daily, meaning databases (over)grow quickly.
Such a problem was not as prominent when our beloved Postgres was built decades ago, but today’s developers need compression solutions for reducing the storage footprint of their datasets.
While TOAST incorporates compression as one of its techniques, it's crucial to understand that its primary role isn't to serve as a database compression mechanism in the traditional sense. TOAST is mainly a solution to one problem: managing large values within the structural confines of a Postgres page.
While this approach can lead to some storage space savings due to the compression of specific large values, its primary purpose is not to optimize storage space across the board.
For example, if you have a 5 TB database made up of small tuples, TOAST won’t help you turn those 5 TB into 1 TB. While there are parameters within TOAST that can be adjusted, this won't transform TOAST into a generalized storage-saving solution.
And there are other inherent problems with using TOAST as a traditional compression mechanism in PostgreSQL; for example:
pglz) with compression rates varying typically from 25-50 percent. Via the TimescaleDB extension, PostgreSQL users have a better alternative. Inspired by the compression design of NoSQL databases, we added columnar compression functionality to PostgreSQL. This transformative approach transcends PostgreSQL’s conventional row-based storage paradigm, introducing the efficiency and performance of columnar storage.
By adding a compression policy to your large tables, you can reduce your PostgreSQL database size by up to 10x (achieving +90 percent compression rates).
By defining a time-based compression policy, you indicate when data should be compressed. For instance, you might choose to compress data older than seven (7) days automatically:
-- Compress data older than 7 days
SELECT add_compression_policy('my_hypertable', INTERVAL '7 days');
Via this compression policy, Timescale will transform the table partitions (which in Timescale are also created automatically) into a columnar format behind the scenes, combining many rows (1,000) into an array. To boost compressibility, Timescale will apply different compression algorithms depending on the data type:
This columnar compression design offers an efficient and scalable solution to the problem of large datasets in PostgreSQL. It allows you to use less storage to store more data without hurting your query performance (it actually improves it). And in the latest versions of TimescaleDB, you can also INSERT, DELETE, and UPDATE directly over compressed data.
Have we piqued your curiosity? Read the following blog posts to learn more about compression in Timescale:
We hope this article helped you understand that while TOAST is a well-thought-out mechanism to manage large values within a PostgreSQL page, it’s not effective for optimizing database storage within the realm of modern applications.
If you’re looking for effective data compression that can move the needle on your storage savings, give Timescale a go. You can try our cloud platform that propels PostgreSQL to new performance heights, making it faster and fiercer—it’s free, and no credit card is required—or you can add the TimescaleDB extension to your self-hosted PostgreSQL database.