Thread

1. Re: Rationale for aversion to the central database?

   g@luxsci.net <g@luxsci.net> — 2018-04-08T22:37:00Z

    
    
   On April 8, 2018 02:40:46 pm PDT, "Guyren Howe" <guyren@gmail.com> wrote:
    
   I am a Rails developer at a medium-large size company. I’ve mostly 
   worked at smaller companies. I’ve some exposure to other web 
   development communities.
   
   When it comes to databases, I have universally encountered the attitude 
   that one should treat the database as a dumb data bucket. There is a 
   *very* strong aversion to putting much of any business logic in the 
   database. I encounter substantial aversion to have multiple 
   applications access one database, or even the reverse: all abstraction 
   should be at the application layer.
   
   My best theory is that these communities developed at a time when 
   Windows was more dominant, and just generally it was *significantly* 
   easier to use MySQL than Postgres for many, particularly new, 
   developers. And it is pretty reasonable to adopt an aversion to 
   sophisticated use of the database in that case.
   
   This attitude has just continued to today, even as many of them have 
   switched to Postgres.
   
   This is only a hypothesis. I am now officially researching the issue. I 
   would be grateful for any wisdom from this community.
   
   Aside: it is rare to find a situation in life or anywhere where one 
   widely adopted thing is worse in *every way* than another thing, but 
   this certainly was and largely still continues to be the case when one 
   compares MySQL and Postgres. So why do folks continue to use MySQL? I 
   find this mystifying.
   
   ===============
   
   Hi there. This issue is close to my heart and I'm with you.  I am 
   however very comfortable with using psql and PL/pgSQL and I am very 
   opinionated.
   I feel *very* strongly that a database that actually matters and where 
   RI is critical, i.e., any PG db I handle, should make sense on its own 
   and be *highly* usable on its own. It should not be dependent on some 
   particular external application code to use it or make sense of things. 
   It follows that I think nonintuituve exceptions/gotchas should be 
   *clear* at a db level, likely using functions to encapsulate that 
   information.
   
   Sure, PL/pgSQL may possibly be slow at some things like doing lots of 
   bigint math, but I would probably use C and ECPG for the appropriate 
   cases.
   Not a large percentage of programmers these days know how fast db tasks 
   can be because they are used to working with relatively slow tools and 
   frameworks. ( Yes, typical Python.)
   
   I am also highly mystified by the dumbstore approach and frankly, I 
   think that folks should KNOW their tools better. Not knowing how to use 
   your database effectively typically results in unnecessary and often 
   very convoluted application code, from my experience.
   
   I keep hearing about db portability but I have yet to see cases where 
   db logic was an issue. But to be honest, I haven't seen many migrations 
   at all. Why? Because I think that it rarely ever happens. If I had to 
   do it, I sure as heck hope that the  db was "clean" and understandable 
   without
   having to review some probably awful app. code. Why would anyone 
   migrate *away* from PG anyway? :)
   
   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.
   
   Cheers,
   -g
   
   

2. Re: Rationale for aversion to the central database?

   Ravi Krishna <sravikrishna3@gmail.com> — 2018-04-08T22:52:03Z

   > > I am however very comfortable with using psql and PL/pgSQL and I am 
   >very opinionated.
   >
   Nothing wrong with this approach and it may very well work 90% of the 
   time.   Until ... a day comes when
   you need to migrate out of PG to another RDBMS.  Good luck at that time.
   
   
   
   

3. Re: Rationale for aversion to the central database?

   Stephen Frost <sfrost@snowman.net> — 2018-04-08T23:43:46Z

   Greetings,
   
   * Ravi Krishna (sravikrishna3@gmail.com) wrote:
   > >> I am however very comfortable with using psql and PL/pgSQL and I am very
   > >opinionated.
   > >
   > Nothing wrong with this approach and it may very well work 90% of the time.
   > Until ... a day comes when
   > you need to migrate out of PG to another RDBMS.  Good luck at that time.
   
   Oh, don't worry, people do that.  What I particularly enjoy are the
   stories (of which there are a number now...) where people moved away
   from PG for some reason or another, then to another database, to
   another, and another, and finally back to PG again, much the wiser for
   it but also rather battle-worn. :)
   
   Thanks!
   
   Stephen
   

4. Re: Rationale for aversion to the central database?

   Sven R. Kunze <srkunze@mail.de> — 2018-04-23T17:22:26Z

   So far, I have nothing to add, but just one thing. See below:
   
   
   On 09.04.2018 00:37, g@luxsci.net 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.
   
   This is actually not the case. You want to have those logic tested as 
   thoroughly as possible being so close to your precious data.
   
   So, you write migration code that substitutes the old logic, test the 
   whole package, if successful, deploy (and thus run the migration).
   
   Cheers,
   Sven
   
   
   

5. Re: Rationale for aversion to the central database?

   Tony Shelver <tshelver@gmail.com> — 2018-04-24T11:36:00Z

   I have done some big contracts for large financial companies, and for most
   of them, ANY changes to the DB structure required extensive 3rd party
   testing and a change control process that sometimes took weeks.
   
   But we did get a waiver for the use of DB 'code' like stored procedures and
   views, which only had to follow the standard development test / acceptance
   procedure by separate developer, end user and third party test teams.
   
   For me, the database is more immutable than the application logic and
   especially the GUI, so it pays to spend a lot of time up front on DB
   design.  Past experience has also lead me to expect that the DBMS will have
   a much longer shelf life than the application language / toolsets used
   against it, or at least, over time the languages / toolsets tend to
   multiply.
   
   For my part, I like to spend a lot of tie in getting an optimal DB design,
   and also putting a lot of validation logic into the DB.
   
   I also like making expensive use of stored procedures, my experience is
   that for a data-intensive multi-tool application they are faster and more
   secure...
   
   
   
   On 23 April 2018 at 19:22, Sven R. Kunze <srkunze@mail.de> wrote:
   
   > So far, I have nothing to add, but just one thing. See below:
   >
   >
   > On 09.04.2018 00:37, g@luxsci.net 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.
   >>
   >
   > This is actually not the case. You want to have those logic tested as
   > thoroughly as possible being so close to your precious data.
   >
   > So, you write migration code that substitutes the old logic, test the
   > whole package, if successful, deploy (and thus run the migration).
   >
   > Cheers,
   > Sven
   >
   >
   

6. Re: Rationale for aversion to the central database?

   Samuel Gendler <sgendler@ideasculptor.com> — 2018-04-24T14:27:04Z

   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.