Simplify: move code into database functions (2015)
sivers.org
sivers.org
Don't FUCKING do this.
I've spent the last 15 months untangling a mess of an e-commerce system that was built exactly like this. The problem is SQL is not a programming language and the only way to accomplish some things directly in the database is by creating solutions with O(n^2) or worse running-time. Our system has queries with O(n^4) time complexity. Less than two weeks ago, I had to untangle a query that was over 3,100 lines long with 18 sub-queries and was able to refactor it into about 100 lines of SQL and another 100 lines of code. Making matters worse, changes to stored procedures don't show up in git, and can break some regression-testing tools, making it very difficult to track changes and hunt down bugs.
Use the right tool for the job: Don't treat every problem like a nail once you have a hammer. Use SQL for data and your language of choice for logic. Don't create a monstrosity that someone else will have to spend weeks fixing just because you can.
This is not necessarily true. It depends on your environment. You can track changes to db with the right tools.
Rest of your points I completely agree. Don't put unnecessary complexity in your db. It's a database, not a code repo.
I wish there were (perhaps I'll be told there is) a migration system that used a single file per table, and was git-aware so that when the live schema didn't match the checked out schema file, it would determine a migration from the historical version that it does match.
Alas, I don't think it's possible, because such a file would describe a state, not, as migration files do, transitions between states. It's tempting to imagine that it's possible to robustly infer transitions from one to another, but I've used Django.
- Th. Doctrine and Hibernate ORMs can do similar things.
- Flyway expects developers to write migrations by themselves, either in Java or in SQL, and just executes them and records state information. It's not too bad to write migrations yourself because the data you work with is usually valuable.
- The current schema has a column A and the new version does not have A but a similar column B. Is this a new column? Or should A be renamed into B? (same for renaming tables) - You add a new table A which contains columns from B, which are also removed from B. Should this data be copied? - You change the structure of a table, and have some of it split off into different tables. How does the schema tool know how to rearrange the current data?
There are probably some more cases, but these are the ones I could think of quickly.
That's exactly why I think Django's migrations are insanely good. Simple ones are autogenerated and everything else is possible as well, with either python code or direct sql queries
If you're working on a big project which requires a team of people (or may grow into a team), then yeah, don't do this. Keep your systems as idiomatic, unsurprising, and consistent as possible, unless you have a very good reason not to. The less cognitive load, the easier and faster it is for others to help without making mistakes.
Also almost every algorithm can be written in PLSQL if needed, so yuu can surely improve on the O(n^4) time complexity.
I agree with the procedure change tracking, that's much harder, but server side programs can be extremely responsive (low latency) and powerful.
The problem is SQL is not a programming language and the only way to
accomplish some things directly in the database is by creating solutions
with O(n^2) or worse running-time. Our system has queries with O(n^4)
time complexity.
1. The approach being espoused is not, "Move everything down into the database layer at all costs, no exceptions." It's more like, "Many things are simpler and faster at the database layer."
2. These O(n^2) (and n^3, n^4, whatever) horrors are at least as common at the app layer. I have dealt with my fair share at every possible level of the stack.
I had to untangle a query that was over 3,100 lines long with 18 sub-queries
If stores procedures become part of the system, they belong into the SCM as well. The deployment process has to include the stored procedures as well. Different to tables and other constructs that have state, stored procedures can be easily migrated.
In general I agree that stored procedures massively underdeliver in many aspects, but not properly treating the database as the application server it has become is part of the problem.
They do if you use database migrations.
Otherwise, you have just created a huge hole in your buuild reproducibility. SQL (including pl/SQL) is a programming language, therefore treat it like a programming language - keep all of it in a versioning system and ensure that you can trace every piece of code that runs inside your database to a piece of code in your VCS.
There are lots of other reasons this is a questionable idea at best, but performance is not necessarily one of them.
Move that logic into a stored procedure and suddenly
your business logic can run in less than the time of
a couple round trips to the database. Lock contention
drops, number of concurrent transactions drops, and
performance could actually increase
There are lots of other reasons this is a questionable
idea at best, but performance is not necessarily one of
them.
Normally the bottleneck of scaling an application is data access and storage, there aren't typically a lot of problems that are CPU bound.
Adding additional layers doesn't increase performance, it increases latency and processing time. The closer you can keep your processing logic to the actual data, the better your performance is going to be.
That being said, there are reasons why you may not want to follow that architecture, but generally performance isn't going to be one of them.
It does make query development much easier to use a database specific IDE for writing the queries versus munging big strings in code.
This just means that modifying sprocs has to be treated with the same caution as deploments. Unit testing is truly difficult though. It doesn't help that many DBMS products (looking at you, Postgres) don't offer an embedded mode.
One of the best features of pg is it's ability to write db logic in your language of choice.
The performance characteristics of these languages meets or exceeds the execution time of plsql in many cases.
In practice, if you’re thinking of changing at that level, the problem probably lies elsewhere. Unless your app is some special snowflake (note: 99.99% aren’t), any of the popular languages/frameworks will do just fine for a very long time.
The only move Sivers arguably needed to make here was away from Perl. To Ruby, PHP or JavaScript, but not all 3.
If those extra moves hadn’t happened, this wouldn’t even be a thing.
I wrote pgproxy[1] in an attempt to overcome some of those issues. So far, it's worked pretty well for me.
These aren't mutually exclusive approaches. You can use an ORM like ActiveRecord to manage migrations and move functionality down to the database layer when reasonable to do so.
I've seen a lot of developers begin to believe in the abstraction, and forget the underlying access.
This has caused performance problems with increased round trips to the server, non-obvious indexing strategy issues, and code tightly coupled to the ORM rather than the data.
I get especially suspicious with framework-type ORMs that can cause a massive rewrite between major versions. It's costly, time consuming, and doesn't provide any application benefit, it's only new forms of abstraction.
ORMs are seductive, because a lot of these issues aren't obvious when you first start using them. The marketing is usually pretty compelling. Unfortunately, by the time you start to run into issues I've described, it's normally too late to change paths.