Burnt TOAST

A financial institution hired the reporter in 2010 to refactor their Postgres cluster architecture. Among the many learning experiences, the reporter developed a profound aversion toward the event_log table, which housed hundreds of thousands of system event entries. The primary issue stemmed from a column holding extensive XML data, which led to sluggish performance both on reading and writing data.

The reporter's initial confusion stemmed from Postgres's "TOAST" system, which aimed to handle large data blocks efficiently by offloading them to separate tables. However, the reporter's deep-seated disdain for TOAST was not unfounded, as it introduced additional complexities and performance overhead. Upon reconstructing the scenario, the reporter created a test table with 100,000 rows to illustrate the impact of TOAST.

The event_data column was populated with XML data, triggering the creation of a TOAST table and subsequent indexing. The reporter then compared the performance of querying the toasted vs. non-toasted version of field 999, finding that the toasted column required significantly more memory and CPU resources. Despite TOAST's efficiency in some cases, the reporter concluded that the primary cause of the sluggish performance was the event_data column's design, which was beyond the scope of TOAST.