On Fri, Jun 2, 2023 at 9:36 AM Oliver Kohll <oli...@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. 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
