`brew` lives across (at least) one fundamental divide from `apt`.
Homebrew is a source-based package manager, where packages are built from a single shared source of build recipes, and distribution of software happens by distributing the whole collection of recipes. Support for a binary cache is added through the identification of build recipes with the hashes of archives of binaries.
APT is what's called a binary package management system. While build recipes can be stored in a central repository, they don't have to be, as binary package management systems typically identify two kinds of packages: source packages and binary packages, which can be distributed separately. (Some repositories may not even include source packages.) Source packages contain metadata like build recipes, but things like archives of the upstream source code, patches, and sometimes helper scripts that might be usd in building. Binary packages include the actual built artifacts; they're installable packages.
In the source-based package management world, if packages can be added to the main collection through external sources, this is typically done by pretending that those packages were embedded in (some particular version of) the main repository of build recipes. Collections of packages that are ‘injected’ into the main package collection like this are typically called ‘overlays’.
In binary package management systems, an external package is not expected to have access to some copy of a complete collection of build recipes from some ‘master’ repository. Instead, source packages are distributed individually using the same mechanisms as binary packages. This means that the global namespace of terms which refer to packages can't be navigated implicitly at build time like on source-based systems, the identification of actual source code and build instructions with some package name happens both at build time and at install time. And in both cases, explicit metadata (package names, package versions, equivalency classes, lists of conflicting packages, etc.) is used to determine what versions of dependencies get pulled in.
As a result, binary package management systems always include dependency solving at install time, and usually include some explicit version constraint for every single dependency declared in a package's build recipe. This means they have the resources to look at what's going to be installed, what's already installed, and say ‘oh, the new installee requires Python = 3.10.2 and you already have python-3.10.3 installed, we don't have to touch Python to install this new package unless you ask us to’.
Pacman is somewhat atypical. The build system is essentially source-based and monolithic (there's no source package format) and the AUR is essentially an overlay, but the high-level CLI tool acts like a binary packager and uses the language of repositories to describe dealing with multiple package sources. Pacman's dependency resolution behavior is also unusually unreliable, and probably closer to what `brew` does than what `apt` does. Pacman has the resources to describe and test version constraints, both in its build system and in its CLI tools, but they're underutilized in a way that's more typical of source-based package management systems than binary ones. Homebrew is similar in that such constraints are radically underutilized (they're totally absent).
The main difference between Homebrew's behavior and Pacman's in this instance is that by default, just `pacman -S` doesn't update pacman's collection of package definitions (pacman -Sy does) and Homebrew always tries to install from the latest build recipes.
I imagine that Pacman's capacity for handling version constraints means that the story with `pacman` will usually be a bit better than with `brew`, but that otherwise `brew install` will be similar to `pacman -Sy` and `HOMEBREW_NO_INSTALL_UPGRADE=1 brew install` will be similar to `pacman -S`. (Pacman also sometimes makes the opposite tradeoff that Howell describes for Homebrew sometimes; it's not unheard of for installing new software or updating software using Pacman to break installed software.)