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

Tom Lane wrote:
> 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 it also helps to avoid tables, containing such
big_horking_data items, to grow as fast as they would before
7.1. In the case

CREATE TABLE customer_call (
cc_callid serial primary key,
cc_custid integer foreign key ...,
cc_priority integer,
cc_calltime timestamp,
cc_callclosed timestamp,
cc_huge_description text
);

UPDATE customer_call
SET cc_callclosed = now()
WHERE cc_callid = 5432;

the cc_huge_description isn't touched. Now think about the
way the non-overwriting storage manager in PostgreSQL works.
Normally it would store a completely new tuple, containing
the description again and VACUUM needs to move alot of data
to condense the table again. TOAST will reuse the previous
toasted value and NOT outdate it, but put another reference
to it into the new tuple. This will avoid alot of write
access to the disks and speedup VACUUM. Also, the UPDATE will
never even read these items, so the update itself is (like
Tom's SELECT sample) working on a small table.

All in all it is a very good solution for the very special
problems we have in PostgreSQL. It might not compare in any
way to what other databases do, but the non-overwriting
technology bought us MVCC to be relatively easy. Now it
lowers the cost of having it.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #