TOAST Table
Overview
The oversized-attribute storage technique (TOAST) is a special storage technique. Only some data in the variable-length form support TOAST, such as the text type. TOAST is used to overcome the limitation that a single column in the database is too large to be directly stored in a standard data page. In GaussDB storage mode, all columns in a record are called a tuple, and multiple tuples form a page. The basic unit of data in file storage is page. GaussDB uses a fixed page size, typically 8 KB. When the size of a row of data exceeds the page size, the database employs TOAST to ensure that these oversized columns can be effectively stored and managed.
Out-of-line storage is a part of TOAST. This technology compresses or slices large column values into multiple physical rows and stores them in another table (known as the TOAST table). The main table retains only references (usually pointers) to these large column values. This avoids performance problems caused by large data in a single row and ensures effective access to large column data.
If a table has an associated TOAST table, the reltoastrelid column in the PG_CLASS system catalog records the OID of the TOAST table. If the table has no such an associated TOAST table, the value of reltoastrelid is 0.
GaussDB supports multiple storage policies for table columns:
- PLAIN:
- Compression and out-of-line storage are not allowed. This policy is usually used for data types that cannot use the TOAST mechanism.
- It is applicable to data types that can be stored without TOAST, such as integer and character types.
- This policy is not applicable to types whose storage length exceeds the page size, for example, text.
- EXTENDED:
- Compression and out-of-line storage are allowed. This is the default storage policy for most data types that can use the TOAST mechanism.
- The database first attempts to compress the data. If the compressed data is still too large, the data is stored out of the line.
- EXTENDED is the default policy for most TOAST data types, such as text and bytea.
- EXTERNAL:
- Out-of-line storage is allowed, but compression is not allowed.
- This strategy makes string operations of the text and bytea data types faster because the entire row of data does not need to be read and decompressed.
- MAIN:
- Compression is allowed.
- It is mainly applicable to data of the numeric type.
- The database first attempts to compress the data. If the compressed data is still too large, the data is stored out of the line.
Examples
-- Create tables. gaussdb=# CREATE TABLE tb_t1(id int, info text); CREATE TABLE gaussdb=# CREATE TABLE tb_t2(id int, age int); CREATE TABLE -- Check the information of the tb_t1 table. The info column is of the text type and Storage is set to extended. -- Below is part of the command output: gaussdb=# \d+ tb_t1 Table "public.tb_t1" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | -- Check the information of the tb_t2 table. All columns are of the int type. -- Below is part of the command output: gaussdb=# \d+ tb_t2 Table "public.tb_t2" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- id | integer | | plain | | age | integer | | plain | | -- The following query shows that the tb_t1 table has associated TOAST tables, while the tb_t2 table does not have any associated TOAST tables. gaussdb=# SELECT oid,relname,reltoastrelid FROM pg_class WHERE relname in ('tb_t1','tb_t2'); oid | relname | reltoastrelid -------+---------+--------------- 16832 | tb_t1 | 16835 16837 | tb_t2 | 0 (2 rows) -- Query information about the TOAST table associated with tb_t1. gaussdb=# SELECT relname,oid FROM pg_class WHERE oid = '16835'; relname | oid ----------------+------- pg_toast_16832 | 16835 (1 row) -- Drop. gaussdb=# DROP TABLE tb_t1,tb_t2; DROP TABLE
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot