Re: [HACKERS] LONG

> Bruce Momjian wrote:
>
> > Should we use large objects for this, and beef them up. Seems that
> > would be a good way. I have considered putting them in a hash
> > bucket/directory tree for faster access to lots of large objects.
> >
> > There is a lot to say about storing long tuples outside the tables
> > because long tuples fill cache buffers and make short fields longer to
> > access.
>
> I thought to use a regular table. Of course, it will eat
> buffers, but managing external files or even large objects
> for it IMHO isn't that simple, if you take transaction
> commit/abort and MVCC problematic into account too. And IMHO
> this is something that must be covered, because I meant to
> create a DATATYPE that can be used as a replacement for TEXT
> if that's too small, so it must behave as a regular datatype,
> without any restrictions WRT beeing able to rollback etc.

OK, I have thought about your idea, and I like it very much. In fact,
it borders on genius.

Our/my original idea was to chain tuple in the main table. That has
some disadvantages:

More complex tuple handling of chained tuples
Requires more tuple storage overhead for housekeeping of chaining data
Sequential scan of table has to read those large fields
Vacuum has to keep the tuples chained as they are moved

Your system would be:

CREATE TABLE pg_long (
refoid OID,
attno int2,
line int4,
attdata VARCHAR(8000);

CREATE INDEX pg_long_idx ON pg_long (refoid, attno, line);

You keep the long data out of the table. When updating the tuple, you
mark the pg_long tuples as superceeded with the transaction id, and just
keep going. No need to do anything special. Vacuum will remove
superceeded tuples automatically while processing pg_long if the
transaction was committed.

The pg_long_idx index will allow rapid access to tuple long data.

This approach seems better than tuple chaining because it uses our
existing code more efficiently. You keep long data out of the main
table, and allow use of existing tools to access the long data.

In fact, you may decide to just extent varchar() and text to allow use
of long tuples. Set the varlena VARLEN field to some special value like
-1, and when you see that, you go to pg_long to get the data. Seems
very easy. You could get fancy and keep data in the table in most
cases, but if the tuple length exceeds 8k, go to all the varlena fields
and start moving data into pg_long. That way, a table with three 4k
columns could be stored without the user even knowing pg_long is
involved, but for shorter tuples, they are stored in the main table.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026