Skip site navigation (1) Skip section navigation (2)

Re: Images in Database

From: Oliver Fromme <olli(at)lurza(dot)secnetix(dot)de>
To: sabiq(at)csociety(dot)org
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Images in Database
Date: 2004-03-30 10:38:17
Message-ID: 200403301038.i2UAcHiF029058@lurza.secnetix.de (view raw or flat)
Thread:
Lists: pgsql-novice
Reshat Sabiq wrote:
 > Oliver Fromme wrote:
 > > That's not really a problem.  You can write a trivial CGI
 > > program in your favourite language (Python, PHP, Perl or
 > > whatever, even /bin/sh) that fetches an image from the DB,
 > > so you can write URLs like http://.../imgfetch.cgi?foo.jpg.
 > > 
 > > You're even a lot more flexible that way, because you can
 > > fetch images by various criteria, not just by filename.
 > 
 > Thanks. Good point. However, this would has a performance penalty.

No, not necessarily.  If you do it right, then it can be as
fast as filesystem access, or even faster.

You should take into account that a filesystem (like UFS)
is some kind of a "database", too.  It is accessed via
path names, which can be more or less efficient than
indices inside a "real" database (such as PostgreSQL).

Apart from that, if you require the performance, then you
can put reverse proxies in front of your web servers to
accelerate them, or let your CGI cache the images.  That's
common practice.

 > I'd 
 > prefer not to use this approach for images accessed frequently, 
 > especially if they are large, and even more so if the DB is not on the 
 > same box as the web server.

Nope.  If they're accesses frequently, a reverse proxy will
help, and also your CGI can easily cache such images (also
take into account that a database performs some caching on
its own).

It shouldn't matter at all whether they're large or not.
The database operation which takes the most time is to find
the actual location of the object (image).  That's also
true for a filesystem.  Once the object is located, it can
be fetched at whatever speed the hardware allows.

It also doesn't make a big difference whether the DB is on
the same physical machine as the web server.  On seriously
big installations, the storage is on a separate file server
anyway (e.g. a NetApp Filer or some other RAID box).  The
network overhead is small if you use gigabit ethernet,
which is standard nowadays.

 > But in general, you guys are right, most of the time storing images in 
 > the DB is better. IMHO, there are however exceptions to that approach, 
 > where referring to the file system is better.

Well, everything has advantages as well as disadvantages,
and everyone has to make his own decision.  But in this
particular case I think the disadvantages (of not storing
images in the DB itself) are negligible, or can be worked
around without problems.

Just my two cents, and YMMV.  :-)

Best regards
   Oliver

-- 
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

Passwords are like underwear.  You don't share them,
you don't hang them on your monitor or under your keyboard,
you don't email them, or put them on a web site,
and you must change them very often.

In response to

pgsql-novice by date

Next:From: Thilo HilleDate: 2004-03-30 12:52:25
Subject: Re: dynamic interval in plpgsql
Previous:From: Colin GillespieDate: 2004-03-30 09:08:53
Subject: Formating Dates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group