Re: How does TOAST compare to other databases' mechanisms? - Mailing list pgsql-general

> > 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.

Yes, that makes it a lot clearer. This is more or less what I'd been wondering, without
really noticing: Why not just remove the limit on physical tuple length? Because the
description of TOAST's inner workings seemed so convoluted (what with compression
etcetera) that it appeared as either a great, strained effort to remove a "deep, hardwired
limitation" (someone's statement regarding the 8K barrier) or something that someone had
thought long and hard about to make particularly cool.

>     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.

I am more than satisfied with both your explanations, thanks. I was afraid I might have to
switch to MySQL (the horror! the horror!), also because of a piece of information from
another recent thread:

> >> For more than 10,000 BLOBs, the database was a clear winner.
> >> That is, it took less time to locate and read an 8k-16k BLOB
> >> from the MySQL database than from the local disk.  For smaller
> >> numbers of BLOBs, the filesystem was faster.
>
> This is undoubtedly right for existing Postgres releases, because
> currently PG stores each BLOB as an independent table --- and just to
> add insult to injury, creates an index for it :-(.  So you actually have
> *two* added files in the DB directory per BLOB.  Needless to say, this
> does not scale real well to large numbers of BLOBs.

. . . Tom goes on to say that TOAST is a solution to most of these issues whilst a better
BLOB implementation is waiting in the wings (scheduled for 7.1 also) which will provide a
more natural solution to some problems - an issue which is also touched upon by Jan in
pgsql-sql:

> >     TOAST  is  finished  and will be shipped with 7.1. It's not a
> >     solution for huge items, but medium sized  text  up  to  some
> >     hundred K works fine.
>
> What do you mean by "..not a solution for huge items"? Does TOAST have a size limit?

. . . Jan goes on to explain that TOAST does not have an explicit size limit and why it is
still less than optimal for really big items.

Which leads to another question in the context of my little content management app: Part
of it is composed of smaller and larger texts, for which TOAST will be great, because I
can still search them, they're not opaque for a, for instance, LIKE query parameter in a
SELECT statement (right?), like BLOBs, if I want to search for texts that contain
particular words or statements (this will probably be slow if the texts are big and a lot
of them are included in the query). But I also have images, most of which will _probably_
not exceed a few hundred K. At which point do I cross the line where TOAST becomes silly
and BLOBs are The Way?

Regards, Frank