Re: Images in Database - Mailing list pgsql-novice

I guess i had wrong assumptions. Since the following points raised are indeed true (although i'm not sure about x.a's), then yes storing them in a DB will not waste storage, probably even save some:
1. Images use a bytea type, which is not fixed size and is proportional (and close) to the image size. I see that it uses 4 bytes more than the value size (apparently to store the size in them).
    1.a. Even a BLOB type, is not fixed size, and is proportional to the value stored.
2. Regular DB dump utilities can be used to transfer databases containing bytea fields. I see that it uses byte escapes to accomplish that.
    2.a. I assume the same is true for BLOBs, although apparently it's a little more difficult.

Then the only reasons i can see to not store images in a DB, and the following:
i. If the same images also need to be accessed from a file system, such as by a web server, as regular URLs.
ii. If there are values that exceed the type limit (1GB for bytea); this is unlikely.
iii. I'm also seeing that BLOBs have some security concerns, which probably can be worked around, but an alternative would be to rely on the file system for permissions, etc.

Of these 3, only i. appears to be a realistic concern for images. It's a lot easier to list a URL on an [X][HT]ML page. But fat clients would probably be fine either way.

Thanks for correcting me. Learning never stops. :)

-- 
Sincerely,
Reshat.
---
If you see my certificate with this message, you should be able to send me encrypted e-mail. 
Please consult your e-mail client for details if you would like to do that.

Rod K wrote:

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Reshat Sabiq
Sent: Saturday, March 27, 2004 6:33 PM
To: Rod K
Cc: M. Bastin; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Images in Database

Valid point. Maintenance might be easier (although i don't think you can dump and move images easily to another DB; you'd probably have to do some kind of direct connection to another DB to move them).  When in a filesystem, you could bzip everything, and move them easily. There could also be an URL-prefix field for each client, followed by a URL suffix field for each image. Thus maintenance most of the time would be as easy as changing the prefix.
However, no matter how small an image is, it takes the same amount of BLOB space, doesn't it. This, IMHO, means a lot of wasted storage. Not sure if that also affects performance to some small degree. Storage is cheap, but still it costs money.

So, still it looks to me storing multi-media w/o additional benefits isn't quite worthwhile. But if there was something like find image LIKE another image, then i'd change my opinion in a sec. :)
But then again, i'm not against it. I just think it doesn't buy much, and wastes storage space.

-- 
Sincerely,
Reshat.