PostgreSQL TOAST 存储技术

TOAST

超大属性存储技术：The Oversized-Attribute Storage Techniques

它是PostgreSQL的一种机制，用于处理大块数据以适应页面缓冲区。当待插入的数据(元组)超过TOAST_TUPLE_THRESHOLD(默认2KB)时候，PostgreSQL将压缩数据，以适应2KB的缓冲区大小。如果对大列(变长数据类型)项数据压缩没有产生更小的块(即小于2KB)，那么该数据将会被分割成更小的块(chunks)，然后创建一个TOAST表来存储和管理该元组。

TOAST仅适用于那些可变长的数据类型，比如VARCHAR、TEXT、JSON和BYTEA等。

对于BLOB、CBLOG等数据类型，它们将使用大对象存储机制来存储。

TOAST 策略

PLAIN 策略

此策略禁用压缩和行外存储。这是唯一可用于non-TOAST-able（不能用TOAST技术）的数据类型（如整数或布尔值）的策略。示例：如果有一个包含整数列的表，您不想将它们保持在行内。

EXTENDED 策略

此策略允许压缩和行外存储。这是大多数TOAST-able（能用TOAST技术）的数据类型（如text或bytea）的默认策略。系统将首先尝试压缩数据；如果行太大，它将存储在行外。示例：有一个包含大量文本列的表，并且想要减小其在磁盘上的大小；此策略将首先尝试压缩它。如果它不适合，行将存储在行外。

EXTERNAL 策略

此策略允许行外存储但禁用压缩。此策略对于经常使用子字符串操作访问的text和bytea列很有用。访问这些列会更快，因为系统只需要获取行外值的所需部分。示例：有一个包含大量文本的表，并且希望在需要子字符串操作时提高性能；此策略将把它存储在行外并避免压缩。

MAIN 策略

此策略允许压缩但禁用行外存储。行外存储仍会执行，但只有在没有其他方法使行足够小以适合一页时才作为最后的手段。示例：有一个包含大量不经常访问的数据的表，并且希望压缩它以节省磁盘空间；此策略将压缩它但会避免把它存储在行外。

因此理解为：尽量不使用行外存储更贴切。

示例

示例example表

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    large_text text,
    my_int int,
    fixed_1024 varchar(1024),
    fixed_2048 varchar(2048),
    fixed_4096 varchar(4096),
    fixed_8192 varchar(8192),
    fixed_10000 varchar(10000)
);

查看Storage存储方式

postgres=# \d+ example
                                                                  Table "public.example"
   Column    |           Type           | Collation | Nullable |               Default               | Storage  | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+-------------------------------------+----------+-------------+--------------+-------------
 id          | integer                  |           | not null | nextval('example_id_seq'::regclass) | plain    |             |              | 
 large_text  | text                     |           |          |                                     | extended |             |              | 
 my_int      | integer                  |           |          |                                     | plain    |             |              | 
 fixed_1024  | character varying(1024)  |           |          |                                     | extended |             |              | 
 fixed_2048  | character varying(2048)  |           |          |                                     | extended |             |              | 
 fixed_4096  | character varying(4096)  |           |          |                                     | extended |             |              | 
 fixed_8192  | character varying(8192)  |           |          |                                     | extended |             |              | 
 fixed_10000 | character varying(10000) |           |          |                                     | extended |             |              | 
Indexes:
    "example_pkey" PRIMARY KEY, btree (id)
Access method: heap

修改存储方式

语法

ALTER TABLE table_name ALTER column_name SET STORAGE storage_name;

将fixed_4096列的存储方式由extended改为external。

ALTER TABLE example ALTER fixed_4096 SET STORAGE EXTERNAL;

查看最新存储情况

postgres=# \d+ example
                                                                  Table "public.example"
   Column    |           Type           | Collation | Nullable |               Default               | Storage  | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+-------------------------------------+----------+-------------+--------------+-------------
 id          | integer                  |           | not null | nextval('example_id_seq'::regclass) | plain    |             |              | 
 large_text  | text                     |           |          |                                     | extended |             |              | 
 my_int      | integer                  |           |          |                                     | plain    |             |              | 
 fixed_1024  | character varying(1024)  |           |          |                                     | extended |             |              | 
 fixed_2048  | character varying(2048)  |           |          |                                     | extended |             |              | 
 fixed_4096  | character varying(4096)  |           |          |                                     | external |             |              | 
 fixed_8192  | character varying(8192)  |           |          |                                     | extended |             |              | 
 fixed_10000 | character varying(10000) |           |          |                                     | extended |             |              | 
Indexes:
    "example_pkey" PRIMARY KEY, btree (id)
Access method: heap

测试用 SQL

INSERT INTO example (fixed_4096) VALUES (repeat('A', 4096));

select pg_size_pretty(pg_table_size('example'));

select relname from pg_class where relname = 'pg_toast_' || (select oid from pg_class where relname = 'example');

select pg_size_pretty(pg_table_size('pg_toast.pg_toast_25073'));

select pg_size_pretty(pg_indexes_size('pg_toast.pg_toast_25073'));

参考

https://www.percona.com/blog/unlocking-the-secrets-of-toast-how-to-optimize-large-column-storage-in-postgresql-for-top-performance-and-scalability/