Navigating PostgreSQL - Views

Table of Contents

1. Views
2. Key Features
3. Types of Views
   1. Standard Views
   2. Materialized Views
4. Example
5. Best Practices
   1. 1. Naming Conventions
   2. 2. Performance Considerations
   3. 3. Security
6. References
7. Next Steps

Views

https://www.postgresql.org/docs/current/tutorial-views.html

Views are virtual tables based on the result set of a SQL statement. Think of them as stored queries that can be treated as if they were tables. When you access a view, PostgreSQL runs the underlying query and presents the results.

Key Features

1. Abstraction: Views hide query complexity from end users
2. Security: Control access to sensitive data by exposing only specific columns/rows
3. Data Independence: Changes to underlying tables don’t affect applications using views
4. Query Reusability: Complex queries can be saved and reused easily

Types of Views

Standard Views

• Virtual tables that run their query each time they’re accessed
• Always show current data
• No additional storage required
• Ideal for frequently changing data

Materialized Views

• Store the result set physically
• Must be refreshed to see updated data
• Excellent for complex queries with infrequently changing data
• Improve query performance for expensive computations

Example

CREATE VIEW product_level_sales_of_top_regions AS
WITH regional_sales AS (
    SELECT
        region,
        SUM(amount) AS total_sales
    FROM
        orders
    GROUP BY
        region
),
top_regions AS (
    SELECT
        region,
        total_sales
    FROM
        regional_sales
    WHERE
        total_sales > 10000
    ORDER BY
        total_sales desc
    LIMIT
        10
), prodcut_wise_data as (
    SELECT
        o.region,
        o.product_id,
        SUM(o.qty) AS product_units,
        SUM(o.amount) AS product_sales
    FROM
        orders o
    JOIN
        top_regions tr on tr.region=o.region
    GROUP BY
        o.region,
        o.product_id
)
select
    *
from
    prodcut_wise_data
;

SELECT * FROM product_level_sales_of_top_regions;

Notice how the view:

1. Simplifies complex queries by presenting them as tables
2. Provides a way to reuse complex queries
3. Allows for data masking and security by exposing only specific columns/rows

Best Practices

1. Naming Conventions

• Use clear, descriptive names
• Consider prefixing views (e.g., v_active_customers or suffixing with _view)
• Document the view’s purpose

2. Performance Considerations

• Use materialized views for compute-intensive queries
• Index materialized views when appropriate
• Be cautious with view chaining (views referencing other views)

3. Security

• Grant minimum necessary permissions
• Use views to implement row-level security
• Consider using views for data masking

References

• PostgreSQL Views
• PostgreSQL Materialized Views
• What is Incremental View Maintenance (IVM)?
• Neon: PostgreSQL Views

Next Steps

In the next parts, we will explore the different types of scans and join strategies in PostgreSQL.