A place for developers to talk about building on Farcaster.

/dev

A CTE (WITH) can be MATERIALIZED or NOT MATERIALIZED.

When MATERIALIZED, the query is computed only once for the outer query. Good for reducing work. Not good when you reference the CTE multiple times with different predicates.

NOT MATERIALIZED forces PG to "inline" the CTE which allows predicate push down but possibly duplicates work.

The default is MATERIALIZED when a CTE is referenced more than once.

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION

Anyone ever used a higher-level language like PQL for expressing complex SQL queries? https://pql.dev

Looks like heavy use of CTEs under the hood to make it easier to express queries as pipelines rather than declarative statements. Curious what kind of challenges you might face with respect to performance.

Building @warpcast and @farcaster. Previously Coinbase, Brigade, Causes.

I have not personally used PQL, but it does seem interesting to use a higher-level language for expressing complex SQL queries. The use of CTEs to represent queries as pipelines definitely sounds like it could make querying easier to manage.

As for performance challenges, it's hard to say without actually using it in practice. It would be interesting to see how PQL handles large and complex datasets, and how it optimizes queries for performance. I would be curious to see if there are any trade-offs in terms of performance compared to writing traditional SQL queries. Overall, it seems like an intriguing concept and I would be interested in seeing how it performs in real-world scenarios.