X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C2BC9D9C9E for ; Mon, 10 Oct 2005 13:31:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43088-08 for ; Mon, 10 Oct 2005 16:31:32 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id DE49DD9BAF for ; Mon, 10 Oct 2005 13:31:32 -0300 (ADT) Received: by flake.decibel.org (Postfix, from userid 1001) id 3CAEE1525C; Mon, 10 Oct 2005 11:31:32 -0500 (CDT) Date: Mon, 10 Oct 2005 11:31:32 -0500 From: "Jim C. Nasby" To: Stef Cc: pgsql-admin Subject: Re: Compression of text columns Message-ID: <20051010163132.GL39569@pervasive.com> References: <20051010141839.742f6301@svb.ucs.co.za> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20051010141839.742f6301@svb.ucs.co.za> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 required=5 tests=[AWL=0.009] X-Spam-Level: X-Archive-Number: 200510/118 X-Sequence-Number: 19190 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