Re: How does TOAST compare to other databases' mechanisms?

Frank Joerdens <frank@joerdens.de> writes:
> Can I go around bragging to my SQL-minded friends about using this
> really cool thing that no other database has, or should I keep my mouth
> shut because it's actually not so cool?

IMHO it's pretty cool.  You get the benefits of BLOB storage without
having to deal with weird declarations or access methods.  I have no
idea whether any other databases do it the same way, but simply removing
the limit on physical tuple length wouldn't have been as nice.  See,
with a toasted column, you don't pay to suck the contents of the column
into memory when you read the row for a query that doesn't actually touch
that column.  So, for example, you might have a table declared like

    CREATE TABLE foo
        (key1 text,
         moddate timestamp,
         big_horking_data text);

and you can do things like

    SELECT big_horking_data FROM foo
    WHERE key1 = 'bar' AND moddate > 'yesterday';

Here the table is essentially acting as an index for the BLOB storage:
the system won't bother to fetch the BLOB values for the rows that
fail the WHERE check.  You can't do that without lots of cruft in any
non-TOAST-like scheme, AFAICS.

            regards, tom lane