[dragonwriter]

> My instinct here is to write the query in such a way that even if pg changes in the future, or if I migrate to an almost compatible alternative tomorrow, it should work the same way.

Transaction semantics are a fundamental feature of pg, of SQL, and of ACID databases generally. Anything that doesn't preserve them is not an "almost compatible alternative".

> Perhaps more importantly, it sends the message to future devs (including me) that rows should only be deleted after they've already been archived.

Using an explicit transaction, even if it is not necessary, communicates to future devs the actual intent, which isn't "rows deleted after they are archived" but "deletion and archive should occur in a single atomic step, and either both happen or neither happen." The ordered approach is just a way to express the least harm alternative in an environment that doesn't provide atomicity guarantees, but that's inappropriate when working in an environment that actually provides atomic transactions.

One of the biggest problems that programmers with a stronger background outside of databases often have in writing SQL DB code is that they keep using ingrained workarounds for the fact that many programming environments lack convenient support for grouping operations into atomic units in SQL, which supports such grouping.