Re: Database versus filesystem for storing images

From: James Neff <jneff(at)tethyshealth(dot)com>
To: John McCawley <nospam(at)hardgeus(dot)com>
Cc: Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com>, imageguy <imageguy1206(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Database versus filesystem for storing images
Date: 2007-01-05 20:26:45
Message-ID: 459EB485.8080203@tethyshealth.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"... and Moses said unto them, 'The eleventh commandment : thou shalt
store images in a database!'..."

What if you had another database where you stored just the images and
not back it up if you don't want to?

As an application developer, I like the idea of storing files and images
in the database because it makes it much easier to control access and
security from an application standpoint.

I think Microsoft SQL Server stores blobs in a separate file, and only
retains pointers in the actually database field for that blob. So when
you SELECT on that blob MS SQL reads the external file for you as if it
lived in the database. I don't know if Postgres does the same thing,
but if it did, you wouldn't have to worry about "bloating" database files.

Sounds like this is for an Apache web application. Think about how web
sites like Flickr and Webshots store their images in a database. You
could write a cool Apache mod so that the url:
"http://mycompany.com/images/01234.jpg" would go through this module,
pull the appropriate image from the database and send it back; all the
while the client is none-the-wiser. Just a thought.

I think its one of those things where there's not right or wrong
answer. Instead you just have to do the minimum of what your
application requires. If you don't need application-level control over
the files, then by all means store them on the file system. But if you
need to control security than you have to prevent physical access to the
file (which means no file system storage) and pull the image from the
database through the application.

My two cents,
James

John McCawley wrote:
> Don't store your images in the database. Store them on the filesystem
> and store their path in the database. Anyone that tells you otherwise
> is a stark raving madman :)
>
> My system is very heavily used, and our pg_dump is only a few gigs.
> Meanwhile our images/documents storage is well over a hundred gigs.
> I'd hate to think that I'd have to dump and restore 100 gigs every
> time I wanted to dump the newest data to the development database.
>
>
> As far as how they actually get to the client machine, typically these
> days people use web servers for this sort of thing.
> Clodoaldo wrote:
>
>> 5 Jan 2007 06:59:18 -0800, imageguy <imageguy1206(at)gmail(dot)com>:
>>
>>>
>>> I think I know the answer,
>>
>>
>> If you know the answer please tell it as I have read some discussions
>> on the web and although I have decided on a solution I'm still not
>> sure about the best answer, if there is a best answer after all.
>>
>>> but if you don't have an "application
>>> server" - ie a webserver, etc,
>>
>>
>> Yes I have an application server, the Apache server.
>>
>>> and many of the workstations/clients
>>> that need access to the images but may not have access to a network
>>> share,
>>
>>
>> network share? I don't understand. The images will be loaded by html
>> pages with the img tag like in <img
>> src="http://domain.com/images/xxx.jpg">
>>
>>> isn't the database the only choice ?
>>
>>
>> No. It is one of the choices. The other is to store the images in the
>> file system, in a directory readable by Apache.
>>
>>> - or is there a postgresql function/utility that will "server" the
>>> file from the file system based on the reference/link embeded in the
>>> database ??
>>
>>
>> I think some procedure languages can read files. In this case what
>> would be the gain in introducing a middle man, the db server?
>>
>> Regards,
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jorge Godoy 2007-01-05 20:26:51 Re: Database versus filesystem for storing images
Previous Message Thomas F. O'Connell 2007-01-05 20:17:02 Re: Interrupted pg_dump / pg_restore Upgrade