Re: Images in Database

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:

> Yes, they can be dumped just fine.
>
> I also don't understand what information you have that leads you to
> make the claim that space is wasted. I guess it is due to
> your assumption that a BLOB is used. A bytea type takes 4 extra bytes
> than the actual size of the data. Furthermore, the field becomes part
> of a bigger file and therefore will have less space loss due to
> differences between block size and file size. Since that difference
> is a maxium per file, the fewer filesystem files you have, the less
> space is lost.
>
> As far as image searching, there are utilities that compare image
> files already. If you need this for your app, I'm sure the procedure
> could be duplicated in the RDBMS.
>
> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Reshat Sabiq
> Sent: Saturday, March 27, 2004 6:33 PM
> To: Rod K
> Cc: M. Bastin; pgsql-novice(at)postgresql(dot)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.
>
>