{"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-11-18T17:42:50","modified_gmt":"2022-11-18T16:42:50","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><!--more--><\/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;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[2602],"type_dbi":[],"class_list":["post-19059","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql-2"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>TOASTing strategies in PostgreSQL - dbi 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;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-20T13:34:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-11-18T16:42:50+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"TOASTing strategies in PostgreSQL\",\"datePublished\":\"2022-09-20T13:34:37+00:00\",\"dateModified\":\"2022-11-18T16:42:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/\"},\"wordCount\":485,\"commentCount\":0,\"keywords\":[\"postgresql\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"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 Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-09-20T13:34:37+00:00\",\"dateModified\":\"2022-11-18T16:42:50+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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 Blog\",\"description\":\"\",\"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\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"TOASTing strategies in PostgreSQL - dbi 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;]","og_url":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2022-09-20T13:34:37+00:00","article_modified_time":"2022-11-18T16:42:50+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"TOASTing strategies in PostgreSQL","datePublished":"2022-09-20T13:34:37+00:00","dateModified":"2022-11-18T16:42:50+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/toasting-strategies-in-postgresql\/"},"wordCount":485,"commentCount":0,"keywords":["postgresql"],"articleSection":["Database Administration &amp; Monitoring"],"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 Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-09-20T13:34:37+00:00","dateModified":"2022-11-18T16:42:50+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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 Blog","description":"","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":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_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\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=19059"}],"version-history":[{"count":9,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19059\/revisions"}],"predecessor-version":[{"id":19080,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19059\/revisions\/19080"}],"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"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=19059"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}