Re: more than 2GB data string save

On Tue, Feb 9, 2010 at 11:51 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2010/2/10 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
>> On Tue, Feb 9, 2010 at 11:26 PM, Steve Atkins <steve(at)blighty(dot)com> wrote:
>>>
>>> On Feb 9, 2010, at 9:52 PM, Scott Marlowe wrote:
>>>
>>>> On Tue, Feb 9, 2010 at 9:38 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>>>>> How to save 2 GB or more text string in Postgresql?
>>>>> Which data type should I use?
>>>>
>>>> If you have to you can use either the lo interface, or you can use
>>>> bytea.  Large Object (i.e. lo) allows for access much like fopen /
>>>> fseek  etc in C, but the actual data are not stored in a row with
>>>> other data, but alone in the lo space.  Bytea is a legit type that you
>>>> can have as one of many in a row, but you retrieve the whole thing at
>>>> once when you get the row.
>>>
>>> Bytea definitely won't handle more than 1 GB. I don't think the lo interface
>>> will handle more than 2GB.
>>
>> That really depends on how compressible it is, doesn't it?
>>
>
> no. It is maximal length for varlena. TOAST is next possible step.
>
> Regards
> Pavel Stehule
>
> p.s.
>
> processing very large SQL values - like bytea, or text longer tens
> megabytes is very expensive on memory. When you processing 100MB
> bytea, then you need about 300MB RAM, Using a bytea over 100MB is not
> good idea. LO interface is better and much more faster.

Agreed. I wonder what kind of strings the OP is storing, and if there
isn't a more efficient way to do what he wants to do. But we're
learning by pieces. First size requirements, then the need for FTS.

So, OP, what are you trying to do? (i.e. big answer, not small) It
could be there are basic inefficiencies in some part of your method
that are more easily recognised when know what you're trying to do.