4 ms·

CTEs are basically temporary views and tables. I’d argue that the extra tables with duplicate data aren’t a space / cpu trade-off as much as they are a data ma

by aidos 3y ago

CTEs are basically temporary views and tables. I’d argue that the extra tables with duplicate data aren’t a space / cpu trade-off as much as they are a data management trade-off. There are cases for duplicating data in the system but I’d leave that to indexes and generated columns unless you really need to materialise it in a different structure.

mulmen 3y ago
> CTEs are basically temporary views and tables. Not in any system I have ever used. CTEs aren't materialized so they're not tables. Tables (or materialized views if you prefer) are literally space/CPU tradeoffs. I have a data pipeline that combines website logs into a table before I join it with product data. If you tried to do both in one query then the DB falls over. You seem to be coming at this from a OLTP perspective but in OLAP data "duplication" is the name of the game. But what naively looks like wasteful duplication is actually creating coherent analytical models from disparate data sources. This leads to much simpler analytical queries.
- aidos 3y ago
  I agree with your further example cases (they were added after my comment). I was thinking more of the original post where someone was complaining about readability when you have joins and subqueries - which is the usecase for CTEs.
  - mulmen 3y ago
    Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I also use views to create a ~"feature store" that pre-joins the underlying star schema. I can then write "simple" analytical queries that utilize CTEs. Those CTEs tend to not have joins and they tend to only apply relevant filters so the final select is clear and concise.
    - aidos 3y ago
      > CTEs aren't materialized so they're not tables. You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance). with x as materialized (select ..) select ...
      - deleted 3y ago
        [deleted]
      - slt2021 3y ago
        neat trick! sometimes you absolutely need to materialize your CTE, especially if it is one big computation and you refer it 4-5 times downstream.
        mulmen 3y ago
        According to the docs [1] this is not necessary: > All queries in the WITH list are computed. These effectively serve as temporary tables that can be referenced in the FROM list. A WITH query that is referenced more than once in FROM is computed only once, unless specified otherwise with NOT MATERIALIZED. [1]: https://www.postgresql.org/docs/15/sql-select.html https://www.postgresql.org/docs/15/sql-select.html
      - deleted 3y ago
        [deleted]

by aidos 3y ago

mulmen 3y ago

> CTEs are basically temporary views and tables. Not in any system I have ever used. CTEs aren't materialized so they're not tables. Tables (or materialized views if you prefer) are literally space/CPU tradeoffs. I have a data pipeline that combines website logs into a table before I join it with product data. If you tried to do both in one query then the DB falls over. You seem to be coming at this from a OLTP perspective but in OLAP data "duplication" is the name of the game. But what naively looks like wasteful duplication is actually creating coherent analytical models from disparate data sources. This leads to much simpler analytical queries.

aidos 3y ago
I agree with your further example cases (they were added after my comment). I was thinking more of the original post where someone was complaining about readability when you have joins and subqueries - which is the usecase for CTEs.
- mulmen 3y ago
  Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I also use views to create a ~"feature store" that pre-joins the underlying star schema. I can then write "simple" analytical queries that utilize CTEs. Those CTEs tend to not have joins and they tend to only apply relevant filters so the final select is clear and concise.
  - aidos 3y ago
    > CTEs aren't materialized so they're not tables. You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance). with x as materialized (select ..) select ...
    - deleted 3y ago
      [deleted]
    - slt2021 3y ago
      neat trick! sometimes you absolutely need to materialize your CTE, especially if it is one big computation and you refer it 4-5 times downstream.
      - mulmen 3y ago
        According to the docs [1] this is not necessary: > All queries in the WITH list are computed. These effectively serve as temporary tables that can be referenced in the FROM list. A WITH query that is referenced more than once in FROM is computed only once, unless specified otherwise with NOT MATERIALIZED. [1]: https://www.postgresql.org/docs/15/sql-select.html https://www.postgresql.org/docs/15/sql-select.html
    - deleted 3y ago
      [deleted]