I recently refactored several functions which kind of organically grew from several different functions each with it's own SQL query into one big query.
Not only did performance increase by a significant amount, the whole function is basically just "run this query and return the result" as the JSON functions in postgres allow me to leave the actual processing in the database and to not have to do the always annoying "loop over the 30,000 rows that were returned to build a JSON file" thing which is never fast.
I'm not even sure why people would use something like Django Rest Framework, when Postgres outputs JSON directly from a query.
Anything more complicated than that, and I'd recommend a simple Go application.
- Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review.
- Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic.
- Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations.
- I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice.
To be honest, you citing that it's easy to get around the validation process when using Postgres is a major red flag. Why would I want to circumvent code review for things that are most undoubtably code?
Functions can be treated the same way as migrations. With PostgreSQL you have transactional DDL, so you can atomically deploy and rollback functions. There plenty of different script packages to help manage this. At the end of the day it’s deploy and rollback SQL scripts in text files, which are easily managed with version control.
Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic.
The trade off here is that you’re trusting the ORM to produce an optimal query (or queries) for whatever you’re trying to do compared with writing the SQL yourself, with knowledge of your schema. Like any trade off, its one you need to decide for yourself.
You may have filtering on the data that you’re returning from the database that you chose not to do in the query itself. At this point, you should just be dealing with data structures. I don’t think this would be different whether you’re using an ORM or not.
Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations.
I’m not sure what you’re getting at here, so I’m not sure if this addresses your concerns: If you’ve got clean separation of your code, you should only need to update a single location for any given piece of logic. Single source of truth, and all that.
I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice.
SQL scripts are generally text files, so anything that supports some kind of SQL syntax highlighting is likely all you need. I suspect PyCharm will work. The Jetbrains folks are pretty sharp.
Why would I want to circumvent code review for things that are most undoubtably code?
Since you can have your scripts under version control, you can likey use the same code review tools that you currently do. You can also use libraries such as pgtap[0] to test your schema and functions indepedent of the rest of your application, if you so choose.
These are all issues I've dealt with in a large production PostgreSQL environment. They're good things to think about, and you're right to consider them. At the end, you need to make a decision you and your team is comfortable with.
[0]: http://pgtap.org
Using functions as an intermediary to raw queries, also decouples the SQL details from the application. What I mean is that if the application calls an SQL function to read, update, insert data then you can revise the SQL functions independently of application code. In other words, if you refactor an SQL function, you can deploy the change to the database without rebuilding and deploying the application.
This is especially helpful in companies that have an onerous verification and validation process for application changes, because often "performance improvements" are classified as administrative changes which can be done with less bureaucracy.
So, embrace PostgreSQL as a permanent part of your application and reap the benefits of its many powerful features.