Re: storing binary files / memory limit

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

------------------------------------------------------------------------



-- 
Sent via pgsql-php mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php