From: Merlin Moncure <mmoncure@gmail.com>
To: Alexandre Vasconcelos <alex.vasconcelos@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Large objetcs performance
Date: Thu, 12 Apr 2007 09:42:03 -0400
Message-ID: <b42b73150704120642p239ea6ddkef9de07afc5921f2@mail.gmail.com> (raw)
In-Reply-To: <26551fb50704040703n75d9b8d9p7ab4bf11109dfe63@mail.gmail.com>
References: <26551fb50704040703n75d9b8d9p7ab4bf11109dfe63@mail.gmail.com>
On 4/4/07, Alexandre Vasconcelos <alex.vasconcelos@gmail.com> wrote:
> We have an application subjected do sign documents and store them
> somewhere. The files size may vary from Kb to Mb. Delelopers are
> arguing about the reasons to store files direcly on operating system
> file system or on the database, as large objects. My boss is
> considering file system storing, because he is concerned about
> integrity, backup/restore corruptions. I'd like to know some reasons
> to convince them to store these files on PosgtreSQL, including
> integrity, and of course, performance. I would like to know the file
> system storing disadvantages as well.
This topic actually gets debated about once a month on the lists :-).
Check the archives, but here is a quick summary:
Storing objects on the file system:
* usually indexed on the database for searching
* faster than database (usually)
* more typical usage pattern
* requires extra engineering if you want to store huge numbers of objects
* requires extra engineering to keep your database in sync. on
postgresql irc someone suggested a clever solution with inotify
* backup can be a pain (even rsync has its limits) -- for really big
systems, look at clustering solutions (drbd for example)
* lots of people will tell you this 'feels' right or wrong -- ignore them :-)
* well traveled path. it can be made to work.
Storing objects on the database:
* slower, but getting faster -- its mostly cpu bound currently
* get very recent cpu. core2 xeons appear to be particularly good at this.
* use bytea, not large objects
* will punish you if your client interface does not communicate with
database in binary
* less engineering in the sense you are not maintaining two separate systems
* forget backing up with pg_dump...go right to pitr (maybe slony?)
* 1gb limit. be aware of high memory requirements
* you get to work with all your data with single interface and
administrate one system -- thats the big payoff.
* less well traveled path. put your r&d cap on and be optimistic but
skeptical. do some tests.
merlin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, alex.vasconcelos@gmail.com
Subject: Re: Large objetcs performance
In-Reply-To: <b42b73150704120642p239ea6ddkef9de07afc5921f2@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox