Re: Storing blobs in PG DB

Nikolay Moskvichev wrote:
> Hi All!
>
> Question is : How suitable PG for storing about 2 000 000 binary files
> 0,5-2,0 Mb size each ? It is not planned the big number of clients or a
> plenty of updatings. Like photoalbum on local host.

In our CMS we store all page data in the database - either text for HTML pages
or the images and other truly binary data.

I agree with most of the comments on why *not* to store binary application data
in the file system, but I'd add the following :

For scalability, we use a very high performance database server (battery backed
RAID controller, lots of disks, lots of RAM, processors etc). Virtually the only
thing it runs is Postgres. This connects to a number of 'front-end' web servers.
These are lower performance, cheaper boxes than the database. Generally 1U,
single disk, not RAID etc. The web-servers run Apache and the processor
intensive stuff like server-side JavaScript.

A load balancer shares web traffic across the front-end servers and can detect
if any of those fail and switch them out of servicing requests.

The front-end servers connect to the database server over gigabit Ethernet to
cut latency to a minimum.

We've found that the single high-spec database server is more than capable of
servicing quite a lot of front-end web servers.

Now in that environment, if you were to choose to store things in the file
system, not only have you got the issue of synchronising file system with
database, but of synchronising all the file systems.

You could get round some of those issues by keeping all the files on one system
and using an NFS share or some complex FTP system or something, but actually
it's a lot easier to hold the data in the database.

So - when you're developing your application it's worth thinking about what
happens as it starts to get busier. What's your route to scaling? Many web
applications are written to work on a single machine with no thought to what
happens when that reaches the limit, other than get a bigger server.

All the best
Peter Wilson
--
http://www.whitebeam.org
http://www.yellowhawk.co.uk
---