Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1aiigN-0003o2-3O for pgsql-performance@arkaria.postgresql.org; Wed, 23 Mar 2016 13:18:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1aiigM-0001FY-IQ for pgsql-performance@arkaria.postgresql.org; Wed, 23 Mar 2016 13:18:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1aiigL-0001F7-Jr for pgsql-performance@postgresql.org; Wed, 23 Mar 2016 13:18:41 +0000 Received: from mail-vk0-x229.google.com ([2607:f8b0:400c:c05::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1aiigH-0000bg-Al for pgsql-performance@postgresql.org; Wed, 23 Mar 2016 13:18:40 +0000 Received: by mail-vk0-x229.google.com with SMTP id z68so18278347vkg.3 for ; Wed, 23 Mar 2016 06:18:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to; bh=lqnZF9YwqIBQvxrkFjkwsgAP9+AVatJzt/xJQd+DPc4=; b=I3dvcQgYXkjx1XUUQ9XQCf24TBjx2J40ydmrAFedbB9RXbYeYQA5kMydOla1zcR3Iq 0TMx1zxbCu1bSg8Sa312/0dP3yoiaW7FqxxJsygXUf8YDmCiB9DqrNSad7cFejAdpoLN 4IlsdWtdSAKhaev2dwZ77pkEqeMeSqF5eMryzhsb3U6nndUfsJ6s0o9At0VKaH6Zp4Nm E+DlggBlb/Qf5VWLuJOmepJyM5h4rki2k7ZIHiWbifr3lbOxGYBoj06Y7lPNw9XzMGa6 fPrkO5qVXLW8ou8PDn8+gMrpgDhHzlpeU6AxH6EUNQWuIjLnO4rQvuE4jxAHOZqjA2ws WXdw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to; bh=lqnZF9YwqIBQvxrkFjkwsgAP9+AVatJzt/xJQd+DPc4=; b=QeRHvQ4Bcfov1hIrGrCf+k9yFBlWuLX+x4TqL/yTovkxW4dKnVzjCD2pgUNwbjQpGZ qB977jIHxqYwjSWWFc9tmNNk30QDv73QuR2Yn/9UVIIaJJS0Q5cNyYn+138GptkmC6uq QIwF1aSTa0kVLa0NPn12HhIArMxpCZXpPD3jsSo8NU5VdV2dRuFB89TOKpBgsBaBanJM gSiNG4vw/G7FtrFG7pFiEw0Yq9DmOGLQfuP6jgCoh3a0dqkPcpOLZ7RKT35qsByNZItZ HHXCtNVKOaEUxfYBt4xSMfzUbqMe5vTNAgPTOFqbSDSJXkRv6mGdHVOGrlc5YntHk/89 0uwA== X-Gm-Message-State: AD7BkJLBYMv8SJXb58jjq3Y0QLvTQ5yVVCZaa/P+18wJHzEO9MVikkmonzCgkahnw2pQbzTFL6Rg7Wj3b1Lggw== MIME-Version: 1.0 X-Received: by 10.31.150.215 with SMTP id y206mr1353638vkd.63.1458739115866; Wed, 23 Mar 2016 06:18:35 -0700 (PDT) Received: by 10.31.209.65 with HTTP; Wed, 23 Mar 2016 06:18:35 -0700 (PDT) In-Reply-To: <037901d184ff$9c873a90$d595afb0$@runbox.com> References: <56BCCDB2.8050407@evolu-s.it> <56C62AAC.4000108@BlueTreble.com> <56CB1DE7.8090204@evolu-s.it> <56E2F44F.8030801@BlueTreble.com> <037901d184ff$9c873a90$d595afb0$@runbox.com> Date: Wed, 23 Mar 2016 09:18:35 -0400 Message-ID: Subject: Re: Architectural question From: Rick Otten To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=001a1140fdd8d272da052eb72a20 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a1140fdd8d272da052eb72a20 Content-Type: text/plain; charset=UTF-8 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 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --001a1140fdd8d272da052eb72a20 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I have another suggestion.=C2=A0 How about putting the ima= ges in RethinkDB?

RethinkDB is easy to set up and manage= , and is scalable and easy (almost trivial) to cluster.=C2=A0 Many of the f= ilesystem disadvantages you mention would be much more easily managed by Re= thinkDB.

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



On Wed, Mar 23, 2016 at 8= :29 AM, Mike Sofen <msofen@runbox.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 fi= le
> > - Have a way to make those files available on all your webservers=
> > - Have completely separate backup and recovery plans for those fi= les
> >
> > 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 transac= tional 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 g= ood 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 c= ontemplation
of blob storage.=C2=A0 I've had to go through this dialog in two differ= ent 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 d= atabase.=C2=A0 Even
though, as Thomas mentioned, it requires more database and app code to mana= ge, it
ends up allowing for both systems to be optimized for their respective duti= es.

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.=C2=A0 In both case= s, usually, the results
can be recreated.=C2=A0 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.=C2=A0 It's working very well so far, = but we're still in development.

Mike



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-perform= ance

--001a1140fdd8d272da052eb72a20--