{"Entry":{"collection":"storage","key":"storage-toast","name":"TOAST","aliases":[],"metadata":{"aliases":[],"category":"Physical structures","content_hash":"6f5d57e76908b46af732fb63a325c365c8de776da3db0c92f8857397fe176bba","derived_content_hash":"ea41d21127821df3098b24bc32439720f8fd05568e0f505265d2457c0545db6a","imported_at":"2026-09-30T00:40:47.098514+08:00","name":"TOAST","name_zh":"","slug":"storage-toast","summary":"This section provides an overview of TOAST (The Oversized-Attribute Storage Technique)."}},"Definition":{"Collection":"storage","Key":"storage-toast","SourceDatabase":"center","Version":"18","SourceTable":"storage_structure","SourceKey":"storage-toast","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"comparison_data":{"layouts":[]},"comparison_hash":"27341929154bfb55531f03501322b65af719c5561f3c4ddc9528a14aa4df69b5","description":["This section provides an overview of TOAST (The Oversized-Attribute Storage Technique)."],"description_zh":"本节概述超长属性存储技术（ TOAST , The Oversized-Attribute Storage Technique）。 PostgreSQL 使用固定页面大小（通常为 8 kB），并且不允许元组跨越多个页面。因此，无法直接存储非常大的字段值。为克服这一限制，大字段值会被压缩并且/或者拆分成多个物理行。这个过程对用户是透明的，而且对大部分后端代码的影响很小。这项技术被亲切地称为 TOAST （或者 “ 自切片面包问世以来最棒的东西 ” ）。 TOAST 基础设施也被用于改进内存中大型数据值的处理。 只有某些数据类型支持 TOAST ，因为没有必要让那些不可能产生大字段值的数据类型承担这部分额外开销。要支持 TOAST ，数据类型必须具有变长（ varlena ）表示形式，在通常情况下，任何已存储值的第一个四字节字都包含该值以字节计的总长度（包括其自身）。 TOAST 对该数据类型表示形式的其余部分没有约束。统称为 经过 TOAST 处理的值 的特殊表示形式，是通过修改或重新解释这个初始长度字来工作的。因此，支持可 TOAST 数据类型的 C 级函数，必须小心处理可能已经过 TOAST 处理的输入值：在被 去 TOAST 化 之前，一个输入值未必真正由四字节长度字和内容组成。（通常是在对输入值做任何处理之前调用 PG_DETOAST_DATUM ，但在某些情况下也可以采用更高效的方法。详见 第 36.13.1 节 。） TOAST 会占用 varlena 长度字中的两个位（在大端机器上是最高的两位，在小端机器上是最低的两位），从而把任何可 TOAST 数据类型值的逻辑大小限制为 1 GB（2 30 - 1 字节）。当这两个位都为零时，该值就是这种数据类型的普通、未经 TOAST 处理的值，长度字的其余位给出该 datum 的总大小（包括长度字），以字节计。当最高位或最低位被置位时，该值使用的不是通常的四字节头部，而是单字节头部；该字节的其余位给出 datum 的总大小（包括长度字节），以字节计。这样既能高效存储短于 127 字节的值，又仍然允许数据类型在需要时增","description_zh_html":"\u003cp\u003e存储结构 storage-toast。\u003c/p\u003e\u003ch3\u003e同版本中文手册节选\u003c/h3\u003e\u003cp\u003e\n本节概述超长属性存储技术（\u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e, The\nOversized-Attribute Storage Technique）。\n\u003c/p\u003e\u003cp\u003e\n\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 使用固定页面大小（通常为 8 kB），并且不允许元组跨越多个页面。因此，无法直接存储非常大的字段值。为克服这一限制，大字段值会被压缩并且/或者拆分成多个物理行。这个过程对用户是透明的，而且对大部分后端代码的影响很小。这项技术被亲切地称为 \u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e（或者\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e自切片面包问世以来最棒的东西\u003c/span\u003e”\u003c/span\u003e）。\u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e\n基础设施也被用于改进内存中大型数据值的处理。\n\u003c/p\u003e\u003cp\u003e\n只有某些数据类型支持 \u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e，因为没有必要让那些不可能产生大字段值的数据类型承担这部分额外开销。要支持 \u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e，数据类型必须具有变长（\u003cem class=\"firstterm\"\u003evarlena\u003c/em\u003e）表示形式，在通常情况下，任何已存储值的第一个四字节字都包含该值以字节计的总长度（包括其自身）。\u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e 对该数据类型表示形式的其余部分没有约束。统称为\u003cem class=\"firstterm\"\u003e经过 \u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e 处理的值\u003c/em\u003e的特殊表示形式，是通过修改或重新解释这个初始长度字来工作的。因此，支持可 \u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e 数据类型的\nC 级函数，必须小心处理可能已经过 \u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e 处理的输入值：在被\u003cem class=\"firstterm\"\u003e去 TOAST 化\u003c/em\u003e之前，一个输入值未必真正由四字节长度字和内容组成。（通常是在对输入值做任何处理之前调用 \u003ccode class=\"function\"\u003ePG_DETOAST_DATUM\u003c/code\u003e，但在某些情况下也可以采用更高效的方法。详见\u003ca class=\"xref\" href=\"/zh/docs/18/xtypes.html#XTYPES-TOAST\"\u003e第 36.13.1 节\u003c/a\u003e。）\n\u003c/p\u003e\u003cp\u003e\n\u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e 会占用 varlena 长度字中的两个位（在大端机器上是最高的两位，在小端机器上是最低的两位），从而把任何可\n\u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e 数据类型值的逻辑大小限制为 1 GB（2\u003csup\u003e30\u003c/sup\u003e - 1 字节）。当这两个位都为零时，该值就是这种数据类型的普通、未经 \u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e 处理的值，长度字的其余位给出该 datum 的总大小（包括长度字），以字节计。当最高位或最低位被置位时，该值使用的不是通常的四字节头部，而是单字节头部；该字节的其余位给出 datum\n的总大小（包括长度字节），以字节计。这样既能高效存储短于 127 字节的值，又仍然允许数据类型在需要时增长到 1 GB。带单字节头部的值不按任何特定边界对齐，而带四字节头部的值至少按四字节边界对齐；省去这部分对齐填充后，相对于短值而言，可以显著节省空间。作为特殊情况，如果单字节头部的其余位全为零（对于包含长度字节自身的长度而言，这是不可能的），则该值是一个指向行外数据的指针，它可能有下面将描述的几种形式。这样一种 \u003cem class=\"firstterm\"\u003eTOAST 指针\u003c/em\u003e的类型和大小，由 datum 第二个字节中存储的编码决定。最后，当最高位或最低位为零、但相邻的一位被置位时，该 datum 的内容已被压缩，使用前必须先解压。在这种情况下，四字节长度字的其余位给出的是压缩后 datum 的总大小，而不是原始数据的大小。请注意，行外数据也可能被压缩，但 varlena 头部不会告诉你是否发生了压缩，这一点要由 \u003cacronym class=\"acronym\"\u003eTOAST\u003c/acronym\u003e\n指针的内容来说明。\n\u003c/p\u003e\u003cp\u003e\u003ca href=\"/zh/docs/18/storage-toast.html\"\u003e查看完整中文手册与上下文\u003c/a\u003e。\u003c/p\u003e","facts":[{"label":"Definition scope","value":"Same-version core physical storage documentation"}],"facts_zh":[{"label":"定义范围","value":"同版本核心物理存储文档"}],"manual_html":"\u003cdiv class=\"sect1\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e66.2. TOAST \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eThis section provides an overview of TOAST (The Oversized-Attribute Storage Technique).\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. The technique is affectionately known as TOAST (or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003ethe best thing since sliced bread\u003c/span\u003e”\u003c/span\u003e). The TOAST infrastructure is also used to improve handling of large data values in-memory.\u003c/p\u003e\n\u003cp\u003eOnly certain data types support TOAST — there is no need to impose the overhead on data types that cannot produce large field values. To support TOAST, a data type must have a variable-length (\u003cem class=\"firstterm\"\u003evarlena\u003c/em\u003e) representation, in which, ordinarily, the first four-byte word of any stored value contains the total length of the value in bytes (including itself). TOAST does not constrain the rest of the data type's representation. The special representations collectively called \u003cem class=\"firstterm\"\u003eTOASTed values\u003c/em\u003e work by modifying or reinterpreting this initial length word. Therefore, the C-level functions supporting a TOAST-able data type must be careful about how they handle potentially TOASTed input values: an input might not actually consist of a four-byte length word and contents until after it's been \u003cem class=\"firstterm\"\u003edetoasted\u003c/em\u003e. (This is normally done by invoking \u003ccode class=\"function\"\u003ePG_DETOAST_DATUM\u003c/code\u003e before doing anything with an input value, but in some cases more efficient approaches are possible. See \u003ca class=\"xref\" href=\"/docs/18/xtypes.html#XTYPES-TOAST\" title=\"36.13.1. TOAST Considerations\"\u003eSection 36.13.1\u003c/a\u003e for more detail.)\u003c/p\u003e\n\u003cp\u003eTOAST usurps two bits of the varlena length word (the high-order bits on big-endian machines, the low-order bits on little-endian machines), thereby limiting the logical size of any value of a TOAST-able data type to 1 GB (2\u003csup\u003e30\u003c/sup\u003e - 1 bytes). When both bits are zero, the value is an ordinary un-TOASTed value of the data type, and the remaining bits of the length word give the total datum size (including length word) in bytes. When the highest-order or lowest-order bit is set, the value has only a single-byte header instead of the normal four-byte header, and the remaining bits of that byte give the total datum size (including length byte) in bytes. This alternative supports space-efficient storage of values shorter than 127 bytes, while still allowing the data type to grow to 1 GB at need. Values with single-byte headers aren't aligned on any particular boundary, whereas values with four-byte headers are aligned on at least a four-byte boundary; this omission of alignment padding provides additional space savings that is significant compared to short values. As a special case, if the remaining bits of a single-byte header are all zero (which would be impossible for a self-inclusive length), the value is a pointer to out-of-line data, with several possible alternatives as described below. The type and size of such a \u003cem class=\"firstterm\"\u003eTOAST pointer\u003c/em\u003e are determined by a code stored in the second byte of the datum. Lastly, when the highest-order or lowest-order bit is clear but the adjacent bit is set, the content of the datum has been compressed and must be decompressed before use. In this case the remaining bits of the four-byte length word give the total size of the compressed datum, not the original data. Note that compression is also possible for out-of-line data but the varlena header does not tell whether it has occurred — the content of the TOAST pointer tells that, instead.\u003c/p\u003e\n\u003cp\u003eThe compression technique used for either in-line or out-of-line compressed data can be selected for each column by setting the \u003ccode class=\"literal\"\u003eCOMPRESSION\u003c/code\u003e column option in \u003ccode class=\"command\"\u003eCREATE TABLE\u003c/code\u003e or \u003ccode class=\"command\"\u003eALTER TABLE\u003c/code\u003e. The default for columns with no explicit setting is to consult the \u003ca class=\"xref\" href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION\"\u003edefault_toast_compression\u003c/a\u003e parameter at the time data is inserted.\u003c/p\u003e\n\u003cp\u003eAs mentioned, there are multiple types of TOAST pointer datums. The oldest and most common type is a pointer to out-of-line data stored in a \u003cem class=\"firstterm\"\u003eTOAST table\u003c/em\u003e that is separate from, but associated with, the table containing the TOAST pointer datum itself. These \u003cem class=\"firstterm\"\u003eon-disk\u003c/em\u003e pointer datums are created by the TOAST management code (in \u003ccode class=\"filename\"\u003eaccess/common/toast_internals.c\u003c/code\u003e) when a tuple to be stored on disk is too large to be stored as-is. Further details appear in \u003ca class=\"xref\" href=\"/docs/18/storage-toast.html#STORAGE-TOAST-ONDISK\" title=\"66.2.1. Out-of-Line, On-Disk TOAST Storage\"\u003eSection 66.2.1\u003c/a\u003e. Alternatively, a TOAST pointer datum can contain a pointer to out-of-line data that appears elsewhere in memory. Such datums are necessarily short-lived, and will never appear on-disk, but they are very useful for avoiding copying and redundant processing of large data values. Further details appear in \u003ca class=\"xref\" href=\"/docs/18/storage-toast.html#STORAGE-TOAST-INMEMORY\" title=\"66.2.2. Out-of-Line, In-Memory TOAST Storage\"\u003eSection 66.2.2\u003c/a\u003e.\u003c/p\u003e\n\u003cdiv class=\"sect2\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e66.2.1. Out-of-Line, On-Disk TOAST Storage \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eIf any of the columns of a table are TOAST-able, the table will have an associated TOAST table, whose OID is stored in the table's \u003ccode class=\"structname\"\u003epg_class\u003c/code\u003e.\u003ccode class=\"structfield\"\u003ereltoastrelid\u003c/code\u003e entry. On-disk TOASTed values are kept in the TOAST table, as described in more detail below.\u003c/p\u003e\n\u003cp\u003eOut-of-line values are divided (after compression if used) into chunks of at most \u003ccode class=\"symbol\"\u003eTOAST_MAX_CHUNK_SIZE\u003c/code\u003e bytes (by default this value is chosen so that four chunk rows will fit on a page, making it about 2000 bytes). Each chunk is stored as a separate row in the TOAST table belonging to the owning table. Every TOAST table has the columns \u003ccode class=\"structfield\"\u003echunk_id\u003c/code\u003e (an OID identifying the particular TOASTed value), \u003ccode class=\"structfield\"\u003echunk_seq\u003c/code\u003e (a sequence number for the chunk within its value), and \u003ccode class=\"structfield\"\u003echunk_data\u003c/code\u003e (the actual data of the chunk). A unique index on \u003ccode class=\"structfield\"\u003echunk_id\u003c/code\u003e and \u003ccode class=\"structfield\"\u003echunk_seq\u003c/code\u003e provides fast retrieval of the values. A pointer datum representing an out-of-line on-disk TOASTed value therefore needs to store the OID of the TOAST table in which to look and the OID of the specific value (its \u003ccode class=\"structfield\"\u003echunk_id\u003c/code\u003e). For convenience, pointer datums also store the logical datum size (original uncompressed data length), physical stored size (different if compression was applied), and the compression method used, if any. Allowing for the varlena header bytes, the total size of an on-disk TOAST pointer datum is therefore 18 bytes regardless of the actual size of the represented value.\u003c/p\u003e\n\u003cp\u003eThe TOAST management code is triggered only when a row value to be stored in a table is wider than \u003ccode class=\"symbol\"\u003eTOAST_TUPLE_THRESHOLD\u003c/code\u003e bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than \u003ccode class=\"symbol\"\u003eTOAST_TUPLE_TARGET\u003c/code\u003e bytes (also normally 2 kB, adjustable) or no more gains can be had. During an UPDATE operation, values of unchanged fields are normally preserved as-is; so an UPDATE of a row with out-of-line values incurs no TOAST costs if none of the out-of-line values change.\u003c/p\u003e\n\u003cp\u003eThe TOAST management code recognizes four different strategies for storing TOAST-able columns on disk:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003ePLAIN\u003c/code\u003e prevents either compression or out-of-line storage. This is the only possible strategy for columns of non-TOAST-able data types.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003eEXTENDED\u003c/code\u003e allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003eEXTERNAL\u003c/code\u003e allows out-of-line storage but not compression. Use of \u003ccode class=\"literal\"\u003eEXTERNAL\u003c/code\u003e will make substring operations on wide \u003ccode class=\"type\"\u003etext\u003c/code\u003e and \u003ccode class=\"type\"\u003ebytea\u003c/code\u003e columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003eMAIN\u003c/code\u003e allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eEach TOAST-able data type specifies a default strategy for columns of that data type, but the strategy for a given table column can be altered with \u003ca class=\"link\" href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE ... SET STORAGE\u003c/code\u003e\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"symbol\"\u003eTOAST_TUPLE_TARGET\u003c/code\u003e can be adjusted for each table using \u003ca class=\"link\" href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE ... SET (toast_tuple_target = N)\u003c/code\u003e\u003c/a\u003e\u003c/p\u003e\n\u003cp\u003eThis scheme has a number of advantages compared to a more straightforward approach such as allowing row values to span pages. Assuming that queries are usually qualified by comparisons against relatively small key values, most of the work of the executor will be done using the main row entry. The big values of TOASTed attributes will only be pulled out (if selected at all) at the time the result set is sent to the client. Thus, the main table is much smaller and more of its rows fit in the shared buffer cache than would be the case without any out-of-line storage. Sort sets shrink also, and sorts will more often be done entirely in memory. A little test showed that a table containing typical HTML pages and their URLs was stored in about half of the raw data size including the TOAST table, and that the main table contained only about 10% of the entire data (the URLs and some small HTML pages). There was no run time difference compared to an un-TOASTed comparison table, in which all the HTML pages were cut down to 7 kB to fit.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e66.2.2. Out-of-Line, In-Memory TOAST Storage \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTOAST pointers can point to data that is not on disk, but is elsewhere in the memory of the current server process. Such pointers obviously cannot be long-lived, but they are nonetheless useful. There are currently two sub-cases: pointers to \u003cem class=\"firstterm\"\u003eindirect\u003c/em\u003e data and pointers to \u003cem class=\"firstterm\"\u003eexpanded\u003c/em\u003e data.\u003c/p\u003e\n\u003cp\u003eIndirect TOAST pointers simply point at a non-indirect varlena value stored somewhere in memory. This case was originally created merely as a proof of concept, but it is currently used during logical decoding to avoid possibly having to create physical tuples exceeding 1 GB (as pulling all out-of-line field values into the tuple might do). The case is of limited use since the creator of the pointer datum is entirely responsible that the referenced data survives for as long as the pointer could exist, and there is no infrastructure to help with this.\u003c/p\u003e\n\u003cp\u003eExpanded TOAST pointers are useful for complex data types whose on-disk representation is not especially suited for computational purposes. As an example, the standard varlena representation of a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e array includes dimensionality information, a nulls bitmap if there are any null elements, then the values of all the elements in order. When the element type itself is variable-length, the only way to find the \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e'th element is to scan through all the preceding elements. This representation is appropriate for on-disk storage because of its compactness, but for computations with the array it's much nicer to have an \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eexpanded\u003c/span\u003e”\u003c/span\u003e or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003edeconstructed\u003c/span\u003e”\u003c/span\u003e representation in which all the element starting locations have been identified. The TOAST pointer mechanism supports this need by allowing a pass-by-reference Datum to point to either a standard varlena value (the on-disk representation) or a TOAST pointer that points to an expanded representation somewhere in memory. The details of this expanded representation are up to the data type, though it must have a standard header and meet the other API requirements given in \u003ccode class=\"filename\"\u003esrc/include/utils/expandeddatum.h\u003c/code\u003e. C-level functions working with the data type can choose to handle either representation. Functions that do not know about the expanded representation, but simply apply \u003ccode class=\"function\"\u003ePG_DETOAST_DATUM\u003c/code\u003e to their inputs, will automatically receive the traditional varlena representation; so support for an expanded representation can be introduced incrementally, one function at a time.\u003c/p\u003e\n\u003cp\u003eTOAST pointers to expanded values are further broken down into \u003cem class=\"firstterm\"\u003eread-write\u003c/em\u003e and \u003cem class=\"firstterm\"\u003eread-only\u003c/em\u003e pointers. The pointed-to representation is the same either way, but a function that receives a read-write pointer is allowed to modify the referenced value in-place, whereas one that receives a read-only pointer must not; it must first create a copy if it wants to make a modified version of the value. This distinction and some associated conventions make it possible to avoid unnecessary copying of expanded values during query execution.\u003c/p\u003e\n\u003cp\u003eFor all types of in-memory TOAST pointer, the TOAST management code ensures that no such pointer datum can accidentally get stored on disk. In-memory TOAST pointers are automatically expanded to normal in-line varlena values before storage — and then possibly converted to on-disk TOAST pointers, if the containing tuple would otherwise be too big.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","manual_path":"/docs/18/storage-toast.html","related":[{"label":"Table AM","url":"/wiki/tableam/?v=18"},{"label":"Storage Parameters","url":"/wiki/relopts/?v=18"}],"release":{"channel":"stable","label":"18.6","major":"18","ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sections":[],"sections_zh":[],"signature":"","sources":[{"label":"PostgreSQL 18 English manual","path":"storage-toast.html","sha256":"acf77f02dfcc808c3e24632ad05409f7f45f62773defd38453a433c15393df90","url":"/docs/18/storage-toast.html"}],"tables":[],"tables_zh":[],"zh_from":{"authored_translation":"source-derived-identity","body_sha256":"008ac8fa81eda8a169efda234039ae3bc72043952fd1093803daa3aed56ba544","kind":"same-version-chinese-manual-with-source-derived-identity","manual":{"anchor":"","build_id":107,"extraction":"concept-introduction","fragment_sha256":"ead2cc0b8ab9dd931de4582e088c7bf6ba3c630848339cbbc370a3ca85b832bf","locale":"zh-Hans","page_key":"storage-toast.html","source_hash":"3935f1dbbda82ec808008d41c43fd71d2cfc64e5cadae86b68e182a61e4e5d05","source_revision":"unknown:rendered-translation-baseline","translated_from_revision":null,"translation_revision":"git:b1b500221dbe5437cfca004d6b04d3f6e422765d;html-sha256:08e987a66af27b98444a8057c256314eafa13611a3fb9e888214fc436824244b","url":"/zh/docs/18/storage-toast.html","version":"18"},"runtime_rerun":false,"source_facts_revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}},"ManualEvidence":{"manual_path":"/docs/18/storage-toast.html","release":{"channel":"stable","label":"18.6","major":"18","ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sources":[{"label":"PostgreSQL 18 English manual","path":"storage-toast.html","sha256":"acf77f02dfcc808c3e24632ad05409f7f45f62773defd38453a433c15393df90","url":"/docs/18/storage-toast.html"}]},"MeasuredEvidence":{}},"Text":{"Collection":"storage","Key":"storage-toast","SourceDatabase":"center","Version":"18","Locale":"en","Title":"TOAST","Summary":"This section provides an overview of TOAST (The Oversized-Attribute Storage Technique).","BodyHTML":"\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2\u003e66.2. TOAST \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eThis section provides an overview of TOAST (The Oversized-Attribute Storage Technique).\u003c/p\u003e\n\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. The technique is affectionately known as TOAST (or \u003cspan\u003e“\u003cspan\u003ethe best thing since sliced bread\u003c/span\u003e”\u003c/span\u003e). The TOAST infrastructure is also used to improve handling of large data values in-memory.\u003c/p\u003e\n\u003cp\u003eOnly certain data types support TOAST — there is no need to impose the overhead on data types that cannot produce large field values. To support TOAST, a data type must have a variable-length (\u003cem\u003evarlena\u003c/em\u003e) representation, in which, ordinarily, the first four-byte word of any stored value contains the total length of the value in bytes (including itself). TOAST does not constrain the rest of the data type\u0026#39;s representation. The special representations collectively called \u003cem\u003eTOASTed values\u003c/em\u003e work by modifying or reinterpreting this initial length word. Therefore, the C-level functions supporting a TOAST-able data type must be careful about how they handle potentially TOASTed input values: an input might not actually consist of a four-byte length word and contents until after it\u0026#39;s been \u003cem\u003edetoasted\u003c/em\u003e. (This is normally done by invoking \u003ccode\u003ePG_DETOAST_DATUM\u003c/code\u003e before doing anything with an input value, but in some cases more efficient approaches are possible. See \u003ca href=\"/docs/18/xtypes.html#XTYPES-TOAST\" rel=\"nofollow\"\u003eSection 36.13.1\u003c/a\u003e for more detail.)\u003c/p\u003e\n\u003cp\u003eTOAST usurps two bits of the varlena length word (the high-order bits on big-endian machines, the low-order bits on little-endian machines), thereby limiting the logical size of any value of a TOAST-able data type to 1 GB (2\u003csup\u003e30\u003c/sup\u003e - 1 bytes). When both bits are zero, the value is an ordinary un-TOASTed value of the data type, and the remaining bits of the length word give the total datum size (including length word) in bytes. When the highest-order or lowest-order bit is set, the value has only a single-byte header instead of the normal four-byte header, and the remaining bits of that byte give the total datum size (including length byte) in bytes. This alternative supports space-efficient storage of values shorter than 127 bytes, while still allowing the data type to grow to 1 GB at need. Values with single-byte headers aren\u0026#39;t aligned on any particular boundary, whereas values with four-byte headers are aligned on at least a four-byte boundary; this omission of alignment padding provides additional space savings that is significant compared to short values. As a special case, if the remaining bits of a single-byte header are all zero (which would be impossible for a self-inclusive length), the value is a pointer to out-of-line data, with several possible alternatives as described below. The type and size of such a \u003cem\u003eTOAST pointer\u003c/em\u003e are determined by a code stored in the second byte of the datum. Lastly, when the highest-order or lowest-order bit is clear but the adjacent bit is set, the content of the datum has been compressed and must be decompressed before use. In this case the remaining bits of the four-byte length word give the total size of the compressed datum, not the original data. Note that compression is also possible for out-of-line data but the varlena header does not tell whether it has occurred — the content of the TOAST pointer tells that, instead.\u003c/p\u003e\n\u003cp\u003eThe compression technique used for either in-line or out-of-line compressed data can be selected for each column by setting the \u003ccode\u003eCOMPRESSION\u003c/code\u003e column option in \u003ccode\u003eCREATE TABLE\u003c/code\u003e or \u003ccode\u003eALTER TABLE\u003c/code\u003e. The default for columns with no explicit setting is to consult the \u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION\" rel=\"nofollow\"\u003edefault_toast_compression\u003c/a\u003e parameter at the time data is inserted.\u003c/p\u003e\n\u003cp\u003eAs mentioned, there are multiple types of TOAST pointer datums. The oldest and most common type is a pointer to out-of-line data stored in a \u003cem\u003eTOAST table\u003c/em\u003e that is separate from, but associated with, the table containing the TOAST pointer datum itself. These \u003cem\u003eon-disk\u003c/em\u003e pointer datums are created by the TOAST management code (in \u003ccode\u003eaccess/common/toast_internals.c\u003c/code\u003e) when a tuple to be stored on disk is too large to be stored as-is. Further details appear in \u003ca href=\"/docs/18/storage-toast.html#STORAGE-TOAST-ONDISK\" rel=\"nofollow\"\u003eSection 66.2.1\u003c/a\u003e. Alternatively, a TOAST pointer datum can contain a pointer to out-of-line data that appears elsewhere in memory. Such datums are necessarily short-lived, and will never appear on-disk, but they are very useful for avoiding copying and redundant processing of large data values. Further details appear in \u003ca href=\"/docs/18/storage-toast.html#STORAGE-TOAST-INMEMORY\" rel=\"nofollow\"\u003eSection 66.2.2\u003c/a\u003e.\u003c/p\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e66.2.1. Out-of-Line, On-Disk TOAST Storage \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eIf any of the columns of a table are TOAST-able, the table will have an associated TOAST table, whose OID is stored in the table\u0026#39;s \u003ccode\u003epg_class\u003c/code\u003e.\u003ccode\u003ereltoastrelid\u003c/code\u003e entry. On-disk TOASTed values are kept in the TOAST table, as described in more detail below.\u003c/p\u003e\n\u003cp\u003eOut-of-line values are divided (after compression if used) into chunks of at most \u003ccode\u003eTOAST_MAX_CHUNK_SIZE\u003c/code\u003e bytes (by default this value is chosen so that four chunk rows will fit on a page, making it about 2000 bytes). Each chunk is stored as a separate row in the TOAST table belonging to the owning table. Every TOAST table has the columns \u003ccode\u003echunk_id\u003c/code\u003e (an OID identifying the particular TOASTed value), \u003ccode\u003echunk_seq\u003c/code\u003e (a sequence number for the chunk within its value), and \u003ccode\u003echunk_data\u003c/code\u003e (the actual data of the chunk). A unique index on \u003ccode\u003echunk_id\u003c/code\u003e and \u003ccode\u003echunk_seq\u003c/code\u003e provides fast retrieval of the values. A pointer datum representing an out-of-line on-disk TOASTed value therefore needs to store the OID of the TOAST table in which to look and the OID of the specific value (its \u003ccode\u003echunk_id\u003c/code\u003e). For convenience, pointer datums also store the logical datum size (original uncompressed data length), physical stored size (different if compression was applied), and the compression method used, if any. Allowing for the varlena header bytes, the total size of an on-disk TOAST pointer datum is therefore 18 bytes regardless of the actual size of the represented value.\u003c/p\u003e\n\u003cp\u003eThe TOAST management code is triggered only when a row value to be stored in a table is wider than \u003ccode\u003eTOAST_TUPLE_THRESHOLD\u003c/code\u003e bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than \u003ccode\u003eTOAST_TUPLE_TARGET\u003c/code\u003e bytes (also normally 2 kB, adjustable) or no more gains can be had. During an UPDATE operation, values of unchanged fields are normally preserved as-is; so an UPDATE of a row with out-of-line values incurs no TOAST costs if none of the out-of-line values change.\u003c/p\u003e\n\u003cp\u003eThe TOAST management code recognizes four different strategies for storing TOAST-able columns on disk:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003ePLAIN\u003c/code\u003e prevents either compression or out-of-line storage. This is the only possible strategy for columns of non-TOAST-able data types.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003eEXTENDED\u003c/code\u003e allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003eEXTERNAL\u003c/code\u003e allows out-of-line storage but not compression. Use of \u003ccode\u003eEXTERNAL\u003c/code\u003e will make substring operations on wide \u003ccode\u003etext\u003c/code\u003e and \u003ccode\u003ebytea\u003c/code\u003e columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e\u003ccode\u003eMAIN\u003c/code\u003e allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eEach TOAST-able data type specifies a default strategy for columns of that data type, but the strategy for a given table column can be altered with \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\" rel=\"nofollow\"\u003e\u003ccode\u003eALTER TABLE ... SET STORAGE\u003c/code\u003e\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003eTOAST_TUPLE_TARGET\u003c/code\u003e can be adjusted for each table using \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\" rel=\"nofollow\"\u003e\u003ccode\u003eALTER TABLE ... SET (toast_tuple_target = N)\u003c/code\u003e\u003c/a\u003e\u003c/p\u003e\n\u003cp\u003eThis scheme has a number of advantages compared to a more straightforward approach such as allowing row values to span pages. Assuming that queries are usually qualified by comparisons against relatively small key values, most of the work of the executor will be done using the main row entry. The big values of TOASTed attributes will only be pulled out (if selected at all) at the time the result set is sent to the client. Thus, the main table is much smaller and more of its rows fit in the shared buffer cache than would be the case without any out-of-line storage. Sort sets shrink also, and sorts will more often be done entirely in memory. A little test showed that a table containing typical HTML pages and their URLs was stored in about half of the raw data size including the TOAST table, and that the main table contained only about 10% of the entire data (the URLs and some small HTML pages). There was no run time difference compared to an un-TOASTed comparison table, in which all the HTML pages were cut down to 7 kB to fit.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e66.2.2. Out-of-Line, In-Memory TOAST Storage \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTOAST pointers can point to data that is not on disk, but is elsewhere in the memory of the current server process. Such pointers obviously cannot be long-lived, but they are nonetheless useful. There are currently two sub-cases: pointers to \u003cem\u003eindirect\u003c/em\u003e data and pointers to \u003cem\u003eexpanded\u003c/em\u003e data.\u003c/p\u003e\n\u003cp\u003eIndirect TOAST pointers simply point at a non-indirect varlena value stored somewhere in memory. This case was originally created merely as a proof of concept, but it is currently used during logical decoding to avoid possibly having to create physical tuples exceeding 1 GB (as pulling all out-of-line field values into the tuple might do). The case is of limited use since the creator of the pointer datum is entirely responsible that the referenced data survives for as long as the pointer could exist, and there is no infrastructure to help with this.\u003c/p\u003e\n\u003cp\u003eExpanded TOAST pointers are useful for complex data types whose on-disk representation is not especially suited for computational purposes. As an example, the standard varlena representation of a \u003cspan\u003ePostgreSQL\u003c/span\u003e array includes dimensionality information, a nulls bitmap if there are any null elements, then the values of all the elements in order. When the element type itself is variable-length, the only way to find the \u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e\u0026#39;th element is to scan through all the preceding elements. This representation is appropriate for on-disk storage because of its compactness, but for computations with the array it\u0026#39;s much nicer to have an \u003cspan\u003e“\u003cspan\u003eexpanded\u003c/span\u003e”\u003c/span\u003e or \u003cspan\u003e“\u003cspan\u003edeconstructed\u003c/span\u003e”\u003c/span\u003e representation in which all the element starting locations have been identified. The TOAST pointer mechanism supports this need by allowing a pass-by-reference Datum to point to either a standard varlena value (the on-disk representation) or a TOAST pointer that points to an expanded representation somewhere in memory. The details of this expanded representation are up to the data type, though it must have a standard header and meet the other API requirements given in \u003ccode\u003esrc/include/utils/expandeddatum.h\u003c/code\u003e. C-level functions working with the data type can choose to handle either representation. Functions that do not know about the expanded representation, but simply apply \u003ccode\u003ePG_DETOAST_DATUM\u003c/code\u003e to their inputs, will automatically receive the traditional varlena representation; so support for an expanded representation can be introduced incrementally, one function at a time.\u003c/p\u003e\n\u003cp\u003eTOAST pointers to expanded values are further broken down into \u003cem\u003eread-write\u003c/em\u003e and \u003cem\u003eread-only\u003c/em\u003e pointers. The pointed-to representation is the same either way, but a function that receives a read-write pointer is allowed to modify the referenced value in-place, whereas one that receives a read-only pointer must not; it must first create a copy if it wants to make a modified version of the value. This distinction and some associated conventions make it possible to avoid unnecessary copying of expanded values during query execution.\u003c/p\u003e\n\u003cp\u003eFor all types of in-memory TOAST pointer, the TOAST management code ensures that no such pointer datum can accidentally get stored on disk. In-memory TOAST pointers are automatically expanded to normal in-line varlena values before storage — and then possibly converted to on-disk TOAST pointers, if the containing tuple would otherwise be too big.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"fbb2e42bd2dd9609ac7385019b044771bab274af5f7577698bda7a11b2e0042d","Payload":{"description":["This section provides an overview of TOAST (The Oversized-Attribute Storage Technique)."],"manual_html":"\u003cdiv class=\"sect1\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e66.2. TOAST \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003eThis section provides an overview of TOAST (The Oversized-Attribute Storage Technique).\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. The technique is affectionately known as TOAST (or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003ethe best thing since sliced bread\u003c/span\u003e”\u003c/span\u003e). The TOAST infrastructure is also used to improve handling of large data values in-memory.\u003c/p\u003e\n\u003cp\u003eOnly certain data types support TOAST — there is no need to impose the overhead on data types that cannot produce large field values. To support TOAST, a data type must have a variable-length (\u003cem class=\"firstterm\"\u003evarlena\u003c/em\u003e) representation, in which, ordinarily, the first four-byte word of any stored value contains the total length of the value in bytes (including itself). TOAST does not constrain the rest of the data type's representation. The special representations collectively called \u003cem class=\"firstterm\"\u003eTOASTed values\u003c/em\u003e work by modifying or reinterpreting this initial length word. Therefore, the C-level functions supporting a TOAST-able data type must be careful about how they handle potentially TOASTed input values: an input might not actually consist of a four-byte length word and contents until after it's been \u003cem class=\"firstterm\"\u003edetoasted\u003c/em\u003e. (This is normally done by invoking \u003ccode class=\"function\"\u003ePG_DETOAST_DATUM\u003c/code\u003e before doing anything with an input value, but in some cases more efficient approaches are possible. See \u003ca class=\"xref\" href=\"/docs/18/xtypes.html#XTYPES-TOAST\" title=\"36.13.1. TOAST Considerations\"\u003eSection 36.13.1\u003c/a\u003e for more detail.)\u003c/p\u003e\n\u003cp\u003eTOAST usurps two bits of the varlena length word (the high-order bits on big-endian machines, the low-order bits on little-endian machines), thereby limiting the logical size of any value of a TOAST-able data type to 1 GB (2\u003csup\u003e30\u003c/sup\u003e - 1 bytes). When both bits are zero, the value is an ordinary un-TOASTed value of the data type, and the remaining bits of the length word give the total datum size (including length word) in bytes. When the highest-order or lowest-order bit is set, the value has only a single-byte header instead of the normal four-byte header, and the remaining bits of that byte give the total datum size (including length byte) in bytes. This alternative supports space-efficient storage of values shorter than 127 bytes, while still allowing the data type to grow to 1 GB at need. Values with single-byte headers aren't aligned on any particular boundary, whereas values with four-byte headers are aligned on at least a four-byte boundary; this omission of alignment padding provides additional space savings that is significant compared to short values. As a special case, if the remaining bits of a single-byte header are all zero (which would be impossible for a self-inclusive length), the value is a pointer to out-of-line data, with several possible alternatives as described below. The type and size of such a \u003cem class=\"firstterm\"\u003eTOAST pointer\u003c/em\u003e are determined by a code stored in the second byte of the datum. Lastly, when the highest-order or lowest-order bit is clear but the adjacent bit is set, the content of the datum has been compressed and must be decompressed before use. In this case the remaining bits of the four-byte length word give the total size of the compressed datum, not the original data. Note that compression is also possible for out-of-line data but the varlena header does not tell whether it has occurred — the content of the TOAST pointer tells that, instead.\u003c/p\u003e\n\u003cp\u003eThe compression technique used for either in-line or out-of-line compressed data can be selected for each column by setting the \u003ccode class=\"literal\"\u003eCOMPRESSION\u003c/code\u003e column option in \u003ccode class=\"command\"\u003eCREATE TABLE\u003c/code\u003e or \u003ccode class=\"command\"\u003eALTER TABLE\u003c/code\u003e. The default for columns with no explicit setting is to consult the \u003ca class=\"xref\" href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION\"\u003edefault_toast_compression\u003c/a\u003e parameter at the time data is inserted.\u003c/p\u003e\n\u003cp\u003eAs mentioned, there are multiple types of TOAST pointer datums. The oldest and most common type is a pointer to out-of-line data stored in a \u003cem class=\"firstterm\"\u003eTOAST table\u003c/em\u003e that is separate from, but associated with, the table containing the TOAST pointer datum itself. These \u003cem class=\"firstterm\"\u003eon-disk\u003c/em\u003e pointer datums are created by the TOAST management code (in \u003ccode class=\"filename\"\u003eaccess/common/toast_internals.c\u003c/code\u003e) when a tuple to be stored on disk is too large to be stored as-is. Further details appear in \u003ca class=\"xref\" href=\"/docs/18/storage-toast.html#STORAGE-TOAST-ONDISK\" title=\"66.2.1. Out-of-Line, On-Disk TOAST Storage\"\u003eSection 66.2.1\u003c/a\u003e. Alternatively, a TOAST pointer datum can contain a pointer to out-of-line data that appears elsewhere in memory. Such datums are necessarily short-lived, and will never appear on-disk, but they are very useful for avoiding copying and redundant processing of large data values. Further details appear in \u003ca class=\"xref\" href=\"/docs/18/storage-toast.html#STORAGE-TOAST-INMEMORY\" title=\"66.2.2. Out-of-Line, In-Memory TOAST Storage\"\u003eSection 66.2.2\u003c/a\u003e.\u003c/p\u003e\n\u003cdiv class=\"sect2\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e66.2.1. Out-of-Line, On-Disk TOAST Storage \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eIf any of the columns of a table are TOAST-able, the table will have an associated TOAST table, whose OID is stored in the table's \u003ccode class=\"structname\"\u003epg_class\u003c/code\u003e.\u003ccode class=\"structfield\"\u003ereltoastrelid\u003c/code\u003e entry. On-disk TOASTed values are kept in the TOAST table, as described in more detail below.\u003c/p\u003e\n\u003cp\u003eOut-of-line values are divided (after compression if used) into chunks of at most \u003ccode class=\"symbol\"\u003eTOAST_MAX_CHUNK_SIZE\u003c/code\u003e bytes (by default this value is chosen so that four chunk rows will fit on a page, making it about 2000 bytes). Each chunk is stored as a separate row in the TOAST table belonging to the owning table. Every TOAST table has the columns \u003ccode class=\"structfield\"\u003echunk_id\u003c/code\u003e (an OID identifying the particular TOASTed value), \u003ccode class=\"structfield\"\u003echunk_seq\u003c/code\u003e (a sequence number for the chunk within its value), and \u003ccode class=\"structfield\"\u003echunk_data\u003c/code\u003e (the actual data of the chunk). A unique index on \u003ccode class=\"structfield\"\u003echunk_id\u003c/code\u003e and \u003ccode class=\"structfield\"\u003echunk_seq\u003c/code\u003e provides fast retrieval of the values. A pointer datum representing an out-of-line on-disk TOASTed value therefore needs to store the OID of the TOAST table in which to look and the OID of the specific value (its \u003ccode class=\"structfield\"\u003echunk_id\u003c/code\u003e). For convenience, pointer datums also store the logical datum size (original uncompressed data length), physical stored size (different if compression was applied), and the compression method used, if any. Allowing for the varlena header bytes, the total size of an on-disk TOAST pointer datum is therefore 18 bytes regardless of the actual size of the represented value.\u003c/p\u003e\n\u003cp\u003eThe TOAST management code is triggered only when a row value to be stored in a table is wider than \u003ccode class=\"symbol\"\u003eTOAST_TUPLE_THRESHOLD\u003c/code\u003e bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than \u003ccode class=\"symbol\"\u003eTOAST_TUPLE_TARGET\u003c/code\u003e bytes (also normally 2 kB, adjustable) or no more gains can be had. During an UPDATE operation, values of unchanged fields are normally preserved as-is; so an UPDATE of a row with out-of-line values incurs no TOAST costs if none of the out-of-line values change.\u003c/p\u003e\n\u003cp\u003eThe TOAST management code recognizes four different strategies for storing TOAST-able columns on disk:\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003ePLAIN\u003c/code\u003e prevents either compression or out-of-line storage. This is the only possible strategy for columns of non-TOAST-able data types.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003eEXTENDED\u003c/code\u003e allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003eEXTERNAL\u003c/code\u003e allows out-of-line storage but not compression. Use of \u003ccode class=\"literal\"\u003eEXTERNAL\u003c/code\u003e will make substring operations on wide \u003ccode class=\"type\"\u003etext\u003c/code\u003e and \u003ccode class=\"type\"\u003ebytea\u003c/code\u003e columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003e\u003ccode class=\"literal\"\u003eMAIN\u003c/code\u003e allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eEach TOAST-able data type specifies a default strategy for columns of that data type, but the strategy for a given table column can be altered with \u003ca class=\"link\" href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE ... SET STORAGE\u003c/code\u003e\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003ccode class=\"symbol\"\u003eTOAST_TUPLE_TARGET\u003c/code\u003e can be adjusted for each table using \u003ca class=\"link\" href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE ... SET (toast_tuple_target = N)\u003c/code\u003e\u003c/a\u003e\u003c/p\u003e\n\u003cp\u003eThis scheme has a number of advantages compared to a more straightforward approach such as allowing row values to span pages. Assuming that queries are usually qualified by comparisons against relatively small key values, most of the work of the executor will be done using the main row entry. The big values of TOASTed attributes will only be pulled out (if selected at all) at the time the result set is sent to the client. Thus, the main table is much smaller and more of its rows fit in the shared buffer cache than would be the case without any out-of-line storage. Sort sets shrink also, and sorts will more often be done entirely in memory. A little test showed that a table containing typical HTML pages and their URLs was stored in about half of the raw data size including the TOAST table, and that the main table contained only about 10% of the entire data (the URLs and some small HTML pages). There was no run time difference compared to an un-TOASTed comparison table, in which all the HTML pages were cut down to 7 kB to fit.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e66.2.2. Out-of-Line, In-Memory TOAST Storage \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eTOAST pointers can point to data that is not on disk, but is elsewhere in the memory of the current server process. Such pointers obviously cannot be long-lived, but they are nonetheless useful. There are currently two sub-cases: pointers to \u003cem class=\"firstterm\"\u003eindirect\u003c/em\u003e data and pointers to \u003cem class=\"firstterm\"\u003eexpanded\u003c/em\u003e data.\u003c/p\u003e\n\u003cp\u003eIndirect TOAST pointers simply point at a non-indirect varlena value stored somewhere in memory. This case was originally created merely as a proof of concept, but it is currently used during logical decoding to avoid possibly having to create physical tuples exceeding 1 GB (as pulling all out-of-line field values into the tuple might do). The case is of limited use since the creator of the pointer datum is entirely responsible that the referenced data survives for as long as the pointer could exist, and there is no infrastructure to help with this.\u003c/p\u003e\n\u003cp\u003eExpanded TOAST pointers are useful for complex data types whose on-disk representation is not especially suited for computational purposes. As an example, the standard varlena representation of a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e array includes dimensionality information, a nulls bitmap if there are any null elements, then the values of all the elements in order. When the element type itself is variable-length, the only way to find the \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e'th element is to scan through all the preceding elements. This representation is appropriate for on-disk storage because of its compactness, but for computations with the array it's much nicer to have an \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eexpanded\u003c/span\u003e”\u003c/span\u003e or \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003edeconstructed\u003c/span\u003e”\u003c/span\u003e representation in which all the element starting locations have been identified. The TOAST pointer mechanism supports this need by allowing a pass-by-reference Datum to point to either a standard varlena value (the on-disk representation) or a TOAST pointer that points to an expanded representation somewhere in memory. The details of this expanded representation are up to the data type, though it must have a standard header and meet the other API requirements given in \u003ccode class=\"filename\"\u003esrc/include/utils/expandeddatum.h\u003c/code\u003e. C-level functions working with the data type can choose to handle either representation. Functions that do not know about the expanded representation, but simply apply \u003ccode class=\"function\"\u003ePG_DETOAST_DATUM\u003c/code\u003e to their inputs, will automatically receive the traditional varlena representation; so support for an expanded representation can be introduced incrementally, one function at a time.\u003c/p\u003e\n\u003cp\u003eTOAST pointers to expanded values are further broken down into \u003cem class=\"firstterm\"\u003eread-write\u003c/em\u003e and \u003cem class=\"firstterm\"\u003eread-only\u003c/em\u003e pointers. The pointed-to representation is the same either way, but a function that receives a read-write pointer is allowed to modify the referenced value in-place, whereas one that receives a read-only pointer must not; it must first create a copy if it wants to make a modified version of the value. This distinction and some associated conventions make it possible to avoid unnecessary copying of expanded values during query execution.\u003c/p\u003e\n\u003cp\u003eFor all types of in-memory TOAST pointer, the TOAST management code ensures that no such pointer datum can accidentally get stored on disk. In-memory TOAST pointers are automatically expanded to normal in-line varlena values before storage — and then possibly converted to on-disk TOAST pointers, if the containing tuple would otherwise be too big.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","related":[{"label":"Table AM","url":"/wiki/tableam/?v=18"},{"label":"Storage Parameters","url":"/wiki/relopts/?v=18"}],"sections":[],"tables":[]}},"RequestedLocale":"en","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}