Technology1 min read

Shaun Thomas: Burnt Toast

By · Published by Everything Blog

In short

The database relies on predefined storage strategies, such as the EXTENDED method, to compress oversized content and split it across designated shadow tables. Each chunk is indexed and linked back to the primary row via an internal pointer. While this technique prevents individual rows from exceeding the system limit, the resulting latency can critically degrade application performance.

Key points

  • Moving large attributes out of primary tables prevents rows from growing past the standar…: Moving large attributes out of primary tables prevents rows from growing past the standard page size.
  • Severe latency caused by accessing chunked secondary tables frequently forces administrat…: Severe latency caused by accessing chunked secondary tables frequently forces administrators to re-architect their applications.
  • Shadow tables store oversized attributes as broken-down chunks linked directly to pointer…: Shadow tables store oversized attributes as broken-down chunks linked directly to pointers kept within original rows.

Sources checked by our analysis pipeline: 1

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.

Technology

More in Technology