Re: Architectural question - Mailing list pgsql-performance

> -----Original Message-----
> Thomas Kellerer Wednesday, March 23, 2016 2:51 AM
>
> Jim Nasby schrieb am 11.03.2016 um 17:37:
> > If the blob is in the database then you have nothing extra to do. It's handled
> just like all your other data.
> >
> > If it's a file in a file system then you need to:
> >
> > - Have application code that knows how and where to get at the file
> > - Have a way to make those files available on all your webservers
> > - Have completely separate backup and recovery plans for those files
> >
> > That's a lot of extra work. Sometimes it's necessary, but many times it's not.
>
> Don't forget the code you need to write to properly handle transactional access
> (writing, deleting) to the files
>
> You usually also need to distribute the files over many directories.
> Having millions of files in a single directory is usually not such a good idea.
>
> In my experience you also need some cleanup job that removes orphaned files
> from the file system.
> Because no matter how hard you try, to get updates/writes to the file system
> right, at some point this fails.
>
> Also from a security point of view having this in the database is more robust
> then in the file system.
>
> The downside of bytea is that you can't stream them to the client. The
> application always needs to read the whole blob into memory before it can be
> used. This might put some memory pressure on the application server.
>
> Thomas

This is really an excellent conversation, and highlights the never-ending contemplation
of blob storage.  I've had to go through this dialog in two different industries - healthcare
and now genomics, creating a new EMR (electronic medical record) system and storing
and manipulating huge genomic data sets.

I have, in both cases, ended up leaving the blob-type data outside of the database.  Even
though, as Thomas mentioned, it requires more database and app code to manage, it
ends up allowing for both systems to be optimized for their respective duties.

In addition, the vastly smaller database sizes result in far faster backups and restores,
transactional replication maintains it's speed, and in general, I find the fault tolerant
behaviors to be excellent.

Yes, losing track of a file would be very bad, and...we're only storing things like xray photos
or ct scans (healthcare), or genomic processing results.  In both cases, usually, the results
can be recreated.  That said, I've never lost a file so haven't needed to pull on that lever.

My latest model is placing large genomic data onto the AWS S3 file system, keeping all of
the metadata inside the database.  It's working very well so far, but we're still in development.

Mike