> All sorts of things are possible but it misses a fantastic opportunity to compartmentalise the data away from the implementation.
That's what schemas are for. You have a schema for your code and a schema for your data. You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data.
> Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL.
Say you wrote your code in PHP, now you want to migrate to Ruby or NodeJS. You can't, you have to rewrite everything. How often do you plan to migrate to another database? In my experience this almost never happens in reality, but the layers above come and go.
> You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data.
That all just sounds a touch complicated compared to data in database, code somewhere else (like git). I'm circling back to the same point in a couple of different ways, but pgSQL specialises in the relational model of data. That isn't a great data model for code and there are already better ways to manage code than shoehorning it into the database. Its cool that it is possible, and I'm not saying that someone who does is making a mistake. But I also don't think they are gaining an advantage and there is a really easy opportunity to separate out where bugs can occur from where data lives.
> How often do you plan to migrate to another database? In my experience this almost never happens in reality, but the layers above come and go.
If you are using a database for its advanced general purpose programming capabilities? The chances probably start to get more likely.
Databases that are a store of data don't need to change because they already do their one job (disk <-> relational model translation) really well. If they are pressured to do 2 or 3 tasks like business logic then suddenly it is a lot more likely that there will be pressure to swap databases.
If I were using SQLite and someone wants to do fancy triggers then maybe I need to swap to PostgreSQL. Avoiding that sort of decision making is a great reason to seal the data completely away from the code.
> That all just sounds a touch complicated compared to data in database, code somewhere else (like git).
You use Postgres as a deployment target and not as a replacement for git. It's not complicated at all. You even get features like transactional deployments and the ability to prohibit applications from directly touching the data.
> pgSQL specialises in the relational model of data
The relational model is SQL:92, Postgres does much more than that. Postgres has JSON support, recursive CTEs, Row Level Security, and Window functions that would require dozens of lines of procedural code to do what can be done with a single OVER in its SELECT clause.
> If I were using SQLite and someone wants to do fancy triggers then maybe I need to swap to PostgreSQL.
If you want to put your business logic into an RDBMS, you wouldn't be using an embedded DB anyway, but rather Oracle, Postgres, SQL Server or DB2, which are designed for this type of architecture.
That's what schemas are for. You have a schema for your code and a schema for your data. You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data.
> Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL.
Say you wrote your code in PHP, now you want to migrate to Ruby or NodeJS. You can't, you have to rewrite everything. How often do you plan to migrate to another database? In my experience this almost never happens in reality, but the layers above come and go.