This is a great idea if you disable materialization (which the author of this post does not mention).
Yes, materialization is fine for small hardcoded values in the post, but for most other lookup tables, it's definitely not fine.
For example, you read this article and think great, I'll make a lookup table to map various ids to be linked across tables.
with ids as (
select u.user_id, u.token, s.customer_id
from users u
join stripe_customer s on s.user_id = u.user_id
),
user_impressions as (
select i.*, ids.id
from impressions i join ids using token
),
payments as (
select p.*, ids.id from payments p
join ids using customer_id
),
select sum(i.views),
sum(i.clicks),
sum(p.amount)
from payments p
join user_impressions i using user_id
where user_id = $1
group by date;
You think, great, this gets some stats for one user since its filtered at the end, and with indexes on stripe_customer(user_id), user_impressions(token), and payments(customer_id), this will be really fast and efficient!
Nope.. since ids is referenced more than once in this query, in Postgres this causes the ids CTE to be materialized onto disk with no indexes. So not only does it take up lots of extra space on disk to store all the users in your system, to join user_impressions and payments you have to O(N) search across the ids dataset. No indexes on a materialized table.
It would be a lot faster to join to users or stripe_customer or both in a loop with indexes than O(N) search through all the users in your system.
This is particularly dangerous because if you remove the payments part, ids only has one reference, so Postgres doesn't materialize the table, and everything is fast.
This can be fixed by adding WITH foobar AS NOT MATERIALIZED to the CTE syntax. IMO it should be a syntax error to not specify AS MATERIALIZED or AS NOT MATERIALIZED.. The default has too many potential performance problems and folks should have to think about whether they want materialization or not.