Re: Storing blobs in PG DB

I have actually never stored data in the database. But in a recent
project I've realized it might have been smart. We store a terabytes of
data on the file system, and many times I would love to have an ACID
compliant file system. For example, if I delete an entry, I need to
delete it from disk and from the database. How can I be sure that was
transactional? Or if I append data to the file, and then update the
database. What then? I wind-up writing "tricky" code that does stuff
like renames a file, updates the DB, and renames it back if there is an
error in an attempt to fake-out atomicity and transactions.

Of course, I may have come-up with even more issues if the company put
this data into a SQL server. Who knows.

Where exactly does PostgreSQL put large blobs? Does it ensure ACID
compliance if I add a 2GB blob in a column?

Merlin Moncure wrote:
> On 4/5/07, Listmail <lists(at)peufeu(dot)com> wrote:
>>
>> > My personal view is that in general, binary files have no place in
>> > databases. Filesystems are for files, databases are for data. My design
>> > choice is to store the files in a fileystem and use the database to
>> hold
>> > metadata as well as a pointer to the file.
>> >
>> > If you *must* put files into the database, then you can do so, and PG
>> > will handle that many files of those sizes with ease. For all intents
>> > and purposes, PG can store an unlimited number of files. You're far
>> more
>> > likely to run into walls in the form of limitations in your disk I/O
>> > system then limitations in what PG will handle.
>>
>> And you can't backup with rsync...
>
> no, but you can do incrementals with PITR, which is just as good (if
> not better) than rsync because you are backing up your database
> 'indexer' and binaries in one swoop...so the backup argument doesn't
> fly, imo.
>
> imo, sql is a richer language for storing and extracting any type of
> data, binaries included, than hierarchal filesystem style
> organization. I think most reasons why not to store binaries in the
> database boil down to performance.
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>