Message-ID: <4DD47D28.3070207@postnewspapers.com.au>
Date: Thu, 19 May 2011 10:15:04 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110428 Fedora/3.1.10-1.fc14 Thunderbird/3.1.10
MIME-Version: 1.0
To: Julien Cigar <jcigar@ulb.ac.be>
CC: pgsql-sql@postgresql.org
Subject: Re: column type for pdf file
References: <4DD42A1C.30202@encs.concordia.ca> <4DD4301F.4010403@ulb.ac.be>
	<20110518210041.GF2498@hermes.hilbert.loc>
	<4DD43867.2040709@ulb.ac.be>
In-Reply-To: <4DD43867.2040709@ulb.ac.be>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 05/19/2011 05:21 AM, Julien Cigar wrote:
> On 05/18/2011 23:00, Karsten Hilbert wrote:
>> On Wed, May 18, 2011 at 10:46:23PM +0200, Julien Cigar wrote:
>>
>>> Unless you've good reasons to do so it's best to store the file on
>>> the file system
>> Why ?
>>
>> If you suggest reasons are needed for storing the PDF in the
>> database I'd like to know the reasons for *not* doing so.
>>
>
> It increases the load, consume connections, but the biggest drawback is
> probably the memory consumption ..
>
> IMHO storing binary data in a database is almost always a bad idea .. it
> could be OK to store things like avatars, small icons, etc, but
> certainly not to store files of several MB ... file systems are done for
> that !

_however_, you lose transactional properties when doing this. You can 
land up with a file that's been added/updated where the associated 
transaction in the DB failed or rolled back. Very careful application 
programming and the use of 2 phase commit can provide reliable 
behaviour, but it's not trivial.

This is one area where I envy Microsoft. As they control the file system 
and the database, they've been able to come up with a really cool system 
where the file system integrates into database transactions, so you kind 
of get the best of both worlds. Very cool. If reiser4 hadn't gone the 
way of the dodo such a thing might've become possible on Linux, but I'm 
not aware of any other Linux file systems that safely support transactions.

--
Craig Ringer