5 ms·

> 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

> 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 https://www.postgresql.org/docs/12/tutorial-window.html [1] https://www.postgresql.org/docs/12/queries-with.html https://www.postgresql.org/docs/12/queries-with.html

cjf4 7y ago
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.
Dowwie 7y ago
This post I made almost six years ago to the day on SO remains relevant: https://stackoverflow.com/questions/20979831/recursive-query-used-for-transitive-closure https://stackoverflow.com/questions/20979831/recursive-query... I hope it helps you.
- Mister_Snuggles 7y ago
  This is great - thank you! This is almost exactly what I'd like to do with recursive CTEs in one of my current projects.
LandR 7y ago
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.
mongol 7y ago
SQL seems to be the most long-lasting skill in the IT industry. Definitely worthwhile to learn well.
kragen 7y ago
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.
- shantly 7y ago
  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.
  - deleted 7y ago
    [deleted]
  - UK-Al05 7y ago
    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.
  - kragen 7y ago
    My past experience generally agrees with yours, but I'm not confident that it generalizes to recursive CTEs.

cjf4 7y ago

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.

Dowwie 7y ago

This post I made almost six years ago to the day on SO remains relevant: https://stackoverflow.com/questions/20979831/recursive-query-used-for-transitive-closure https://stackoverflow.com/questions/20979831/recursive-query... I hope it helps you.

Mister_Snuggles 7y ago
This is great - thank you! This is almost exactly what I'd like to do with recursive CTEs in one of my current projects.

LandR 7y ago

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.

mongol 7y ago

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

kragen 7y ago

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.

shantly 7y ago
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.
- deleted 7y ago
  [deleted]
- UK-Al05 7y ago
  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.
- kragen 7y ago
  My past experience generally agrees with yours, but I'm not confident that it generalizes to recursive CTEs.