5 ms·
Very well written and detailed article, with the caveat that they never mention a use case they consider legitimate. Does anyone here have any uses? I could ima
by BeefySwain 4y ago
Very well written and detailed article, with the caveat that they never mention a use case they consider legitimate. Does anyone here have any uses? I could imagine some sort of ETL type tasks which are transient could make sense. Thoughts?
- anarazel 4y agoSession state is a very common one, where the journaling overhead also can be particularly pronounced (much easier to hide in batch workloads).
- jpalomaki 4y agoI've been using these for some larger analytical queries that run in batch fashion (typically something that's done like once or couple of times per day). I find it hard to get good performance from large queries with CTEs. Often, it's much easier (and uglier) to just split them to multiple steps, create intermediate tables for each step and add necessary indexes. Temporary tables are then of course even better, since usually you don't want to have these left around. But temporary tables are also unlogged.
- avianlyric 4y agoWhich versions of Postgres have you been running your large CTE based queries with? Up until Postgres 12, all CTE were materialised before their results were used, effectively making every CTE a temporary table. In Postgres 12 onwards, for side-effect free CTE that are only referenced once, Postgres will take constrains from the parent query, and push them into the CTE, reducing the amount of data they need process, and allowing better use of indexes. OOI have you tried converting your CTEs into sub-queries as an experiment to see if they’re faster? Even in Postgres 12 onwards, sub-queries and CTEs are treated the same by the query planner, and you can get some surprising differences in performance.
- RandomBK 4y agoI can't speak to Parent's situation, but temp tables are common practice in OLAP workloads across many database engines (personal experience with Postgres, Presto, Spark, Netezza). At a certain level of query complexity, no query planner is able to accurately predict the characteristics of sub-queries or CTE, resulting in query plans that are ill-suited to the problem. More often than not, materializing CTEs inside a large query into 2-3 temporary tables results in order-magnitude performance as the database now knows exactly how many rows its dealing with, stats on # of nulls, etc.
- avianlyric 4y agoCertainly don’t disagree, I’m well acquainted with the limits of the query planner. Especially when dealing with high complexity queries. My broader point is that CTEs in Postgres are more nuanced than they appear at first blush. They’re frequently presented as simply a method of writing cleaner, more readable SQL. The fact that CTEs receive special treatment by the planner is often missed, along with the fact that two functionally identical queries, one using sub-queries, the other CTEs, can have wildly different performance characteristics.
- candiddevmike 4y agoI use them for caching in lieu of redis, they work well and by using triggers I can ensure they're never stale
- hyperman1 4y agoI use them for importing from files to tables. Run some cleanup and validation, before moving the data to its final destination. If pg crashes halfway, I still have the file imported and restart from zero. A second use is importing httpd combined logs and doing some analysis when finding out which chains of http calls caused some kinds of behaviour. When done, the tables get deleted. It allows easy ad hock queries, correlating with monitoring tables, indexing... I used to write some python scripts, but pg did better than expected, and this way of working stuck somehow.
- ilyt 4y agoSession store is obvious enough use case. Anything that's essentially a cache or copy of data existing somewhere else
- ghiculescu 4y agoFaster test suite, on a CI only database.