Tag: view
View Access Logging – Fix it in PostgreSQL
By Kirk Wolak Continuous Improvement is an important part of reducing technical debt. Over 30 years our active database has collected some technical debt. We wanted to rename all of our views to be consistently named. Besides that, we had a lack of code reviews for what is happening in the database. The latter situation […]
UNION ALL, data types and performance
© Laurenz Albe 2022 A while ago, I wrote about the performance impact of query parameter data types. Recently I encountered a similar problem with UNION ALL that baffled me for a while, so I’d like to share it with you. UNION ALL to implement polymorphism Different entities can have something in common. For example, […]
View permissions and row-level security in PostgreSQL
© Laurenz Albe 2022 The details of how view permissions are checked have long puzzled me. PostgreSQL v15 introduces “security invoker” views, which change how permissions are checked. The new security invoker views make it possible to use row-level security effectively. I’ll use this opportunity to explain how view permissions work and how you can […]
Creating and refreshing materialized views in PostgreSQL
A “materialized view” is a database object which stores the result of a precalculated database query and makes it easy to refresh this result as needed. Materialized views are an integral feature of pretty much all advanced database systems. Naturally, PostgreSQL also provides support for materialized views, and offers the end-user a powerful tool to […]
Tracking view dependencies in PostgreSQL
We all know that in PostgreSQL we cannot drop an object if there are view dependencies on it: Some people like it because it keeps the database consistent; some people hate it because it makes schema modifications more difficult. But that’s the way it is. In this article I want to explore the mechanics […]
CREATE VIEW vs ALTER TABLE in PostgreSQL
In PostgreSQL, a view is a virtual table based on an SQL statement. It is an abstraction layer, which allows to access the result of a more complex SQL fast an easily. The fields in a view are fields from one or more real tables in the database. The question many people now ask if: […]
PostgreSQL performance: How the optimizer handles views
“How does the PostgreSQL optimizer handle views?” or “Are views good or bad?” I assume that every database consultant and every SQL performance expert has heard this kind of question already. Given the fact that views are a really essential feature of SQL, it makes sense to take a closer look at the topic in […]