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@xxxxxxxxxx> 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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
