I have a project in which there’s a dashboard per client of all the tasks they have access to. There’s a tasks table of all tasks, but some clients only have access to certain tasks, and this access is defined by the values two columns on the tasks table. These values can change, so over time these clients may get access to more or less tasks.
I thought about creating a set of views at runtime that serves as each client’s dashboard, and whenever the access values change for a client, I create a new view for that client at runtime and drop the previous view.
Is changing DB structure at runtime a bad thing in general?
Thanks for any pointers.
There is no reason you can’t do it but I don’t really see a benefit from what you’ve explained so far. Are you planning to materialize these views? That has its own complications and is probably a premature optimization. You haven’t really presented a case for doing anything out of the ordinary here. Why can you not just run a query on the tasks table that includes variables in a where clause to be checked against your access columns?
3 Likes
The assumption is that these views will be read more than written to, so materializing them has tangible benefits.
Thats true of almost all database tables. I’d measure the benefit before embarking on this. You realize with a materialized view you have to refresh it yourself right? So any function that updates this table needs to invoke refresh. The refresh is blocking by default, so readers can’t read while its running. Using a non-blocking refresh takes longer and means you will have people reading stale data. Trust me, people only do this when performance requires it.
3 Likes
I don’t necessarily disagree with your point, but since postgres 9.4 you can refresh materialized views concurrently which allows reads while refreshing https://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html
1 Like
