I think this would be a more effective article if it were titled “PostgreSQL Keys in Depth”. There’s a lot of advice in here that relies implicitly on how Postgres is implemented and if the advice is ported to a database that behaves differently (say SQL Server), that advice will lead to serious performance problems.
I feel a bit silly for this, but I’d never thought of reading UNIQUE as “this is a key” until reading this article, even though I intuitively use them that way. I doubt it impacts my designs much, but it definitely changes how I’ll conceptualize my schemas. (It also does a good job explaining why PostgreSQL doesn’t cluster tables on the primary key.)