Re:New In Postgres 12: Generated Columns
by taffer on Wednesday October 02, 2019 @02:41PM
(#21139482)
Incorrect separation of data from logic is a human problem, not a Postgres problem. Put your logic in one schema[1], let's call it code schema and your data in another schema, the data schema. There you have your separation. Now you can:
* Change your application logic very easily.
* Use a transaction to deploy new code! Zero downtime! [2]
* Use read replicas to balance load in many cases.
* Rollback bad application updates
* Test anything [3]
* Use miriads of development tools from Git to CI systems.
* Use row level security, so that every user can only see his own data [4]
* Only allow applications to call your code schema, never let them touch your data directly.
[1] https://www.postgresql.org/docs/current/ddl-schemas.html
[2] https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr...
[4] https://www.postgresql.org/docs/current/ddl-rowsecurity.html
Changing database schema is a big deal. It might take a lot of time or it must be done with great caution to keep database online. It's hard to properly version it and it's often hard or just impossible to roll back bad update.
Generally database is state and application is stateless. You can couple it, but decoupling works better.