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