Optimizing PostgreSQL Queries: Solving the N+1 Problem with CTEs

May 8, 2025

—

The N+1 query problem is one of the most common performance bottlenecks in database-driven applications. It occurs when your code executes one initial query to retrieve a set of records, followed by N additional queries to fetch related data for each of those records. For large datasets, this approach can severely impact performance and scalability.

In this post, we’ll explore how to leverage PostgreSQL’s Common Table Expressions (CTEs) to solve the N+1 query problem efficiently. We’ll walk through real-world examples, performance comparisons, and best practices to help you optimize your database queries.

Understanding the N+1 Problem

Let’s start with a classic example. Imagine we have a simple blog application with the following tables:

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id),
    user_name VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Now, let’s say we want to display a list of the 10 most recent posts, each with its author and the count of comments. The N+1 problem would manifest like this in pseudocode:

# Query 1: Get the 10 most recent posts
posts = db.execute("SELECT * FROM posts ORDER BY published_at DESC LIMIT 10")

# For each post, execute 2 more queries
for post in posts:
    # Query 2 to N+1: Get the author for each post
    post.author = db.execute("SELECT * FROM authors WHERE id = %s", post.author_id)
    
    # Query N+2 to 2N+1: Get comment count for each post
    post.comment_count = db.execute("SELECT COUNT(*) FROM comments WHERE post_id = %s", post.id)

This approach results in 1 + 10 + 10 = 21 separate database queries. As the number of posts increases, the number of queries grows linearly, leading to poor performance.

Introducing Common Table Expressions (CTEs)

Common Table Expressions, or CTEs, are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement in PostgreSQL. They’re defined using the WITH clause and can help solve the N+1 problem by allowing us to join complex subqueries efficiently.

Here’s the basic syntax of a CTE:

WITH cte_name AS (
    -- CTE query definition
    SELECT column1, column2 FROM table
)
-- Main query that uses the CTE
SELECT * FROM cte_name WHERE condition;

Solving the N+1 Problem with CTEs

Let’s rewrite our blog example using CTEs to eliminate the N+1 problem:

WITH recent_posts AS (
    SELECT id, author_id, title, content, published_at
    FROM posts
    ORDER BY published_at DESC
    LIMIT 10
),
comment_counts AS (
    SELECT post_id, COUNT(*) AS comment_count
    FROM comments
    WHERE post_id IN (SELECT id FROM recent_posts)
    GROUP BY post_id
)
SELECT 
    p.id, p.title, p.content, p.published_at,
    a.id AS author_id, a.name AS author_name, a.email AS author_email,
    COALESCE(c.comment_count, 0) AS comment_count
FROM recent_posts p
JOIN authors a ON p.author_id = a.id
LEFT JOIN comment_counts c ON p.id = c.post_id
ORDER BY p.published_at DESC;

With this approach, we’ve reduced 21 queries to just 1 complex query. Let’s break down how this works:

The recent_posts CTE gets our 10 most recent posts
The comment_counts CTE calculates the comment count for each of these posts
Our main query joins these CTEs with the authors table to return all the data we need

Performance Comparison

Let’s look at the performance difference between the N+1 approach and our CTE solution:

N+1 Approach (21 queries)

Initial query: ~5-10ms
10 author queries: ~5ms each
10 comment count queries: ~5ms each
Total time: ~105-160ms

CTE Approach (1 query)

Single complex query: ~15-30ms
Total time: ~15-30ms

That’s a performance improvement of 70-80%! And this gap only widens as your dataset grows.

Advanced CTE Techniques for N+1 Problems

Recursive CTEs for Hierarchical Data

One of the most powerful features of PostgreSQL CTEs is their ability to be recursive. This is especially useful for hierarchical data like nested comments or organizational charts:

WITH RECURSIVE comment_tree AS (
    -- Base case: top-level comments for a specific post
    SELECT id, post_id, user_name, content, created_at, 0 AS depth, ARRAY[id] AS path
    FROM comments
    WHERE post_id = 123 AND parent_comment_id IS NULL
    
    UNION ALL
    
    -- Recursive case: replies to comments
    SELECT c.id, c.post_id, c.user_name, c.content, c.created_at, ct.depth + 1, ct.path || c.id
    FROM comments c
    JOIN comment_tree ct ON c.parent_comment_id = ct.id
    WHERE c.post_id = 123
)
SELECT * FROM comment_tree
ORDER BY path;

This query retrieves an entire comment thread hierarchy in a single query, avoiding the N+1 problem when displaying nested comments.

CTEs with Window Functions

We can combine CTEs with window functions to solve more complex N+1 problems:

WITH post_stats AS (
    SELECT 
        post_id,
        COUNT(*) AS comment_count,
        MAX(created_at) AS last_comment_at
    FROM comments
    GROUP BY post_id
),
author_stats AS (
    SELECT 
        author_id,
        COUNT(*) AS total_posts,
        SUM(views) AS total_views,
        ROW_NUMBER() OVER (ORDER BY SUM(views) DESC) AS popularity_rank
    FROM posts
    GROUP BY author_id
)
SELECT 
    p.id, p.title, p.published_at,
    a.name AS author_name,
    ast.total_posts, ast.total_views, ast.popularity_rank,
    ps.comment_count, ps.last_comment_at
FROM posts p
JOIN authors a ON p.author_id = a.id
LEFT JOIN post_stats ps ON p.id = ps.post_id
LEFT JOIN author_stats ast ON p.author_id = ast.author_id
WHERE p.published_at > NOW() - INTERVAL '30 days'
ORDER BY p.published_at DESC;

This query retrieves posts with comprehensive author and comment statistics in a single query, which would otherwise require numerous individual queries.

Materialized CTEs for Better Performance

For very complex queries, PostgreSQL allows materialized CTEs, which compute and store the results instead of recalculating them for each reference:

WITH MATERIALIZED user_activity AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS session_count,
        COUNT(*) AS action_count,
        MAX(timestamp) AS last_action
    FROM user_actions
    WHERE timestamp > NOW() - INTERVAL '7 days'
    GROUP BY user_id
)
SELECT * FROM user_activity WHERE session_count > 5;

The MATERIALIZED keyword tells PostgreSQL to compute the CTE once and store it in memory, which can significantly improve performance for CTEs referenced multiple times in a complex query.

Best Practices for Using CTEs to Solve N+1 Problems

1. Identify N+1 Patterns in Your Code

Look for loops that execute queries or ORM code that might be generating additional queries. Tools like Django Debug Toolbar, Rails Mini-Profiler, or database query logs can help identify these patterns.

2. Use EXPLAIN ANALYZE to Verify Performance

Always verify your query optimizations with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
WITH recent_posts AS (
    -- CTE definition
)
-- Main query

This will show you the execution plan and actual runtime statistics.

3. Be Careful with Large Datasets

CTEs in PostgreSQL are optimization fences prior to PostgreSQL 12, meaning the optimizer can’t push conditions into the CTE. For very large datasets, test whether a subquery might perform better:

-- Using a subquery instead of a CTE
SELECT 
    p.id, p.title, a.name AS author_name,
    (SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id) AS comment_count
FROM posts p
JOIN authors a ON p.author_id = a.id
WHERE p.published_at > NOW() - INTERVAL '30 days'
ORDER BY p.published_at DESC;

4. Balance Query Complexity vs. Multiple Queries

Sometimes, a middle ground between N+1 queries and a single complex query is the best approach. For example, you might execute two or three well-optimized queries instead of one extremely complex query or dozens of simple ones.

5. Consider JSON Aggregation for Complex Related Data

When you need to retrieve hierarchical or nested related data, consider using JSON aggregation:

WITH post_data AS (
    SELECT 
        p.id, p.title, p.content, p.published_at,
        a.id AS author_id, a.name AS author_name, a.email AS author_email
    FROM posts p
    JOIN authors a ON p.author_id = a.id
    WHERE p.published_at > NOW() - INTERVAL '30 days'
)
SELECT 
    pd.*,
    COALESCE(
        (SELECT jsonb_agg(
            jsonb_build_object(
                'id', c.id,
                'user_name', c.user_name,
                'content', c.content,
                'created_at', c.created_at
            )
        )
        FROM comments c
        WHERE c.post_id = pd.id),
        '[]'::jsonb
    ) AS comments
FROM post_data pd
ORDER BY pd.published_at DESC;

This retrieves posts with their authors and all comments as a JSON array in a single query.

Real-world Example: Optimizing a Dashboard Query

Let’s look at a real-world example of using CTEs to optimize a dashboard query:

WITH daily_stats AS (
    SELECT 
        DATE_TRUNC('day', created_at) AS day,
        COUNT(*) AS total_posts,
        COUNT(DISTINCT author_id) AS unique_authors
    FROM posts
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY DATE_TRUNC('day', created_at)
),
engagement_stats AS (
    SELECT 
        DATE_TRUNC('day', c.created_at) AS day,
        COUNT(*) AS total_comments,
        COUNT(DISTINCT c.post_id) AS posts_with_comments,
        AVG(LENGTH(c.content)) AS avg_comment_length
    FROM comments c
    JOIN posts p ON c.post_id = p.id
    WHERE c.created_at >= NOW() - INTERVAL '30 days'
    GROUP BY DATE_TRUNC('day', c.created_at)
),
top_authors AS (
    SELECT 
        a.id, a.name,
        COUNT(*) AS post_count,
        SUM(
            (SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id)
        ) AS total_comments
    FROM authors a
    JOIN posts p ON a.id = p.author_id
    WHERE p.created_at >= NOW() - INTERVAL '30 days'
    GROUP BY a.id, a.name
    ORDER BY total_comments DESC
    LIMIT 5
)
SELECT 
    ds.day,
    ds.total_posts,
    ds.unique_authors,
    COALESCE(es.total_comments, 0) AS total_comments,
    COALESCE(es.posts_with_comments, 0) AS posts_with_comments,
    COALESCE(es.avg_comment_length, 0) AS avg_comment_length,
    (
        SELECT jsonb_agg(
            jsonb_build_object(
                'id', ta.id,
                'name', ta.name,
                'post_count', ta.post_count,
                'total_comments', ta.total_comments
            )
        )
        FROM top_authors ta
    ) AS top_authors
FROM daily_stats ds
LEFT JOIN engagement_stats es ON ds.day = es.day
ORDER BY ds.day DESC;

This single query provides comprehensive dashboard statistics that would otherwise require dozens of separate queries.

Conclusion

The N+1 query problem is a common performance bottleneck, but PostgreSQL’s Common Table Expressions provide an elegant solution. By combining multiple queries into a single, well-structured query, you can dramatically improve your application’s performance.

Remember these key takeaways:

Use CTEs to organize complex queries and eliminate N+1 problems
Use recursive CTEs for hierarchical data
Combine CTEs with window functions and JSON aggregation for advanced scenarios
Always test performance with EXPLAIN ANALYZE
Consider the tradeoffs between query complexity and multiple simpler queries

By applying these techniques, you can ensure your PostgreSQL-backed applications remain fast and scalable even as your data grows.