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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
