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,