Thread: Interconnected views
Hi,
Just wondering, does anyone else create apps which might not have 'big' data, but quite complex arrangements of views joining to each other?
If so, do you have scripts to aid refactoring them e.g. drop/recreate/test them in the right order etc.?
I'm really impressed with the way Postgres handles these multi-layered views. Some of our explain analyze outputs could fill a book each! Tools like Depesz' can be very useful. Sometimes a little tweaking or a judicious index is necessary, but the performance is very reliable and scalable.
Blog post about it here: https://blog.agilebase.co.uk/2023/05/21/refactoring-sql-views/
Oliver
On Fri, Jun 2, 2023, 09:36 Oliver Kohll <oliver@agilebase.co.uk> wrote:
Hi,Just wondering, does anyone else create apps which might not have 'big' data, but quite complex arrangements of views joining to each other?If so, do you have scripts to aid refactoring them e.g. drop/recreate/test them in the right order etc.?
In Ledgersmb, we did this and also same with functions. We used versioned scripts, database schema change management tools, and reload utilities as well as Pgtap for testing.
I'm really impressed with the way Postgres handles these multi-layered views. Some of our explain analyze outputs could fill a book each! Tools like Depesz' can be very useful. Sometimes a little tweaking or a judicious index is necessary, but the performance is very reliable and scalable.Blog post about it here: https://blog.agilebase.co.uk/2023/05/21/refactoring-sql-views/
Yeah, I have always been impressed by Postgres here too.
Hace you taken a look at dbt (https://www.getdbt.com/)? It's aimed at analytics but can be used for any purpose.
Regards,
Guillermo González de Agüero
El vie, 2 jun 2023 15:36, Oliver Kohll <oliver@agilebase.co.uk> escribió:
Hi,Just wondering, does anyone else create apps which might not have 'big' data, but quite complex arrangements of views joining to each other?If so, do you have scripts to aid refactoring them e.g. drop/recreate/test them in the right order etc.?I'm really impressed with the way Postgres handles these multi-layered views. Some of our explain analyze outputs could fill a book each! Tools like Depesz' can be very useful. Sometimes a little tweaking or a judicious index is necessary, but the performance is very reliable and scalable.Blog post about it here: https://blog.agilebase.co.uk/2023/05/21/refactoring-sql-views/Oliver
On Fri, Jun 2, 2023 at 9:36 AM Oliver Kohll <oliver@agilebase.co.uk> wrote:
Hi,Just wondering, does anyone else create apps which might not have 'big' data, but quite complex arrangements of views joining to each other?If so, do you have scripts to aid refactoring them e.g. drop/recreate/test them in the right order etc.?I'm really impressed with the way Postgres handles these multi-layered views. Some of our explain analyze outputs could fill a book each! Tools like Depesz' can be very useful. Sometimes a little tweaking or a judicious index is necessary, but the performance is very reliable and scalable.Blog post about it here: https://blog.agilebase.co.uk/2023/05/21/refactoring-sql-views/Oliver
Oliver,
I built a couple of such scripts to chase down dependency trees on views. Based on a Cybertec (Laurenz Albe) article.
https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/
Basically, I hunted down the dependent views (we have a rich hierarchy of views). Not like yours, maybe 10-15 deep.
Basically, I hunted down the dependent views (we have a rich hierarchy of views). Not like yours, maybe 10-15 deep.
But it also impacts us on Column changes...
Anyways, I generate the DROP commands, inside of a single transaction, in the right order.
Then I apply the CREATE VIEW ... in the reverse order of the drops.
Then I commit the transaction.
I was hours away from implementing something that grabbed the view definitions, and dropped and recreated.
But that was only useful for the column type changes, and by the time I was there, we were pretty much done changing them.
I find it a nice feature that I can drop views within a transaction, then recreate them, in the same transaction.
So that nothing breaks. But we have not tested this under an "realistically heavy load".
HTH
Then I apply the CREATE VIEW ... in the reverse order of the drops.
Then I commit the transaction.
I was hours away from implementing something that grabbed the view definitions, and dropped and recreated.
But that was only useful for the column type changes, and by the time I was there, we were pretty much done changing them.
I find it a nice feature that I can drop views within a transaction, then recreate them, in the same transaction.
So that nothing breaks. But we have not tested this under an "realistically heavy load".
HTH