On Fri, Oct 23, 2009 at 1:42 PM, Tom Hughes <[email protected]> wrote: > On 23/10/09 18:25, Anthony wrote: > >> On Fri, Oct 23, 2009 at 1:11 PM, Tom Hughes<[email protected]> wrote: >>> >>> In the real world we don't all have infinite CPU time and I/O bandwidth >> >> Hence my point about using a modern database, where you can create >> things like materialized views to make accessing the database >> efficient without making the core schema itself a mess. > > I understand the concept of a materialized view, but I've never actually > used one myself. There is of course a significant cost in terms of disk > space if you create materialized views with a large data set like ours.
You're likely already using that disk space, though. If you used a materialized view for the current_nodes table, you wouldn't need the current_nodes table. You're basically already implementing materialized views, you're just doing so in the code rather than in the database, which if nothing else makes it more prone to mistakes. Anyway, I don't care what you do with your database. I'm merely explaining why I do what I do with my databases, and why I've answered Peter's question the way I have (which is the way I know how to do things, not the way you or anyone else does things). > One other obvious question is how materialized views interact with updates > to the underlying tables? Do they have to be recomputed from scratch every > time an underlying record is changed? Only it seems that solving the > generalised problem of working out how to update a view given a specific > change in the underlying data would be a hard one to solve. If you want the answers to those questions, follow the link I gave to Frederick (or buy Enterprise Rails by Dan Chak) and read chapter 14. Mr. Chak goes into all the details of how to implement materialized views in PostgreSQL much better than I can repeat them here. I'm sorry if I'm being off topic. As these are direct questions being posed to me, I hope the list members can forgive me. _______________________________________________ Geocoding mailing list [email protected] http://lists.openstreetmap.org/listinfo/geocoding
