For example, you might want to see all customers who live in Texas that have bought more than three products in the past five months. Rather than rewriting this query each time, you can create a view that allows you to find a subset of customers. You will start with a query that tracks customers by order frequency, how much they ordered, and where they are ordering from. Imagine you have an online store and want to send out coupons to customers, offering them different deals based on how often they shop and where they live. Creating a Materialized View in PostgreSQL You’ll see an example of this in the next section. This reruns the query and caches the new results. So in the example above, if a customer changed their address and we made our view a materialized view, we would not see the change until we refreshed the view.
#CREATE VIEW PSQL UPDATE#
The drawback to materialized views is that the cached results do not automatically update when the data in the base tables changes. This makes running queries against the view much faster.
Materialized views allow you to cache the results of the query on disk in a temporary table. For costly queries and large datasets, this can be a drawback.Ī better solution when performance is a concern might be to create a materialized view.
Views are great for simplifying code, but with large datasets, you're not really saving any time when you run them because a view is only as fast as its underlying query. WHERE city = 'Chicago' Materialized Views in PostgreSQL
0 kommentar(er)
