Recursive CTEs are almost never what you want. They're the database equivalent of pointer-chasing in code. If you design your schema such that you need a recursive CTE to query it, be prepared for bad performance unless your CTE only ever does a handful of iterations over a handful of rows.

I like paths for representing hierarchy, but closure tables can also be a good idea, depending on what you're modelling and how you query it.

> They're the database equivalent of pointer-chasing in code.

That's the general case, but more specific CTE queries can be optimized, e.g. by adding database indexes. Recent versions of Postgres have greatly improved wrt. not making CTE's overly inefficient.

I said recursive CTEs. CTEs being an optimization barrier is a different issue - and often desirable with Postgres with its lack of optimization hints. Hence with not / materialized etc.

Yeah, and it's a straightforward standardized way to solve this.

But I still think it's useful to know the flattening idea. I've seen it show up other places like map reduce. It's a useful general idea imo.