X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 145CDD1B4F8 for ; Wed, 19 Nov 2003 23:20:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 87233-04 for ; Wed, 19 Nov 2003 19:20:13 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 2DECFD1CA65 for ; Wed, 19 Nov 2003 19:20:11 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id hAJNK5Nu051242 for ; Wed, 19 Nov 2003 23:20:05 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id hAJN9pER048932 for pgsql-general@postgresql.org; Wed, 19 Nov 2003 23:09:51 GMT From: "John Gray" X-Newsgroups: comp.databases.postgresql.general Subject: Re: uploading files Date: Wed, 19 Nov 2003 23:05:38 +0000 Organization: Azuli IT Lines: 34 Message-ID: References: <1069164075.986.239.camel@bbking.uio.no> <20031118142919.GI4953@libertyrms.info> <1069180780.2769.91.camel@haggis.homelan> <019301c3ae07$d714e220$0700a8c0@trogdor> <87llqdqy37.fsf@asmodeus.mcnaught.org> <20031118205057.K606@hermes.hilbert.loc> <87fzglqvzq.fsf@asmodeus.mcnaught.org> <20031118212256.L606@hermes.hilbert.loc> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Complaints-To: usenet@news.hub.org User-Agent: Pan/0.12.94 (Are We There Yet?) To: pgsql-general@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/1071 X-Sequence-Number: 52721 On Tue, 18 Nov 2003 21:22:56 +0100, Karsten Hilbert wrote: >> I think the field will still be competely loaded into memory on the >> server side though, while LOs are stored in "chunks" and can >> theoretically be streamed to the client. I'm not really a definitive >> authority, though... > Ah ! Sounds about right ! Something new to learn every day :-) > Actually, bytea and text are chunked behind the scenes (the technique known as TOAST). They are also compressed(LZ) by default. However if you anticipate substringing them a lot (or if they are not very compressible anyway), use ALTER TABLE xxx ALTER COLUMN yyy SET STORAGE EXTERNAL to turn off the automatc compression of values. This allows the substr operation to read the minimum number of chunks necessary of disk. Bear in mind of course, that the whole value will take up more disk space, so reading it in its entirety will be slower (IO is normally the limiting performance factor -CPU on a DB server is often cheap by comparison, so decompressing/compressing to save IO is a good idea). If however you always fetch small parts (e.g. you store large images and usually want to read the header info from them, EXTERNAL is a good bet (and depending on the image format, the compression might not compress them very much anyway). Finally, note that the substr optimisation for text only really buys you anything if the character-set is single-byte. Hope this helps John Gray (implementer of substr optimisation many moons ago!)