Shaun Thomas: Burnt Toast

The piece examines how PostgreSQL’s TOAST storage affects performance when a table contains large JSON or XML values. In tests with 100,000 rows, a toasted table occupied 47 MB, compared with 318 MB when the values remained inline. Accessing the large field was costly because PostgreSQL had to retrieve and decode TOAST data, but queries that did not need it benefited from the smaller heap: counting rows took 26 ms rather than 92 ms, scanning to the last row took 16 rather than 53 ms, and an update took 62 rather than 128 ms. The results show that TOAST can speed ordinary heap operations even when reading the toasted column itself is expensive.

For applications that frequently query other columns but rarely need large payloads, the proposed remedy is to move the payload into a separate table and join it only when required. In the example, this reduced the main table’s heap from 47 MB to about 5 MB; a view with a LEFT JOIN lets PostgreSQL omit that join when queries do not request the payload. The tradeoff is added schema complexity, and PostgreSQL’s minimum threshold for TOAST means smaller values cannot simply be forced out of line. The discussion also compares compression methods: in a test of 100,000 large values, LZ4 compressed more than 30 times faster than pglz, while decompression was about 20% slower. Its central lesson is to treat TOAST as a useful compromise, consider separating optional bulky data, and use LZ4 when faster compression matters.