Re: How to store text files in the postgresql?

Yaroslav Tykhiy wrote:
> DimitryASuplatov wrote:
>>
>> My task is to store a lot (10^5) of small ( <10 MB) text files in the
>> database with the ability to restore them back to the hard drive on
>> demand.
>
> I cannot but ask the community a related question here: Can such design,
> that is, storing quite large objects of varying size in a PostgreSQL
> database, be a good idea in the first place? I used to believe that
> what RDBMS were really good at was storing a huge number of relations,
> each of a small and mostly uniform size if expressed in bytes; but today
> people tend to put big things, e.g., email or files, in relational
> databases because it's convenient to them. That's absolutely normal as
> typical data objects we have to deal with keep growing in size, but how
> well can databases stand the pressure? And can't it still be better to
> store large things as plain files and put just their names in the
> database? File systems were designed for such kind of job after all,
> unlike RDBMS.
>
> Thanks!
>
> Yar
>

I'd have to vote yes, its a good idea. It offers consistency that you
cant get with the file system. We store assessor information in a
database along with a photo of the house. If the photos were on the
file system whats to prevent someone from blowing away the folder? (our
network admin loves to find big folders taking up lots of disk space and
blow them away)

Sure, you say, they can "delete from photos", but that's different (and
because our network admin does not know sql). If they delete a file,
I'll still have a photo record and know they used to have a photo, and I
know the path to the photo, but there is no photo there. But with the
photo in the database, if there is no photo, there is no record either.

If you are looking for speed, yea, I'd say store it on the file system.
But we are looking for convenience and consistency.

-Andy