Re: Rationale for aversion to the central database? - Mailing list pgsql-general

On Sun, Apr 8, 2018 at 15:37 g@luxsci.net <g@luxsci.net> wrote:

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.