11

I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data transparently with a tool like zlib, or some other native space saving methods.

The data is primarily read only data, so writing is not a problem.

Improve this question
3
  • 3
    from the manual: "Long strings are compressed by the system automatically" and here is a more detailed description: postgresql.org/docs/current/static/storage-toast.html
    – user1822
    Commented May 17, 2015 at 9:24
  • 3
    We use quite a low ratio compression algorithm though, because it must have a low CPU cost. So it can still be worth doing another form of compresion and setting EXTERNAL storage on the column.
    – Craig Ringer
    Commented May 18, 2015 at 0:25
  • @CraigRinger When you say another form of compression do you mean setting some additional Postgres options, or compiling some additional module, whether a ready made library or one of your own?
    – vfclists
    Commented May 18, 2015 at 10:28

2 Answers 2

Reset to default
4

By default Postgres automatically compresses everything TEXT. It uses a simple lzcompress algorythm:

https://www.postgresql.org/docs/9.3/storage-toast.html

There is a plugin that will probably evolve to LZ4 compression support for TEXT:

https://github.com/zilder/pg_lz4

There is a FDW that also support compression:

https://www.citusdata.com/blog/2014/04/03/columnar-store-for-analytics/

Improve this answer
-1

If you are doing this for logs, then TimescaleDB compression is a good option. This is because there is a strong time-based component in logs and command output and thus we consider this time-series data and can use time-based techniques to compress this data.

Disclosure: I work for Timescale and helped develop this feature.

Improve this answer
2
  • 1
    So, how exactly the TimescaleDB columnar compression helps with large text values? Your link says explicitly that's exactly what TOAST compression is for.
    – mustaccio
    Commented Mar 17 at 19:49
  • Also want to see a better answer as a fan of TimescaleDB to get a better understanding how text data can be managed in a more efficient way.
    – kworr
    Commented Mar 24 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.