Engineering Notes

When a database query is slow, the first question people usually ask is: “Is there an index on that column?” Indexes are central to database performance, yet they’re often treated as a magic switch. Why do they help? And why does a query sometimes ignore an index that’s clearly there? In this post I’ll answer both questions using real EXPLAIN output from the WordPress database (MariaDB) that runs this blog. An index is a book’s index The classic analogy holds up. To find every page that mentions “transaction” in a thick technical book, you either read every page, or you go to the index at the back, find the entry, …

Fast-loading websites often share a trick you never see: the server compresses HTML, CSS, and JavaScript right before sending them, using a format like gzip or Brotli, and the browser decompresses them before rendering the page. None of this exchange shows up on screen, but it has a large effect on how much data actually crosses the wire. Following the last two posts on OGP and HTTP cache headers — both things that live in HTTP response headers rather than <head> — this one covers how compression works. Note: gzip has been around since 1992 and is supported almost everywhere, by both browsers and servers. Brotli is newer, published by …

Japan Standard Time (JST) is a fixed UTC+9 offset with no daylight saving time to complicate it. That simplicity makes it tempting to assume timezone handling is “just add or subtract nine hours.” In practice, most timezone bugs don’t come from getting that arithmetic wrong — they come from losing track of which basis a given timestamp is even using in the first place. This article walks through three real examples, drawn from actual code, logging, and this very blog’s own publishing workflow. Pitfall 1: values that don’t carry their own timezone Note: a “naive” datetime is a date/time value with no timezone information attached at all. An “aware” datetime …

When two people or processes update the same piece of data at nearly the same time, one can end up overwriting the other’s change without ever knowing it happened. This is the classic “lost update” problem, and it shows up anywhere a database, or any multi-process application, allows concurrent writes. There are two broad strategies for handling it: pessimistic locking and optimistic locking. The names are opposites, and so is the approach each one takes. The “silent overwrite” problem Say a table tracks inventory, and two processes, A and B, both read the same row at nearly the same moment. Both see “10 units in stock” and both compute “subtract …

Last time we looked at what actually happens between git add and git commit — the mechanics of the moment a commit gets created. This time we shift focus to a different kind of question: given a long history of past commits, how do you track down the one that introduced a specific bug? git blame and git bisect both dig into that history, but they’re suited to very different situations. git blame: following one line’s history directly Running git blame <file> lists, for every line in that file, the most recent commit that touched it — hash, author, and date included. If you already suspect a particular line is …

Two articles back, we covered cookie+nonce authentication, and last time we looked at how WordPress’s login cookie is actually a hybrid: signature verification is fully self-contained, but revocation depends on a server-side list of valid tokens. This time we look at the opposite end of the design spectrum: JWT (JSON Web Token), which aims for authentication that is fully stateless — no server-side token list at all. Comparing it against WordPress’s hybrid model makes the trade-off between stateful and stateless authentication much easier to see. What a JWT actually is: three dot-separated parts A JWT looks like a single string split into three parts by dots — xxxxx.yyyyy.zzzzz. These correspond …

Anyone who has wired up a browser-side call to an API on a different domain has probably hit the red has been blocked by CORS policy message in the console — even though the server responded just fine. The browser is the one refusing to hand the response over. This article walks through the same-origin policy that causes this, and what CORS (Cross-Origin Resource Sharing) actually does to relax it safely. What “origin” means Note: an origin is the combination of a URL’s scheme (https://), hostname (wpmm.jp), and port. The path (/blog/, etc.) doesn’t count. https://wpmm.jp and https://en.wpmm.jp are different origins because the hostname differs; https://wpmm.jp and http://wpmm.jp are different …

When building a Python application, configuration values tend to live in one of three places: real OS environment variables, a .env file, or a hard-coded default in the code itself. These three can conflict, and when a value doesn’t seem to be “taking effect,” the cause is usually a misunderstanding of which one wins. This article walks through the resolution order between the three, and why it’s designed the way it is. What a .env file actually is Note: a .env file is a plain text file listing KEY=value pairs, one per line. It’s used to keep environment-specific or sensitive values — API keys, database connection strings — out of …

How Python’s venv Works — Why It Keeps Projects From Fighting Over the System Python Anyone who has worked with Python tooling has run into “virtual environments” (venv) sooner or later. A single command, python3 -m venv .venv, creates a directory that most Python projects treat as a given. What is that directory actually doing, and why has it become such a standard part of the workflow? This post looks at the problem venv solves and how it works under the hood. The Problem: Projects Sharing One Python Installation Working on multiple Python projects on the same machine eventually runs into a conflict: one project needs version 2 of a …

Write code that tries to SSH into several sites in parallel and you’ll quickly run into two questions: how many connections should run at once, and in what order should the results come back? In Python, the foundation for both is the standard library’s queue module, and the concurrent.futures.ThreadPoolExecutor built on top of it. This article looks at how the two relate, and at a property that’s easy to overlook: the order tasks are submitted in is not the same as the order they finish in. What queue.Queue actually is Note: queue.Queue is a FIFO (first-in, first-out) data structure for safely passing items between threads. Conceptually it’s no different from …