Skip site navigation (1) Skip section navigation (2)

Re: Storing many big files in database- should I do it?

From: David Wall <d(dot)wall(at)computer(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Storing many big files in database- should I do it?
Date: 2010-04-29 17:51:53
Message-ID: 4BD9C739.70708@computer.org (view raw or flat)
Thread:
Lists: pgsql-general
>> Huh ???  isn't that point of using bytea or text datatypes.
>>
>> I could have sworn bytea does not use large object interface it uses
>> TOAST or have i gone insane
>>
>>      
> You're not insane :)
>
> Put it another way: bytea values are not stored in the pg_largeobject
> catalog.
>    

I missed the part that BYTEA was being used since it's generally not a 
good way for starting large binary data because you are right that BYTEA 
requires escaping across the wire (client to backend) both directions, 
which for true binary data (like compressed/encrypted data, images or 
other non-text files) makes for a lot of expansion in size and related 
memory.

BYTEA and TEXT both can store up to 1GB of data (max field length), 
which means even less "file size" supported if you use TEXT with base64 
coding.  LO supports 2GB of data.  In JDBC, typically BYTEA is used with 
byte[] or binary stream while LOs with BLOB.  I think LOs allow for 
streaming with the backend, too, but not sure about that, whereas I'm 
pretty sure BYTEA/TEXT move all the data together you it will be in 
memory all or nothing.

Of course, to support larger file storage than 1GB or 2GB, you'll have 
to create your own "toast" like capability to split them into multiple rows.

David

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2010-04-29 18:03:21
Subject: Re: Writing SRF
Previous:From: John GageDate: 2010-04-29 17:48:29
Subject: Re: [SQL] Tsearch not searching 'Y'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group