pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

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





view thread (4+ messages)

Message-ID: <1223796307.26952.31.camel@happy.mcmillan.net.nz>
Permalink:  ../1223796307.26952.31.camel@happy.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1223796307.26952.31.camel@happy.mcmillan.net.nz

 · 

reply

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