sgarland

Even with indexes, joins are costly, especially under load at scale with millions of simultaneous users. You can avoid a lot of this by simply having that sub-table information inside a JSON field with the row in question.

They’re really not that bad. Even on large-ish tables (hundreds of millions of rows), the typical query time I see for a query with 1-2 inner joins is 1-2 msec. That can of course vary with result set size, but in general it’s going to be dwarfed by network RTT.

If you’ve tested your schema with normalized and denormalized versions and found a significant difference that justifies it, by all means, but IME query speed for any non-trivial query is generally dominated by query shape, index design, and schema design (specifically for clustering indices, not taking advantage of it to have physical and logical tuple correlation).

I have no idea why you’ve been downvoted, as you accurately described EAV. I’m a DBRE who cares deeply about schema design, FWIW.

That’s usually because they’ve seen a lot of failure modes over the years, and what seems fine to you can have surprising outcomes later.

My personal experience has been the opposite of yours: lots of data inconsistency issues, data loss only resolved by the database team spinning up backups, etc. And somehow, even after yet another incident, there’s never been appetite to properly fix things.

I’ve found normal forms to sometimes be at odds with query efficiency and ease of use, which is critical when you’re moving fast—sometimes it’s just easier to dump data into a jsonb column.

If you’re a startup, the performance cost of storing everything in JSONB is going to outstrip any gains you might get from denormalization. JOINs are simply not that hard if you design your schema intelligently. Additionally, allowing freeform text columns for things like statuses will eventually bite you with fun problems like `closed != CLOSED != Closed`.

Use foreign keys with cascading deletes for low-volume tables, particularly where database consistency and correctness are important. Careful at higher volume.

Absolutely. Just be careful with 1:M, or M:N, for large values of M and N. You don’t want to trigger a surprise deletion of hundreds of thousands of rows.

Indexes by default use a btree implementation. It’s most helpful to think of indexes as just another table in Postgres, with data stored in a specific format which is optimized for lookups (more on this later).

For a single row lookup (which is what this section was referring to), yes. For range scans, if the indexed column isn’t k-sortable, a sequential scan can start beating the performance of the multiple lookups pretty quickly.

There are cases where you think an index should be used, but the query planner is still seq scanning anyway, despite table statistics being up to date and the index being valid.

This is usually caused by one of two things: forgetting that indices are (generally) B+trees and having data laid out in a manner that is inefficient for the query, or having data that isn’t uniformly distributed - for example, for some / many companies, the geographical distribution of users is going to be heavily clustered around more populous cities. Histograms are one way to deal with this.

Another topic not discussed in TFA is other index types - BRIN in particular can be incredibly performant while adding almost zero overhead, if the shape of your data makes sense for them (time-series is the obvious one, but anything with useful clustering should be considered).

All in all, this is one of the better tl;dr articles on Postgres I’ve read. Well done, Hatchet.

monetary budget

I’ve never attempted to calculate it, but I’ve always felt that the money lost from major incidents must outweigh the additional engineering time spent doing things right. Of course, that assumes that the engineering skill exists, and that the deliberate choice is being made to push out sub-optimal code in favor of velocity (which is then negated by the incidents anyway).

AIs today are far more knowledgable and much quicker than humans, but still missing a few bits that keep them from being broadly super human.

Generally, yes, but it’s the non-determinism that gets me. Even easy stuff: I’m currently on a road trip, and asked Claude (Fable) to plan various routes, optimizing for scenery while also ensuring a maximum drive time each day, and ending every night in a town that has a decent hotel. Along the way, I’ve made various tweaks, like “we’d like to drive an extra hour or two today, so adjust the rest of the route so we don’t end up with a short intermediate day.” While in general it’s done quite well, it occasionally made errors on distance between stops, something that’s trivially queryable. It wasn’t off by a few miles, either, it was hundreds.

Until they can get the confidently incorrect problem solved, I don’t see it being a viable replacement. Maybe if you have agents that are aggressively checking the main ones’ output, but that seems unnecessarily wasteful of resources to me.

My personal belief is that the most effective leaders do in fact deeply understand the technical side of their business, yes. To be fair, I am biased, because I was previously a nuclear reactor operator on a USN fast attack submarine, and the Commanding Officer is absolutely expected to fully understand technical issues. I think that a lot of organizational and technical problems would be solved if companies took several notes from the Nuclear Navy’s playbook. FWIW, Google’s SRE program was heavily influenced by it.

I of course don’t have a crystal ball, and can’t say with certainty that AI will never replace human intelligence and judgment. However, I don’t see a viable path forward where that occurs, and we all still have jobs. Why would I be kept around if a superhuman AI can do my job 1000x better and faster, and at a drastically cheaper price? Or, if I was kept around as a human QA agent, why would I be paid nearly what I am now?

I don’t see humans allowing AI to replace them. UBI is a pipe dream that would be destroyed by rent-seeking behavior anyway, and there’s no way billionaires would go for an AI tax, so we’ll be at an impasse.

If my experience with RDBMS generalizes, I predict that this trend will result in companies hiring skilled developers to unfuck their codebases.

Furthermore, I predict they will identify massive structural issues, propose huge lifts to redo it correctly, and will instead be told to do patchwork fixes to stop the bleeding.

Now, I do think AI is (or will be) good enough to implement these patchwork fixes when given explicit instructions, but someone will need to figure it out for them.

The problem is if you don’t understand the root cause, you’ll at best feed the LLM a list of symptoms, and it will dutifully fix them. “Our p99 on this endpoint is breaching” becomes “I’ve added an index for this query,” or “I’ve added a caching layer,” when the actual problem may well be that you’re storing huge blobs that bloat your working set, cause buffer pool churn, and saturate your NIC. Is the symptom solved? Yes. Is it scalable? No, you’ll next need to vertically scale the instance, which is more expensive, and has an upper bound. Eventually you’ll decide to shard, which adds complexity, when the actual solution would’ve eliminated all of those issues at once.

As others have pointed out throughout the thread, the reason we defend the skills is that there’s a high likelihood that these tools will always be available on any machine; PowerShell and nushell, not so much.

THANK YOU. I kept nodding along to all of the xargs comments, thinking “sure, but what about the even easier solution?”

This resonates with me. I’ve always found the term annoying, because it felt like it was distilling artistic expression down to its most abstract form: content. If all you’re producing is abstract “content,” why should I participate? What makes it special?

10/10, no notes.

That loss is real and it's worth naming.

Annnnnd TFA is written by AI. I want to get off Mr. Bones' Wild Ride.

Everything you create should be an artistic endeavour aiming for perfection.

Amen. If you go to a bakery, you expect them to care. If you hire a photographer, you expect them to care. Software isn’t (usually) a factory line; CRUD may be similar concepts throughout, but everyone is making it themselves.

Give a shit about what you make.

Wait until you read about its quirks [0]. My favorite:

“NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in the middle of strings in SQLite. This can lead to unexpected behavior.”

0: https://sqlite.org/quirks.html

The grandparent comment is just racist.

I wouldn’t jump to that conclusion. The concept of low and high-trust societies is well-studied [0], though how a given country maps to it may be disputed.

0: https://pmc.ncbi.nlm.nih.gov/articles/PMC3997396/

How people are running any RDBMS in prod — but especially Postgres - and not using a connection pooler with the capability to pause incoming connections is beyond me.

The entire “how do we drain connections” issue described in TFA becomes trivial, as does gradually raising the amount of incoming connections, let alone the load advantages from multiplexing the client connections.

This is the only statement I disagree with:

PowerShell gets a lot right with structured data.

CLI programs should operate on text. If you want to parse and format it, do so, but the default output mode should be plain text, so that I can pipe it into grep or awk without a second thought.

I am continuously irritated that the AWS CLI defaults to outputting in JSON. No one (I hope…) is using that tool in programs; that’s what boto3 and its ilk are for. But if humans are reading it, why default to something that they’re almost certainly going to be piping into jq if only for the formatting help?

Very optimistic of you to assume that someone who doesn’t understand why DB indices in general are important would care enough to read a detailed explanation about them.

Same. Every time I think about this, I come back to the fact that I was excellent at my job before AI, and even enjoyed aspects of it, so I would be fine if tomorrow it all blew up.

If absolutely nothing else, I applaud the author for manually writing their post — though, of course, the irony of someone using AI to write a post about letting AI die would be incredible.

Correct me if I'm wrong, but MongoDB appears to have a 100-msec gap for its journaling behavior [0] by default. This would be akin to setting innodb_flush_log_at_trx_commit [1] = 2, which is not its default.

I also note that in their FAQs [2], they erroneously state:

"MongoDB’s data modeling best practice suggests storing related data together in a single document using a variety of data types, including arrays and embedded documents. So, a lot of the time, ACID is not required as it is a single-document transaction."

Whether or not you're operating on a single document has nothing to do with its ability to meet durability guarantees (or consistency, for that matter).

NoSQL databases make tradeoffs for performance, and making the lives of devs easier in the short term. That's fine, if and only if you accept what you're losing, and document it for others who may not be aware. If at any point you can have your application get a write ack'd and subsequently lose the write, you do not have a durable data store, and you do not have ACID compliance. Whether that's the fault of the DBMS, the operating system (Postgres' fsyncgate), or hardware (drives lying to the OS about the write's durability without the benefit of PLP) is irrelevant – you have to understand the entire chain to make those guarantees, or at the very least, trust your upstream provider to have understood it for you and made the correct decisions.

0: https://www.mongodb.com/docs/manual/core/journaling/

1: https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.ht...

2: https://www.mongodb.com/resources/products/capabilities/acid...

I think the author has a certain writing style that you apparently dislike, which is fine, but it’s hardly slop. I agree that the comparison between Sketch being somewhat unreliable but fast undercuts the claim that speed and reliability often go hand-in-hand — though one could argue that the modifier “often” saves it.

I’ve found that writers who self-profess to have ADHD often write in this way, with multiple, seemingly disparate points being made that can tie together if you squint. As an ADHD person who enjoys writing, it makes sense, and at least in my head, these points always connect; I’m just not great at demonstrating how they connect. I’ve no idea if the author is neurodivergent, but it’s one possible explanation.

I don’t think I’ve ever noticed a difference in speed on the terminal between distros. Shells (or more accurately, plugins / frameworks - I recently gave up oh-my-zsh in favor of zimfw for that reason), yes, but not the terminal itself.

Document behaviors. Model the system. Let the system decide what data storage it requires.

Counterpoint: force the system to use an RDBMS to store data in properly normalized schema, because it’s the only thing guaranteeing that the data will continue to exist as you expect.

Much like I can’t take Prisma seriously because they shipped an ORM that couldn’t do JOINs, I can’t take any database seriously that can’t manage ACID. “bUt wE haVe BAsE.” Cool story. Relational databases are some of the oldest and best-tested pieces of software that exist. I trust them more than anything else - if you write it, it is persisted, full stop.

Profile your code sometime; I assure you, with a properly indexed query, the actual query time is insignificant compared to everything else, unless your app is Rust, C, Nim, etc.

The overwhelming majority of OLTP queries I see running on massive prod systems execute in < 1 msec. More time is spent in network RTT than execution, let alone the ORM parsing the result.

I’m a DBRE, and also happen to like SQL. With that as a disclaimer, I really do not think it’s a difficult language to learn. Learning the intricacies of your RDBMS’ behavior for various functions (like MySQL’s ORDER BY and GROUP BY optimizations) is complicated, but that’s what docs are for.

Fun fact, NIST also still runs the TIME protocol (RFC 868 [0]) [1].

0: https://www.rfc-editor.org/rfc/rfc868

1: https://tf.nist.gov/tf-cgi/servers.cgi

I’ve not tried encoding my own MP3s in at least a decade, but when I was doing so, 128 kbps was instantly distinguishable to me on anything with cymbals, especially hi-hat: it loses that shimmery sound. At 192 kbps I could tell if I really, really tried, but it was so minute I didn’t really care. I was never able to reliably tell the difference between 256 and 320 kbps rips.