Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E9CD964FC09 for ; Sun, 12 Oct 2008 04:32:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 96499-01 for ; Sun, 12 Oct 2008 04:32:43 -0300 (ADT) X-Greylist: delayed 00:07:26.346014 by SQLgrey-1.7.6 Received: from hoppy.mcmillan.net.nz (hoppy.mcmillan.net.nz [202.78.240.82]) by postgresql.org (Postfix) with ESMTP id 9BEC164FC08 for ; Sun, 12 Oct 2008 04:32:42 -0300 (ADT) Received: from [IPv6:2404:130:b055:0:215:afff:fe48:6c48] (unknown [IPv6:2404:130:b055:0:215:afff:fe48:6c48]) by hoppy.mcmillan.net.nz (Postfix) with ESMTPS id C939722EEA; Sun, 12 Oct 2008 20:25:10 +1300 (NZDT) Subject: Re: storing binary files / memory limit From: Andrew McMillan To: Tomas Vondra Cc: pgsql-php@postgresql.org In-Reply-To: <48F11D6C.1000209@fuzzy.cz> References: <48F0D3CD.9020306@fuzzy.cz> <1223756619.26952.7.camel@happy.mcmillan.net.nz> <48F11D6C.1000209@fuzzy.cz> Content-Type: text/plain Date: Sun, 12 Oct 2008 20:25:07 +1300 Message-Id: <1223796307.26952.31.camel@happy.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.22.3.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/11 X-Sequence-Number: 3908 On Sat, 2008-10-11 at 23:41 +0200, Tomas Vondra wrote: > > > > If you're using tables with very large columns, make sure you index on > > every other column you're going to access it by. If PostgreSQL has to > > resort to full-table scans on this table, and especially with a low > > memory constraint, you could easily end up with it doing an on-disk sort > > on a copy of the data. > > But I'm not sure what you mean by 'low memory contraint' - the memory > limit I've been talking about is purely PHP feature, so it's related to > inserting / reading and escaping / unescaping data. In this case I'm not referring to PHP memory, but to PostgreSQL memory. If you're on a memory constrained shared system then it's not just PHP which will be configured for a smaller memory footprint... > I *want* to store it in a table column Yes, that's certainly what you seem to be saying. Personally I would steer clear of storing many megabytes in a bytea column on a memory constrained system, but you're closer to the application and will make your own decision. > If you know a better way to store binary data, please describe it here. > The only other way I'm aware of is LOB - it solves the problem of > inserting data (by streaming), but has other disadvantages (no > referential integrity, etc.) Yes, your trade-off is essentially efficiency vs. referential integrity. This is a common trade-off, and if you have tight control over how rows will be inserted/deleted from your table then referential integrity is merely a nice-to-have. If people will be creating / deleting these things all over the application, without the benefit of an API to do so, then referential integrity obviously becomes much more important. Cheers, Andrew. ------------------------------------------------------------------------ andrew (AT) morphoss (DOT) com +64(272)DEBIAN Writing is turning one's worst moments into money. -- J.P. Donleavy ------------------------------------------------------------------------