Hi All!

Question is : How suitable PG for storing about 2 000 000 binary files
0,5-2,0 Mb size each ? It is not planned the big number of clients or a
plenty of updatings. Like photoalbum on local host.

You don't like filesystems ?

On Wed, 04 Apr 2007 07:44:57 +0200, Nikolay Moskvichev <mnv@papillon.ru>
wrote:

> Hi All!
>
> Question is : How suitable PG for storing about 2 000 000 binary files
> 0,5-2,0 Mb size each ? It is not planned the big number of clients or a
> plenty of updatings. Like photoalbum on local host.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

>
> You don't like filesystems ?
>
You know file system which supports SQL, referential integrity, and
managed transactions ?

I recently heard from Josh Berkus that at least one major CMS
application uses Postgres to store entire HTML pages (including image
files) in order to support full versioning.

As a general rule, I prefer not to store BLOBS in a DB- I'd rather
leave the BLOB in the file system and let the db save only a pointer
to it. However, if you need to store BLOBs in a database, Postgres is
one of the better platforms for it.

On 4/3/07, Nikolay Moskvichev <mnv@papillon.ru> wrote:
> Hi All!
>
> Question is : How suitable PG for storing about 2 000 000 binary files
> 0,5-2,0 Mb size each ? It is not planned the big number of clients or a
> plenty of updatings. Like photoalbum on local host.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Postgres User пишет:
> I recently heard from Josh Berkus that at least one major CMS
> application uses Postgres to store entire HTML pages (including image
> files) in order to support full versioning.
>
> As a general rule, I prefer not to store BLOBS in a DB- I'd rather
> leave the BLOB in the file system and let the db save only a pointer

Yes, current solution uses the similar approach

> to it. However, if you need to store BLOBs in a database, Postgres is
> one of the better platforms for it.
>
Thanks

This doesn't answer your question, but I thought I'd throw my opinion in
anyway.

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.

- Naz.

Nikolay Moskvichev wrote:
> Hi All!
>
> Question is : How suitable PG for storing about 2 000 000 binary files
> 0,5-2,0 Mb size each ? It is not planned the big number of clients or
> a plenty of updatings. Like photoalbum on local host.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

> 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...

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 4/5/07, Listmail <lists@peufeu.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@postgresql.org so that your
message can get through to the mailing list cleanly

Merlin Moncure wrote on 05.04.2007 23:24:
> I think most reasons why not to store binaries in the
> database boil down to performance.

Having implemented an application where the files were stored in the filesystem
instead of the database I have to say, with my experience I would store the
files in the DB the next time. Once the number of files in a directory exceeds a
certain limit, this directory is very hard to handle.

Things like "dir", or "ls" or listing the contents through a FTP connection
become extremely slow (using HP/UX as well as Windows).

And you have to backup only _one_ source (the database), not two. Moving the
data around from system a to system b (e.g. staging (windows) -> production
(HP/UX)) is a lot easier when you can simply backup and restore the database (in
our case it was an Oracle database, but this would be the same for PG)

Thomas


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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@peufeu.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@postgresql.org so that your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly