PG Phriday: Burnt TOAST

A financial firm hired me in 2010 to revise their Postgres cluster architecture. During my stint there, I learned a healthy respect for their event_log table. But perhaps “respect” is the wrong word here, as it was more of a deep loathing. This table, you see, was the canonical ledger of system events and consisted of several million rows.The high row count wasn’t a problem by itself. We kept a tight visibility window and archived frequently. Unfortunately for us, one of the columns was an unconstrained TEXT type that commonly held a large blob of XML. Thus any fetch from this table was inordinately slow. Writing was equally painful. We went through great pains to shape the application itself to avoid touching it except when absolutely necessary. It was evil incarnate.The performance of this table initially confused and enraged me—until I remembered about TOAST. Postgres likes to innocuously dub this backronym “The Oversized-Attribute Storage Technique” but DBAs in the field maintain a love/hate relationship with the system. Whether that’s justified is a different question.On the surface, it’s a way to store huge blocks of data elsewhere to keep tuples small and avoid exceeding the relatively petite 8KB data page size. Postgres creates a shadow table for the bulky content, splits it up into chunks, and stores a pointer in the original row. It’s an elegant solution to a pressing issue, but there are deep performance implications.So let’s dig into precisely why DBAs secretly dread large text-based columns, shall we?Getting TOASTyTo really understand what happened in my event_log case, it’s good to reconstruct some semblance of it.CREATE TABLE event_log ( event_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ticker VARCHAR(4) NOT NULL, strike INT NOT NULL, shares INT NOT NULL, event_data JSON NOT NULL -- Ours was XML, but modern times... --- More columns went here ); And then we can bootstrap the table with 100k rows. Just enough to make it annoying! The data column needs to be big, but not absolutely gigantic. Let’s start with up to 1000 data points:INSERT INTO event_log (ticker, strike, shares, event_data) SELECT left(md5((a.id % 100)::TEXT), 4), a.id % 100, a.id % 100 * 10, (SELECT jsonb_object_agg('field_' || f, random(1, 1000)) FROM generate_series(1, id % 1000 + 1 ) f) FROM generate_series(1, 100000) a (id); Great! Now where did all of that data go? If you thought, “Into the event_log table of course!” you’re wrong. Instead, Postgres helpfully created a TOAST table for most of it. Let’s check the pg_class catalog for the other objects:SELECT c.relname, t.relname AS toast_table, i.relname AS toast_index FROM pg_class c JOIN pg_class t ON t.oid = c.reltoastrelid JOIN pg_index x ON x.indrelid = t.oid JOIN pg_class i ON i.oid = x.indexrelid WHERE c.relname = 'event_log'; relname | toast_table | toast_index -----------+----------------+---------------------- event_log | pg_toast_16401 | pg_toast_16401_index That’s three objects for the price of one: the original table, a toast table, and a toast table index. This doesn’t always happen, but if your table has a text-like column without a maximum length, it probably did. In this case, it’s because an unbounded JSON column can contain anything, so Postgres preemptively initialized a TOAST table to store the “anything”.What goes in is a bit more subtle, and is where the trouble starts brewing.Compounded ButterThere’s a kind of inherent hierarchy that determines how data gets managed. The typstorage column in the pg_type catalog explains how this works for each type Postgres understands. There are four valid storage methods:PLAIN (p): The value stays inline and uncompressed. This is the default for fixed-width types like INT and TIMESTAMPTZ.MAIN (m): Compression is allowed, with TOAST only as the last resort. The default for NUMERIC.EXTERNAL (e): TOAST is allowed, but compression is not.EXTENDED (x): Anything is fair game. This is the default for TEXT, BYTEA, JSONB, and most other variable-length types.Postgres imposes a loop on all text-based column types in a tuple that it applies until the entire row is under the standard 8KB page threshold (minus overhead). It looks something like this:Being the default for JSON, all of our data follows the rules for EXTENDED. We can see it in action by checking a few rows:SELECT ctid, event_id, length(event_data::TEXT) AS chars, pg_column_size(event_data) AS stored_bytes, pg_column_toast_chunk_id(event_data) AS toast_id FROM event_log WHERE event_id BETWEEN 335 AND 340 ORDER BY event_id; ctid | event_id | chars | stored_bytes | toast_id --------+----------+-------+--------------+---------- (57,2) | 335 | 5907 | 1981 | (57,3) | 336 | 5930 | 2004 | 16414 (57,4) | 337 | 5942 | 1987 | 16415 (57,5) | 338 | 5953 | 2005 | 16416 (57,6) | 339 | 5978 | 1996 | 16417 (57,7) | 340 | 5988 | 2034 | 16418 The toast_tuple_target storage setting tells Postgres when to toast column contents. The default is around 2KB of compressed data, and anything after that is moved into TOAST automatically. The example above has a row that compresses right under that limit, and as a result, never gets moved.If we examine page 57 specifically, this becomes even more clear:CREATE EXTENSION IF NOT EXISTS pageinspect; SELECT lp, lp_len FROM heap_page_items(get_raw_page('event_log', 57)) LIMIT 5; lp | lp_len ----+-------- 1 | 2022 2 | 2029 3 | 66 4 | 66 5 | 66 The tuple size of the rows that didn’t get toasted is much larger than those that did. That’s thanks to a pointer replacing the toasted values. And that’s not all. Toasted data is also broken into chunks. Every toasted value gets broken into 1996-byte blocks and stored in sequential order as rows in the toast table.Now consider what this means for reads:Any toasted value implies at least one fetch from the toast table. That’s a read on the toast index and a read on the toast table itself per chunk.Chunks must be re-assembled. That’s extra memory usage and can be significant depending on chunk count.Compressed data must also be decompressed. That’s both memory and CPU overhead.The intuitive read of this is that TOAST is always worse. But is it?Turning Up the HeatLet’s run an experiment! We already have a table where some of the data is toasted. What happens if we force Postgres to select all of it?This requires some setup. To begin, we create a non-toasted table by setting the event_data column to use MAIN storage. That allows it to be compressed, but not toasted unless the full tuple exceeds a Postgres page size. Like so:CREATE TABLE non_toast (LIKE event_log INCLUDING INDEXES); ALTER TABLE non_toast ALTER event_data SET STORAGE MAIN; INSERT INTO non_toast SELECT * FROM event_log; Then we can directly compare the costs of pulling out field 999 from the data:EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF) SELECT count(event_data->>'field_999') FROM event_log; Aggregate (actual time=6589.068..6589.070 rows=1.00 loops=1) Buffers: shared hit=246472 read=41655 -> Seq Scan on event_log (actual time=0.221..17.022 rows=100000.00 loops=1) Buffers: shared read=5969 EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF) SELECT count(event_data->>'field_999') FROM non_toast; Aggregate (actual time=6166.877..6166.880 rows=1.00 loops=1) Buffers: shared read=40754 -> Seq Scan on non_toast (actual time=0.180..51.434 rows=100000.00 loops=1) Buffers: shared read=40754 Take a look at that! Shared buffers really saved our bacon on this one. Total buffer reads on the toasted column required 288127 total reads versus 40754 for the non-toasted version. Conversely, the toasted table is much smaller:SELECT pg_size_pretty(pg_relation_size('event_log')) AS event_log, pg_size_pretty(pg_relation_size('non_toast')) AS non_toast; event_log | non_toast -----------+----------- 47 MB | 318 MB While Postgres can read through the heap faster, it then has to spend a lot of time seeking through the toast table.

This size difference translates to some non-intuitive behavior.