pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jnasby@pervasive.com>
To: Stef <svb@ucs.co.za>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Compression of text columns
Date: Mon, 10 Oct 2005 11:31:32 -0500
Message-ID: <20051010163132.GL39569@pervasive.com> (raw)
In-Reply-To: <20051010141839.742f6301@svb.ucs.co.za>
References: <20051010141839.742f6301@svb.ucs.co.za>

On Mon, Oct 10, 2005 at 02:18:39PM +0200, Stef wrote:
> 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. 

You're thinking of EXTERNAL. From http://lnk.nu/postgresql.org/4ff.html:

EXTERNAL is for external, uncompressed data, and EXTENDED is for
external, compressed data. EXTENDED is the default for most data types
that support non-PLAIN storage. Use of EXTERNAL will make substring
operations on text and bytea columns faster, at the penalty of increased
storage space.

> 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.

Are you seeing much gain using ztext over using EXTENDED?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



view thread (3+ messages)  latest in thread

Message-ID: <20051010163132.GL39569@pervasive.com>
Permalink:  ../20051010163132.GL39569@pervasive.com/
Also on:    postgresql.org/message-id/20051010163132.GL39569@pervasive.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: jnasby@pervasive.com, svb@ucs.co.za
  Subject: Re: Compression of text columns
  In-Reply-To: <20051010163132.GL39569@pervasive.com>
  References: <20051010141839.742f6301@svb.ucs.co.za> <20051010163132.GL39569@pervasive.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox