Database compression in postgres

Question

As the database size keeps growing, as part of business requirement, we have been asked to compress data for our prod database. Need to know what are some disadvantages of database compression in postgres.

Answer 1

This is too broad for a good answer. For one, how do you want to compress the data? I'll try to give a general answer though.

With all compression, the trade-off is between size savings and extra work for the CPU. And storage space is usually the cheaper resource.

Normally it is not a good idea to compress a database completely. Instead, consider

• increasing the storage space. It is comparatively cheap.

• moving old data to an archive database.

Compressing large data in columns can be a good idea, but PostgreSQL does that automatically anyway:

large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. The technique is affectionately known as TOAST (or “the best thing since sliced bread”).