On Fri, Apr 11, 2014 at 9:53 PM, lkcl . <luke.leighton@gmail.com> wrote:
> On Fri, Apr 11, 2014 at 1:26 PM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> On 04/11/2014 03:20 PM, lkcl . wrote:
>>>
>>> so i had an idea. there already exists the concept of indexes. there
>>> already exists the concept of "cached queries". question: would it be
>>> practical to*merge* those two concepts such that specific queries
>>> could be*updated* as new records are added, such that when the query
>>>
>>> is called again it answers basically pretty much immediately? let us
>>> assume that performance degradation on "update" (given that indexes
>>> already exist and are required to be updated) is acceptable.
>>
>>
>> I think you just described materialized views.
>
> ... well... dang :)
>
> http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
>
> ok so definitely not the snapshot materialised views, but yes! the
> eager materialised views, definitely.
>
>> The built-in materialized
>> views in PostgreSQL are not updated immediately as the tables are modified,
>
> ... but that would probably be enough.
>
>> but it's entirely possible to roll your own using views and triggers. There
>> are a few links on the PostgreSQL wiki, in the "Versions before 9.3"
>> section: https://wiki.postgresql.org/wiki/Materialized_Views.
When updating a materialized view, or refreshing it, you need as well
to be aware that an exclusive lock is taken on it during the refresh
in 9.3, so the materialized view cannot be accessed for read queries.
> awesome. uhhh, well that was easy *lol*. once i am paid, whom do i
> send the payment to for the fast response and incredibly valuable
> information? :) [this is a serious question!]
This can be helpful:
http://www.postgresql.org/about/donate/
--
Michael