Re: Compression of tables

On 10 December 2013 15:15, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> I doubt you'll ever see generally heap compressed data in the way
> you're thinking: postgres has a strong informal policy of not
> implementing features which are dubious and or excessively complicated
> with limited benefit, particularly if there are ways to handle this
> outside the database; there are various operating system level tricks
> that can cause a compressed file or even an entire tablespace (o/s
> folder) masquerade as a regular structures. So maybe you are asking
> for a feature we already have: CREATE TABLESPACE.
>
> For example take a look here:
>
> https://btrfs.wiki.kernel.org/index.php/Compression#How_do_I_enable_compression.3F
>
> (out of curiosity, if this strategy fits the bill for you I wouldn't
> mind seeing a follow up on how this handles your static data use
> case).
>

Thanks for the suggestion. I see your point, those other database
generally do more themselves (direct IO, raw disk devices etc).

So I started experimenting with btrfs. I copied a 1.1TB pg_data
directory onto a zlib compressed btrfs raid1 filesystem, and it used
~840GB of physical disk. Not a great compression ratio, but I didn't
explore options other than the default for compression. Then my 3.2
kernel printed a few nasty messages and all IO locked up... I should
probably try a more recent kernel!

Googling, I see a number of people have reported success with PG on
ZFS with compression, with 2x to 4x compression and faster scans
due to increased effective IO bandwidth.

This does seem to make a lot of sense for my append-only static data
use case. I'll have to work on my fear of new filesystems.