Re: Seeking advice on database table design for storing

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 engine or yourself. You also have to worry about keeping a directory consistent with the database (easy, but it's one more step than if you did just store them as blobs to start with)

I hope this helps!

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Dennis Gearon
Sent: Friday, February 07, 2003 11:45 AM
To: pgsql-general(at)postgresql(dot)org; chris(dot)gamble(at)CPBINC(dot)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(dot)gamble(at)CPBINC(dot)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