Not necessarily. You can make it so that cross replicated databases can function independently and then sync the data together. This of course assumes that you don't have something like an ordering system and you absolutely need to make sure that you don't sell more than your inventory.
That makes sense, yeah. I admittedly haven’t studied the pieces of the stack, but maybe something like PostgREST allows routing certain requests to master vs any replica, where you could make the atomic transactions needed for inventory based updates.
This is all completely outside the realm of things I’ll ever get into haha. IMO so what if the current app/db server boundary is “slow” or “adds too many moving parts” I already spent my entire career learning it, there is value in that to me.
Oracle really pushed this idea too, because they knew that it would mean more load in the database so you’d need a bigger box with with more cpus. Oracle charged per tiered cpu so their incentives were not aligned with the dev’s. IMHO this is pretty damning reason to generally avoid using the db for this reason. It may be true that a db has lots of spare cycles to utilize but so do your hot spare servers, and I’d hope that you’re not trying to wring performance out of those without thinking long and hard as to what hot spare means.
Source: migrated code from oracle to mysql to avoid insane licensing fees once upon a time. Luckily we only had limited stored procedures at the time, most of which I could ignore.
Really anything can be a good or a bad idea depending on the problem domain and how it's implemented. He doesn't really go into how he made it work too closely, so it's easy to say it was a success. The real interesting thing to understand would be, "Why was it a success versus other approaches?" What really made it a better choice vs other approaches? What was the team make up? He says that it made the code easy to understand. To whom? Interesting the team made it work, but not enough to say, "Sure let's try it without a lot more forethought."
Exactly! If you're writing a C compiler, it would probably be a bad idea to have that logic in a database, though it could be fun to try. On the other hand if your application is summarizing a lot data by different dimensions, it would probably be a bad idea to transfer all that data over the network only to summarize it in an application layer. Most applications have a little bit of both though and so will need both an application layer and database layer.
Nice work; PostgREST seems neat. However, the aim of this stack is simplicity, but it is incompatible with serverless products, which I now vastly prefer for ease of use and lower effort- traits I would consider part of "simplicity".
But I'm sure there is a valid argument contrasting simplicity with a managed nature of a tech stack, not to mention a cost comparison.
> However, the aim of this stack is simplicity, but it is incompatible with serverless products, which I now vastly prefer for ease of use and lower effort- traits
The issue with this statement is that you're comparing things that works on different layers. PostgREST is for someone to host the database without any backend, serverless as you describe it is just a SaaS service you use. If there was a SaaS that exposed PostgREST as a service, it'll be a similar experience.
So unless you're comparing running a serverless platform yourself with running PostgREST yourself (which would be pretty obvious which one is the easiest), I feel like you missed with your argument here.
I’m talking about the stack as a whole, which happens to be built on PostgREST.
The OP’s goal was to simplify their stack, which they did do. However, they cannot run this stack without managing a database, as the database and REST API are run in the same container. My point is that another way to lower a project’s required effort is to offload database management.
Sure, OP now only has two layers in the stack instead of three. But now they are locked into managing the database entirely, including updates, backups, and scaling.
Okay, I think this idea has legs. There's a proven market for Serverless offerings, so how would people react to a SaaS product that:
a) Did database management for you, a la Amazon RDS (perhaps later supporting BYO Cloud Database)
b) Provided a software programming layer that simplified coding, versioning, deployment and rollbacks of Stored Procedures
c) Perhaps provided a language environment that lets you program with an SDK in your language of choice, and then "lifted" that into stored procedures
My first thought when faced with this is, how do I do application monitoring, logging, exception handling, paging? Perhaps this is a solved problem, but I'm curious.
Using a database as a backend comes up time and again and developers are sharpely divided over this concept. I feel that developers who started their careers before the advent of NoSQL DBs/MEAN stack/etc had to go through the rigor of fine tuning their SQL queries and witness the powers (and quirks) of a performant SQL DB. These devs are usually a fan of using DB-as-a-backend. Personally, I am a big fan of PostgREST and I apply it whenever I find a use case that suits it. I find it quite pragmatic and it just shines when it plays to it's strength. Pragmatic developers should took a pragmatic look at such approaches and try to use the powerhouse that are SQL DB engines.
Shameless plug: I've written an article to showcase it's philosophy and how easy it is to get started with PostgREST.
This is a wonderfully in depth tutorial, thank you for taking the time to write this up! I’ve debated using PostgREST for projects before and I think this will push me to use it on my next one.
The primary issue I have seen with stored procedures is how you update them. I would be curious how they manage that.
Generally when releasing new code you want to do a gradual release so that a bad release is mitigated. It would be possible by creating multiple functions during the migration and somehow dispatching between them in PostgREST but I would be interested to see what they do.
The other obvious concern is scaling which was only briefly mentioned. In general the database is the hardest component of a stack to scale, and if you start doing it do more of the computation you are just adding more load. Not to mention that you may have trouble scaling CPU+RAM+Disk separately with them all being on a single machine.
> Generally when releasing new code you want to do a gradual release so that a bad release is mitigated
Make each stored procedure not depend on any other procedure. If you change one procedure, make sure that every existing procedure can work with both the current and the future version. Once compatible, start upgrading procedures one by one. Add in monitoring and automatic checking of the values during deployment, and you have yourself a system that can safely roll out changes slowly.
I'm not advising anyone to do this, it sounds horribly inefficient to me, but it's probably possible with the right tooling.
Many scaling problems in databases are because the computation is not done in the database. Outside of exotic cases, good database engines are rarely computation-bound in a competently designed system. By pushing computation outside of the database, it tends to increase the consumption of database resources that actually are bottlenecks, typically effective bandwidth (both memory and I/O).
As a heuristic, moving computation to the data is almost always much more scalable and performant than moving the data to the computation. The root cause of most scalability issues is excessive and unnecessary data motion.
That's not been my experience at all. Quite the opposite.
I actually can't remember a single time in 15 years where I've ever seen that. Maybe it's happened, but I don't remember, but I can easily recount tons of poorly performing SQL queries though. Sub-selects, too many joins, missing indexes, dead-locks, missing foreign keys...
I'm not saying it wouldn't cause a problem, I'm saying I've never, ever seen production code where someone dumped tons of data out and then processed it.
I have seen people try and make extremely complex ORM calls that were fixed by hand-coded SQL, but that's a different problem.
That's only works for sql optimized case / queries, aggregation with indexed keys is one of them. However there are cases where application level is more optimized, such as cases where more complex data type is required, or array processing. Furthermore sql logic cannot be easily encapsulated and reuseable, which leaves you with many duplication without proper planning.
I think read replicas scale easily, so if it’s views and such then there is no problem. If you have to compute stuff during writes, then it’s hard, but probably solved by normalizing the data.
> The primary issue I have seen with stored procedures is how you update them. I would be curious how they manage that.
1. You put your stored procedures in git.
2. You write tests for your stored procedures and have them run as part of your CI.
3. You put your stored procedures in separate schema(s) and deploy them by dropping and recreating the schema(s). You never log into the server and change things by hand.
Wouldn't that lose all the cached query plans every deploy? Could be wrong, bit rusty on that stuff now, been a while since I worked on something where we had to worry about that.
rebuilding query plan takes less than 50ms and is routinely done by the engine itself without you ever realizing it.
what you probably wanted to mention is table statistics - but they are cleared only if you truncate your table, but then again - once you populate your table - the engine will recalc statistics by itself.
overall RDBMS does a lot of stuff behind the scenes for you, and you should take advantage of it, and instead think about more important atuff - business logic, data modeling, schema evolution, etc
The one problem this technique has is that data and software normally have two very different speed and correctness requirements. That means that data and software should evolve following different procedures, what heavily implies that you want different people working at them, or at least you want people to look at them at different times.
For that, you really want independent access controls and CI tooling.
Of course, you can't separate much if you are a 1 or 2 people team at early stages of some project. And it may help you move faster.
But:
> "Rebuilding the database is as simple as executing one line bash loop (I do it every few minutes)"
This denounces a very development-centric worldview where operations and maintenance don't even appear. You can never rebuild a database, and starting with the delusion that you will harm you on the future.
> You can never rebuild a database, and starting with the delusion that you will harm you on the future.
That seems like a silly assumption. Who's to say you can't use databases that you can rebuild from scratch whenever you want? What about append-only logs built with CRDTs and the other ways?
"rebuilt from scratch" means "as if the production server just fell into a woodchipper and I'm taking a new one out of the crate now"
You cannot do that with databases because the data needs to come from somewhere.
I'm sure you could implement some kind of echo server in SQL somehow and yeah that is a "database" that could be rebuilt from scratch with no loss but that's obviously not the kind of thing we are talking about here.
For this reason you should always separate data and code and put them into separate schemas. In such a setup, deploying new code essentially means dropping and re-creating the code schema in a single transaction.
I work with one codebase similar to that (DBMS only), and that's exactly what I do there (it's not that clear-cut, some code validates data, some data is short-lived). There is more than one "code" schema, with different lifecycles too.
There are plenty of interesting problems with it. I would prefer to completely separate code from data if it wouldn't impact elsewhere. As it is, separating them would severely harm us, so it's kept this way. It brings a lot of productivity, but we have a very small team working on it, and completely separated procedures for them.
In my experience I actually got A LOT more from this approach using postgraphile/Hasura in front of my DB and later moved to dosco/super-graph in order to add custom functionality that was kind of a pain to do on the DB.
I really liked the mixed approach of have the DB do everything that it can and have a light backend that can handle http requests, api callbacks, image manipulation and whatever else.
Out of interest (as the PostGraphile maintainer) did you look into https://www.graphile.org/postgraphile/make-extend-schema-plu... for extending the PostGraphile schema to do whatever you need, or were you specifically looking to implement the extensions in Go?
Er ... SQL Server Management Studio is based on Visual Studio shell, and it does a fine job debugging stored procedures. I also code in C# and agree it also works well for that.
IME I've never needed one. You get a copy of prod and test against that. SPs are typically short, even when they're long their functionality tends to be well defined, it's never been a problem for me.
You certainly can and do add logging, this can be useful.
I don't know what industry you are in or what kind of data your users entrust in you, but "just get a copy of prod" is not an idea that would EVER fly in any organisation I have worked in. Developers do not get access to production customers' financial records, medical data or PII, ever.
Hands up, it's a fair point. I've worked with data sets that are mainly large aggregates of public data so security isn't an issue. It's the code that's carefully protected.
One exception is having access to that data because I was working ON the prod server. Read that again. It was deeply uncomfortable to work like this. Very worrying every day.
In those cases you have a set of test data that you use for development and testing. It's not like you can attach a Visual Studio debugger to production either.
You can setup a view (accessed by postgrest) that returns an error when there are no (correct) filters in the query so that takes care of select *... problem (also you can have the same logic in the proxy (nginx) layer
I'm currently part of a team trying to UNDO this very concept. It sounded great in the beginning, but after years of headaches and wasted cash; we're building the backend in something else. I wasn't part of the original team. Nor was I part of the decision to migrate off of the system. I just know that for my employer, it was a bad decision those many years ago...
We're staying away from stored procs as far as possible. For us the main driver is that it would make it harder to run older versions of our program alongside newer versions.
We rely on this to be able to bring updates quickly to our customers when their needs change.
We also found the tooling lacking for our database server.
On the other hand, we don't use ORMs, instead mostly relying on handwritten select queries, with "dumb" insert/updates being handled by library and others by hand. So we normally don't pull more data over the wire than we need.
This takes a balance to get right. For some data yes drag it over and keep it on the 'client'. But for other data just join it out and let the SQL handle it. However, for some reason SQL seems to scare a lot of people. So they end up making lots of microservices/functions/methods that just drag the data together and mash it together in a language they understand. That comes at a cost of network bandwidth.
I found that if you are 'happy' with the style of lots of stored procs and data lifting on the server side you usually have a decent source delivery system in place. If you do not have that you will fight every step of the way changing the schema and procs as this monolithic glob that no one dares to touch.
Everything old is new again. This was a common way to write client-server software in the pre-web world, clients talking directly to a central rdbms.
I guess the one true thing in software dev is the cycle/pendulum keeps rotating/swinging. Often without people realizing it's swinging back instead of brand new!
I almost can't believe we are talking about this now. I mean that in the sense of, I have built applications every possible way - when you work in a big company and inherit one of everything, you have to do that often.
In my past, I built one application that was fully database driven, and at the time (2004-2005) it would have been difficult to pull off without stored procedures and table driven logic - fully maximizing the power of SQL - especially in the timeframe I had to do it (<3 months). I mean, I pushed the technology HARD (expert system for fraud detection that worked hand-in-hand with basic machine learning).
I will never forget how I was derided by people for that choice - even though, that system is still running today and working well, in the bowels of an acquirer. I mean, literally, I was derided to the point of getting imposter syndrome for feeling that I made a choice that others regarded as so limiting.
The truth is, I learned everything else about distributed applications and databases because of being derided in that way. Ultimately, I now know how to architect things many different ways and can choose when I feel it is appropriate. I also know not to let the negativity of others prevent success.
I am not sure the moral of this story. If you can build a system, and it serves its purpose well and for a long time, and it works and provides the needed value.. it really may not matter. But, you can choose sometimes, and if there is one truth it must be that there is not always only one way to do something. Try to pick the right tool for the job as best as you are able.
Don't think you're dumb just because other people don't like your idea. Keep your mind open, and be willing to learn, but if you can make it work, and prove it works, you are just as right as anyone else.
What I've found being the problem with this approach is that sql does not lend itself very well to composability. Sure, you can make functions, views and stored procedures and compose them. But when you start actually composing queries of these parts the could lead to different execution plans having wildly different performance characteristics.
Also tooling around SQL, i.e. refactoring tools and debuggers, is not great - if even available at all.
sql is not a code, like c#, you dont debug it, you inspect results, or inspect execution plan/traces.
how would you debug HTML, for example? Open it in browser, right?
same for sql: run it and see if it works as you wanted
same goes for composability: you can compose SQL same way you can compose HTML, but you still need to understand the big picture
explicit control flow in SQL (cursors, for loops and stuff) is absolutely an antipattern. You need to think in terms of relational algebra and functional programming in order to write clean SQL
Back in the mid-90s, this was how many of the high-end web apps were built, using Oracle. Despite its limitations, and Oracle license fees (but back then you were paying for Sun servers anyway), it worked surprisingly well and was a reasonable model for many database-driven web applications. Some of these systems were used for mission-critical global operations handling enormous amounts of money and complex business. It had a lot of advantages versus other web app stacks at the time (commonly a mountain of Perl).
Oracle was happy to sell database licenses but made zero effort to develop this way of using the database as a first-class capability. They were astonished that it even worked as well as it did, they did not expect people to take it that far.
For some types of applications, something along these lines is likely still a very ergonomic way of writing web apps, if the tooling was polished for this use case.
Not surprising. Databases are built to be really efficient, and with enough hardware, they can forgive a lot. Mediocre developers can produce a lot of "working" code if they stand on the shoulders of giants. (Not saying you or your team was mediocre, I'm just saying that I've seen really bad code run a lot of business processes. At the end of the day, it worked and the company made money.)
Just like I keep seeing plenty of mediocre teams doing for loops over GB of downloaded data and pegging Webserver CPUs for stuff that should have stayed on the DB.
Usually that is one of the things I end up fixing when asked for performance tips, other is getting rid of ORMs and learn SQL.
Oh don't get me wrong, there is a time and a place for everything. There are many things that should be in the database, and there are a lot of people that avoid it because they don't want to learn SQL. You can have really good reasons and elegant code in SQL. I'm merely saying when someone immediately says, "that's dumb, you shouldn't do that," they aren't looking at the whole picture possibly and it was the choice that got the programming running which is important.
I did a take-home exercise earlier this week. They provided access to a database and said << do thing >>. Implied was that I should << do thing >> with a program rather than a query.
One of my solutions used Golang. It pulled data, looped a lot, then put results back. It took approximately 4 minutes.
I included a second solution, using SQL. It selected into the results table with a little bit of CTE magic. It took approximately 600ms.
Interesting, and I've seen and implemented similar optimisations plenty of times. The right query in the right place at the right time can make all the difference.
Did you hear back from them with a positive response?
It should be noted too that the 90s was really a different world back then. Processing capabilities, and development tools were vastly different.
There was just so much stuff that you had to manually build you don't have to now. (There are other complexities that we get to worry about now.) One thing I noticed back then is that putting stuff in the database just made things easier. In some instances, it really did act as like an amplifier for getting work done. Need to sort data? The database has things in place to do that and really efficiently too. I remember working with people, and if they had to make things like a linked list or a binary tree, they were lost (Yes, these were CS people too. We can argue about their education, but yes, they did graduate with a CS degree). There was nothing really like Stack Overflow to ask for help. You were really on your own for a large portion of it. Database code really took care of a lot of that for you. (Truthfully, the companies I worked for used MSSQL Server back then, so your results might have been different.)
Another unusual usage for SQL databases (today!) is as a data type server. SQL databases have a very rich set of data types with rigorous specification and myriad operators, conversions, etc for all the types, more so than just about any other programming environment. If you need correct support for a complex data type operation in a development environment with weak or non-existent type support, you can use a SQL runtime to do that computation for you over a local client connection.
I've used PostgreSQL this way a few times to great effect. The connection is over local IPC and never touches storage, so the operation throughput is quite high.
Yeah nobody in the 1990s with a real CS degree didn't know what a linked list or binary tree was. They just didn't want to bother coding it and testing it, when the database was there and being used anyway.
I have worked on an application that heavily used sprocs for business logic. I would not wish it on anyone. It will seem fine (and actually it will seem better - faster and easier to manage) while your project is small.
In a few years when you need to make some major changes you will see why it was a bad idea, and it will be too late. Have fun modifying hundreds or thousands of sprocs when you need to make a large-scale change to the structure of your data, because SQL won't compose. Have fun modifying dozens of sprocs for each change in business logic, because SQL won't compose. I guarantee you will have mountains of duplicated code because SQL won't compose.
I agree. To me this sounds more like bad programming (high coupling and low cohesion) and like it would be a problem even if a separate application acted as the backend.
I believe this in the abstract, but would be very interested in some concrete explanation for why sprocs compose worse than other programming languages. How would the OP's situation have improved if instead of sprocs he had a similarly complex webapp and had to deal with the schema changes there? I'm not challenging the OP; I don't have enough experience with sprocs to have an informed opinion, but this information is necessary to make sense of the OP's criticism.
The best argument against sprocs that I've heard is that you really don't want any code running on your database hosts that you don't absolutely need because it steals CPU cycles from those hosts and they don't scale horizontally as well as stateless web servers. This is a completely different argument than the OP's, however.
Databases are engineered for high reliability - data integrity above all else. That means they develop more slowly and are perpetually behind what you expect from a modern programming language.
If someone created a database with the intention of it being a good development framework, it would probably be more pleasurable to code against, but would you trust it with your data?
> Databases are engineered for high reliability - data integrity above all else.
So should be most of your (micro)services. I have seen more instances of sloppy system design (non-transactional but sold as such) than coherent eventually consistent ones.
Surely that's situation dependent? Databases are built to handle anything up to and including financial and health data.
On the other hand, does it matter if my social network updoot microservice loses a few transactions? With code running outside of the database, you get to decide how careful you want to be.
Yes it does. Are you mentioning in the internal services documentation that they can lose data second unknown conditions? And on your product hunt page that "your content might be randomly lost"? If you do not then you are lying. Everybody using the service expects transactional consistency.
Note that what you described is not eventual consistency but rather "certain non-determinism", there is an abyss of difference.
I don't mean specifically with transactional consistency (I don't think I even mentioned eventual consistency), but just generally the level of engineering required to write a system as well tested as postgres inevitably will slow down its feature development. This means databases don't have the latest features typically enjoyed within application development environments.
However I believe you CAN tolerate some level of failure and defects in your app code, knowing the more battle hardened database will - for the most part - ensure your data is safe once committed. Yes, there will always probably be bugs and yes some of those bugs may cause data loss in extreme cases, but if you're saying you perform the same level of testing and validation on a product hunt style app as you would on a safety critical system, or as postgres do on their database, I find that extraordinary and very unrepresentative of most application development.
I'm not saying defects are good or tolerated when found, but from an economic perspective you have to weigh up the additional cost of testing and verification against the likely impact these unknown bugs could have. Obviously everyone expects any given service to work correctly - but when is that ever true outside of medical, automotive and aerospace which have notoriously slow development cycles?
Personally I'd pick rapid development over complete reliability in most cases.
Relative to most RDBMS it evolves quickly, but from a language perspective it's barely different to the SQL I was writing at the start of my career. Meanwhile Go didn't exist then and does now, and has better tooling for any normal development workflow.
My impression of Postgres development is markedly different. It seems like each release since 8 or 9 has brought significant, exciting new features. I don’t pay close attention, but IIRC, a lot of investment has gone into JSON, window functions, and lots of miscellaneous but important things (e.g., upsert). Meanwhile Go’s language changes have all been minor and boring (not a bad thing!).
Procedures don't have arrays/structs/classes/objects. This means they can't share data unless through parameters.
Polymorphism is possible, but not really encapsulation. One could argue that arrays/objects would cause consistency problems, and therefore don't belong in a data language.
Someone expecting python-like OO programming will go mad.
The performance argument depends on the whether the database is limited by CPU or bandwidth and what the load looks like. It's shown in benchmarks that the round-trip between db/network/orm/app takes many orders of magnitude longer than the procedures themselves.
Becuase of lack of tooling there is tons of duplicate logic in SQL based systems, I see the same logic repeated across queries which already exists in some view but no knows about it so it ends up being repeated.
Lack of abstractions like modules and namespaces for logically grouping stored procedures makes it hard to understand where is what
Thanks that is very interesting, I would imagine having all the data in one schema, and then few other schemas containing views and functions grouped together logically.
Say for example you need two queries, one for fetching a paged list of customers ordered by birth date, and one for fetching a paged list of customers who are male.
You will find you'll need two queries with a huge amount of duplicate logic - the SELECT clauses, even if they're pulling the same columns, will need to be duplicated (don't forget to keep them in sync when the table changes!), because there's no reasonable way to compose the query from a common SELECT clause. The paging logic will be duplicated for the same reason. So although the queries are similar, only sorted and filtered differently, you will need to duplicate logic if you want to avoid pulling the entire data set first and performing filtering and sorting in separate modules.
These problems become significantly worse when you're talking about inserting, updating or upserting data that involves validation and business rules. These rules are not only more difficult to implement in SQL, but they change more often than the structure of data (in most cases) so the duplication becomes a huge issue.
I haven't tried this on a system that uses Postgres functions so I could be way off base here, my experience was pure sprocs in MS SQL.
MSSQL can certainly do all of that. panarky offered one way of doing it, but you can also write a single SQL Function that takes a parameter for filtering by gender (or not), and encapsulates the pagination logic. You will then be able to select form that function passing different parameters for filtering by gender and only adding a sort at the end.
Likewise the business rule validation could be encapsulated in triggers or in a stored procedure that would validate all changes, much like your C# code would.
I'm trying to figure out what kind of code duplication that you'd be dealing with that you can get around by avoid stored procedures. Unless you're relying on an ORM to construct your queries for you, you're going to have SQL queries somewhere in your code. It's either going to be stored in application code or in the database but either way you're going to have the same problem.
21 years ago, I joined a young team that wrote an ERP, with all business logic in PL/SQL. Customers are still amazed how fast these guys are going. 10 years ago I joined Oracle to work on APEX (created in 1999), which is a web app development and runtime environment, written in PL/SQL. We estimate that there are about 260K developers world-wide who are developing apps with APEX. All of their business logic is also on the server. Call me biased because I am working for the man, but my data points are completely different.
I agree, Apex is awesome. Oracle Apex being written in stored procedures was actually a big inspiration for a low code tool project I did myself, which while not being written in stored procedures took alot of other ideas from Apex (I was an Oracle Apex developer for 4 years)
I worked with two other ERP class systems that did this, one using Oracle (with an Oracle Forms/Reports front-end), the other PostgreSQL.
I can confirm your experience. There's no doubt that stored procedure/function based business logic requires a certain discipline, knowledge set, and the ability to get a team in marching in the same direction. But if you can achieve the organizational discipline to make it work there are definite advantages, especially in the ERP space.
And that may well be differentiator... those of us working with more traditional business systems have trade-offs that you won't find in a startup. For example, a COTS ERP system is more likely to not really be a single "application", but a bunch of applications sharing data. This means a lot of application servers, integrations, etc., not all from the people that made the ERP, needing access to the ERP data. The easiest integration is often at the database, especially since you can have technology from different decades (and made according to the fashions of their time) needing to access that one common denominator. Since many ERP systems are built on traditional RDBMSs, and talking to those doesn't change much over long periods of time... having the logic be there to make these best of breed systems work sensibly with the ERP can be very helpful. All that said, in this context I'm an advocate of the approach.
Now, take a team that maybe has a high churn, sees the database as some dark and frighting mystical power that can only safely be approached with an ORM, or a team that thinks their "cowboy coding" is their core strength (if perhaps not that thought directly) and database based business logic certainly has many foot-guns. But, there are really few technologies that don't suffer without a good well-rounded, consistent approach.
It's posts like this that make HN great (again). Thank you.
I want to add that your talent pool will also influence where to create a hardened boundary: inside the database server (if you have lots of SQL people), or inside the application server (if you have lots of C#/Java/Python people).
Back in 2009 I helped to put together a small app in APEX, which then was quite new, in the two days, or day and a half, before the lead developer left for an overseas vacation. I had never before used APEX, she had been introduced to it an an Oracle tech day, but that was the extent of either's experience with it. The application is long gone, but got a lot of use over several months.
PL/SQL is an excellent tool for implementing business logic, and I really like APEX largely because I can call PL/SQL at any of several points.
Oracle used to push the idea that there is no need for an application layer, just use the database. Then they stopped being silly. I used APEX as a user, while at Oracle. It was a decent reporting tool but was only really used because the CRM had terrible latency.
If you write your views, tvf and stored procedures to use common elements defined once and once only (this starts with once and once only tables), maintenance is easy peasy. If you let every developer create their own view for the same business logic, then you are creating an unmanageable creature.
This also applies to "composable" code written in the application layer outside the database.
The issue is when central ideas about how your application work changes. Suppose you’re a US bank and after a few decades you want to open a branch in Europe and suddenly every dollar value needs to be associated with a specific currency.
At it’s core that’s a very basic change, but the issue with this stuff is how your adapting to such changes without introducing massive technical debt. Databases that elegantly to your data are easy to work with. However, it’s always tempting to use the lazy solution, which is why systems relying on stored procedures tends to age poorly. In effect they tend to corrupt the purity of your schema.
I don't see how that has anything to do with stored procedures. For such large changes, your schema and application logic both need to be changed anyway. So if you're already changing the schema, why is it easier to change the application logic if it lives in a separate codebase?
If anything, it seems like it would go the other way around: With stored procedures, you're guaranteed that any application logic is at least consistent with the current database schema, whereas with separate services, you need to maintain that consistency yourself. Not that I'm advocating for stored procedures in general because they come with a mountain of other drawbacks, I just don't buy this particular argument.
> why is it easier to change the application logic if it lives in a separate codebase?
Because stored procedures don’t include the ability to do things like use inheritance to easily add extra layers of abstraction and let the compiler detect when you’re messing up. Adding a column is easy enough, but enforcing that it means something and every one of your prior calculations are now meaningless without taking it into account is difficult. Making such changes elegant and enforcing them to avoid future bugs is even harder.
Remember this example is at it’s core a minor difference. If you’re changing something like amounts being measured by volume instead of weight, then basic assumptions no longer hold making things much worse.
Sounds like the issue is a type system then, no? So stored procedures should be no worse than all of that Python, JS, Ruby, Elixir, Clojure, etc etc etc that’s running web backed a all over, right?
The parent was arguing that stored procedures are worse than conventional programming languages because the compiler won't warn them about any errors. I responded by pointing out that lots of web application backends are written in dynamically typed languages such as Ruby where there is also no compiler to warn of type errors. I'm definitely not arguing that stored procedures should look more like Ruby.
In fact, stored procedures (in relational DBs at least) are statically typed, so they will error if you change the type of a referenced column to something that no longer makes sense. This gives you more safety than a dynamically typed application language can, at least out of the box.
> Adding a column is easy enough, but enforcing that it means something and every one of your prior calculations are now meaningless without taking it into account is difficult.
But again, I don't see how this is any easier to solve in application code than it is at the DB level. I don't know of any programming language or framework that allows you to encode rules such as "this DB column must be used". And even if such a thing existed, it wouldn't prevent you from making mistakes in using that column.
On the other hand, using stored procedures at least prevents errors in the other direction: referencing columns in a way that no longer makes sense (because the type was changed, it no longer exists, etc).
“This DB column must be used” is kind of a complex topic here. OOP for example is about building internal API’s which in theory force users of those API’s to account for these kinds of changes. On the other hand those API’s are generally used by the people creating them so it’s more about assistance than forced compliance.
Stored Procedures have all kinds of other issues, but this is an obvious failure.
I have seen large database heavy apps. They are the cleanest I've ever worked with. I've had no trouble composing SQL. I can't think of one example where this has been an issue. You need to rely on views, functions and tables to store your business rules and look up the rules.
The big down side I see is the learning curve. Writing business logic in the database is not easy and not easy to maintain. It can take half a day to grow a large function. Even longer to debug sometimes. But you learn to structure things well.
Spreading your business logic across all of your code base is a bad idea in any language. Not doing it as a primary design pattern is language agnostic. Your argument has nothing to do with whether the code executes in the database, a JVM, or native to the OS.
I'm very curious about this pattern, but does the tooling exist to make this feasible across a team.
One of the big problems with SQL views is their lack of discoverability.
The lack of tooling also plagues SQL based systems, I would miss goto definition, find all references and sensible autocompletion.
I am curious why no one has built suitable tooling around all this, because it is a great idea for lot's of scenarios
At a previous job I was at 2007-2014, we were doing classified ads[1] we had a lot of logic in stored procedures in our Postgres database written in pl/pgsql. It made sense at the time, but after a while these got really unwieldy and deploying things got tricky, since you we needed to update both appserver code and database stored procs at the same time. There also wasn't a great way to update stored procs at the time - we built this into an RPM that just ran a postinstall script to do an atomic swap.
I wouldn't build this into the database were I to build it today.
At a previous job I was at, we put each release of the sprocs into a different schema. Upgraded app servers called sprocs in schema "r102" while the not-yet-upgraded app servers continued to call sprocs in schema "r101" until they were eventually upgraded.
I'm very interested in this, as I come from the web world yet find myself in a company that has a 30-year legacy of putting business logic into the database. It served them really well, and that they run quite lean in terms of hardware. But they're built on Microsoft tech ($$$), and moving things to a traditional database=datastore cloud architecture would require a substantial change. Does anyone have resources they can point me to on the wisdom or folly of keeping this logic-in-database approach? It seems like the tradeoffs in the different approaches are all about where the smarts are, and whether you can horizontally scale those smarts. Does that sound accurate?
> It seems like the tradeoffs in the different approaches are all about where the smarts are, and whether you can horizontally scale those smarts. Does that sound accurate?
The real question is whether you are going to ship your code to your data or your data to your code.
This won't answer this question in full but perhaps some part of it.
Projects like PostgREST (and PostGraphile) use schema introspection to generate the API. When the schema changes it's automatically reflected in the API. Sure one has to keep the changes synced on the frontend but the premise is you don't want to decouple.
Use as many "base" schemas (namespaces) as needed, and only one `api` schema which has only views & functions you want to expose.
I take this further by having an `api` schema per major version (e.g. `api_v2`). The downside is it becomes impossible to maintain more than 2 major versions at once, so you have to get everyone from v1->v2 before you can make much progress on v3, but the upside is that you should really try to avoid hard breaking changes in the DB very often.
I personally would not make this choice for new projects. I only use technology that is appropriate for the domain I'm using it in. Although, I am hoping this trend encourages better tooling and more modernization of SQL.
As others stated, my concerns are primarily that SQL lacks a lot of general-purpose language features (modules, namespacing, composability, no easy way to debug, etc.) which seem to be ideal for writing applications.
I would be interested to see a project written with PL/Python + PL/SQL stored functions in Postgres. PL/Python functions would be the interface between API calls and the database, and SQL would do what it is currently designed to do: straight-forward data manipulation.
195 comments
[ 3.3 ms ] story [ 306 ms ] threadThis is all completely outside the realm of things I’ll ever get into haha. IMO so what if the current app/db server boundary is “slow” or “adds too many moving parts” I already spent my entire career learning it, there is value in that to me.
Source: migrated code from oracle to mysql to avoid insane licensing fees once upon a time. Luckily we only had limited stored procedures at the time, most of which I could ignore.
But I'm sure there is a valid argument contrasting simplicity with a managed nature of a tech stack, not to mention a cost comparison.
[1]: https://supabase.io/beta
The issue with this statement is that you're comparing things that works on different layers. PostgREST is for someone to host the database without any backend, serverless as you describe it is just a SaaS service you use. If there was a SaaS that exposed PostgREST as a service, it'll be a similar experience.
So unless you're comparing running a serverless platform yourself with running PostgREST yourself (which would be pretty obvious which one is the easiest), I feel like you missed with your argument here.
The OP’s goal was to simplify their stack, which they did do. However, they cannot run this stack without managing a database, as the database and REST API are run in the same container. My point is that another way to lower a project’s required effort is to offload database management.
Sure, OP now only has two layers in the stack instead of three. But now they are locked into managing the database entirely, including updates, backups, and scaling.
a) Did database management for you, a la Amazon RDS (perhaps later supporting BYO Cloud Database) b) Provided a software programming layer that simplified coding, versioning, deployment and rollbacks of Stored Procedures c) Perhaps provided a language environment that lets you program with an SDK in your language of choice, and then "lifted" that into stored procedures
My first thought when faced with this is, how do I do application monitoring, logging, exception handling, paging? Perhaps this is a solved problem, but I'm curious.
Shameless plug: I've written an article to showcase it's philosophy and how easy it is to get started with PostgREST.
[^1]: https://samkhawase.com/blog/postgrest/postgrest_introduction...
Generally when releasing new code you want to do a gradual release so that a bad release is mitigated. It would be possible by creating multiple functions during the migration and somehow dispatching between them in PostgREST but I would be interested to see what they do.
The other obvious concern is scaling which was only briefly mentioned. In general the database is the hardest component of a stack to scale, and if you start doing it do more of the computation you are just adding more load. Not to mention that you may have trouble scaling CPU+RAM+Disk separately with them all being on a single machine.
Make each stored procedure not depend on any other procedure. If you change one procedure, make sure that every existing procedure can work with both the current and the future version. Once compatible, start upgrading procedures one by one. Add in monitoring and automatic checking of the values during deployment, and you have yourself a system that can safely roll out changes slowly.
I'm not advising anyone to do this, it sounds horribly inefficient to me, but it's probably possible with the right tooling.
As a heuristic, moving computation to the data is almost always much more scalable and performant than moving the data to the computation. The root cause of most scalability issues is excessive and unnecessary data motion.
I actually can't remember a single time in 15 years where I've ever seen that. Maybe it's happened, but I don't remember, but I can easily recount tons of poorly performing SQL queries though. Sub-selects, too many joins, missing indexes, dead-locks, missing foreign keys...
I'm not saying it wouldn't cause a problem, I'm saying I've never, ever seen production code where someone dumped tons of data out and then processed it.
I have seen people try and make extremely complex ORM calls that were fixed by hand-coded SQL, but that's a different problem.
We've just seen a lower-level example of this principle in action, with Apple's M1 processor and its Unified Memory Architecture.
1. You put your stored procedures in git.
2. You write tests for your stored procedures and have them run as part of your CI.
3. You put your stored procedures in separate schema(s) and deploy them by dropping and recreating the schema(s). You never log into the server and change things by hand.
Then when new version is being deployed, the tool compares the database with the XML, and generates SQL to alter the database.
For stored procs etc it compares the text of them, so those that weren't changed are ignored.
It's a simple homebrew tool but it gets the job done.
what you probably wanted to mention is table statistics - but they are cleared only if you truncate your table, but then again - once you populate your table - the engine will recalc statistics by itself.
overall RDBMS does a lot of stuff behind the scenes for you, and you should take advantage of it, and instead think about more important atuff - business logic, data modeling, schema evolution, etc
For that, you really want independent access controls and CI tooling.
Of course, you can't separate much if you are a 1 or 2 people team at early stages of some project. And it may help you move faster.
But:
> "Rebuilding the database is as simple as executing one line bash loop (I do it every few minutes)"
This denounces a very development-centric worldview where operations and maintenance don't even appear. You can never rebuild a database, and starting with the delusion that you will harm you on the future.
That seems like a silly assumption. Who's to say you can't use databases that you can rebuild from scratch whenever you want? What about append-only logs built with CRDTs and the other ways?
You cannot do that with databases because the data needs to come from somewhere.
I'm sure you could implement some kind of echo server in SQL somehow and yeah that is a "database" that could be rebuilt from scratch with no loss but that's obviously not the kind of thing we are talking about here.
Safe to assume views go in the “code” schema with procs/funcs/packages, leaving just tables and sequences (if needed) in the “data” schema?
Considering building a side project using this kind of approach...
There are plenty of interesting problems with it. I would prefer to completely separate code from data if it wouldn't impact elsewhere. As it is, separating them would severely harm us, so it's kept this way. It brings a lot of productivity, but we have a very small team working on it, and completely separated procedures for them.
I really liked the mixed approach of have the DB do everything that it can and have a light backend that can handle http requests, api callbacks, image manipulation and whatever else.
Of course, many programming languages also do not have a debugging quality-of-life compared to Visual Studio.
https://dbeaver.com/docs/wiki/PGDebugger/
https://www2.navicat.com/manual/online_manual/en/navicat/mac...
https://www.cybertec-postgresql.com/en/debugging-pl-pgsql-ge...
Other platforms have options as well:
https://docs.microsoft.com/en-us/sql/ssms/scripting/transact...
You certainly can and do add logging, this can be useful.
One exception is having access to that data because I was working ON the prod server. Read that again. It was deeply uncomfortable to work like this. Very worrying every day.
Better I can even optimize to native code, and still debug them.
That's a strange claim, in my work it's always been 100% data ops.
> “Letting people connect directly to the database is a madness” — yes it is.
why?
> Killing database by multiple connections from single network port is simply impossible in real life scenario. You will long run out of free ports
Depends entirely on the workload. And it's very possible. All too entirely so.
2. always hide postgREST behind API gateway/load balancer/waf/ids+ips/rate limiter and you will be more secure from stuff lile this
We rely on this to be able to bring updates quickly to our customers when their needs change.
We also found the tooling lacking for our database server.
On the other hand, we don't use ORMs, instead mostly relying on handwritten select queries, with "dumb" insert/updates being handled by library and others by hand. So we normally don't pull more data over the wire than we need.
Then again, I guess we're a bit old fashioned.
I found that if you are 'happy' with the style of lots of stored procs and data lifting on the server side you usually have a decent source delivery system in place. If you do not have that you will fight every step of the way changing the schema and procs as this monolithic glob that no one dares to touch.
I guess the one true thing in software dev is the cycle/pendulum keeps rotating/swinging. Often without people realizing it's swinging back instead of brand new!
In my past, I built one application that was fully database driven, and at the time (2004-2005) it would have been difficult to pull off without stored procedures and table driven logic - fully maximizing the power of SQL - especially in the timeframe I had to do it (<3 months). I mean, I pushed the technology HARD (expert system for fraud detection that worked hand-in-hand with basic machine learning).
I will never forget how I was derided by people for that choice - even though, that system is still running today and working well, in the bowels of an acquirer. I mean, literally, I was derided to the point of getting imposter syndrome for feeling that I made a choice that others regarded as so limiting.
The truth is, I learned everything else about distributed applications and databases because of being derided in that way. Ultimately, I now know how to architect things many different ways and can choose when I feel it is appropriate. I also know not to let the negativity of others prevent success.
I am not sure the moral of this story. If you can build a system, and it serves its purpose well and for a long time, and it works and provides the needed value.. it really may not matter. But, you can choose sometimes, and if there is one truth it must be that there is not always only one way to do something. Try to pick the right tool for the job as best as you are able.
Don't think you're dumb just because other people don't like your idea. Keep your mind open, and be willing to learn, but if you can make it work, and prove it works, you are just as right as anyone else.
Maybe that's the moral of the story.
Also tooling around SQL, i.e. refactoring tools and debuggers, is not great - if even available at all.
how would you debug HTML, for example? Open it in browser, right? same for sql: run it and see if it works as you wanted
same goes for composability: you can compose SQL same way you can compose HTML, but you still need to understand the big picture
explicit control flow in SQL (cursors, for loops and stuff) is absolutely an antipattern. You need to think in terms of relational algebra and functional programming in order to write clean SQL
Oracle was happy to sell database licenses but made zero effort to develop this way of using the database as a first-class capability. They were astonished that it even worked as well as it did, they did not expect people to take it that far.
For some types of applications, something along these lines is likely still a very ergonomic way of writing web apps, if the tooling was polished for this use case.
Just delivered one last month with plenty of T-SQL code in it.
Usually that is one of the things I end up fixing when asked for performance tips, other is getting rid of ORMs and learn SQL.
One of my solutions used Golang. It pulled data, looped a lot, then put results back. It took approximately 4 minutes.
I included a second solution, using SQL. It selected into the results table with a little bit of CTE magic. It took approximately 600ms.
Did you hear back from them with a positive response?
When you see a bottleneck, you could use 2 systems ( eg. EF by default and Dapper when doing optimizations)
There was just so much stuff that you had to manually build you don't have to now. (There are other complexities that we get to worry about now.) One thing I noticed back then is that putting stuff in the database just made things easier. In some instances, it really did act as like an amplifier for getting work done. Need to sort data? The database has things in place to do that and really efficiently too. I remember working with people, and if they had to make things like a linked list or a binary tree, they were lost (Yes, these were CS people too. We can argue about their education, but yes, they did graduate with a CS degree). There was nothing really like Stack Overflow to ask for help. You were really on your own for a large portion of it. Database code really took care of a lot of that for you. (Truthfully, the companies I worked for used MSSQL Server back then, so your results might have been different.)
I've used PostgreSQL this way a few times to great effect. The connection is over local IPC and never touches storage, so the operation throughput is quite high.
In a few years when you need to make some major changes you will see why it was a bad idea, and it will be too late. Have fun modifying hundreds or thousands of sprocs when you need to make a large-scale change to the structure of your data, because SQL won't compose. Have fun modifying dozens of sprocs for each change in business logic, because SQL won't compose. I guarantee you will have mountains of duplicated code because SQL won't compose.
The best argument against sprocs that I've heard is that you really don't want any code running on your database hosts that you don't absolutely need because it steals CPU cycles from those hosts and they don't scale horizontally as well as stateless web servers. This is a completely different argument than the OP's, however.
If someone created a database with the intention of it being a good development framework, it would probably be more pleasurable to code against, but would you trust it with your data?
So should be most of your (micro)services. I have seen more instances of sloppy system design (non-transactional but sold as such) than coherent eventually consistent ones.
On the other hand, does it matter if my social network updoot microservice loses a few transactions? With code running outside of the database, you get to decide how careful you want to be.
Note that what you described is not eventual consistency but rather "certain non-determinism", there is an abyss of difference.
However I believe you CAN tolerate some level of failure and defects in your app code, knowing the more battle hardened database will - for the most part - ensure your data is safe once committed. Yes, there will always probably be bugs and yes some of those bugs may cause data loss in extreme cases, but if you're saying you perform the same level of testing and validation on a product hunt style app as you would on a safety critical system, or as postgres do on their database, I find that extraordinary and very unrepresentative of most application development.
I'm not saying defects are good or tolerated when found, but from an economic perspective you have to weigh up the additional cost of testing and verification against the likely impact these unknown bugs could have. Obviously everyone expects any given service to work correctly - but when is that ever true outside of medical, automotive and aerospace which have notoriously slow development cycles?
Personally I'd pick rapid development over complete reliability in most cases.
Polymorphism is possible, but not really encapsulation. One could argue that arrays/objects would cause consistency problems, and therefore don't belong in a data language.
Someone expecting python-like OO programming will go mad.
The performance argument depends on the whether the database is limited by CPU or bandwidth and what the load looks like. It's shown in benchmarks that the round-trip between db/network/orm/app takes many orders of magnitude longer than the procedures themselves.
You will find you'll need two queries with a huge amount of duplicate logic - the SELECT clauses, even if they're pulling the same columns, will need to be duplicated (don't forget to keep them in sync when the table changes!), because there's no reasonable way to compose the query from a common SELECT clause. The paging logic will be duplicated for the same reason. So although the queries are similar, only sorted and filtered differently, you will need to duplicate logic if you want to avoid pulling the entire data set first and performing filtering and sorting in separate modules.
These problems become significantly worse when you're talking about inserting, updating or upserting data that involves validation and business rules. These rules are not only more difficult to implement in SQL, but they change more often than the structure of data (in most cases) so the duplication becomes a huge issue.
I haven't tried this on a system that uses Postgres functions so I could be way off base here, my experience was pure sprocs in MS SQL.
Write one view for selecting customers, with all the joins, sub-queries and case-when logic.
Then select from this view order by birth date, and select from the same view by sex.
Yes, the limit/offset paging clause will be duplicated, but all the joins, sub-queries and case-when stuff can be DRY'd in a single view.
Likewise the business rule validation could be encapsulated in triggers or in a stored procedure that would validate all changes, much like your C# code would.
I can confirm your experience. There's no doubt that stored procedure/function based business logic requires a certain discipline, knowledge set, and the ability to get a team in marching in the same direction. But if you can achieve the organizational discipline to make it work there are definite advantages, especially in the ERP space.
And that may well be differentiator... those of us working with more traditional business systems have trade-offs that you won't find in a startup. For example, a COTS ERP system is more likely to not really be a single "application", but a bunch of applications sharing data. This means a lot of application servers, integrations, etc., not all from the people that made the ERP, needing access to the ERP data. The easiest integration is often at the database, especially since you can have technology from different decades (and made according to the fashions of their time) needing to access that one common denominator. Since many ERP systems are built on traditional RDBMSs, and talking to those doesn't change much over long periods of time... having the logic be there to make these best of breed systems work sensibly with the ERP can be very helpful. All that said, in this context I'm an advocate of the approach.
Now, take a team that maybe has a high churn, sees the database as some dark and frighting mystical power that can only safely be approached with an ORM, or a team that thinks their "cowboy coding" is their core strength (if perhaps not that thought directly) and database based business logic certainly has many foot-guns. But, there are really few technologies that don't suffer without a good well-rounded, consistent approach.
I want to add that your talent pool will also influence where to create a hardened boundary: inside the database server (if you have lots of SQL people), or inside the application server (if you have lots of C#/Java/Python people).
Back in 2009 I helped to put together a small app in APEX, which then was quite new, in the two days, or day and a half, before the lead developer left for an overseas vacation. I had never before used APEX, she had been introduced to it an an Oracle tech day, but that was the extent of either's experience with it. The application is long gone, but got a lot of use over several months.
PL/SQL is an excellent tool for implementing business logic, and I really like APEX largely because I can call PL/SQL at any of several points.
This also applies to "composable" code written in the application layer outside the database.
At it’s core that’s a very basic change, but the issue with this stuff is how your adapting to such changes without introducing massive technical debt. Databases that elegantly to your data are easy to work with. However, it’s always tempting to use the lazy solution, which is why systems relying on stored procedures tends to age poorly. In effect they tend to corrupt the purity of your schema.
If anything, it seems like it would go the other way around: With stored procedures, you're guaranteed that any application logic is at least consistent with the current database schema, whereas with separate services, you need to maintain that consistency yourself. Not that I'm advocating for stored procedures in general because they come with a mountain of other drawbacks, I just don't buy this particular argument.
Because stored procedures don’t include the ability to do things like use inheritance to easily add extra layers of abstraction and let the compiler detect when you’re messing up. Adding a column is easy enough, but enforcing that it means something and every one of your prior calculations are now meaningless without taking it into account is difficult. Making such changes elegant and enforcing them to avoid future bugs is even harder.
Remember this example is at it’s core a minor difference. If you’re changing something like amounts being measured by volume instead of weight, then basic assumptions no longer hold making things much worse.
But again, I don't see how this is any easier to solve in application code than it is at the DB level. I don't know of any programming language or framework that allows you to encode rules such as "this DB column must be used". And even if such a thing existed, it wouldn't prevent you from making mistakes in using that column.
On the other hand, using stored procedures at least prevents errors in the other direction: referencing columns in a way that no longer makes sense (because the type was changed, it no longer exists, etc).
Stored Procedures have all kinds of other issues, but this is an obvious failure.
The big down side I see is the learning curve. Writing business logic in the database is not easy and not easy to maintain. It can take half a day to grow a large function. Even longer to debug sometimes. But you learn to structure things well.
https://www.slideshare.net/beamrider9/scaling-etsy-what-went...
https://www.youtube.com/watch?v=eenrfm50mXw
I wouldn't build this into the database were I to build it today.
[1] blocket.se/leboncoin.fr/segundamano.(es,mx)
The real question is whether you are going to ship your code to your data or your data to your code.
I think the one-liner would be better as
or even better as something like [1] http://mywiki.wooledge.org/ParsingLsProjects like PostgREST (and PostGraphile) use schema introspection to generate the API. When the schema changes it's automatically reflected in the API. Sure one has to keep the changes synced on the frontend but the premise is you don't want to decouple.
I take this further by having an `api` schema per major version (e.g. `api_v2`). The downside is it becomes impossible to maintain more than 2 major versions at once, so you have to get everyone from v1->v2 before you can make much progress on v3, but the upside is that you should really try to avoid hard breaking changes in the DB very often.
As others stated, my concerns are primarily that SQL lacks a lot of general-purpose language features (modules, namespacing, composability, no easy way to debug, etc.) which seem to be ideal for writing applications.
I would be interested to see a project written with PL/Python + PL/SQL stored functions in Postgres. PL/Python functions would be the interface between API calls and the database, and SQL would do what it is currently designed to do: straight-forward data manipulation.
https://github.com/Delibrium/delibrium-postgrest/blob/master...