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.

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s