Hacker News Reader

Comment by zz865 | Hacker News Reader

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.

View on HN

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.

"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.

> 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.

"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.

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.

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.

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

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

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)

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.

Reply on news.ycombinator.com