PostgreSQL的字段存储类型了解

在 PostgreSQL 中，每个字段（列）都有其存储类型，这些存储类型决定了数据库如何存储和处理该字段的数据。了解和适当地利用这些存储类型，可以提高数据库的性能和存储效率。

主要的存储类型

PostgreSQL 提供了四种主要的存储类型：

1. PLAIN（p）: 不允许 TOAST，数据始终存在于数据页中。适用于较短的数据类型，如整数和短的字符串。
2. MAIN（m）: 默认行为，尽可能在数据页中存储数据，若超出空间限制则使用 TOAST 进行外部存储，但不会压缩。
3. EXTERNAL（x）: 将数据存储在 TOAST 表中，但不压缩数据。适用于较大的数据块，需要避免压缩开销时。
4. EXTENDED（e）: 使用 TOAST 表，首先尝试压缩数据，如果压缩无效则使用外部存储。是 TOAST 表存储的默认方式。

注意: TOAST (The Oversized-Attribute Storage Technique) 是 PostgreSQL 用于存储超大数据的一个方法。

检查字段的存储类型

要检查特定字段的存储类型，可以查询系统视图 pg_attribute。

SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'your_table'::regclass
AND attname = 'your_column';

其中 attstorage 的值为：

• p: PLAIN
• m: MAIN
• x: EXTERNAL
• e: EXTENDED

更改字段的存储类型

使用 ALTER TABLE ... SET STORAGE 语句可以更改字段的存储类型。

-- 更改字段的存储类型为 EXTENDED
ALTER TABLE your_table
ALTER COLUMN your_column SET STORAGE EXTENDED;

-- 更改字段的存储类型为 MAIN
ALTER TABLE your_table
ALTER COLUMN your_column SET STORAGE MAIN;

-- 更改字段的存储类型为 EXTERNAL
ALTER TABLE your_table
ALTER COLUMN your_column SET STORAGE EXTERNAL;

-- 更改字段的存储类型为 PLAIN
ALTER TABLE your_table
ALTER COLUMN your_column SET STORAGE PLAIN;

存储类型的适用场景

1. PLAIN:

   • 用于无法使用 TOAST 技术的字段，比如短整型、布尔型等。
   • 数据较短且频繁访问，不需要额外存储操作。

2. MAIN:

   • 默认存储方式，适用于大部分数据。
   • 数据不特别长，适用一般用途。

3. EXTERNAL:

   • 适用于需要存储长数据但不希望压缩的场景。
   • 比如当压缩和解压缩数据代价较高或时间紧迫的应用。

4. EXTENDED:

   • 常用于大字段，比如文本、大对象。
   • 数据会被压缩并在必要时外部存储，以节省空间。

小结

通过理解和合理选择字段的存储类型，可以帮助提高 PostgreSQL 数据库的性能和存储效率。PLAIN、MAIN、EXTERNAL 和 EXTENDED 各有其使用场景，根据具体需求和数据特性选择合适的存储策略是数据库优化的重要一环。正确配置和使用 TOAST 与 PGLZ 压缩，也能有效节约存储空间，提升性能。