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 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.
When to Use PostgreSQL Materialized Views?
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.
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, you will 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.
PostgreSQL Materialized Views on Steroids: Continuous Aggregates
While similar to PostgreSQL materialized views, continuous aggregates are continually and 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, you get accurate and up-to-date results faster because you're benefitting from pre-computed aggregates for a large portion of the result.
If you need a recap about PostgreSQL materialized views, you can always go back to our in-depth article, but we hope we have piqued your curiosity about the potential of continuous aggregates. Want to learn more?