Обсуждение: large string storage in Postgresql

On 17/01/2010 1:51 PM, AI Rumman wrote:
> Hello all,
> I have to store 2 GB text in the database. Previously I was using Oracle
> database with CLOB data type.

Do you mean individual text objects that are 2GB or larger each?

Or do you mean you have a bunch of smaller (how big?) text objects that
add up to 2GB or so?

Assuming you mean individual objects >= 2GB, you'll have to use
PostgreSQL BLOBs or use external files.

What do you need to do with this text? What operations are you
performing on it? Full-text search?

> Could anyone please tell me how can I do it in Postgresql 8.0 or later?

Do you need to actually support 8.0? For a new project I'd favour
starting with 8.3 or 8.4 .

--
Craig Ringer

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

AI Rumman wrote:
> I have an individual text string of 2 GB.
> I may use Postgresql 8.4 also. But what data type should I use to
> store this column value of 2GB?

GB data objects ni a database is going to make backups, maintenance, etc
a real pain

normal data in postgres can not be over 1GB, so you will need to use the
Larg OBJect facility, where you store an object ID in your actual table,
then store the data via a special Large Object api,
http://www.postgresql.org/docs/current/static/lo-interfaces.html  which
can handle up to 2GB max.

quite frankly, i'd as soon store stuff that large as files and and store
their path in the database.

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

I thought LO were limited to <= 2GB ?  TOAST can handle up to 1GB.  >
2GB and you'll need to break it up into several LOs.