PostgreSQL中的文本压缩

本文介绍了PostgreSQL中的文本压缩的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我知道在SQL中我们可以像以下那样压缩文本字段：

I know in SQL we can compress the text field like the following:

CREATE TABLE TableName (FieldName CHARACTER(255) WITH COMPRESSION);

我想知道如何在Postgres中实现文本压缩。

I want to know how to achieve the text compression in Postgres.

推荐答案

默认情况下，所有字符串类型均启用压缩，您不必告诉数据库即可进行压缩。查看有关 TOAST

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

• PLAIN防止压缩或离线存储；此外，它禁止对varlena类型使用单字节标头。对于不可TOAST数据类型的列，这是唯一的可能的策略。
• EXTENDED允许压缩和离线存储。 这是大多数TOAST数据类型的默认。将首先尝试压缩，然后在尝试离线存储行仍然太大。
• EXTERNAL允许离线存储，但不允许压缩。使用EXTERNAL 可使宽文本和bytea列上的子字符串操作更快（增加存储空间的代价），因为这些操作已优化为获取当未压缩时，只有超出值的部分会被压缩。
• MAIN允许压缩，但不允许超出范围行存储。 （实际上，对于此类列，的离线存储仍将执行，但是当没有缩小行时，只有作为最后手段足以容纳在页面上。）

• 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.)