Welcome to my PinkLetter. A short, weekly, technology-agnostic, and pink newsletter where we cultivate timeless skills about web development.
My Ramblings This Week
What’s the thing you suck the most at?
I’d say SQL is high on my list. I have zero problems with simple queries, but when things get complex and require good performance, I start sweating.
I don’t know how to approach a complex query. The declarative nature of SQL makes it challenging to transfer the decompose-recompose wisdom I learned in functional programming.
A couple of weeks ago, I started tweaking a report. Its data comes from seven tables, part of the query is in SQL, and part is in Ruby. I don’t blame the original author: the database schema is intricated.
The query retrieves data un-nested and loops through it to re-nest with additional information. To make it go fast enough, the endpoint is paginated. It’s one of those cases where pagination wasn’t required by UX but employed to hide performance issues.
I’ll admit it. I tried several times, but I never got anywhere. Each time, I found myself in front of a shitty solution that didn’t promise to get anywhere. I was not confident in my skills, so I tried to do as much as possible in Postgres and then course correct in Ruby. But the nested schema, the complex query, and the ORM didn’t help identify a good split. Plus, in Ruby, I would have needed a ton of triple-nested loops, which didn’t look performant.
I reached a point of despair on the n-th attempt. I needed to start over again, I was way out of time, and I still wasn’t sure how to proceed. But I realized each failed try taught me something new: I got more intimate with the database schema, I discovered COALESCE would allow me to move some Ruby to Postgres, and I got a long list of things that don’t work.
Fuck it. I’m going to have similar problems in this project and later in my career. Let’s get after it. I decided to write the entire logic in Postgres without any support from neither the ORM nor Ruby. The fact that I had half a day to figure this out didn’t stop me.
I went heavy on Common Table Expressions, hoping they would help with decomposing. At every step, I manually tested the solution. For a while, this strategy worked. I was excited to see the data structure getting together statement by statement. Using json_build_object and json_agg I was building the final JSON directly in Postgres. I got so close, and then I hit a roadblock.
I got stuck on one query. I didn’t even know if what I was trying to do was even possible. I froze and thought I was sitting on a failed attempt—yet another one. So I did what every desperate developer would do: I tried every possible combination of SQL keywords I knew. My despair grew with every error Postgres threw at me. But right when I was ready to give up, something happened.
The query run and the result was correct. I wanted to stand up and start screaming. But I couldn’t wait to do a speed test!
The new solution is five to ten times faster, it’s not paginated, it requires one HTTP request instead of two, and puts less pressure on the system memory. In fact, it’s so fast I’ve been keeping a tab open in my phone to fidget with it whenever I feel like celebrating.
Yay!
By the way, I’m still looking for resources on how to write complex SQL queries. Do you have any suggestions?
Elsewhere on the Web
Take your first steps with Rust by Microsoft
Interested in learning a new programming language that’s growing in use and popularity? Start here! Lay the foundation of knowledge you need to build fast and effective programs in Rust.
In this learning path, you’ll:
- Install the tools you need to write your first lines of Rust code.
- Learn basic concepts in Rust.
- Learn how to handle errors.
- Manage memory in Rust.
- Use generic types and traits.
- Set up modules for packages and crates.
- Write and run automated tests.
- Create a command-line program.
(Riccardo: You should definitely give Rust a try. I did the first ten days of the Advent Of Code with it and had a ton of fun.)
Deep dive in CORS: History, how it works, and best practices by Ilija Eftimov
Learn the history and evolution of same-origin policy and CORS, understand CORS and the different types of cross-origin access in depth, and learn (some) best practices.
(Riccardo: I bet you are going to learn a couple of new things on CORS.)
Speeding up SQL queries by orders of magnitude using UNION by Ben Levy and Christian Charukiewicz
One of the most common cases where SQL query performance can degrade significantly is in a diamond shaped schema, where there are multiple ways of joining two tables together. In such a schema, a query is likely to use OR to join tables in more than one way, which eliminates the optimizer’s ability to create an efficient query plan.
(Riccardo: I haven’t used this technique this week, but the article is perfect for this email’s topic.)
Proposal: Downward assignments by Yusuke Endoh
Rightward assignments have been introduced since 3.0. To be honest, I’m not a big fan of the syntax because it does not add a new dimension to Ruby. Why don’t we bring Ruby to the next dimension?
(Riccardo: Already oldie, but goodie.)