Articles on Sql
Last updated: 2022/11/16
Top deep-dives on Sql
Building a DB from scratch will give you a pretty good idea of how it works, and this series by João Henrique Machado Silva does exactly that. João also explains a lot of the decisions he makes, which gets a thumbs up from me.
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.
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.
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.
Vicent Marti delves into PlanetScale's Boost, which is a new design for a caching architecture that is more performant than a cache and more efficient than a materialized view.
- In essence they're pushing all of the potential work done when reading data to writing data
- Traditional materialized views are expensive because they need to generate every row on every update
- Boost is essentially a partial materialized view created on only updated data