5,538 karma · joined May 18, 2020
email: <stephan@{username}.dev>
meet.hn/city/us-Charlotte
Socials: - github.com/stephanGarland
Interests: DevOps, Hardware, Databases, Open Source, Linux
---
Then I moved to one in North Carolina that was god-awful. Dues started at $65/month and went up every year. They’d send out neighborhood reminders that a pressure-washing service would be in the neighborhood next week if anyone wanted to hire them; anyone who didn’t got notices a few weeks later that they needed to pressure-wash their driveway and/or house. I also had to buy a specific brand and sheen of paint to repaint my mailbox; of course, the lettering was only available from a single supplier.
I now live on an acre lot that sits between two neighborhoods. I have no HOA, and it is glorious.
Personally, I like regexes, in the same way that I like shell string manipulation: it looks utterly inscrutable, and performs magic. If I write one in code meant for others, I usually use extended mode with each line commented.
Fantastic analogy, and I assume you already know where I'm going with this.
The Titan submersible [0] implosion was by all accounts due to studiously ignoring best practices and test results. They shipped fast.
In contrast, James Cameron's Deepsea Challenger [1] was a slow and methodical build, and has heavily redundant backup systems for everything. For example, the ballast release system operates by demagnetizing coils that hold the ballast; that way if they have a system failure or power loss, it fails open, and they ascent. If that somehow fails, the supports for the ballast will corrode from sea water before the oxygen runs out, so in the worst case, it's sitting in the dark for a few hours before heading back up.
Or, let's look at the U.S. Navy submarine program. I was a propulsion plant operator (reactor operator + throttleman) on a Virginia-class sub. Though I can't go into specifics, "redundant" doesn't begin to describe it. FFS, the San Francisco (688-class sub) ran into an underwater mountain at flank [2] and survived. The SUBSAFE program [3] was a direct result of the loss of the Thresher [4], and to date, no SUBSAFE-certified submarine has been lost.
> Then look at software. When is the last time you encountered a bug? Have you ever heard of bug free software?
You're conflating "free of defects" with "capable of gracefully handling defects." NASA writes as close to bug-free as I think you'll find, but even theirs isn't perfect [5]. The difference is the ability for the software to work through or around those problems. Submarine / submersible design and construction is the same. Are the engineers, welders, machinists, electricians, etc. attempting to do perfect work? Of course. They also are aware that perfection is impossible, and so the design accounts for that.
0: https://en.wikipedia.org/wiki/Titan_(submersible)
1: https://en.wikipedia.org/wiki/Deepsea_Challenger
2: https://en.wikipedia.org/wiki/USS_San_Francisco_(SSN-711)#Gr...
3: https://en.wikipedia.org/wiki/SUBSAFE
4: https://en.wikipedia.org/wiki/USS_Thresher_(SSN-593)#Disaste...
Rows store all columns together
Not guaranteed. The DYNAMIC (default) and COMPRESSED row formats [0] can shift larger values of variable-length columns in off-page records. In the example given, this wouldn’t occur, but it isn’t a guarantee per se. This is roughly equivalent to Postgres’ TOAST, though Postgres’ default threshold for TOAST is 1/4 of a page (2 KiB with its default 8 KiB page size), whereas InnoDB is 1/2 of a page (8 KiB with its default 16 KiB page size).
0: https://dev.mysql.com/doc/refman/9.7/en/innodb-row-format.ht...
It’s a fantastic tool, with much better defaults, but man that tiny thing irks me every time I use it.
The other major problem is lack of statistics. JSON[B] is opaque to Postgres; it has no idea about the distribution of values within an object. This starts to become very obvious if you're doing JOINs and have predicates from the JSON object. Take for example geographical information: maybe you want to find some number of customers from Delaware who have onboarded in the last week. If that geographical information is in a JSON object, and you have 5,000,000 rows, Postgres has no idea how many rows it should expect, so it may not choose the optimal join strategy. The same is true in the opposite direction - it's probable that you have more customers in California than you do in Delaware purely based on relative population, but because the planner doesn't know that, it's unlikely to choose the optimal strategy.
To be fair, for correlated columns (extending the above example, think of postal_code and state), the planner can also make the same mistake in underestimations, because by default it assumes the columns are uncorrelated. This can be solved with extended statistics [1], where you explicitly tell it which column[s] are correlated, and in what manner.
I created a table in PG18 on my Mac, with 5,000,000 rows of fake customers (various columns like names, geo information, email, opaque ids, integer id, date times, etc.), and another where most of it was shifted into a JSONB column, exact same information, same ordering of rows. In general, simple point lookups on the scalar table took 0.1 msec, as you'd expect; the JSONB table could at best do 2.5 msec, if and only if the correct operator was used. Use the wrong operator, and it still works, but it reverts to a parallel sequential scan, which took ~175 - 400 msec, depending on the specific operator being used. For joins, it was 20-30% slower with an expression index, and 20-350% (yes, large range) slower with a GIN index.
JSON[B] also struggles with memory usage during queries, and WAL generation. Since the entire object has to be extracted, intermediate query results can get quite large; if you don't have a large-enough `work_mem`, this slows things down (the same is true for scalars, of course, but JSON objects tend to be larger, if nothing else due to repeated keys). If you ever have to UPDATE a JSON[B] object, that's a full re-write, and an update to any GIN indices it may have. I measured 5.5 sec vs. 2 sec for updating 1 attribute on 100K rows, and 831 MB WAL vs. 227 MB WAL generation. Those times would be much, much more if this wasn't on a fast local NVMe drive, as well.
There's also TOAST/DE-TOAST [2] time, which I didn't measure, because my objects were small. With a large enough value (it doesn't have to be JSON, but it usually is) - by default, 2 KB - Postgres will first attempt to compress, and then shift those values out to a separate table, vaguely similar to InnoDB's DYNAMIC page format. That means when you're querying with predicates on the TOAST'd object, it now has to do another indirect lookup, retrieve the page, and check the predicate. At scale, this can severely slow things down.
Finally, and most importantly IMO, guaranteeing invariants is next to impossible with JSON objects. Was that field left NULL when it shouldn't have been? Did the email field actually contain an email address? Hopefully your application is checking everything rigorously, and no one has changed its logic from what was intended.
0: https://www.postgresql.org/docs/current/datatype-json.html#J...
1: https://www.postgresql.org/docs/current/sql-createstatistics...
2: https://www.postgresql.org/docs/current/storage-toast.html
JSON is slow as hell, both for serdes, and especially querying it. Use it sparingly, if at all. Normalization is not that hard, and it's trivial now with LLMs. Tell Claude you want your schema in BCNF, or 5NF if you're feeling froggy. Denormalize if and only if you have performance issues.
Now, you could argue that AI didn’t fix that, I told it what to fix, and it just wrote the code. That’s valid, I suppose. The view I’m coming around to is that LLMs do know how to write performant code, but their corpus is so full of garbage that you have to tell it explicitly what you want, which requires you to know how to spot the problems and solve them.
I will not make any claims that they can write kernels or compilers well, because I haven’t tried to do either of those, on my own or with an LLM.
Another anecdote: yesterday, I got tired of dealing with Mumble on my LAN (random audio issues on the Mac clients, poor echo cancellation, etc.) and asked Claude to build a replacement. I gave it a rough spec, including non-goals (private use only, so no encryption needed, and I don’t care about supporting a large number of clients), then told it to build it in Rust, supporting MacOS and Windows both natively. Two hours later, it worked. Not just kind of, it worked flawlessly on Macs and Windows, and the audio quality and echo cancellation was perfect. The only bug I found - which it fixed - was denormal numbers was causing relatively high CPU (~1/2 of a core) usage on Windows due to x86_64 handling of that. The binary is 5 MB. It ran its own echo cancellation training while writing it to tune parameters. It implemented RFC 2198 without me mentioning it (hell, I didn’t even know about it - it’s fascinating!). When I fed it back some logs, it saw that one client had higher latency than expected (meshed WiFi without a backhaul), and then proceeded to create a network simulator so it could test various latency and jitter scenarios to solve it. While doing so, it also determined that Windows was lying about its latency numbers by 25 msec, and addressed that.
So again, I think LLMs are capable of excellent work, you just have to know what you need to ask for.
You’d think they’d have found this issue out before now; if there’s no usable index for the predicate, non-locking reads would’ve also been slow.
Also, I look forward to the next update wherein your colleagues create an index, and then discover the joys of gap locking under REPEATABLE-READ.
It is for this reason that my lights (Hue) and switches (Inovelli) are bound in ZigBee groups. HA adds some niceties, but the lights still work if it’s down. Accent lights I have bound to the Hue Bridge, because their app adds features HA doesn’t have, and they aren’t required for emergency egress.
The only gap I have, I think, is that because I’ve set all bulbs to respond to power restoration after a cut in last known state, if everything somehow lost its Zigbee binding, putting switches into dumb mode locally still wouldn’t help. However, since that’s an improbable scenario, I accept the remote possibility in exchange for not being awakened from lights coming on in the middle of the night. Plus I keep flashlights / headlamps throughout the house.
Plenty of other countries with rural areas seem to do fine without one. They also usually have much better / more granular representation, instead of a hard cap of 435 for a population that is orders of magnitude larger than at the country’s founding.
Also, of course, the majority of SaaS apps (perhaps most apps in general?) are not compute-bound, so gains from performance alone shouldn’t be the only metric.
My current job has a mix of Python and Go. Personally, I dislike Go. I don’t like its syntax, I don’t like its utter lack of formatting standards (gofmt is not a standard; also it sets tabs - ugh), I don’t like its insistence on static linking, and I don’t like how people claim it’s a great scripting language when you can’t just run a file on its own (and its stdlib is weak sauce compared to Python).
With how magical handoff / continuity / whatever it’s called is, it is baffling to me why Apple allows the HomePod to so aggressively take over requests when it sucks so, so much at it.
“Thousands.” Yes, in the same way that the earth is thousands of years old. 4.5 million thousands, but still, thousands.
https://www.extremetech.com/cars/227828-the-mclaren-f1-super...
Relevant: https://www.joelonsoftware.com/2000/04/06/things-you-should-...