Text compression in PostgreSQL

Question

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

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

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

For store the compressed string, see also http://stackoverflow.com/q/2958073/287948 — Peter Krauss, May 07 '12 at 10:32

score 40 · Answer 1 · answered Sep 27 '10 at 06:24

40

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

answered Sep 27 '10 at 06:24

Frank Heikens

8

From the docs, it looks like TOAST compression is only active for values greater larger than 2kb. The OP's example wouldn't hit the 2k threshold, and thus won't be compressed. – Frank Farmer Jul 11 '11 at 22:38
1

You can enforce compression, depends on storage parameters: http://www.postgresql.org/docs/current/interactive/storage-toast.html – Frank Heikens Mar 06 '12 at 19:52
2

By changing TOAST_TUPLE_THRESHOLD ? I think that requires a recompile? – Frank Farmer Mar 06 '12 at 20:13
No, use ALTER TABLE: "Each TOAST-able data type specifies a default strategy for columns of that data type, but the strategy for a given table column can be altered with ALTER TABLE SET STORAGE." – Frank Heikens Mar 07 '12 at 06:58
1

SET STORAGE is effectively a noop for rows smaller than 2k though, right? "The TOAST code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB)" – Frank Farmer Mar 07 '12 at 18:03
2

It appears to me, from the docs, that if a row is > 2KB and any of its columns are TOAST-able then TOAST will try and compress, then store out of band, TOAST-able columns until it reaches TOAST_TUPLE_TARGET for the row. So recompiling with different parameters would be the only way to force compression for < 2KB rows. – rogerdpack Jan 29 '15 at 23:42

1 Answers1