Message-ID: <434A5E22.9040500@wildenhain.de>
Date: Mon, 10 Oct 2005 14:27:14 +0200
From: Tino Wildenhain <tino@wildenhain.de>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050929)
MIME-Version: 1.0
To: Stef <svb@ucs.co.za>
Cc: pgsql-performance@postgresql.org
Subject: Re: Compression of text columns
References: <20051010141721.0bebbaa7@svb.ucs.co.za>
In-Reply-To: <20051010141721.0bebbaa7@svb.ucs.co.za>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Stef schrieb:
> I have a table in the databases I work with,
> that contains two text columns with XML data 
> stored inside them.
> 
> This table is by far the biggest table in the databases,
> and the text columns use up the most space. 
> I saw that the default storage type for text columns is
> "EXTENDED" which, according to the documentation, uses up extra
> space to make possible substring functioning faster. 
> 
> Suppose that the data in those columns are only really ever
> _used_ once, but may be needed in future for viewing purposes mostly,
> and I cannot really change the underlying structure of the table,
> what can I possibly do to maximally reduce the amount of disk space
> used by the table on disk. (There are no indexes on these two columns.)
> I've thought about compression using something like :
> ztext http://www.mahalito.net/~harley/sw/postgres/
> 
> but I have to change the table structure a lot and I've already 
> encountered problems unzipping the data again.
> The other problem with this solution, is that database dumps almost double
> in size, because of double compression.
> 
> Any suggestions much appreciated

Well, text columns are automatically compressed via the toast mechanism.
This is handled transparently for you.