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

Frank Joerdens <frank(at)joerdens(dot)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