Articles on Sql
Last updated: 2022/06/15
Top deep-dives on Sql
SQL queries are a great way to get data from a database (durr). I like to abstract away more complicated ones to the database layer, because it just looks cleaner and is more understandable to me. One great tool for building more complicated queries is CTEs. Preetam Jinka presents how you can use these for your queries, using a real life example inspired by Github issues.
Subqueries are a pretty intuitive way of connecting data from different tables in SQL. Personally I use them quite a lot, but I never realized the implications. Jamie Brandon's article underlines the potential inefficiencies of subqueries, and educates on the approaches used by different databases to better optimize queries. Pretty rad stuff.
A typical problem in programming is when two processes need to use the same resource, at the same time. Fortunately it's a well documented problem, and the internet is full of information on how you can avoid it. In this short article by Ke Pi, we're brought the problem as it relates to databases. Ke Pi covers how SQL blocking happens and how you can troubleshoot it for your own database.
It's not a bad ideas to get to know how SQL databases work. A lot of projects use them. In this article of an extensive series, Pamela Mooney breaks down how the SQL sserver works and how to tune queries.
Doing a meta analysis of merges/commits to a software project can tell you a lot about the characteristics of the code. Łukasz Langa's concise article does precisely that, by analyzing the CPython repo. Łukasz presents the hot parts of the codebase, who is contributing and where, and stats for merging a PR. Not super technically heavy, but some interesting data to explore.
Vlad Mihalcea demonstrates how small issues in Java concurrency implementation can lead to big financial problems.
Databases access patterns are common throughout many applications, because most applications have similar requirements; some data should be accessed a lot, and some data should barely be accessed. How do you go about optimizing a system for this when you usually don't explicitly state where the boundary is? In this informative article, Arpit Bhayani presents the caching system used by Postgres to solve common database access patterns using multiple queues.
After writing SQL for a while, you start realizing that some queries take A LOT longer than others, especially when you're using CTEs or subqueries. Being a proponent of "do first, read later", I've just developed an intuitive feel. Well now I have an explantion, because Jamie Brandon explains the effect nested loops have on SQL queries, how other databases attempt to replace them, and a deep dive into the approaches of CockroachDB and Materialize.
Have you had to setup or refactor three different databases recently? No? Well I have, and let me tell you what, it's a lot easier to do it when you have an idea of what you're doing. Fortunately if you don't, but are planning on pulling a DB hat-trick anytime soon, Pamela Mooney's article is for you. In this series, Pamela covers all the essentials of being the big bad database admin, but in this one specifically she illuminates a plethora of backup and restore strategies that'll save your ass when everything goes to shit.
As a developer, I sometimes forget about the fact that most people don't have a wired internet connection and powerful computer that make minced meat out of most tasks. But that's an important thing not to forget about, because it can have a major impact on how others experience what we create. Lukas Eder's article gives a couple of examples on how you could simulate exaggerated latency in your database, to explore what it feels like to use your creation for people with a lower hardware potential.
Modern web development technology makes it possible (and fairly easy) to do some very cool implementations. Are they always absolutely necessary? Not really, but humans love fun things, and making the web fun is great. In this ultimate article of a five part series, Lydia Cupery does the fairly simple job of replacing fake data with real data queried from a GraphQL database. However, I'm sharing this post because the series as a whole is fairly interesting, so be a weirdo like me and read the end first, then start it from the beginning!
Jonathan Lewis illuminates some common database query optimizations one can make when the Oracle Optimizer is being underwhelming.