PGCon 2008
My presentation on materialized views in Postgres, and especially materialized views with time dependencies, has been accepted to PGCon 2008.
Abstract: Complex queries repeated in application code often are recast as views. This makes programming easier, but it does nothing to improve the performance of the underlying query. Materializing the view is the next logical step. Materialized views are stored in standard tables, so joins, procedure evaluation, and conditions from the original query are pre-computed before queries are run against the materialized view. Because the data is in a table, performance can be further improved by adding indexes.
Many developers shy away from materializing views in Postgres because the functionality is not built in, as it is in Oracle. In this talk, I will present a straightforward approach that can be applied to materialize any view, even those that Oracle cannot handle. Case in point: views that have a time-dependency (such as those based on the current time, now()) don’t have convenient trigger points (such as commits) to signal that the view should be refreshed, and are a major stumbling block for view materialization. This talk presents a technique to handles those cases as well, bringing materialized views even more powerful than those available in commercial products to Postgres.
Hope to see you in Ottawa!
Speeding up ActiveRecord with Hashes, Take 2
A few weeks ago, I posted about the release of my gem, hash_extension, which makes Ruby hashes act a little more like plain old objects. That’s a good thing, because ActiveRecord requests that return hashes instead of full-blown ActiveRecord objects are about 50% faster. 50% is not a performance tweak — it’s basically the best thing that’s ever happened to ActiveRecord.
In version 0.0.1, the only way to get back hashes from ActiveRecord for use with hash_extension was to use select_all:
Foo.connection.select_all("select * from foo")
In the docs for hash_extension I put out a call for someone to extend ActiveRecord itself to return hashes from more natural, ActiveRecord-esque methods. Elliot Laster answered that call, and now, in version 0.0.2, find_as_hashes and find_by_sql_as_hashes are now available:
Foo.find_as_hashes(:all)
Foo.find_as_hashes(:first)
Foo.find_as_hashes(:all, :conditions => "bar = 'baz'")
Foo.find_by_sql_as_hashes("complex sql goes here")
To learn more about the gem and to download, go here.
1 comment