Seeking advice on database table design for storing images

As far as I know, Postgresql uses a separate space for storing the large
objects (bytea and text, in this case) to prevent against
performance-issues.
For storing images you might improve your performance and such things by
simply using the LOB-interface of Postgresql, in that case Postgres
stores the images as normal files and stores the OID in your table.

Since the normal performance tips for storing images involve storing
them on your filesystem and only the location in the database, it
doesn't sound to stupid to have postgresql handle that automatically for
you.

You'd probably have to check whether the above performance tips hold for
your application, but I'm pretty sure postgresql stores all text fields
seperately (or at least as soon as they don't fit into the local record)
and it would surprise me if bytea was handled differently.

Regards,

Arjen

-----Oorspronkelijk bericht-----
Van: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Namens
chris.gamble@CPBINC.com
Verzonden: vrijdag 7 februari 2003 17:19
Aan: pgsql-general@postgresql.org
Onderwerp: [GENERAL] Seeking advice on database table design
for storing images

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

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:

As an explanation of wanting to use the db:
The application is client/server that is distributed over various internal
links. There is no common file sharing available, and it would seem that
managing an extra security layer for file store would be an excessive
addition for the it guys. So I'm using some of past karma to hope that my
plan is workable if not the most efficient.

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@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

Dennis Gearon wrote:

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)

It's not the only advantage. What about making a consistent online
backup that includes a snapshot of the image collection? What about
session authenticated access to image data so that a user can only see
the images associated with an invoice where he has permissions for the
branch or department?

I store binary data b64-encoded in text fields.

Jan

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

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #