{"id":19059,"date":"2022-09-20T15:34:37","date_gmt":"2022-09-20T13:34:37","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=19059"},"modified":"2022-09-20T15:34:37","modified_gmt":"2022-09-20T13:34:37","slug":"toasting-strategies-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/","title":{"rendered":"TOASTing strategies in PostgreSQL"},"content":{"rendered":"<p>In Oracle a single row can span more than one block, which is called row chaining. As PostgreSQL does not allow a row to span multiple blocks, there needs to be a way to work around that. The default block size in PostgreSQL is 8kB (same as in Oracle), so something needs to happen when a row comes in which is larger than that. Actually the limit is less than 8kB, but this will be the topic of a follow up post.<\/p>\n<p>What PostgreSQL will do transparently in the background if a tuple\/row is too large, is called <a href=\"https:\/\/www.postgresql.org\/docs\/current\/storage-toast.html\" target=\"_blank\" rel=\"noopener\">TOAST<\/a>ing. In short, PostgreSQL will compress, and later on maybe move the row out of the main table. Before we take a look at how that happens, we need to look at the TOASTing strategies. The strategy for TOSTing is defined on the data type, it is not a property of the row\/tuple. You can ask <a href=\"https:\/\/www.postgresql.org\/docs\/current\/catalog-pg-attribute.html\">pg_attribute<\/a> for the strategies currently defined for the columns currently available in the system (this is from a fresh PostgreSQL 16devel build):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# select distinct attstorage from pg_attribute ;\n attstorage \n------------\n m\n p\n x\n(3 rows)\n<\/pre>\n<p>The meaning of the characters is:<\/p>\n<ul>\n<li>m = Main: This means no compression, no out of line storage. This is for data types which are not TOASTable at all.<\/li>\n<li>p = Plain: Compression, but no out of line storage.<\/li>\n<li>x = Extended: Compression and out of line storage.<\/li>\n<\/ul>\n<p>We can easily verify this by creating a small table and ask PostgreSQL afterwards for the TOASTing strategies of the columns we&#8217;ve defined:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# create table t ( a int, b text, c numeric );\nCREATE TABLE\npostgres=# select attname, atttypid::regtype,\n                  case attstorage when 'p' then 'plain'\n                                  when 'e' then 'external'\n                                  when 'm' then 'main'\n                                  when 'x' then 'extended'\n                  end AS strategy\n             from pg_attribute\n            where attrelid = 't'::regclass and attnum &gt; 0;\n attname | atttypid | strategy \n---------+----------+----------\n a       | integer  | plain\n b       | text     | extended\n c       | numeric  | main\n(3 rows)\n<\/pre>\n<p>This means an integer column can be compressed, a text column can be both compressed and moved out of line, and a numeric column can neither be compressed nor moved out of line. If you want to know which strategy is defined for which (data) type, you can directly ask <a href=\"https:\/\/www.postgresql.org\/docs\/current\/catalog-pg-type.html\" target=\"_blank\" rel=\"noopener\">pg_type<\/a> for the strategy of the type:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# select typname, typstorage \n             from pg_type where typname in ('int4','numeric','text');\n typname | typstorage \n---------+------------\n int4    | p\n text    | x\n numeric | m\n(3 rows)\n<\/pre>\n<p>What happens when you create your table is, that the strategy for the data types for your columns is copied from the underlying types, which are defined in pg_type.<\/p>\n<p>For now we&#8217;ve seen three TOASTing strategies, but there is one more, which is called &#8220;external&#8221;. This one is useful for data, for which you already know, that compression does not make any sense. Examples are PDFs or PNGs or MP3s. Trying to compress those kinds of data would only waste CPU cycles, and you can actually avoid that by setting the strategy for those columns to extended:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# create table tt ( a bytea );\nCREATE TABLE\npostgres=# select attname, atttypid::regtype,\n                  case attstorage when 'p' then 'plain'\n                                  when 'e' then 'external'\n                                  when 'm' then 'main'\n                                  when 'x' then 'extended'\n                  end AS strategy\n             from pg_attribute\n            where attrelid = 'tt'::regclass and attnum &gt; 0;\n attname | atttypid | strategy \n---------+----------+----------\n a       | bytea    | extended\n(1 row)\npostgres=# alter table tt alter column a set storage extended;\nALTER TABLE\npostgres=# select attname, atttypid::regtype,\n                  case attstorage when 'p' then 'plain'\n                                  when 'e' then 'external'\n                                  when 'm' then 'main'\n                                  when 'x' then 'extended'\n                  end AS strategy\n             from pg_attribute\n            where attrelid = 'tt'::regclass and attnum &gt; 0;\n attname | atttypid | strategy \n---------+----------+----------\n a       | bytea    | extended\n(1 row)\n<\/pre>\n<p>The complete list of strategies if therefore:<\/p>\n<ul>\n<li>m = Main: This means no compression, no out of line storage. This is for data types which are not TOASTable at all.<\/li>\n<li>p = Plain: Compression, but no out of line storage.<\/li>\n<li>x = Extended: Compression and out of line storage.<\/li>\n<li>e = External: No compression, but out of line storage.<\/li>\n<\/ul>\n<p>Now, that we know the strategies, we can  have a look at TOASTing in more detail in the next post.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle a single row can span more than one block, which is called row chaining. As PostgreSQL does not allow a row to span multiple blocks, there needs to be a way to work around that. The default block size in PostgreSQL is 8kB (same as in Oracle), so something needs to happen when&hellip;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-19059","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>TOASTing strategies in PostgreSQL - dbi services | Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"TOASTing strategies in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"In Oracle a single row can span more than one block, which is called row chaining. As PostgreSQL does not allow a row to span multiple blocks, there needs to be a way to work around that. The default block size in PostgreSQL is 8kB (same as in Oracle), so something needs to happen when&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-20T13:34:37+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"headline\":\"TOASTing strategies in PostgreSQL\",\"datePublished\":\"2022-09-20T13:34:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/\"},\"wordCount\":485,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/\",\"name\":\"TOASTing strategies in PostgreSQL - dbi services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-09-20T13:34:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/toasting-strategies-in-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"TOASTing strategies in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"TOASTing strategies in PostgreSQL - dbi services | Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"TOASTing strategies in PostgreSQL","og_description":"In Oracle a single row can span more than one block, which is called row chaining. As PostgreSQL does not allow a row to span multiple blocks, there needs to be a way to work around that. The default block size in PostgreSQL is 8kB (same as in Oracle), so something needs to happen when&hellip;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2022-09-20T13:34:37+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"headline":"TOASTing strategies in PostgreSQL","datePublished":"2022-09-20T13:34:37+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/"},"wordCount":485,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/","name":"TOASTing strategies in PostgreSQL - dbi services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-09-20T13:34:37+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"TOASTing strategies in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19059","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=19059"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19059\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=19059"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=19059"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=19059"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}