Re: Compression of tables

<div dir="ltr">On 10 December 2013 15:15, Merlin Moncure <span dir="ltr"><<a href="mailto:mmoncure@gmail.com"
target="_blank">mmoncure@gmail.com</a>></span>wrote:<br /><div class="gmail_extra"><div
class="gmail_quote"><blockquoteclass="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div
class=""><divclass="h5"><span style="color:rgb(34,34,34)">I doubt you'll ever see generally heap compressed data in the
way</span><br/></div></div> you're thinking: postgres has a strong informal policy of not<br /> implementing features
whichare dubious and or excessively complicated<br /> with limited benefit, particularly if there are ways to handle
this<br/> outside the database; there are various operating system level tricks<br /> that can cause a compressed file
oreven an entire tablespace (o/s<br /> folder) masquerade as a regular structures.  So maybe you are asking<br /> for a
featurewe already have: CREATE TABLESPACE.<br /><br /> For example take a look here:<br /><a
href="https://btrfs.wiki.kernel.org/index.php/Compression#How_do_I_enable_compression.3F"
target="_blank">https://btrfs.wiki.kernel.org/index.php/Compression#How_do_I_enable_compression.3F</a><br/><br /> (out
ofcuriosity, if this strategy fits the bill for you I wouldn't<br /> mind seeing a follow up on how this handles your
staticdata use<br /> case).<span class=""><font color="#888888"><br /></font></span></blockquote></div><br /></div><div
class="gmail_extra"><divclass="gmail_extra">Thanks for the suggestion.  I see your point, those other
database</div><divclass="gmail_extra">generally do more themselves (direct IO, raw disk devices etc).</div><div
class="gmail_extra"><br/></div><div class="gmail_extra">So I started experimenting with btrfs.  I copied a 1.1TB
pg_data</div><divclass="gmail_extra">directory onto a zlib compressed btrfs raid1 filesystem, and it used</div><div
class="gmail_extra">~840GBof physical disk.  Not a great compression ratio, but I didn't</div><div
class="gmail_extra">exploreoptions other than the default for compression.  Then my 3.2</div><div class="gmail_extra">
kernelprinted a few nasty messages and all IO locked up...  I should</div><div class="gmail_extra">probably try a more
recentkernel!</div><div class="gmail_extra"><br /></div><div class="gmail_extra">Googling, I see a number of people
havereported success with PG on</div><div class="gmail_extra">ZFS with compression, with 2x to 4x compression and
fasterscans<br />due to increased effective IO bandwidth.<br /><br />This does seem to make a lot of sense for my
append-onlystatic data<br />use case.  I'll have to work on my fear of new filesystems.</div></div></div>