Hacker News Reader

Comment by Mister_Snuggles | Hacker News Reader

> Learn how to really use a relational database, relational data modeling, and SQL.

I have to second this. There is so much power in relational databases that is untapped by most developers. The best part about this is that, for the most part, this type of knowledge can apply to multiple databases.

Some specific things that I want to gain a deeper understanding of are window functions[0] and recursive CTEs[1]. In particular, I've used window functions to identify peaks in sensor data (e.g., finding spikes in temperature, water level, etc), which would otherwise require iterating through rows and maintaining a bunch of state. I've never actually written a recursive CTE, but I'm pretty sure it would simplify virtually anything dealing with a hierarchy.

[0] https://www.postgresql.org/docs/12/tutorial-window.html

[1] https://www.postgresql.org/docs/12/queries-with.html

View on HN

This post I made almost six years ago to the day on SO remains relevant: https://stackoverflow.com/questions/20979831/recursive-query...

I hope it helps you.

This is great - thank you!

This is almost exactly what I'd like to do with recursive CTEs in one of my current projects.

SQL seems to be the most long-lasting skill in the IT industry. Definitely worthwhile to learn well.

In the first 15+ years of my career I never used or understood recursive ctes in sql. Then finally learned it and used them multiple times in last year or so.

They can be incredibly helpful once you grok them! And recognise when they can be used.

I'm not sure recursive CTEs simplify things. Yes, they allow you to express Turing-complete computations in SQL, including indefinite and parallel iteration. But if you weren't going to express those computations in SQL, you'd probably express them in Python or Lua or JS. Is doing them in SQL really going to be an improvement? So far I have not impressed with the results.

My experience has been, especially if your application layer's in a scripting language, pushing as much data-fiddling to the DB as possible will save you serious performance headaches down the road, even if the application layer implementation looks OK at first. They're all really slow and memory-inefficient, and often moving that stuff to the application layer also means more queries (else, typically, why not do it in the DB?) which means more network latency, which an be a real killer. In a lot of cases fixing the performance means, at the very lease, re-implementing a bunch of what your DB already does to support fast & efficient data manipulation.

I've also seen the application change on top of the DB way more than I've seen the reverse, so I'm inclined to avoid putting data manipulation in the application when possible. That way it's there, for free, when we need a second application to access the same thing, or when we break off some chunk of a program into its a separate service and re-write it in Go because it turns out to be a performance bottleneck, or WTF ever.

The problem is when the database becomes the performance bottleneck.

Scaling a database is incredibly difficult, and requires a lot of expertise.

Some of the biggest engineering projects I've been a part of is removing a central db that everybody connects to in large companies.

My past experience generally agrees with yours, but I'm not confident that it generalizes to recursive CTEs.

Third- for an analysis project, I had to look ahead to the next transaction. Tried a bunch of implementations, ranging from offset self joins to iterators to recursion.

Turns out the fastest and simplest solution, by far, was a mix of window functions and CTEs directly in the database.

Reply on news.ycombinator.com