Thread: 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
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: >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 >
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
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 #