3 ms·

Consider using Postgres views! You can create a view into your data and use that for reading and writing. Then, when you need to change the underlying data mo

Consider using Postgres views! You can create a view into your data and use that for reading and writing. Then, when you need to change the underlying data model, you create a new view with the renamed column. Old clients will target the old view, new clients will target the new view. Then, when all old clients are removed, you can remove the old view safely.

topspin 5y ago
"Consider using Postgres views!" The minute you create a view modifying the underlying table usually gets blocked, in many cases even for changes that have zero relevance to the view. You have to drop the view(s) to get the ALTER TABLE to go through. Absolute nightmare. Oracle, for example, does a far better job of handing this type of evolution and dependency management.
- zozbot234 5y ago
  > You have to drop the view(s) to get the ALTER TABLE to go through. So? With Postgres you can use transactions in your DDL. So it's possible to seamlessly drop the view, alter the underlying table and recreate it, all in one step once the transaction is commited.
  - topspin 5y ago
    "it's possible to seamlessly drop the view, alter the underlying table and recreate it" And if that were the only problem it wouldn't be too bad, except that other databases don't inflict this task or limit the degree of the problem far better. Unfortunately the problem isn't limited to views. Materialized views, for instance, have to be dropped and recreated along with their indexes. That can involve a large IO operation; one that risks failure. Pretty soon an otherwise innocuous ALTER TABLE that conceptually has zero impact on dependencies becomes a major undertaking.
- gigatexal 5y ago
  Does Postgres support a NO SCHEMA BINDING option like Redshift does for views? If so you get a view but it allows for the underlying table to change — this has advantages and disadvantages but it fixes the view/table coupling issue.

topspin 5y ago

"Consider using Postgres views!" The minute you create a view modifying the underlying table usually gets blocked, in many cases even for changes that have zero relevance to the view. You have to drop the view(s) to get the ALTER TABLE to go through. Absolute nightmare. Oracle, for example, does a far better job of handing this type of evolution and dependency management.

zozbot234 5y ago
> You have to drop the view(s) to get the ALTER TABLE to go through. So? With Postgres you can use transactions in your DDL. So it's possible to seamlessly drop the view, alter the underlying table and recreate it, all in one step once the transaction is commited.
- topspin 5y ago
  "it's possible to seamlessly drop the view, alter the underlying table and recreate it" And if that were the only problem it wouldn't be too bad, except that other databases don't inflict this task or limit the degree of the problem far better. Unfortunately the problem isn't limited to views. Materialized views, for instance, have to be dropped and recreated along with their indexes. That can involve a large IO operation; one that risks failure. Pretty soon an otherwise innocuous ALTER TABLE that conceptually has zero impact on dependencies becomes a major undertaking.
gigatexal 5y ago
Does Postgres support a NO SCHEMA BINDING option like Redshift does for views? If so you get a view but it allows for the underlying table to change — this has advantages and disadvantages but it fixes the view/table coupling issue.