Re: Implementing Incremental View Maintenance

>> Another use case is a ticket selling system. The system shows how many
>> tickets remain in a real time manner. For this purpose it needs to
>> count the number of tickets already sold from a log table. By using
>> IVM, it could be accomplished in simple and effective way.
>
> Wouldn't the app just have a table like ticket(id, name, quantity), decrement the quantity when the ticket is sold, and read the current quantity to know the remaining tickets? If many consumers try to buy tickets for a popular event, the materialized view refresh would limit the concurrency.

Yes, as long as number of sold ticks is the only important data for
the system, it could be true. However suppose the system wants to
start sort of "campaign" and the system needs to collect statistics of
counts depending on the city that each ticket buyer belongs to so that
certain offer is limited to first 100 ticket buyers in each city. In
this case IVM will give more flexible way to handle this kind of
requirements than having adhoc city counts column in a table.

> I think we need to find a typical example of this. That should be useful to write the manual article, because it's better to caution users that the IMV is a good fit for this case and not for that case. Using real-world table names in the syntax example will also be good.

In general I agree. I'd try to collect good real-world examples by
myself but my experience is limited. I hope people in this community
come up with such that examples.

> "outweigh" means "exceed." I meant that I'm wondering if and why users prefer ON STATEMENT's benefit despite of its additional overhead on update statements.

I already found at least one such user in the upthread if I don't
missing something.

> Bottom line: The use of triggers makes me hesitate, because I saw someone's (probably Fujii san) article that INSERTs into inheritance-and-trigger-based partitioned tables were 10 times slower than the declaration-based partitioned tables. I think I will try to find a good use case.

Great. In the mean time we will try to mitigate the overhead of IVM
(triggers are just one of them).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp