Download to read offline
Uploaded byDatavail
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.
Mastering PostgreSQL Storage: The Complete Tale of the TOAST Table and Its Impact on Database Performance
- 1.
- 2. www.datavail.com 2 Shailesh Ranganiis 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 aGlance 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 DatavailCore 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.
- 6. www.datavail.com 6 Database Page/BlockBehavior (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 PostgreSQL hasfixed 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 Header P1 P2 P3 P4 Special Tuple1 Tuple2 Tuple3 Tuple4 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.
- 10. www.datavail.com 10 Column VS Storage Technique postgres=> CREATETABLE emp_data ( id int, sal numeric, name varchar(10), emp_info text ); 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'::regclass AND 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 VSStorage 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 StorageModification Options ***************************************************************************************************************** postgres=> ALTER TABLE emp_data ALTER COLUMN emp_info SET 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_data ALTER COLUMN id SET STORAGE main/extended/external; ERROR: column data type integer can only have storage PLAIN ***************************************************************************************************************** postgres=> ALTER TABLE emp_data ALTER COLUMN name SET STORAGE main/plain/external; ALTER TABLE
- 13. www.datavail.com 13 Storage Techniques TOAST-able Columns PLAIN • Preventscompression & 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_class WHERE relname = 'emp_data' ); relnamespace | relname --------------+---------------- pg_toast | pg_toast_30449 (1 row) ***************************************************************************************************************** postgres=> SELECT reltoastrelid FROM pg_class WHERE 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 Id SalName Emp Info 1 12.5 John <DATA> -> 2.5K 2 20.0 Michael <DATA> -> 1.5K 3 15.0 Olivia <DATA> -> 6.5K 4 13.5 Sam <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_idchunk_seq chunk_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> Id Sal Name Emp_Info 1 12.5 John TOAST Pointer (30458) 2 20.0 Michael <DATA> 3 15.0 Olivia TOAST Pointer (30459) 4 13.5 Sam TOAST Pointer (30460) pg_toast.pg_toast_30449 EMP_INFO
- 18. www.datavail.com 18 EXTENDED VSEXTERNAL 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 VSEXTERNAL 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 VSPerformance Impact postgres=> d+ emp_data_2K Table "public.emp_data_2K" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------+---------+-----------+----------+-----------------------------------------------+----------+--------------+------------- 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 | Description ----------+---------+-----------+----------+-----------------------------------------------+----------+--------------+------------- 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 VSPerformance 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 andAnalyze 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_tup desc 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_tup desc 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 CompressionAlgorithm 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_data text 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 tablemechanism 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 toWIN Virtual Reality Headset w/Controller & Headphones
- 26. www.datavail.com 26 Thank You ShaileshRangani Director & Global Practice Lead : PostgreSQL Services shailesh.rangani@datavail.com +1 866-623-4956