Re: Seeking advice on database table design for storing - Mailing list pgsql-general

I believe your statement to be wrong. It's been a while, and maybe things have changes, but the last time I tested
this,images in the database were _significantly_ faster. At that time, I attributed it to the operating system overhead
(quota,rights, etc navigating the directory structure) vs. database overhead (rights, index->disk translation). 

Furthermore, there are issues were filenames can collide. The script is rather easy for pulling image data out:
SRC="image.php?id=10"then in image.php send the mime type, then the data.  

Additionally with files laying around on a disk, then can de deleted or corrupted by things other than the database
engineor yourself. You also have to worry about keeping a directory consistent with the database (easy, but it's one
morestep than if you did just store them as blobs to start with) 

I hope this helps!

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dennis Gearon
Sent: Friday, February 07, 2003 11:45 AM
To: pgsql-general@postgresql.org; chris.gamble@CPBINC.com
Subject: Re: [GENERAL] Seeking advice on database table design for
storing images


It's faster to store the images in the file system, and the path/filename in the database.

For one thing, the file system itself is just faster.
You would have to provide the client's browser with a URL for the image, and feed that through
some sort of switchyard script application, when with a filesystem based image, you just specifiy
where it is and let apache worry about it.

The only real advantage to putting images in the database, or hiding them behind another name in
the document tree and using a switchyard application to redirect the image request is to protect
your image directory and images from any use but in your site's documents (until they are
downloaded once)

2/7/2003 8:18:56 AM, chris.gamble@CPBINC.com wrote:

>I am working on an application that will store images with every product
>ordered from a given company. Doing this type of application on other
>databases, I have always been told to use a seperate table for the image
>store. Doing this has given me the table designs listed below. My question
>is: Is it within the design of postgres 7.3 to store 30k to 1mb images in a
>bytea field, and if so can the two tables below be joined into a single
>table without suffering adverse effects?
>
>TABLE - tdatInvoiceLineItems
>invoiceid   int8
>productid  int4
>quantityordered  int4
>samplestocustomer  int4
>adcost  numeric  10,4
>adheight  float4  4
>adwidth  float4  4
>workorderid  int8
>objectid  int8  8
>needsart  bool
>
>TABLE - tdatCustomerArt
>lineitemid   int8
>artwork  bytea
>extension  varchar
>
>
>Chris Gamble
>CPB Inc
>p: 972-579-1642 x 22
>f: 972-579-1355
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org