From: Andrew McMillan <andrew@morphoss.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-php@postgresql.org
Subject: Re: storing binary files / memory limit
Date: Sun, 12 Oct 2008 20:25:07 +1300
Message-ID: <1223796307.26952.31.camel@happy.mcmillan.net.nz> (raw)
In-Reply-To: <48F11D6C.1000209@fuzzy.cz>
References: <48F0D3CD.9020306@fuzzy.cz>
<1223756619.26952.7.camel@happy.mcmillan.net.nz>
<48F11D6C.1000209@fuzzy.cz>
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
------------------------------------------------------------------------
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-php@postgresql.org
Cc: andrew@morphoss.com, tv@fuzzy.cz
Subject: Re: storing binary files / memory limit
In-Reply-To: <1223796307.26952.31.camel@happy.mcmillan.net.nz>
References: <48F0D3CD.9020306@fuzzy.cz> <1223756619.26952.7.camel@happy.mcmillan.net.nz> <48F11D6C.1000209@fuzzy.cz> <1223796307.26952.31.camel@happy.mcmillan.net.nz>
* 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