Using Views as Tables

Hi All,

For some reason I'm having a hard time getting over to some developers that
assuming that you can use a view in Postgres as you would use a table is
probably a bad idea. This is in versions of Postgres before 9.3, 9.1 and
8.3, where materialized views came in, just to clarify.

As some background, there are a number of tables containing completely
static data that is updated every few months via a batch import into
different tables by date - table_201603 or table_201607. A view has then
been created called 'table' which clients then use which is just a 'SELECT
* FROM' of the table. When an updated batch of data is put into a new table
the view is then updated to point at the new table for consistency. This
means an in-place rename of the table does not need to take place that
might mean downtime. These tables generally have about 100 million rows in
them.This is geospatial data, so they're doing geospatial queries on a
view, whatever difference that might make.

This is apparently leading to some confusing results when people are
querying these views with inconsistent query times. Sometimes queries are
taking two or three seconds, other times 20 or 30 milliseconds, the latter
being what we'd expect. Naturally, the fact that these are views they are
querying is the first thing I'm questioning here and I've never seen query
times of seconds using the table directly on the limited experiments I've
done.

What are the pitfalls of using plain views like this and is there a refresh
interval to them of some kind which would account for the differing query
times? Is there a better way of doing this prior to 9.3? Naturally, a
materialized view would be better for this but an upgrade might not happen
in the near future.

Thanks in advance,