Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fCCER-0007cy-7t for pgsql-general@arkaria.postgresql.org; Fri, 27 Apr 2018 22:52:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fCCEP-0002ZA-RN for pgsql-general@arkaria.postgresql.org; Fri, 27 Apr 2018 22:52:45 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fCCEP-0002Z0-0h for pgsql-general@lists.postgresql.org; Fri, 27 Apr 2018 22:52:45 +0000 Received: from rs113.luxsci.com ([72.32.40.101]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fCCEL-0006tL-Dx for pgsql-general@postgresql.org; Fri, 27 Apr 2018 22:52:43 +0000 Received: from rs113.luxsci.com (localhost.localdomain [127.0.0.1]) by rs113.luxsci.com (8.14.7/8.14.9) with ESMTP id w3RMqdqE046671 for ; Fri, 27 Apr 2018 17:52:39 -0500 Received: (from lshttpd@localhost) by rs113.luxsci.com (8.14.7/8.14.9/Submit) id w3RMqdes046670; Fri, 27 Apr 2018 22:52:39 GMT Message-Id: <201804272252.w3RMqdes046670@rs113.luxsci.com> From: "g@luxsci.net" Date: Fri, 27 Apr 2018 22:52:39 +0000 To: pgsql-general@postgresql.org Reply-To: g@luxsci.net Errors-To: g@luxsci.net MIME-Version: 1.0 X-Comment: Charset encoding changed from (utf-8) to (UTF-8) for compatibility with message contents. X-Comment: 85389-8711-1 Subject: Re: Rationale for aversion to the central database? Content-Type: multipart/mixed; boundary="------------------_Boundary_64830870.7214547"; charset="utf-8" DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=luxsci.net; h=message-id:from:date:to:reply-to:mime-version:subject:content-type; s=p1; bh=F0TUcck9DNBMLSctmXYARe4aQeKvkkUc2ja5ohNtcBk=; b=TXmLEmbpQucAXQGKy3Bo1N8LOWD5W6NetzfAawXjFMdcQHcFk/CzhmQyr6AeMqgXP8ScEDuZPLZqHvJVT4jYGJmdj4pWO6isyXI5JcHvISTLK7dXmxbOo1FX6jeyLJn1YuU7FxnRXwtSKLCA87fhRDBN08aL2yiebRNLTVn13Ig= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. If you cannot view the attachment(s), then your email client does not support MIME. --------------------_Boundary_64830870.7214547 Content-Type: multipart/alternative; boundary="-------------3977378.30799131" ---------------3977378.30799131 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable =C2=A0 =C2=A0 On April 24, 2018 07:27:59 am PDT, "Sam Gendler"=20 wrote: =C2=A0 =C2=A0 On Sun, Apr 8, 2018 at 15:37 [1]g@luxsci.net <[2]g@luxsci.net> wrote: =C2=A0 =C2=A0 On April 8, 2018 02:40:46 pm PDT, "Guyren Howe" <[3]guyren@gmail.com> wr= ote: One advantage to using logic and functions in=C2=A0 the db is that you=20 can fix things immediately without having to make new application=20 builds. That in itself is a huge advantage, IMO. =C2=A0 =C2=A0 I doubt most of us would consider this any kind of advantage outside of=20 the momentary temptation to do it when an app is completely broken and=20 needs to be up in a hurry. Application changes, whether in the dB or in=20 application logic, need to be tested, and they need to be revision=20 controlled and released in a manner that can be easily rolled back in=20 an automated manner. The fact that putting logic in the database can=20 effectively allow developers to make unreleased changes to production=20 apps is specifically one of the problems that I am trying to avoid when=20 I keep most logic in the app instead of the dB. It=E2=80=99s a whole lot=20 harder to make arbitrary manual changes to code in the app, whether=20 interpreted or compiled, if it is running inside a container that=20 cannot be updated. Even if you go in with a shell and update an=20 interpreted file, the next time that container is launched the change=20 will be lost, which is usually sufficient motivation to keep devs from=20 doing that kind of thing. =C2=A0 I=E2=80=99ll put some things in the db, either for performance or because I= =20 want that logic to be built into the data and not be part of the=20 application, but I choose those contexts carefully and I write them in=20 as portable a manner as possible. And for those who say migrations=20 don=E2=80=99t happen, I=E2=80=99ve certainly been through a few, usually as= part of=20 an acquisition or the like, but sometimes simply because another dB=20 server better meets our needs after a time. And migrating stored procs=20 can be really difficult. Such code usually has less complete unit and=20 integration tests, which makes validating those changes more difficult,=20 too. =C2=A0 But the biggest reason is that databases often have to scale up rather=20 than out, so keeping as much logic in the application code allows my=20 scaling requirements for the dB server to be as minimal as possible.=20 Sure, there are workloads where pushing raw data across the wire will=20 be more work than processing it in the dB, and in those cases, I may do=20 that, but I consider it premature optimization to just assume that is=20 necessary without hard evidence from production examples to suggest=20 otherwise. =C2=A0 Finally, there=E2=80=99s the consistency argument. I want to find all of th= e=20 logic in one place. Either entirely in the source code or entirely in=20 the dB. Having to trace things from the code to the dB and back again=20 can make it a whole lot harder to see, at a glance, what is happening=20 in the code. Having logic in the dB also means it can be difficult or=20 impossible to have two releases talking to the same schema version at=20 the same time - so canary builds and rolling deployments can be=20 difficult. Of course, schema changes can cause this problem, regardless=20 of whether there are stored procs, but the more of your logic that=20 lives in the db, the more likely it is that your releases will conflict=20 over the db. So I=E2=80=99m more likely to be able to do a rolling release = if=20 I keep the db as a dumb data store and keep logic in the application=20 code. =3D=3D=3D=3D=3D=3D=3D I could have worded that better but I think that we're coming at it=20 from different directions. You think of your application as the=20 "master" operator. I think of a PG db as the "master", not a slave. I=20 believe that we shouldn't _have_ to use an external application for the=20 database to be useful and coherent.=C2=A0 I like to think of external=20 applications as subservient to the db and not the other way around.=20 Yeah, I know, probably not a popular viewpoint. Sorry, I don't really understand why it would be so hard to migrate,=20 say pl/pgsql functions. You can maybe expect to write some likely=20 convoluted application code, though. :) Reusable functions in the db=20 that are solid also means that developers don't have to reinvent the=20 wheel in whatever language and debugging also becomes simpler. Perhaps I'm extreme. In my ideal world, developers might not even know=20 table names! I'm kidding ,sorta... Thanks much for your comments, Gerry =C2=A0 References Visible links 1. mailto:g@luxsci.net 2. mailto:g@luxsci.net 3. mailto:guyren@gmail.com ---------------3977378.30799131 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable  
 
On April 24, 2018 07:27:59 am PDT, &quo= t;Sam Gendler" <sgendler@ideasculptor.com> wrote:
 
 
On Sun, Apr 8, 2018 at 15:37 g@luxsci.net <g@luxsci.net&g= t; wrote:
 
 
On April 8, 2018 02:40:46 pm PDT, "G= uyren Howe" <= guyren@gmail.com> wrote:

One advantage to using logic and functions in  the db is that you can = fix things immediately without having to make new application builds. That = in itself is a huge advantage, IMO.
 
 
I doubt most of us would consider this any kind of advant= age outside of the momentary temptation to do it when an app is completely = broken and needs to be up in a hurry. Application changes, whether in the d= B or in application logic, need to be tested, and they need to be revision = controlled and released in a manner that can be easily rolled back in an au= tomated manner. The fact that putting logic in the database can effectively= allow developers to make unreleased changes to production apps is specific= ally one of the problems that I am trying to avoid when I keep most logic i= n the app instead of the dB. It’s a whole lot harder to make arbitrar= y manual changes to code in the app, whether interpreted or compiled, if it= is running inside a container that cannot be updated. Even if you go in wi= th a shell and update an interpreted file, the next time that container is = launched the change will be lost, which is usually sufficient motivation to= keep devs from doing that kind of thing.
 
I’ll put some things in the db, either for performa= nce or because I want that logic to be built into the data and not be part = of the application, but I choose those contexts carefully and I write them = in as portable a manner as possible. And for those who say migrations don&r= squo;t happen, I’ve certainly been through a few, usually as part of = an acquisition or the like, but sometimes simply because another dB server = better meets our needs after a time. And migrating stored procs can be real= ly difficult. Such code usually has less complete unit and integration test= s, which makes validating those changes more difficult, too.
 
But the biggest reason is that databases often have to sc= ale up rather than out, so keeping as much logic in the application code al= lows my scaling requirements for the dB server to be as minimal as possible= . Sure, there are workloads where pushing raw data across the wire will be = more work than processing it in the dB, and in those cases, I may do that, = but I consider it premature optimization to just assume that is necessary w= ithout hard evidence from production examples to suggest otherwise.
 
Finally, there’s the consistency argument. I want t= o find all of the logic in one place. Either entirely in the source code or= entirely in the dB. Having to trace things from the code to the dB and bac= k again can make it a whole lot harder to see, at a glance, what is happeni= ng in the code. Having logic in the dB also means it can be difficult or im= possible to have two releases talking to the same schema version at the sam= e time - so canary builds and rolling deployments can be difficult. Of cour= se, schema changes can cause this problem, regardless of whether there are = stored procs, but the more of your logic that lives in the db, the more lik= ely it is that your releases will conflict over the db. So I’m more l= ikely to be able to do a rolling release if I keep the db as a dumb data st= ore and keep logic in the application code.

=3D=3D=3D=3D=3D=3D=3D

I could have worded that better but I think that we're coming at it fro= m different directions. You think of your application as the "master&q= uot; operator. I think of a PG db as the "master", not a slave. I= believe that we shouldn't _have_ to use an external application for th= e database to be useful and coherent.  I like to think of external app= lications as subservient to the db and not the other way around. Yeah, I kn= ow, probably not a popular viewpoint.

Sorry, I don't really understand why it would be so hard to migrate, sa= y pl/pgsql functions. You can maybe expect to write some likely convoluted = application code, though. :) Reusable functions in the db that are solid al= so means that developers don't have to reinvent the wheel in whatever l= anguage and debugging also becomes simpler.

Perhaps I'm extreme. In my ideal world, developers might not even know = table names! I'm kidding ,sorta...

Thanks much for your comments,
Gerry
 
---------------3977378.30799131-- --------------------_Boundary_64830870.7214547--