Re: question about postgres storage management - Mailing list pgsql-hackers

From Christopher Browne
Subject Re: question about postgres storage management
Date
Msg-id CAFNqd5UpARCQ8Ug2D6Pu5dnkU2gaq7BMzC5XyOFqpRo4i_3+kw@mail.gmail.com
Whole thread Raw
In response to question about postgres storage management  (Will Childs-Klein <willck93@gmail.com>)
Responses Re: question about postgres storage management
List pgsql-hackers

I would expect the strategy you have in mind to be more useful to apply at the filesystem level, so that it's not in Postgres altogether.  (Ala "Stacker", remember DR-DOS?)

But, to speak arguable heresy, the demerits of this sort of thing are described nicely in Another Database's Documentation: <http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-internals-algorithms.html>

The relevant bit that seems to describe fairly aptly why what you are suggesting is unlikely to turn out well:
  
"Some operating systems implement compression at the file system level. Files are typically divided into fixed-size blocks that are compressed into variable-size blocks, which easily leads into fragmentation. Every time something inside a block is modified, the whole block is recompressed before it is written to disk. These properties make this compression technique unsuitable for use in an update-intensive database system."

The principle described is as applicable to Postgres as it is to InnoDB, and is as applicable to attempting to compress disk blocks from within the database as it is to apply it at the filesystem layer.

Postgres *does* make use of data compression, where applicable; see the documentation for TOAST: <http://www.postgresql.org/docs/9.2/static/storage-toast.html>

You should avail yourself of the code on TOAST:
./src/backend/catalog/toasting.c
./src/backend/access/heap/tuptoaster.c
./src/include/catalog/toasting.h
./src/include/access/tuptoaster.h

pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: [GENERAL] currval and DISCARD ALL
Next
From: Merlin Moncure
Date:
Subject: Re: question about postgres storage management