Assuming I want to build a web based data repository to store, serve and
provide interaction for all different kinds of datasets, possibly
holding up to 1 million different datasets (timeseries, bulk data, etc.)
ranging from 1kB to 1MB in size. Would it be a good idea to do this
using a database or should only the meta data of those datasets be
stored using a database and the data itself simply by using files for
each dataset?

Any help would be appreciated.

Many thanks

Andre

On Mar 11, 10:54*am, André <j... (AT) hrz (DOT) tu-chemnitz.de> wrote:
There was a time when I would definitely have said "put those large
objects in files". Nowadays, I do not think the answer is so clean
cut. For example, Oracle does better than it used to when storing
large data objects. So the answer really is to test it. Create
prototype systems, one using files the other storing in the DB, and
compare performance under conditions similar to your expected
application load. If there is no clear winner, go with what seems
easier for you to maintain in the long run.

One other point to consider if it is a tossup: how will backups be
performed. With DB only, there is just one data backup process. With
DB and files, you need two that MUST stay in sync.

HTH,
Ed

On 2010-03-11, André <joa (AT) hrz (DOT) tu-chemnitz.de> wrote:
1 MB is fairly small, I would put it in the database if it's not got
silly limits on size, it saves you from race conditions and data-file
synchronisation problems.

eg, if postgres.

CREATE TABLE datasets (
filename text PRIMARY KEY,
content bytea,
mtime timestamptz,
mime-type text);


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

On 03/11/2010 04:54 PM, André wrote:
I agree to Ed and Jasen. I think from a maintenance and consistency
point it is much easier storing everything in the database. Especially
the backup topic can become complex and fragile quickly if you have
different storages that you need to synchronize. With Oracle (or any
other decent RDBMS) you just backup the database and are done.

However, I' would also consider storing data sets as they are structured
in the database and go from there. That way you could also fetch only
parts of sets reducing the overhead and IO bandwidth needed. If, on the
other hand, a dataset is something like a JPEG image, then storing it as
blob is more appropriate (although even then you might want to
additionally store some meta data in the table). It really depends on
the use case - although, rereading your message it seems your situation
is more like the latter.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/