Mastering PostgreSQL Storage: The Complete Tale of the TOAST Table and Its Impact on Database Performance

Have you ever wondered how PostgreSQL manages to efficiently store and access data that exceeds its 8 KB page size limit? The answer lies in one of its most powerful internal mechanisms — TOAST (The Oversized-Attribute Storage Technique).

In this technical deep dive, “Tale of the PostgreSQL TOAST Table,” Shailesh Rangani, Director and Global Practice Leader of PostgreSQL Services at Datavail, breaks down the architecture, performance impact, and optimization techniques behind PostgreSQL’s TOAST tables — a crucial feature enabling scalable data storage.

PostgreSQL’s 8 KB page size restriction means a single row cannot span multiple pages, which poses a challenge when handling large data values such as long text fields or binary data. The TOAST mechanism elegantly resolves this limitation by breaking oversized field values into smaller segments, storing them “out of line” in a dedicated TOAST table associated with the user table.

This presentation explores how TOAST works under the hood, what storage strategies are available, and how to optimize performance when working with large data types in PostgreSQL. Shailesh Rangani draws on real-world experience to explain best practices and provide insights that database professionals can immediately apply.

What You’ll Learn:

Introduction to the TOAST Table Mechanism: Understand how PostgreSQL handles oversized attributes and the purpose of TOAST tables.

Storage Strategy Options: Learn about PLAIN, MAIN, EXTERNAL, and EXTENDED storage types — and how each impacts compression, storage efficiency, and performance.

Performance Optimization: Discover how storage settings influence query speed, I/O performance, and data retrieval efficiency.

Metadata and System Catalog Insights: Learn how to identify, query, and analyze TOAST table information directly from PostgreSQL’s metadata.

Impact of Text Size on Performance: Explore the direct relationship between column data size, disk usage, and query latency.

Whether you’re a database administrator, PostgreSQL developer, or cloud architect, this session offers invaluable insights into how PostgreSQL’s storage engine maintains efficiency and scalability for large datasets.

Presented by:
Shailesh Rangani
Director & Global Practice Leader – PostgreSQL Services, Datavail

👉 Explore the full presentation here:
https://www.datavail.com/resources/tale-of-the-postgresql-toast-table/

Uncover the story behind TOAST — and master the techniques that keep PostgreSQL performant, efficient, and robust at scale.

1. Tale of the
PostgreSQL
TOAST Table

2. www.datavail.com 2
Shailesh Rangani is Practice Lead for the PostgreSQL
services with 18+ years’ experience in database
domain.
He holds certifications on cloud platforms like AWS,
Azure, and OCI, along with database platforms like
PostgreSQL, MongoDB, Oracle and DB2 LUW.
He is an expert in the design, deployment,
administration, and management of data-intensive
applications that enable organizations to effectively
analyze and process large volumes of structured and
unstructured data.
Shailesh specializes in Cloud platforms and DBMS
technologies. He has successfully delivered the data
architecture strategy for projects and large-scale
platforms.
www.datavail.com 2
S
Shailesh
Rangani
Director & Global Practice
Lead -PostgreSQL,
Datavail

3. www.datavail.com 3
Datavail at
a Glance
Delivering a superior
approach to leveraging
data through the
application of a tech-
enabled global delivery
model & deep
specialization in databases,
data management, and
application services.
$25
M
Invested
in IP that improves the
service experience and
drives efficiency
13
+
Years
building and operating
mission critical data and
application systems
1,000
+
Employees
staffed 24x7, resolving over
2,000,000 incidents per year
2022
www.datavail.com 3

4. www.datavail.com 4
Leveraging Datavail Core Competencies
Helping customers leverage data to drive business outcomes.
Databases
Business
Applications
Data
Management
& Analytics
ON-PREM
CLOUD
Infrastructure & Security
Databases
Modernization and management of client’s data estates
across all leading relational and modern data platforms
Data Management & Analytics
Data management, governance, integration, visualization,
analytics, and reporting
Applications
Create, modernize, and manage business applications
(Microsoft custom solutions and Oracle packaged
applications)
On-Prem, AWS, Azure, Oracle Cloud Infrastructure
Plan, architect, migrate, modernize, and manage databases,
analytics, and applications

5. TOAST Table

6. www.datavail.com 6
Database Page/Block Behavior (Extended Row)
Oracle
Row Chaining
DB2 LUW
extended_row_sz(DB Parameter should be ON)
stored as large object (LOB) data outside of the data row
SQL Server
ROW_OVERFLOW_DATA allocation unit
LOB_DATA allocation unit
4K 4K
Block 1 Block 2
IN_ROW_DATA ROW_OVERFLOW_DATA LOB_DATA

7. www.datavail.com 7
PostgreSQLhas fixed page size: Most deployments are 8 kB
postgres=> SELECT current_setting('block_size');
current_setting
-----------------
8192
(1 row)
Tuple cannot span multiple pages
PostgreSQL will compress and try to fit into 2kB
TOAST_TUPLE_THRESHOLD (2K default)
To Modify:
ALTER TABLE<table_name> SET (TOAST_TUPLE_TARGET = ZZZ);
ABC needs to be between "128" and "8160"
PostgreSQL Page
HeaderP1 P2 P3 P4
SpecialTuple1Tuple2Tuple3Tuple4
8K

8. www.datavail.com 8
PostgreSQL Page… Continued
What if compressed value is > 2K?
•Data will be split into smaller chunk (< 2K)
•Stored into TOAST table
•TOAST =
The Oversized-Attribute Storage Technique
Only variable-length data types supported in
TOAST table
Toasting is enabled by default
All tables will have TOAST table associated
with it
Toast table gets created in “pg_toast” schema
relation | size
---------------------------------- +---------
pg_toast.pg_toast_36565463 | 54 GB
pg_toast.pg_toast_6578665 | 34 GB
pg_toast.pg_toast_76890 | 21 GB
pg_toast.pg_toast_16123 | 15 GB
pg_toast.pg_toast_16789 | 10 GB

9. Attribute Storage
Technique

10. www.datavail.com 10
Column VS
Storage
Technique
postgres=>
CREATE TABLE emp_data
(
id int,
salnumeric,
name varchar(10),
emp_infotext
);
CREATE TABLE
***********************************************************
postgres=>
SELECT attname, atttypid::regtype,
CASE attstorage
WHEN 'p' THEN 'plain'
WHEN 'e' THEN 'external'
WHEN 'm' THEN 'main'
WHEN 'x' THEN 'extended'
END AS storage
FROM pg_attribute
WHERE attrelid= ‘emp_data'::regclassAND attnum> 0;
attname| atttypid | storage
----------+-------------------+----------
id | integer | plain
sal | numeric | main
name | character varying | extended
emp_info| text | extended

11. www.datavail.com 11
Column VS Storage Technique…
postgres=> \d+ emp_data
Table "public.emp_data"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+----------------------- +-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
sal | numeric | | | | main | |
name | character varying(10) | | | | extended| |
emp_info| text | | | | extended| |
Access method: heap
************************************************************************************************************

12. www.datavail.com 12
Column Storage Modification Options
*****************************************************************************************************************
postgres=> ALTER TABLE emp_dataALTER COLUMN emp_infoSET STORAGE external;
ALTER TABLE
*****************************************************************************************************************
postgres=> \d+ emp_data
Table "public.emp_data"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+----------------------- +-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
sal | numeric | | | | main | |
name | character varying(10) | | | | extended | |
emp_info| text | | | | external| |
Access method: heap
*****************************************************************************************************************
postgres=> ALTER TABLE emp_dataALTER COLUMN id SET STORAGE main/extended/external;
ERROR: column data type integer can only have storage PLAIN
*****************************************************************************************************************
postgres=> ALTER TABLE emp_dataALTER COLUMN name SET STORAGE main/plain/external;
ALTER TABLE

13. www.datavail.com 13
Storage
Techniques
TOAST-able
Columns
PLAIN
•Prevents compression & out-of-line storage
•TOAST is not in use at all
•For short data types like “integer type”
EXTENDED
•Allows compression & out-of-line storage
•Default storing technique for TOAST-able data types
•Attempt for compression (up to 2K)
•If still large, then out-of-line storage
EXTERNAL
•Allows out-of-line storage but not compression
•Increased storage
•Few operations will be faster on text of bytea
columns
MAIN
•Allows compression but not out-of-line storage
•In rare cases, out-of-line storage will still be
performed for such columns

14. www.datavail.com 14
TOAST Table
postgres=> SELECT relnamespace::regnamespace, relname
FROM pg_class
WHERE oid= (
SELECT reltoastrelid
FROM pg_classWHERE relname= 'emp_data' );
relnamespace| relname
--------------+----------------
pg_toast | pg_toast_30449
(1 row)
*****************************************************************************************************************
postgres=> SELECT reltoastrelid
FROM pg_classWHERE relname= 'emp_data';
reltoastrelid
---------------
30449
(1 row)
*****************************************************************************************************************
postgres=>\d+ pg_toast.pg_toast_30449
TOAST table "pg_toast.pg_toast_30449"
Column | Type | Storage
------------+---------+---------
chunk_id | oid | plain
chunk_seq | integer | plain
chunk_data| bytea | plain
Owning table: "public.datavail_pg_table "
Indexes:
"pg_toast_30449_index" PRIMARY KEY, btree(chunk_id, chunk_seq)
Access method: heap
chunk_id •A reference to a toasted value.
chunk_seq •A sequence within the chunk.
chunk_data •The actual chunk data.

15. www.datavail.com 15
Sample
Table/
Records
IdSalName Emp Info
112.5John <DATA> -> 2.5K
220.0Michael <DATA> -> 1.5K
315.0Olivia <DATA> -> 6.5K
413.5Sam <DATA> -> 4.5K

16. www.datavail.com 16
*****************************************************************************************************************
postgres=>
SELECT
chunk_id,
chunk_seq,
length(chunk_data),
left(encode(chunk_data,'escape')::text, 10) || '...' || right(encode( chunk_data,'escape')::text, 10) toast_data
FROM pg_toast.pg_toast_30449;
chunk_id| chunk_seq| length | toast_data
----------+-----------+--------+-------------------------
30458 | 0 | 1996 | EEDJCGCDHF...CLCIIFKKCL
30458 | 1 | 504 | HKDKKHILJE...DHHEILDKJD
30459 | 0 | 1996 | FDKGKIIFDK...BKLHJCIHDF
30459 | 1 | 1996 | CCJIHEGDDD...FKDHLDHDEK
30459 | 2 | 1996 | HKKJDEFCBD...JKBIGIJHLE
30459 | 3 | 512 | KJLJHFDCGE...GHCEKHJGJE
30460 | 0 | 1996 | FHGHJHKIDI...FICFCFCGDJ
30460 | 1 | 1996 | JLCJEKGHFK...CLDCDDBLCJ
30460 | 2 | 508 | EGJIKKKJBH...IEKDBDLJFJ
(9 rows)
*****************************************************************************************************************
ID=1
ID=3
ID=4

17. www.datavail.com 17
TOAST Table
chunk_id chunk_seqchunk_data(< 2K)
30458 0 <DATA>
30458 1 <DATA>
30459 0 <DATA>
30459 1 <DATA>
30459 2 <DATA>
30459 3 <DATA>
30460 0 <DATA>
30460 1 <DATA>
30460 2 <DATA>
IdSal Name Emp_Info
112.5John TOAST Pointer (30458)
220.0Michael<DATA>
315.0OliviaTOAST Pointer (30459)
413.5Sam TOAST Pointer (30460)
pg_toast.pg_toast_30449
EMP_INFO

18. www.datavail.com 18
EXTENDED VS EXTERNAL
postgres=> \d+ emp_data_extended
Table "public.emp_data_extended "
Column | Type | Collation | Nullable | Default | Storage | Stats target | Descript ion
----------+---------+-----------+----------+----------------------------------------------- +----------+--------------+-------------
id | integer | | not null | nextval('emp_data_extended_id_seq '::regclass) | plain | |
emp_info| text | | | | extended| |
Access method: heap
***************************************************************************************************************************** ******
postgres=> \d+ emp_data_external
Table "public.emp_data_external "
Column | Type | Collation | Nullable | Default | Storage | Stats target | Descript ion
----------+---------+-----------+----------+----------------------------------------------- +----------+--------------+-------------
id | integer | | not null | nextval('emp_data_external_id_seq '::regclass) | plain | |
emp_info| text | | | | external| |
Access method: heap
***************************************************************************************************************************** ******
datavail=> insert into emp_data_extended (emp_info) values
(
array_to_string(ARRAY(
SELECT chr((ascii('B') + round(random() * 5)) :: integer)
FROM generate_series(1,2100)),''));
INSERT 0 1
***************************************************************************************************************************** ******
postgres=> insert into emp_data_external (emp_info) values
(
array_to_string(ARRAY(
SELECT chr((ascii('B') + round(random() * 5)) :: integer)
FROM generate_series(1,2100)),''));
INSERT 0 1

19. www.datavail.com 19
EXTENDED VS EXTERNAL
emp_data_extended
postgres=> SELECT
chunk_id,
chunk_seq,
length(chunk_data),
left(encode(chunk_data,'escape')::text, 10) || '...' || right(encode( chunk_data,'escape')::text, 10) toast_data
FROM pg_toast.pg_toast_30431;
chunk_id| chunk_seq| length | toast_data
----------+-----------+--------+------------
(0 rows)
emp_data_exeternal
postgres=> SELECT
chunk_id,
chunk_seq,
length(chunk_data),
left(encode(chunk_data,'escape')::text, 10) || '...' || right(encode( chunk_data,'escape')::text, 10) toast_data
FROM pg_toast.pg_toast_30440;
chunk_id| chunk_seq| length | toast_data
----------+-----------+--------+-------------------------
30447 | 0 | 1996 | BDEDBCEFDE...BBBEEGEFFC
30447 | 1 | 104 | BFGDEDGCCC...EDBBFDCGCD
(2 rows)
Extended: Compress and check if < 2K {If Yes then no TOAST else TOAST}
External: Check if < 2K {If Yes then no TOAST else TOAST}

20. www.datavail.com 20
TOAST VS Performance Impact
postgres=> \d+ emp_data_2K
Table "public.emp_data_2K"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Descript ion
----------+---------+-----------+----------+----------------------------------------------- +----------+--------------+-------------
id | integer | | not null | nextval('emp_data_2K_id_seq':: regclass) | plain | |
emp_info| text | | | | extended | |
Access method: heap
***************************************************************************************************************************** ******
postgres=> \d+ emp_data_4K
Table "public.emp_data_4K"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Descript ion
----------+---------+-----------+----------+----------------------------------------------- +----------+--------------+-------------
id | integer | | not null | nextval('emp_data_4K_id_seq':: regclass) | plain | |
emp_info| text | | | | extended | |
Access method: heap

21. www.datavail.com 21
TOAST VS Performance Impact
postgres=> EXPLAIN (ANALYZE) SELECT * FROM emp_data_2K WHERE id = 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------- ----------
Gather (cost=1000.00..128604.27 rows=1 width=1808) (actual time=78.805..1299.065 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on toast_test_medium (cost=0.00..127604.17 rows=1 width=1808) (actual time=874.001..1277.811 rows=0 loops=3)
Filter: (id = 6000)
Rows Removed by Filter: 166666
Planning Time: 0.062 ms
Execution Time: 1299.116 ms
(8 rows)
***************************************************************************************************************************** ******
postgres=> EXPLAIN (ANALYZE) SELECT * FROM emp_data_4K WHERE id = 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------- --
Gather (cost=1000.00..6789.27 rows=1 width=22) (actual time=3.099..128.207 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on toast_test_large (cost=0.00..5789.17 rows=1 width=22) (actual time=56.233..91.847 rows=0 loops=3)
Filter: (id = 6000)
Rows Removed by Filter: 166666
Planning Time: 0.102 ms
Execution Time: 128.246 ms
(8 rows)

22. www.datavail.com 22
Vacuum and Analyze on TOAST Table
postgres=>
SELECT schemaname, relname, last_vacuum, last_analyze, n_dead_tup
FROM pg_stat_all_tables
WHERE schemaname='pg_toast' order by n_dead_tupdesc limit 5;
schemaname| relname | last_vacuum | last_analyze| n_dead_tup
------------+-----------------+------------------------------- +--------------+------------
pg_toast | pg_toast_540865 | | | 6844
***************************************************************************************************************************** ******
postgres=> vacuum pg_toast.pg_toast_540865; (Vacuuming base table also vacuums TOAST table automatically)
VACUUM
***************************************************************************************************************************** ******
postgres=>
SELECT schemaname, relname, last_vacuum, last_analyze, n_dead_tup
FROM pg_stat_all_tables
WHERE schemaname='pg_toast' order by n_dead_tupdesc limit 5;
schemaname| relname | last_vacuum | last_analyze| n_dead_tup
------------+-----------------+------------------------------- +--------------+------------
pg_toast | pg_toast_540865 | 2022 -09-01 17:46:36.185345+00 | | 0
***************************************************************************************************************************** ******
postgres=> analyze pg_toast.pg_toast_540865;
WARNING: skipping "pg_toast_540865" ---cannot analyze non-tables or special system tables
ANALYZE

23. www.datavail.com 23
TOAST Compression Algorithm
postgres=> show default_toast_compression ;
default_toast_compression
---------------------------
pglz
(1 row)
TOAST’s new compression algorithm LZ4 in PostgreSQL 14
postgres=>
create table emp_data
(id int, emp_datatext compression pglz, emp_data_2 text COMPRESSION lz4, emp_data_3 text);
CREATE TABLE
postgres=# \d+ emp_data
Table "public.emp_data"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
emp_data | text | | | | extended | pglz | |
emp_data_2 | text | | | | extended | lz4 | |
emp_data_3 | text | | | | extended | | |
Access method: heap
LZ4 has significantly improved the compression and decompression performance of the TOAST

24. www.datavail.com 24
TOAST table mechanism
TOAST table storage strategy options
Optimize query time with the TOAST table storage strategy
Information about TOAST tables using PostgreSQL metadata
Impact of Texts Size on PostgreSQL performance
Use external attribute if you are using image or very large complex LOB data
Adjust TOAST_TUPLE_THRESHOLD according to your need
Monitor dead tuples on the TOAST tables along with base tables
Test TOAST compression using LZ4 option
Summary

25. Complete
Session
Evaluation
For a Chance
to WIN
Virtual Reality Headset
w/Controller &
Headphones

26. www.datavail.com 26
Thank You
Shailesh Rangani
Director & Global Practice Lead : PostgreSQL Services
[email protected]
[phone]