HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

What are the compression options for storing large amounts of text in Postgres?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thewhatpostgresarecompressiontextoptionslargeamountsfor

Problem

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.

Solution

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/

Context

StackExchange Database Administrators Q#101718, answer score: 4

Revisions (0)

No revisions yet.