Text compression in PostgreSQL

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

Leave a Comment