揭开 TOAST 的秘密：如何优化 PostgreSQL 中的大型列存储以获得最佳性能和可扩展性

PostgreSQL 是一个很棒的数据库，但如果您要存储图像、视频、音频文件或其他大型数据对象，则需要“烘烤”它们以获得最佳性能。这篇文章将着眼于使用超大属性存储技术 (TOAST) 来提高性能和可伸缩性。 

PostgreSQL 使用固定大小的页面，这使得直接存储巨大的数据值具有挑战性。为了解决这个问题，大数据值被压缩并分成多个较小的块。此过程自动完成，不会显着影响数据库的使用方式。这种称为 TOAST 的技术改进了大数据值在数据库中的存储和使用方式。

因此，TOAST 是 PostgreSQL 中使用的一种存储技术，用于处理图像、视频和音频文件等大型数据对象。TOAST 技术通过将大数据对象分成更小的块并将它们与主表分开存储，从而允许高效存储大数据对象。这可以提高查询和索引的性能，并减少存储数据所需的磁盘空间量。

当表包含类型为 OID、bytea 或具有 TOATable 存储类的任何其他数据类型的列时，TOAST 表由 PostgreSQL 自动创建。然后使用 TOAST 表存储大数据对象，而主表存储对 TOAST 表的引用。

下面是在 PostgreSQL 中使用 TOAST 技术的示例：

• 创建一个包含大数据列的表：

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

• 在表中插入大图像：

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

• 查询表可以看到大数据对象存储在一个 TOAST 表中：

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

在示例中，images 表包含一个名为 data 的 bytea 类型的列，它可以保存大量二进制数据。当向表中插入大图像时，PostgreSQL 会自动创建一个 TOAST 表，将图像数据与主表分开存储。然后查询 pg_class 系统目录表以显示已创建 TOAST 表。

请务必注意，虽然 TOAST 表有助于存储大型数据对象，但它们会增加数据库的复杂性，因此应谨慎使用。此外，在某些情况下，当数据分布在不同的表中时，查询性能会降低，具体取决于查询条件。

如果你有很多大数据，你不需要查询/索引；您可以考虑的另一种选择是将其存储在文件系统中的数据库之外，并将对它的引用存储在数据库中，类似于 TOAST 表的工作方式。

在 PostgreSQL 中，您可以通过在列上设置“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”是四种 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”（压缩和离线存储），您可以随时更改表列的存储策略。但是，请务必注意，更改列的存储策略可能会影响查询的性能和表大小。因此，建议使用不同的存储策略测试您的特定用例，以确定哪个提供最佳性能。

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

克服在 PostgreSQL 中使用 TOAST 的陷阱

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

1. 增加存储空间：由于 TOAST 表与主表分开存储大型数据对象，因此它们会增加存储数据所需的磁盘空间量。如果表包含许多大型数据对象，这可能会特别成问题。要解决此问题，请考虑在将数据存储到 TOAST 表之前压缩数据，或者使用针对处理大型数据对象（例如文件系统或对象存储）而优化的存储解决方案。
2. 查询性能：涉及存储在 TOAST 表中的大型数据对象的查询可能比具有较小数据对象的查询慢。这是因为数据库需要先从 TOAST 表中获取数据才能用于查询。要解决此问题，请尝试在 TOAST 表上创建索引或考虑使用缓存层来减少需要从 TOAST 表中提取数据的次数。
3. 真空性能：PostgreSQL 运行一个称为“真空”的进程，该进程从已删除或更新的行中回收磁盘空间以维持数据库的性能。当 TOAST 表中存储了大量大型数据对象时，清理过程可能会很慢。要解决此问题，请尝试在数据库活动较少期间运行 vacuum 进程，或考虑使用针对处理大型数据对象（例如文件系统或对象存储）而优化的存储解决方案。
4. 有限的数据类型：TOAST 表仅为定义为 oid、bytea 或具有 TOATable 存储类的任何其他数据类型的列创建。您不能将 TOAST 表用于文本或 varchar 等数据类型，它们也可能很大。

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

PostgreSQL 中 TOAST 系统的一个常见问题是 TOAST 表的大小可能会失去控制。当向表中插入大量数据时，可能会发生这种情况，导致表变得比可用磁盘空间大。有几种方法可以解决这个问题：

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

结论

总之，PostgreSQL 中的 TOAST 系统是一个强大的特性，它允许数据库处理无法放入单个数据库块的大列值。系统使用多种策略来存储这些列，包括 PLAIN、EXTENDED、EXTERNAL 和 MAIN。每种策略都有其优势和用例，适当的策略将取决于您的应用程序的具体要求。

例如，如果您有一个包含大量文本列的表并且希望在需要子字符串操作时提高性能，则可以使用 EXTERNAL 策略。设计表时，请考虑存储在列中的数据的大小和类型，并选择能够满足应用程序性能和空间要求的适当存储策略。也可以随时更改列的存储策略，尽管这可能会影响查询的性能和表的大小。因此，强烈建议在确定最佳策略之前测试不同的策略。

最终，了解 TOAST 系统以及如何有效地使用它可以显着提高 PostgreSQL 应用程序的性能和可伸缩性。

Percona Distribution for PostgreSQL 在单一发行版中提供来自开源社区的最好和最关键的企业组件，这些组件经过设计和测试可以协同工作。

原文标题：Unlocking the Secrets of TOAST: How To Optimize Large Column Storage in PostgreSQL for Top Performance and Scalability

原文作者：Ibrar Ahmed

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