max.engineer archives search feed

Postgres WITH Optimization Fence

Came across this brief and insightful exchange in Hacker News comments.

vector_spaces:

My understanding is that CTEs are an optimization fence in some databases so aren’t great for web application queries? I think that this is no longer the case in Postgres, but I recall learning that like ~6 years ago when working with other databases. Or is that total nonsense?

zomgwat:

As of PostgreSQL 12, whether the optimization fence is used or not is controlled with MATERIALIZED and NOT MATERIALIZED.

srcreigh:

By default it’s an optimization fence if a CTE is referenced 2+ times, and not an optimization fence if referenced 0-1 times. The MATERIALIZED and NOT MATERIALIZED overrides the default.

aarondf:

What is an optimization fence?

simcop2387:

The database and query planner can’t look past it to see that it can simplify the operations that the query will do.

Tags
postgres
Date
July 21, 2023
Up next
Finding good names My go-to resources for finding good names for variables, functions, and everything else that needs naming. thesaurus — nicely laid-out
Previously
SaveIf concern for Rails optimistic locking without lock_version I wrote a proposal for Rails to implement an optimistic locking feature that doesn’t require lock_version column. There are some use cases where