Steven Lacroix asks:
> ... what kind of performance hits do BLOBS have on a database ...
> Note that it would be for web database project.
I haven't tried this with PostgreSQL, but I ran some experiments
to compare the speed of access for large numbers of BLOBs stored
in a single MySQL table and in a directory in the local filesystem.
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 pretty
much what you should expect: reading a file through a system
call should be faster than doing a network operation to request
a BLOB. Conversely, Unix filesystems store directories as unsorted
lists, which are a lot slower to search than the database's
structured indexes.
I'm currently working on a web publishing system that stores
all content as BLOBs in a back-end MySQL database; performance
is very good so far.
I'll be giving a talk about this at BSDCon in Monterey next month
with more details...
- Tim Kientzle