A Guide to PostgreSQL Views

What Are PostgreSQL Views?

PostgreSQL views are a way to store an alias for a query in the database. PostgreSQL will replace the view name with the query you use in the view definition.

Views can be good for reducing the complexity of larger queries for the user, so they don’t have to write out complex JOINs, but can also lead to performance problems if they are overused and because hiding the complexity can make it harder to identify potential performance pitfalls.

One thing you’ll notice is that views can improve the user experience, but they won’t really ever improve performance because they don’t actually run the query, they just alias it. If you want something that runs the query, you’ll need a materialized view. If you want something that improves the performance, you'll need a materialized view, which runs the query and stores the result.

What Are PostgreSQL Materialized Views?

A materialized view actually runs the query and stores the results. In essence, this means the materialized view acts as a cache for the query. Caching is a common way to improve performance in all sorts of computing systems.

One of the cool things about materialized views is that you can create indexes on them because, under the hood, they’re just tables that store the results of a query.

For an in-depth explanation of PostgreSQL views and materialized views, check out our blog post on how they influenced some of our features. Keep reading for the basics on the topic.‍

Standard PostgreSQL Views vs. Materialized Views

Views can be good for reducing the complexity of larger queries for the user, so they don’t have to write out complex JOINs, but they can also lead to performance problems if they are overused because hiding the complexity can make it harder to identify potential performance pitfalls.

One thing you’ll notice is that views can improve the user experience, but they won’t really ever improve performance because they don’t actually run the query; they alias it. If you want something that runs the query, you’ll need a materialized view. If you want something that improves the performance, you'll need a materialized view, which runs the query and stores the result.

Benefits of Materialized Views: When to Use Them 

Materialized views offer better query-reading performance since they run the query upfront—ideally, at a time of low database load. They will also save you time, as you don’t have to write the same old query again and again whenever you want to retrieve data that is specific to that query.

Here are a few practical examples of applications where PostgreSQL materialized views can enhance performance:

• Dashboards and visualizations. You can leverage materialized views when you have complex queries powering user-facing visualizations that need to load quickly to provide a great user experience.

• Data aggregations. Materialized views are ideal for scenarios where data needs to be aggregated regularly (sum, average, count, etc.), and the aggregated results are queried frequently.

• Frequent reports. Materialized views will help you if you’re generating reports that are generated often and require consistent and fast data retrieval, especially when dealing with large volumes of data and complex queries.

• Rollups (or reducing data granularity). Materialized views will also help you when you’re, for example, collecting data by second but would like to create views by minute or by hour. 

• Read-heavy operations. Make use of materialized views when the database is subjected to read-heavy operations and there’s a need to offload the main database to maintain optimal performance.

• Analysis of historical data. Materialized views can help you store and quickly access historical data snapshots for trend analysis. 

• Data warehousing and BI. Materialized views are beneficial in data warehousing and business intelligence applications where complex queries, data transformation, and aggregations are the norms.

PostgreSQL Refreshed Materialized View

Essentially, materialized views are only as accurate as the last time they ran the query they were caching. You need to run REFRESH MATERIALIZED VIEW to ensure they are up to date.

Once you run REFRESH MATERIALIZED VIEW, we’ll end up with the new data in our materialized view. And a refresh is all or nothing.

How Materialized Views Work (And Why They Get Out of Date)

To understand how materialized views get out of date and what refresh is doing, it helps to understand a little about how they work under the hood. Essentially, when you create a materialized view, you are creating a table and populating it with the result data from the defining query.

The database stores the defining query to reuse it later when being refreshed, much like a view. However, it's not the most efficient way to refresh the materialized result data.

While that might be okay when you’re working with, say, online transactional processing (OLTP) data that PostgreSQL works with, and your updates/deletes are randomly spread around your data set, it starts to seem pretty inefficient when you’re working with time-series data, where the writes are mostly in the most recent period.

In order to get the materialized view to be up to date, we need to REFRESH it, but for time-series use cases, a) you have to refresh it frequently for it to be up to date, and b) the refresh is inefficient because we have to delete and re-materialize all the data, maybe going back years to get the up-to-date data. And that’s one of the main reasons we developed continuous aggregates at Timescale.

Continuous aggregates are incrementally updated with only the newest data being materialized. They enable users to pre-aggregate data in the background and make it quickly available when necessary. 

In sum, a query on a continuous aggregate uses real-time aggregation by default to combine materialized aggregates with recent data from the source hypertable. With continuous aggregates—automatically updated and incremental materialized views—you get accurate and up-to-date results faster because you're benefitting from pre-computed aggregates for a large portion of the result.

We hope we have piqued your curiosity about the potential of continuous aggregates. Want to learn more? Learn how we made them even better—yes, you read that right—and how to use them.