From: Andrew McMillan <andrew@catalyst.net.nz>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-php@postgresql.org
Subject: Re: storing binary files / memory limit
Date: Sun, 12 Oct 2008 09:23:39 +1300
Message-ID: <1223756619.26952.7.camel@happy.mcmillan.net.nz> (raw)
In-Reply-To: <48F0D3CD.9020306@fuzzy.cz>
References: <48F0D3CD.9020306@fuzzy.cz>
On Sat, 2008-10-11 at 18:26 +0200, Tomas Vondra wrote:
>
> Is there any other way to solve storing of large files in PostgreSQL?
No, not until there are functions that let you fopen() on the bytea
column.
Also, your "... || more_column" solution will generate large numbers of
dead rows and require frequent vacuuming.
> - Optimization is a serious criterion, as is reliability.
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.
If you *have* to store it in a table column (and it really isn't the
most efficient way of doing it) then create a separate table for it
which is just SERIAL + data.
Cheers,
Andrew McMillan.
------------------------------------------------------------------------
Andrew @ McMillan .Net .NZ Porirua, New Zealand
http://andrew.mcmillan.net.nz/ Phone: +64(272)DEBIAN
It is often easier to tame a wild idea than to breathe life into a
dull one. -- Alex Osborn
------------------------------------------------------------------------
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@catalyst.net.nz, tv@fuzzy.cz
Subject: Re: storing binary files / memory limit
In-Reply-To: <1223756619.26952.7.camel@happy.mcmillan.net.nz>
References: <48F0D3CD.9020306@fuzzy.cz> <1223756619.26952.7.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