How to cache PostGIS views?

Question

Does anyone knows if there exists a caching system for views or queries in postgis? I have a system that gives information through a webpage using a postgis view as backend. The problem is that, sometimes the view is big enough that takes 5 seconds per user to serve the info. I know there's this system named geocache that works very good for raster data using wms protocol. I was wondering if there's a similar tool for vector / table data.

Answer 1

You could create a TEMPORARY (or better UNLOGGED if you use pg9+) table to store the precalculated data (along with trigger functions to keep it in sync), but that's not an out-of-the-box solution as you'd have to write the queries yourself.

But before you do that, you may want to try EXPLAIN and EXPLAIN ANALYZE on your view query, you may be able to find bottlenecks to optimize it enough not to need a cache.