How PostgreSQL stores the oversized or extended fields?

Recently I was loading the very large analytics data set to the PosgreSQL table and compare to the rows/tuples size, table has claimed the around 200X of the storage. Upon the investigation I found the issue related to toast bloating and have to reclaim the space. Let’s learn about the toast table in this article.

PostgreSQL loads and stores the data into pages. The page size is commonly 8KB. The page is used to store tuples, indexes etc. Even WAL files are written 8KB pages. Therefore it is not possible to store the very large field values directly to the page. To store the large filed values, PostgreSQL compresses the values and sliced into multiple rows. This technique is known as TOAST. TOAST the values (compressing and slicing) will also help handling large values in the memory.

Toast is enabled by default and all tables will have the toast table associated with it. You can check the toast table by querying the pg_class. Toast tables are resides in the PG_Toast schema.

select relname from pg_class where oid = (select reltoastrelid from pg_class where relname=’TABLE_NAME’)

or

select oid, relname,reltoastrelid, relkind from pg_class where relname = ‘table name’

select oid, relname, relkind from pg_class where oid = ‘reltoastrelid from above query’

In the next article we will check more information about the toast table bloating and how to reclaim the space from toast table.

TOAST in the AI era: this is where embeddings live

If you have adopted pgvector, you have met TOAST again without knowing it. pgvector’s vector type is stored out-of-line in the TOAST table once it passes the threshold — a 1,536-dimension embedding is about 6 KB, so every embedding you store for RAG lives in TOAST, uncompressed. The bloat story from this post now applies to embedding tables too: watch your chunk tables, and keep embeddings in a table keyed by chunk rather than on hot parent rows. How those embeddings get used: The 9 Types of RAG, Explained