1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>50.2.TOAST</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="storage.html" title="Chapter50.Database Physical Storage">
<link rel="prev" href="storage.html" title="Chapter50.Database Physical Storage">
<link rel="next" href="storage-page-layout.html" title="50.3.Database Page Layout">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="storage-toast"></a>50.2.TOAST</h2></div></div></div>
<a name="id844943"></a><a name="id844949"></a><p>This section provides an overview of <acronym class="acronym">TOAST</acronym> (The
Oversized-Attribute Storage Technique).</p>
<p>Since <span class="productname">PostgreSQL</span> uses a fixed page size (commonly
8Kb), and does not allow tuples to span multiple pages, it's not possible to
store very large field values directly. Before <span class="productname">PostgreSQL</span> 7.1
there was a hard limit of just under one page on the total amount of data that
could be put into a table row. In release 7.1 and later, this limit is
overcome by allowing large field values to be 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 <acronym class="acronym">TOAST</acronym> (or “<span class="quote">the best thing since sliced bread</span>”).</p>
<p>Only certain data types support <acronym class="acronym">TOAST</acronym> [mdash ] there is no need to
impose the overhead on data types that cannot produce large field values.
To support <acronym class="acronym">TOAST</acronym>, a data type must have a variable-length
(<em class="firstterm">varlena</em>) representation, in which the first 32-bit word of any
stored value contains the total length of the value in bytes (including
itself). <acronym class="acronym">TOAST</acronym> does not constrain the rest of the representation.
All the C-level functions supporting a <acronym class="acronym">TOAST</acronym>-able data type must
be careful to handle <acronym class="acronym">TOAST</acronym>ed input values. (This is normally done
by invoking <code class="function">PG_DETOAST_DATUM</code> before doing anything with an input
value, but in some cases more efficient approaches are possible.)</p>
<p><acronym class="acronym">TOAST</acronym> usurps the high-order two bits of the varlena length word,
thereby limiting the logical size of any value of a <acronym class="acronym">TOAST</acronym>-able
data type to 1Gb (2<sup>30</sup> - 1 bytes). When both bits are zero,
the value is an ordinary un-<acronym class="acronym">TOAST</acronym>ed value of the data type. One
of these bits, if set, indicates that the value has been compressed and must
be decompressed before use. The other bit, if set, indicates that the value
has been stored out-of-line. In this case the remainder of the value is
actually just a pointer, and the correct data has to be found elsewhere. When
both bits are set, the out-of-line data has been compressed too. In each case
the length in the low-order bits of the varlena word indicates the actual size
of the datum, not the size of the logical value that would be extracted by
decompression or fetching of the out-of-line data.</p>
<p>If any of the columns of a table are <acronym class="acronym">TOAST</acronym>-able, the table will
have an associated <acronym class="acronym">TOAST</acronym> table, whose OID is stored in the table's
<code class="structname">pg_class</code>.<code class="structfield">reltoastrelid</code> entry. Out-of-line
<acronym class="acronym">TOAST</acronym>ed values are kept in the <acronym class="acronym">TOAST</acronym> table, as
described in more detail below.</p>
<p>The compression technique used is a fairly simple and very fast member
of the LZ family of compression techniques. See
<code class="filename">src/backend/utils/adt/pg_lzcompress.c</code> for the details.</p>
<p>Out-of-line values are divided (after compression if used) into chunks of at
most <code class="literal">TOAST_MAX_CHUNK_SIZE</code> bytes (this value is a little less than
<code class="literal">BLCKSZ/4</code>, or about 2000 bytes by default). Each chunk is stored
as a separate row in the <acronym class="acronym">TOAST</acronym> table for the owning table. Every
<acronym class="acronym">TOAST</acronym> table has the columns <code class="structfield">chunk_id</code> (an OID
identifying the particular <acronym class="acronym">TOAST</acronym>ed value),
<code class="structfield">chunk_seq</code> (a sequence number for the chunk within its value),
and <code class="structfield">chunk_data</code> (the actual data of the chunk). A unique index
on <code class="structfield">chunk_id</code> and <code class="structfield">chunk_seq</code> provides fast
retrieval of the values. A pointer datum representing an out-of-line
<acronym class="acronym">TOAST</acronym>ed value therefore needs to store the OID of the
<acronym class="acronym">TOAST</acronym> table in which to look and the OID of the specific value
(its <code class="structfield">chunk_id</code>). For convenience, pointer datums also store the
logical datum size (original uncompressed data length) and actual stored size
(different if compression was applied). Allowing for the varlena header word,
the total size of a <acronym class="acronym">TOAST</acronym> pointer datum is therefore 20 bytes
regardless of the actual size of the represented value.</p>
<p>The <acronym class="acronym">TOAST</acronym> code is triggered only
when a row value to be stored in a table is wider than <code class="literal">BLCKSZ/4</code>
bytes (normally 2Kb). The <acronym class="acronym">TOAST</acronym> code will compress and/or move
field values out-of-line until the row value is shorter than
<code class="literal">BLCKSZ/4</code> bytes 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 <acronym class="acronym">TOAST</acronym> costs if
none of the out-of-line values change.</p>
<p>The <acronym class="acronym">TOAST</acronym> code recognizes four different strategies for storing
<acronym class="acronym">TOAST</acronym>-able columns:
</p>
<div class="itemizedlist"><ul type="disc">
<li><p> <code class="literal">PLAIN</code> prevents either compression or
out-of-line storage. This is the only possible strategy for
columns of non-<acronym class="acronym">TOAST</acronym>-able data types.
</p></li>
<li><p> <code class="literal">EXTENDED</code> allows both compression and out-of-line
storage. This is the default for most <acronym class="acronym">TOAST</acronym>-able data types.
Compression will be attempted first, then out-of-line storage if
the row is still too big.
</p></li>
<li><p> <code class="literal">EXTERNAL</code> allows out-of-line storage but not
compression. Use of <code class="literal">EXTERNAL</code> will
make substring operations on wide <code class="type">text</code> and
<code class="type">bytea</code> 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.
</p></li>
<li><p> <code class="literal">MAIN</code> 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.)
</p></li>
</ul></div>
<p>
Each <acronym class="acronym">TOAST</acronym>-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 <code class="command">ALTER TABLE SET STORAGE</code>.</p>
<p>This 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 <acronym class="acronym">TOAST</acronym>ed 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 <acronym class="acronym">TOAST</acronym> 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-<acronym class="acronym">TOAST</acronym>ed
comparison table, in which all the HTML pages were cut down to 7Kb to fit.</p>
</div></body>
</html>
|
