CTEs vs. Views in PostgreSQL: How to Choose the Right Tool
- Sasikala R
- Oct 11
- 4 min read
In PostgreSQL, both Common Table Expressions (CTEs) and views offer powerful ways to simplify and organize complex queries. While they may seem similar, they serve different purposes and have distinct characteristics that impact readability, reusability, and performance. This quick guide helps you decide which tool is best for your situation.
Common Table Expressions (CTEs)
A CTE is a temporary, named result set defined within a single query using the WITH clause. Think of it as a subquery with a name that improves readability by breaking down a complex query into simpler, more manageable parts.
Key characteristics of CTEs:
Temporary: A CTE exists only for the duration of the query it is defined in. Once the query finishes, the CTE is discarded.
No persistence: Because they are temporary, CTEs are not stored as objects in the database and cannot be reused by other queries.
Recursive capabilities: CTEs are the standard way to handle recursive queries, which are essential for navigating hierarchical or tree-like data structures.
Optimization: In PostgreSQL 12 and later, non-recursive CTEs are often "inlined" by the query optimizer, meaning they are treated like a regular subquery and executed as part of the main query. This can lead to better performance compared to older versions, but you can use the MATERIALIZED keyword to force it to be computed separately.
Example of a CTE for a one-time calculation
Let's say you want to find the customers with sales above the average for a specific month. A CTE can simplify this logic:
WITH monthly_sales AS (
-- CTE to calculate the total sales per customer in a given month
SELECT
customer_id,
SUM(amount) AS total_monthly_sales
FROM sales
WHERE sale_date >= '2025-09-01' AND sale_date < '2025-10-01'
GROUP BY customer_id
)
SELECT
customer_id,
total_monthly_sales
FROM monthly_sales
WHERE total_monthly_sales > (SELECT AVG(total_monthly_sales) FROM monthly_sales);
In this example, monthly_sales is a temporary, non-reusable result set that makes the final query clear and easy to understand.
✅ Advantages of CTEs:
Makes long and complex queries readable and modular.
Supports multiple CTEs in the same query, making stepwise transformation easy.
Useful for quick, one-time analytics or data exploration.
⚠️ Limitations of CTEs:
They are not reusable across queries.
They don’t improve performance — in fact, sometimes they can make it worse if used excessively.
Results are recalculated each time the query runs.
Views:
A view is a stored query that acts like a virtual table. Unlike a CTE, a view is a persistent database object that can be reused across different queries and sessions.
Key characteristics of views:
Persistent: Views are saved in the database schema and remain available until they are explicitly dropped.
Reusable: A single view can be referenced by multiple queries, other views, and stored procedures, which promotes code reuse and helps maintain a single source of truth for complex logic.
Enhanced security: Views can restrict access to sensitive data by exposing only specific columns or rows to different user roles, providing an additional layer of security.
No data storage: Standard views do not store data physically. The underlying query is executed every time the view is accessed, meaning the data is always current.
Materialized views: For performance-critical scenarios, you can use a materialized view, which stores the query result physically on disk. While this requires more storage and needs to be refreshed periodically, it can dramatically speed up queries on complex or slow computations.
Example of a view for a frequently used report
Suppose your sales team frequently needs a list of top customers by sales. You can create a view to encapsulate this logic:
Query:
CREATE VIEW top_customers_by_sales AS
SELECT
c.customer_id,
c.customer_name,
SUM(s.amount) AS total_sales
FROM customers c
JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_sales DESC;
-- Now, we can use this simplified query
SELECT * FROM top_customers_by_sales LIMIT 10;
✅ Advantages of Views:
Promote consistency in business logic (one definition, used everywhere).
Ideal for standardizing KPIs and metrics across teams.
Makes reports, dashboards, and ETL processes cleaner and more maintainable.
Useful for security — Views can limit what data users can access.
⚠️ Limitations of Views:
Underlying query executes every time, which may impact performance with large datasets.
You can’t store data in a view itself — it’s not materialized by default.
When to use CTEs vs. views
The takeaway:
Choosing between a CTE and a view comes down to your needs for reusability, persistence, and performance:
Use a CTE for ad-hoc, complex queries that you don't need to reuse. They enhance readability and are essential for recursion.
Use a view when you need to abstract and reuse complex query logic across multiple reports or applications. They are ideal for simplifying access and implementing security.
Use a materialized view if you have a complex view that is queried frequently, but the data does not need to be real-time. This provides significant performance benefits by pre-computing the results.
