解锁TOAST的秘密：如何优化PostgreSQL的大型列存储以最佳性能和可扩展性-摩杜云开发者社区

PostgreSQL是一个很棒的数据库，但如果要存储图像、视频、音频文件或其他大型数据对象时，需要TOAST以获得最佳性能。本文主要介绍使用TOAST技术来提高性能和可扩展性。

TOAST 介绍

PG使用固定大小的页面，这就给存储大值带来了巨大挑战。为解决这个问题，大数据值被压缩并分成多个较小的块。这个过程自动完成，不会显著影响数据库的使用方式。这种称为 TOAST 的技术改进了大数据值在数据库中的存储和使用方式。TOAST技术通过将大数据对象分成更小的块并将他们与主表分开存储，从而允许高效存储大数据对象。这可以提高查询和索引的性能，并减少存储数据所需要的磁盘空间量。当表包含 OID、bytea 或具有 TOASTable 存储类的任何其他数据类型的列时，PG会自动创建TOAST表。然后使用TOAST表存储大数据对象，而主表存储对TOAST表的引用。下面是一个案例：

创建一个包含大字段的表：

CREATE TABLE images ( id SERIAL PRIMARY KEY, data BYTEA );

插入一个图像

INSERT INTO images (data) VALUES (E'\\x...');

从pg_class中可以看到大数据对象存储到了TOAST表

SELECT relname, relkind FROM pg_class WHERE relname LIKE 'pg_toast%';

上面案例中，images表包含一个data列，类型为bytea。它可以保存大量二进制数据。当向表中插入大图像时，PG会自动创建一个TOAST表，将图像数据和主表分开存储。然后查询 pg_class 系统表可以看到已创建的TOAST表。但是需要注意：虽然TOAST表有助于存储大对象数据，但会增加数据库的复杂性，因此应该谨慎使用。此外，在某些情况下，当数据分布在不同的表中时，查询性能会降低，具体取决于查询条件。如果由很多大数据，不需要查询/索引，可以考虑另一种方式：将其存储在文件系统中数据库之外，并将对他的引用存储在数据库中，类似于TOAST表的工作方式。 PG中，可以通过列上设置“storage”属性来使用不同的TOAST存储策略。

CREATE TABLE mytable ( id serial primary key, large_column dat);
postgres=# \d+ mytable
Table "public.mytable"
Column | Type    | Collation | Nullable | Default                             | Storage 
-------------+---------+-----------+----------+-------------------------------------+----------
id | integer |           | not null | nextval('mytable_id_seq'::regclass) | plain 
large_column | bytea |           |          |                                     | extended 
Indexes:
"mytable_pkey" PRIMARY KEY, btree (id)
Access method: heap
postgres=# ALTER TABLE mytable ALTER COLUMN large_column SET STORAGE PLAIN;
ALTER TABLE
postgres=# \d+ mytable
Table "public.mytable"
Column | Type    | Collation | Nullable | Default                             | Storage 
-------------+---------+-----------+----------+-------------------------------------+----------
id | integer |           | not null | nextval('mytable_id_seq'::regclass) | plain 
large_column | bytea |           |          |                                     | plain 
Indexes:
"mytable_pkey" PRIMARY KEY, btree (id)
Access method: heap
postgres=# ALTER TABLE mytable ALTER COLUMN large_column SET STORAGE MAIN; 
ALTER TABLE
postgres=# \d+ mytable
Table "public.mytable"
Column | Type    | Collation | Nullable | Default                             | Storage 
-------------+---------+-----------+----------+-------------------------------------+----------
id | integer |           | not null | nextval('mytable_id_seq'::regclass) | plain 
large_column | bytea |           |          |                                     | main 
Indexes:
"mytable_pkey" PRIMARY KEY, btree (id)
Access method: heap

其中“data_type”是列的数据类型（例如 text、bytea）。“strategy”是4种TOAST存储策略：PLAIN、EXTENDED、EXTERNAL、MAIN。可以使用pg_attribute系统表来查询列使用的策略：

SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'tablename'::regclass AND attnum > 0;
postgres=# SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'mytable'::regclass AND attnum > 0;
attname | attstorage 
--------------+------------
id | p
large_column | m
(2 rows)

值得注意的是：大多数列的默认存储策略是“EXTENDED”（压缩和离线存储），可以随时更改表列的存储策略。但是，请务必注意，更改列的存储策略可能会影响查询的性能和表的大小。因此，建议使用不同存储策略测试您的特定用例，以确定哪个提供最佳性能。

PLAIN 策略该策略禁用压缩和行外存储，还禁用对varlena类型使用单字节头。这是唯一可用于非TOAST数据类型（例如整数和布尔值）的策略。
EXTENDED策略该策略允许压缩和行外存储。这是大多数支持TOAST的数据类型的默认策略。系统将首先尝试压缩数据。若行太大，则会将其存储在行外。比如，由一个包含大量文本的表，希望减少在磁盘上的大小，该策略将首先尝试压缩，如果仍旧不合适，则将行存储在行外。
EXTERNAL 策略该策略允许行外存储，但禁止压缩。该策略对于经常使用子字符串操作访问的text和bytea列很有用。因为系统只需要获取行外值所需的部分，所以访问这些列很快。比如由一个包含大量文本列的表，希望在需要进行子字符串操作时提高性能，该策略会将其存储在行外并避免压缩
MAIN 策略该策略允许压缩，但禁用行外存储。行外存储仍会执行，但是仅作为最后的手段。当没有其他方法使行足够小以适合页面时才会行外存储。比如，有一个表，其中包含大量不经常访问的数据列，希望对其进行压缩以节省空间；该策略将压缩它，但会避免将其存储在行外。

避免PG中使用TOAST陷阱

虽然TOAST技术可用于处理PG中的大型数据对象，但可能会遇到一些问题。以下是一些常见问题及解决方法：

增加存储空间由于TOAST表和主表分开存储大型数据对象。因此他们会增加存储数据所需的磁盘空间量。如果表包含许多大型数据对象，这可能出现问题。要解决这个问题，请考虑将数据存储到TOAST表前压缩数据，或者使用针对处理大型数据对象（例如文件系统或对象存储）而优化的存储解决方案。
查询性能涉及存储在TOAST表中的大型数据对象的查询可能比具有较小数据对象的查询慢。因为数据库需要先从TOAST表中获取数据才能用于查询。要解决这个问题，请尝试在TOAST表上创建索引或考虑使用缓存层来减少需要从TOAST表中获取数据的次数。
Vacuum 性能 PG运行一个vaccum进程，用来回收被删除或被更新行的空间，从而维护数据库的性能。当TOAST表中存储大量大数据对象时，vacuum进程会变得很慢。要解决这个问题，请在数据库负载较小期间尝试运行vacuum进程，或考虑使用针对处理大数据对象而优化的存储解决方案。
有限的数据类型仅当定义表表有仅oid、bytea或其他TOASTable存储类的数据类型列时才会创建TOAST表。varchar 等数据类型可能存储的数据也很大，但不能使用TOAST表。

解决TOAST表增长问题：策略和解决方案

TOAST系统中一个常见的问题就是TOAST表的大型可能会失控。当向表中插入大量数据时，可能会发生这种情况，导致表变得很大。以下是几种解决方法：

增加磁盘空间最简单的解决方案是增加PG实例可用的磁盘空间。这将允许TOAST表继续增长，应该被视为一个临时解决方案。
VACUUM 和 ANALYZE 运行VACUUM和ANALYZE命令可以帮助回收TOAST表中不再需要的空间。Vacuum回收四记录占用的空间，analyze将帮助查询规划器做出更准确的决策。
为 TOAST 表设置大小限制可以使用max_toast_size配置参数为TOAST表设置最大大小限制。一旦表达到这个大小，任何额外的数据都将被拒绝。
选择更合适的存储策略如前所述，为数据类型和访问模式选择更合适的存储策略有助于避免TOAST表不必要的增长
归档旧数据从表中删除旧数据或很少访问的数据有助于减小表的大小。此外考虑将旧数据归档到不同的存储位置，例如磁盘或云存储。
压缩数据如果使用的是plain或external存储，可以考虑将数据存储到表中前压缩数据，从而使用更少的磁盘空间。

结论

总之，TOAST是一个强大的特性，允许数据库处理无法放入单个数据库块的大列值。系统使用多种策略存储这些列，包括PLAIN、EXTERNAL、EXTENDED和MAIN。每种策略都有其优势和用例，适当的策略将取决于应用程序的具体要求。例如有一个包含大量文本列的表并希望在需要子字符串操作时提高性能，则可以使用EXTERNAL策略。设计表时，请考虑存储在列中数据的大小和类型，并选择能够满足应用程序性能和空间要求的合适存储策略。也可以随时更高列的存储策略，尽管可能会影响查询的性能和表的大小。因此，强烈建议在确定最佳策略之前测试不同的策略。