Re: Architectural question

I have another suggestion. How about putting the images in RethinkDB?

RethinkDB is easy to set up and manage, and is scalable and easy (almost
trivial) to cluster. Many of the filesystem disadvantages you mention
would be much more easily managed by RethinkDB.

A while back I wrote a Foreign Data Wrapper for RethinkDB. I haven't
updated it to the latest version, but it wouldn't be hard to bring it up to
date. (It might even work as-is.) By leveraging the FDW, you could have
all of the awesome Relational Power and performance of PostgreSQL combined
with the scalable, easily clustered, NoSQL powers of RethinkDB, yet still
have a common interface - if you need it.

On Wed, Mar 23, 2016 at 8:29 AM, Mike Sofen <msofen(at)runbox(dot)com> wrote:

> > -----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
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>