1

I have a use case that some external 3rd party vendor services send our service an item with price and a time range, the data schema includes :

vendor_id, item_id, price, time_range

it means the price for this item from this vendor is $price value during the time interval of time_range.

Our service needs to support

  • given a item id, query for all the vendors current price
  • given a future timestamp, item id, and vendor id, query for the price

I consider to create materialized view of the database because I don't want to hit the database too frequently for a large amount of queries.

From the doc in oracle,

A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site,

It looks like I just create a table locally in memory and it's a "copy" of the remote database. The concerns of using view are:

  • it takes much memory usage, and
  • because the database is updated often with different vendor new feed, and it is updated when the price is invalid (out of the time range), using materialized view means I have to refresh often to get the latest view, so it will not reduce network loads as mentioned in pros of materialized view.

None of the two concerns above is listed as potential cons of materialized view. Does it still fit for my use case then?

Improve this question
3
  • Back of the envelope calculation: how many queries and in what timespan? Are you 100% certain this will saturate the link?
    – Kain0_0
    Jan 22, 2021 at 8:05
  • @Kain0_0 I was wondering the reason you were asking this? Is it because view is not necessarily better to handle large RPS, or are there other clear cons that makes view not a good choice in my use case unless the rps is really very large?
    – MLEE
    Jan 22, 2021 at 23:07
  • I'm not to sure what your actual use case is. How tolerant is this application to stale data? Does the relevant data contain an audit history (append only or at least an update strictly increasing timestamp)? The reason I asked if you had a fermi estimation (or even better empirical results) is that a materialised view is not transparent, it does have subtle implications for the validity of the data it contains, and as such is not a drop in solution. The easiest solution is to not have it, so have you actually checked that it is needed?
    – Kain0_0
    Jan 24, 2021 at 21:59

1 Answer 1

Reset to default
2

A materialized view gives you faster reads at the cost of potentially stale data. If stale data is unacceptable a materialized view should not be used. Choosing a refresh rate may avoid stale data issues, but now you need to calculate if that refresh rate is actually cheaper than just handling the reads. For example, if you refresh every 30 seconds but only receive one or two requests in that time frame it's probably not worth it. If you have a huge number of reads a replicated version of your data to handle all reads may be a better option (this is basically CQRS).

A materialized view is for doing an expensive operation asynchronously from requests so they can return quickly. The data in a materialized view should be relatively stable data, because it's essentially a cached copy. If the underlying query isn't that expensive or the data changes too fast other solutions are probably better than a materialized view.

Improve this answer
1
  • Thank you for your input. Have two questions: 1. Is materialized view loaded in memory so that the data size is a consideration as well? 2. Do we have to refresh the view to get update (poll) or is there a mechanism we can be somehow pushed by the updates?
    – MLEE
    Jan 22, 2021 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.