If anyone is interested, Winand's book "SQL Performance Explained" [1] really goes deep into understanding and optimizing SQL queries and improving performance of SQL queries.<p>[1] <a href="https://sql-performance-explained.com/?utm_source=winand.at&utm_campaign=for-developers&utm_medium=web&utm_content=sql-performance-explained" rel="nofollow">https://sql-performance-explained.com/?utm_source=winand.at&...</a>
See also: <a href="https://winand.at/conferences/past" rel="nofollow">https://winand.at/conferences/past</a><p>Includes an updates version of the first slideset: <a href="https://modern-sql.com/slides/ModernSQL-2019-05-30.pdf" rel="nofollow">https://modern-sql.com/slides/ModernSQL-2019-05-30.pdf</a>
Markus's site <a href="https://modern-sql.com/" rel="nofollow">https://modern-sql.com/</a> is fantastic, I've learned a ton of useful tricks from it.
I recently found out that SQLite added support for FILTER aggregate clauses in version 3.30.0, released 2019-10-04: <a href="https://til.simonwillison.net/sqlite/sqlite-aggregate-filter-clauses" rel="nofollow">https://til.simonwillison.net/sqlite/sqlite-aggregate-filter...</a>
Great post: I found the problem/solution approach super helpful. I've used some of the more recent features, but have missed grouping sets, recursive CTEs, and MATCH_RECOGNIZE. hope to put these in application someday.
I really don't like this whole "OFFSET" kills performance thing that you see everywhere because it's only relevant for huge sets, think millions of rows.<p>And then, there's a reason why people are using offsets, cursors are usually not a good alternative and the after_id pattern is not flexible and don't allow to go to a random page number...<p>But still, some people are preaching for alternatives that are not actually solving the same problem as offsets.