Why updateable views, or, Why modules matter? June 6, 2024 by Eduardo Bellani

Continuing a rich conversation that sparked other posts, I was asked to justify updateable views which, to me, are analogous to module' signatures (a topic for another post). Here’s the full exchange.

1. Why is the logical model more likely to be correct and immutable from the perspective of the app than the physical one?

This question seems to mistake logical and physical independence.

Logical independence

the ability to change the form without affecting clients.

Physical independence

the ability to change the implementation without changing the form.

1. In an updateable view world, how would you explain deadlocks occurring to the developers consuming the model (since they can no longer “see” the physical tables that implement it)

2. Similarly, how will you explain the performance characteristics of that model when someone updates the updatable view (example: updating a column that is a primary key is MUCH cheaper then one that is a foreign key - yet they look the same in the logical representation shows to the developer)

3. In the same line of reasoning: How do you explain to developers why there is a vastly different performance characteristics selecting data from the same view even though queries look almost identical?

All these seem to boil down to: how do you explain performance and concurrency issues to the clients of the view? I’ll make an analogy with RPC endpoints, which are the most widely used alternative to updateable views. Such endpoints use documentation to explain their capabilities limitations to their clients.

1. Which skill is more common and cheapest to acquire: A database developer who can create such a logical model or the developer who can modify apps in case we got something about the model wrong?

The logical model will be created, and SQL is a better language for that, since it at least can be declarative. In that sense, I think SQL is cheaper because it provides a better language than the ones mostly used. But the real advantages are:

1. Avoidable rework. The logical contract is done once. In RPC alternatives such contract can be implemented in multiple apps.
2. Consistency. Having the DBMS be the source of truth instead of multiple DBMS instances avoids consistency problems, which are widely considered the most expensive problems in the industry.