PostgreSQL CTEs: Recursive Queries

Tip: Basic CTE

WITH active_users AS (
    SELECT id, name FROM users WHERE active = true
)
SELECT * FROM posts WHERE user_id IN (SELECT id FROM active_users);

Makes complex queries readable.

Gotcha: CTEs Are Optimization Fences

PostgreSQL materializes CTEs — they run once and store results. This can be slower than a subquery for simple cases.

Tip: Recursive CTE for Hierarchies

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 as level
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, t.level + 1
    FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY level;

Traverses unlimited-depth trees.

Gotcha: Infinite Loops in Recursive CTEs

If your data has circular references, the recursive CTE runs forever. Add a depth limit.

Tip: Multiple CTEs in One Query

WITH a AS (...), b AS (...), c AS (...)
SELECT * FROM a JOIN b ON ... JOIN c ON ...;

Chain multiple CTEs for complex data pipelines.

Gotcha: MATERIALIZED Hint (PG 12+)

WITH a AS MATERIALIZED (...)

Forces materialization. Without it, PostgreSQL may inline the CTE.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Common Table Expressions (CTEs) are PostgreSQL's answer to readable complex queries. I've used recursive CTEs to query hierarchical data (category trees, org charts) that would require multiple queries with Eloquent. But CTEs are optimization fences in PostgreSQL — the planner treats each CTE as a separate optimization unit, which can prevent join elimination. For performance-critical queries, test with and without CTE inlining (PostgreSQL 12+ inlines CTEs automatically).

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)