PostgreSQL 中的文本压缩
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3801416/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-20 00:19:15 来源:igfitidea点击:
Text compression in PostgreSQL
提问by karthi_ms
I know in SQL we can compress the text field like the following:
我知道在 SQL 中我们可以像下面这样压缩文本字段：
CREATE TABLE TableName (FieldName CHARACTER(255) WITH COMPRESSION);
I want to know how to achieve the text compression in Postgres.
我想知道如何在 Postgres 中实现文本压缩。
回答by Frank Heikens
Compression is enabled by default for all string types, you don't have to tell the database to do it. Check the manual about TOAST
默认情况下，所有字符串类型都启用压缩，您不必告诉数据库执行此操作。查看关于TOAST的手册
- PLAIN prevents either compression or out-of-line storage; furthermore it disables use of single-byte headers for varlena types. This is the only possible strategy for columns of non-TOAST-able data types.
- EXTENDED allows both compression and out-of-line storage. This is the default for most TOAST-able data types.Compression will be attempted first, then out-of-line storage if the row is still too big.
- EXTERNAL allows out-of-line storage but not compression. Use of EXTERNAL will make substring operations on wide text and bytea columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.
- MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)
- PLAIN 防止压缩或离线存储；此外，它禁止对 varlena 类型使用单字节标头。对于不可 TOAST 的数据类型的列，这是唯一可能的策略。
- EXTENDED 允许压缩和离线存储。这是大多数 TOAST 数据类型的默认设置。如果行仍然太大，将首先尝试压缩，然后进行外存储。
- EXTERNAL 允许离线存储但不允许压缩。使用 EXTERNAL 将使对宽文本和 bytea 列的子字符串操作更快（以增加存储空间为代价），因为这些操作经过优化，可以在未压缩的情况下仅获取外部值的所需部分。
- MAIN 允许压缩但不允许离线存储。（实际上，仍然会为此类列执行离线存储，但仅作为最后的手段，因为没有其他方法可以使行足够小以适合页面。）
