Re: Re: Large Objects

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
>> 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.

> Wow, can anyone confirm this (with Postgres preferrably)? In talking
> with some developers at my old job, they all agreed that storing large
> pieces of data (1k < x < 16K) was significantly faster on the FS than
> in the DB. They were running Postgres 6.5 w/ JDBC on Solaris.

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.

Denis Perchine has done the legwork to store BLOBs in a more reasonable
fashion, ie all together in one big (but indexed!) table. His patch
needs review by a core person before we'll consider it safe to commit,
but I have that as a personal "must do for 7.1". So those MySQL results
should also apply to Postgres in 7.1 and later.

You may be wondering "why bother, given TOAST?" (TOAST is long-tuple
support, for those who haven't been paying attention.) Although TOAST
should make many uses of BLOBs obsolete, there will be lots of
applications that don't get updated right away (if ever), and still some
where BLOBs are a natural, unforced solution. So I think it's worth
capturing this performance improvement. It's just a shame we didn't get
round to it sooner when BLOBs were the only game in town.

regards, tom lane