4 ms·

My biggest problem with databases is always versioning. IE renaming a column will break old clients. If there was a way you could have multiple schema versions

by zz865 5y ago

My biggest problem with databases is always versioning. IE renaming a column will break old clients. If there was a way you could have multiple schema versions so you could upgrade database then clients later it would be the best. EDIT: yes thanks for the comments, views and creating and API layers and adding instead of subtracting do all work, but I believe they're all workarounds for the underlying problem. Fixing versioning would make everyone's lives easier.

DanHulton 5y ago
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.
withinboredom 5y ago
The ideas is not to validate your schema in a client during runtime. Just like you don’t care if a new key is present in a json blob. You should be able to add new features without breaking things.
johnchristopher 5y ago
Besides using an ORM that would hide the renaming or views, why not build an API layer ?
- _3u10 5y ago
  Because Postgres views enforce API versioning in ways that don’t break older clients. Pretty much any schema modification that would break an older client is forbidden by REPLACE VIEW. It is certainly possible to break older clients but you have to work at it. Also… you don’t want to build an API layer because postgrest builds one for you. One that is close to on par with GraphQL (arbitrary joins / filters)
vaughan 5y ago
Becomes too complex. You have to maintain mapping of old columns to new columns and when you are splitting tables it becomes too crazy.
pbowyer 5y ago
Yes! I would see this tied in with temporal support throughout the database, so you can query the schema and data as it was at 2021-09-03 11:53:28. This avoids some big problems with temporal data but I can't see it ever being efficient to do.

by zz865 5y ago

DanHulton 5y ago

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.

withinboredom 5y ago

The ideas is not to validate your schema in a client during runtime. Just like you don’t care if a new key is present in a json blob. You should be able to add new features without breaking things.

johnchristopher 5y ago

Besides using an ORM that would hide the renaming or views, why not build an API layer ?

_3u10 5y ago
Because Postgres views enforce API versioning in ways that don’t break older clients. Pretty much any schema modification that would break an older client is forbidden by REPLACE VIEW. It is certainly possible to break older clients but you have to work at it. Also… you don’t want to build an API layer because postgrest builds one for you. One that is close to on par with GraphQL (arbitrary joins / filters)

vaughan 5y ago

Becomes too complex. You have to maintain mapping of old columns to new columns and when you are splitting tables it becomes too crazy.

pbowyer 5y ago

Yes! I would see this tied in with temporal support throughout the database, so you can query the schema and data as it was at 2021-09-03 11:53:28. This avoids some big problems with temporal data but I can't see it ever being efficient to do.