Re: Seeking advice on database table design for storing

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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] Namens
> chris(dot)gamble(at)CPBINC(dot)com
> Verzonden: vrijdag 7 februari 2003 17:19
> Aan: pgsql-general(at)postgresql(dot)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