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 1fCDIM-0003iz-QH for pgsql-general@arkaria.postgresql.org; Sat, 28 Apr 2018 00:00:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fCDIL-0002j6-Hx for pgsql-general@arkaria.postgresql.org; Sat, 28 Apr 2018 00:00:53 +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 1fCDIK-0002ix-V9 for pgsql-general@lists.postgresql.org; Sat, 28 Apr 2018 00:00:53 +0000 Received: from mail-ot0-x236.google.com ([2607:f8b0:4003:c0f::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fCDIH-0008Sr-Uj for pgsql-general@lists.postgresql.org; Sat, 28 Apr 2018 00:00:51 +0000 Received: by mail-ot0-x236.google.com with SMTP id g7-v6so3869704otj.11 for ; Fri, 27 Apr 2018 17:00:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=odqMjy/3K0hltbaxWg7LMBK8icQwY8f+jqiNWnmk3Y4=; b=NRq3AuVez2QlewvaMPMNYPd+SdmxC/h8MtDwbT0zIQfYAX9b0d1lJB/PDayQ4Lg8v1 a4eWEL1WEOMTgImH0ri2VYQGyhyC4ovPsCtNPDthSq6NA6Vi7y1plrgENzaWOoklJuPZ daR2fcgCcrh3emVJaWuCOPuQz/0DFYSvhEi0ZwEKJHVLsRT4xO80UY+j38Dh74p7Vg/1 pb0z/y9D/+YfdDHBe3y0Dz8gXk8aQeegfrhbeFGhd/CjdipJ68oaEXyaBV+YZjWnpZIN /V0SyWHQAUlT9dZ5HMECK7FhtrISM4vu0AJVjWiWDLFXjL0bxViA10I9VnVnKDD8NqqD GGYA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=odqMjy/3K0hltbaxWg7LMBK8icQwY8f+jqiNWnmk3Y4=; b=fto19CZxIp7aaNIHXvKieCLihQautkFuL1FjkT1b0z8ujpJ1DHXJPcH+PQCpeueCYF d/9uFjpMpMIAjf3n1g+UPsJPQHVghp5met9zonkZQj+LgSETKV7Vy25H2RFuu5brHkM9 d8Cge8BlRoMcuoJ61RsTIutNqvUZP8TJxXQRBqlqzxoD3uuthAipyw4nQyN+P052NtXY BJ1SXnaHIn2/P9u/84bzb/bPgIOSD7jTAEZSEYOa45qOCExhgRjuI47sceKLmQTe3+mT UJWfv2O6QxYk4hmJV5SCJ+/AwahSLzD/4JdoWieQhQGeyheq6xl8d8GuzRS47eXVWuqC CRCw== X-Gm-Message-State: ALQs6tDWFp5YTG4Lr1G+DJpGn4M6vwUhwBdR/eL3BJvx89AtH1GErrIa iIRqnc8ulsZUky/yHKo7ROaGxA== X-Google-Smtp-Source: AB8JxZoreyxADv7p3RM2Iz6pRF3bGswaEtZw+6JB2I3pce3vOAly7shLS/0j3gIU8YxdplAiwUAzew== X-Received: by 2002:a9d:28e7:: with SMTP id s94-v6mr3001477ota.346.1524873648259; Fri, 27 Apr 2018 17:00:48 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id l72-v6sm1308197oib.55.2018.04.27.17.00.46 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 27 Apr 2018 17:00:47 -0700 (PDT) Subject: Re: Rationale for aversion to the central database? To: pgsql-general@lists.postgresql.org References: <201804272252.w3RMqdes046670@rs113.luxsci.com> From: Ron Message-ID: <33364fad-7a39-2f4c-fc95-020ee99abbea@gmail.com> Date: Fri, 27 Apr 2018 19:00:46 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.7.0 MIME-Version: 1.0 In-Reply-To: <201804272252.w3RMqdes046670@rs113.luxsci.com> Content-Type: multipart/alternative; boundary="------------B076F16C384412B97CD0C0E1" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------B076F16C384412B97CD0C0E1 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 04/27/2018 05:52 PM, g@luxsci.net wrote: > > On April 24, 2018 07:27:59 am PDT, "Sam Gendler" > wrote: > On Sun, Apr 8, 2018 at 15:37 g@luxsci.net > > wrote: > > > On April 8, 2018 02:40:46 pm PDT, "Guyren Howe" > 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 advantage 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 dB 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 > automated manner. The fact that putting logic in the database can > effectively allow developers to make unreleased changes to production apps > is specifically one of the problems that I am trying to avoid when I keep > most logic in the app instead of the dB. It’s a whole lot harder to make > arbitrary 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 with 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 performance 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’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 really difficult. > Such code usually has less complete unit and integration tests, which > makes validating those changes more difficult, too. > But the biggest reason is that databases often have to scale up rather > than out, so keeping as much logic in the application code allows 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 > without hard evidence from production examples to suggest otherwise. > Finally, there’s the consistency argument. I want to 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 back again can make it > a whole lot harder to see, at a glance, what is happening in the code. > Having logic in the dB also means it can be difficult or impossible to > have two releases talking to the same schema version at the same time - so > canary builds and rolling deployments can be difficult. Of course, 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 likely it > is that your releases will conflict over the db. So I’m more likely to be > able to do a rolling release if I keep the db as a dumb data store and > keep logic in the application code. > > ======= > > I could have worded that better but I think that we're coming at it from > different directions. You think of your application as the "master" > 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 the database to be > useful and coherent.  I like to think of external applications as > subservient to the db and not the other way around. Yeah, I know, probably > not a popular viewpoint. > > Sorry, I don't really understand why it would be so hard to migrate, say > pl/pgsql functions. You can maybe expect to write some likely convoluted > application code, though. :) Reusable functions in the db that are solid > also means that developers don't have to reinvent the wheel in whatever > language and debugging also becomes simpler. And it's not like the developers don't rewrite the code every time they migrate to the Latest and Greatest Language... -- Angular momentum makes the world go 'round. --------------B076F16C384412B97CD0C0E1 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 04/27/2018 05:52 PM, g@luxsci.net wrote:
 
 
On April 24, 2018 07:27:59 am PDT, "Sam Gendler" <sgendler@ideasculptor.com> wrote:
 
 
On Sun, Apr 8, 2018 at 15:37 g@luxsci.net <g@luxsci.net> wrote:
 
 
On April 8, 2018 02:40:46 pm PDT, "Guyren 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 advantage 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 dB 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 automated manner. The fact that putting logic in the database can effectively allow developers to make unreleased changes to production apps is specifically one of the problems that I am trying to avoid when I keep most logic in the app instead of the dB. It’s a whole lot harder to make arbitrary 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 with 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 performance 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’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 really difficult. Such code usually has less complete unit and integration tests, which makes validating those changes more difficult, too.
 
But the biggest reason is that databases often have to scale up rather than out, so keeping as much logic in the application code allows 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 without hard evidence from production examples to suggest otherwise.
 
Finally, there’s the consistency argument. I want to 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 back again can make it a whole lot harder to see, at a glance, what is happening in the code. Having logic in the dB also means it can be difficult or impossible to have two releases talking to the same schema version at the same time - so canary builds and rolling deployments can be difficult. Of course, 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 likely it is that your releases will conflict over the db. So I’m more likely to be able to do a rolling release if I keep the db as a dumb data store and keep logic in the application code.

=======

I could have worded that better but I think that we're coming at it from different directions. You think of your application as the "master" 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 the database to be useful and coherent.  I like to think of external applications as subservient to the db and not the other way around. Yeah, I know, probably not a popular viewpoint.

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

And it's not like the developers don't rewrite the code every time they migrate to the Latest and Greatest Language...


--
Angular momentum makes the world go 'round.
--------------B076F16C384412B97CD0C0E1--