PostgreSQL views are one of the most underrated features in the database. Developers know they exist, but few truly take advantage of them. Views let you encapsulate complex queries, simplify joins, and build clean, reusable layers of logic that make your SQL easier to understand and maintain.

In this guide, we'll explore standard views, materialized views, and build a real example in the Pagila Sample Database: identifying the top renting customers per month.

Create a View

A view is essentially a saved SELECT statement. It doesn't store data — it simply runs the underlying query every time you reference it. Think of it as a virtual table that always reflects current data.

Create a simple view:

CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, active
FROM customer
WHERE active = 1;

Querying the view is just like querying a normal table:

SELECT * FROM active_customers;

Views are perfect for hiding repetitive joins, enforcing business logic, and presenting clean, developer-friendly interfaces to complex schemas.

Materialized Views

A materialized view is different. Instead of running the query every time, PostgreSQL stores the results on disk. This makes materialized views incredibly fast for reporting, dashboards, and analytics — but the data becomes stale until you refresh it.

Create a materialized view:

CREATE MATERIALIZED VIEW rental_summary AS
SELECT customer_id,
       COUNT(*) AS total_rentals
FROM rental
GROUP BY customer_id;

Query it:

SELECT * FROM rental_summary;

Refresh it:

REFRESH MATERIALIZED VIEW rental_summary;

Materialized views are ideal when the underlying query is expensive and the data doesn’t need to be real-time.

Example: Top Renting Customers per Month

Let's build a practical example using Pagila. Our goal: determine which customers rent the most movies each month.

Step 1: Create a monthly rental summary view

CREATE VIEW monthly_rentals AS
SELECT
    DATE_TRUNC('month', rental_date) AS rental_month,
    customer_id,
    COUNT(*) AS rentals
FROM rental
GROUP BY rental_month, customer_id;

Step 2: Rank customers within each month

SELECT *
FROM (
    SELECT
        rental_month,
        customer_id,
        rentals,
        RANK() OVER (
            PARTITION BY rental_month
            ORDER BY rentals DESC
        ) AS rank
    FROM monthly_rentals
) ranked
WHERE rank = 1
ORDER BY rental_month;

The result gives you each month's top customer (or customers, if tied), along with the number of rentals. This is where views shine — the final query is clean because the logic is encapsulated upstream. If you plan on running this repeatedly, you can even wrap this query in its own view so all that’s left is a simple SELECT * from a single object!

Conclusion

Views make your SQL cleaner. Materialized views make it faster. Together, they give you a powerful toolkit for simplifying application logic, improving performance, and organizing your analytics layer.

Mastering views isn't just about writing less SQL — it's about writing better SQL. And once you start using them intentionally, you'll wonder how you ever managed without them!