Date: 2022-05-27
Time: 15:45–16:45
Room: Stream 3
Level: Intermediate
Postgres handles long attributes in table by compressing and slicing them to the chunks, which stored in hidden corresponding relation, these technology called TOAST. Any access to such attribute requires lookup chunks using Btree index and then implicit joining to the hidden relation, which can cause a catastrophic slowdown of all queries to such attributes. Nowaday, users prefer to use JSON because of its flexible nature and ubiquitousness, they often operate with long JSON and have real performance issues. Unfortunately, current TOAST is too universal in regards to the types of data, it has no knowledge about internal structure of attribute an consider it as a black box. We propose a new pluggable TOAST API, which allows per data type optimizations of storage for long attributes. I will also show several examples of how to use the new TOAST and what benefits it brings, including streaming to table using appendable bytea and very efficient JSONB, which demonstrates orders of magnitude performance gain on selects and updates.
The following slides have been made available for this session: