Re: large string storage in Postgresql

On 17/01/2010 2:13 PM, AI Rumman wrote:
> I have an individual text string of 2 GB.

OK. And what sorts of things do you do with that text in your current
database? What queries do you do on it?

Do you just need to read it? Update it? Update only selected parts of
it? Search for text strings based on keywords within the text? etc.

What *is* the text? And is it really text, in the sense of
human-readable text, or is it just binary data that happens to all be
printable characters?

In any case, for objects > 2GB you don't really have much choice except
to use PostgreSQL's large object features. See:

http://www.postgresql.org/docs/current/static/largeobjects.html

These aren't really stored directly in the row. Rather, you store a `lo'
object in the row that is really just the ID of the large object. You
can then access the large object using the lo_ functions.

You are likely to find the following contributed module useful:

http://www.postgresql.org/docs/current/static/lo.html

as the `lo' type it defines will give you behavior somewhat more similar
to what you're used to from CLOB. It'll let you (appear to) store the
data "in" the row.

--
Craig Ringer