Обсуждение: storing files: blob, toasted text of filesystem?

Hello everyone,

Sorry if this is a FAQ, but I've groups.googled the subject and
can't find a definite answer (if such a thing exists). I'm working
on a db in postgresql on a debian stable server, ext3 filesystem.
The db will contain files, not too many (I expect somewehere between
10 and 100 files max to be inserted daily), and not too big (mostly
pdf files, some images. The size will rarely be larger than 1Mb).

My plan was to store the files in the db as BLOBs, which seemed the
most elegant solution because these files are logically related to
objects that are in the db (customers, people etc.) Recently someone
warned me that this would have a large performance impact on the db
and said it's better to store the files on the filesystem and keep
some sort of pointer in the db.

Google was contradictory, some people even had performance problems
when using the filesystem/pointer approach and went to BLOBs for
that reason. Can anyone tell me (or point me in the right direction)
what is the best way to do this, BLOBs / filesystem+pointer /
toasted text?

Thanks!

Hi there, Joolz;


On Sun, 3 Oct 2004 10:48:25 +0200 (CEST)
"Joolz" <joolz@arbodienst-limburg.nl> wrote:

> Google was contradictory, some people even had performance
> problems when using the filesystem/pointer approach and went to
> BLOBs for that reason. Can anyone tell me (or point me in the
> right direction) what is the best way to do this, BLOBs /
> filesystem+pointer / toasted text?

Though not running postgresql for that solution: We are running an
enterprise-scaled document management system to keep track of
currently > 2*10^3 documents (mostly *.hpgl and *.plt files, some
*.pdfs and *.zips in between), and the (proprietary) dms software we
are using for that purpose exclusively relies on the
filesystem+pointer approach instead of storing all the files inside
the tablespace of the database.

Even while the current system is in migration due to some severe
limits of the current setup (Windows NT 4 servers running an
old MSSQL...:/ ), by now this concept seems to work well there even
with both a database and a filesystem which don't really go for top
performance.

Hope this helps, have a nice weekend.
Kris



--
Kristian Rink     -- Programmierung/Systembetreuung
planConnect GmbH * Strehlener Str. 12 - 14 * 01069 Dresden
0351 4657702 * 0176 24472771 * krink@pm-planc.de

On Sun, 3 Oct 2004 12:34:57 +0200
Kristian Rink <krink@pm-planc.de> wrote:

> Though not running postgresql for that solution: We are running an
> enterprise-scaled document management system to keep track of
> currently > 2*10^3 documents (mostly *.hpgl and *.plt files, some
    ^^^^^^^^^^^^^^^

Should be "2*10^6", of course - messing with 2000 files probably
ain't that challenging after all...


Cheers,
Kris

--
Kristian Rink     -- Programmierung/Systembetreuung
planConnect GmbH * Strehlener Str. 12 - 14 * 01069 Dresden
0351 4657702 * 0176 24472771 * krink@pm-planc.de