This is the inherent problem with e-mails (or any distributed system for that matter). For example, a user might have mistyped their e-mail or the mail may have ended up in spam, or it has been filtered by some custom filter, and ultimately the user never sees the e-mail.
E-mail delivery is a “best effort” operation. You can only know that the user received it if they click on some unique link in that e-mail, which will lead to a request made to the site.
So we need to keep this property in mind and design the system accordingly. For example, if the e-mail is an activation e-mail, we’ll keep the user in the activating state until they click on the link, optionally removing or invalidating the user if they don’t active within some time-frame. That is indeed added complexity, but that complexity is inherent to the challenge. You can alternatively pretend that these issues don’t exist, and it might work mostly fine, but given enough time and users these problems will surface sooner or later.
Using a local mail server will certainly reduce the chances of something going wrong, but it won’t eliminate the problems completely. If the mail-server stops, or if it’s very busy, or if the rest of the system is very busy, you still might experience a scenario where an e-mail is enqueued and delivered, but the application gets an error and rolls the transaction back. See the “Application-level failures” in this article for some real-world examples.
Beyond that, the issues mentioned by @benwilson512 still remain. You might send an e-mail successfully, but something may fail on the PostgreSQL side. To reduce the chance of this happening, you should strive to send the e-mail as the very last thing in the transaction, which is cumbersome and error-prone.
Ultimately, the naive approach increases the chances of e-mail being sent while the transaction is rolled back. I find this to be a serious issue, since we may end up misinforming the user (e.g. we tell the user that they have registered, while the database entry is in fact missing).
OTOH the persisted queue eliminates such possibility. Of course, this means you might end up saving something to the db while the e-mail is not sent, but as I already said, it’s always possible that the user won’t receive an e-mail, so you need to plan for such scenario anyway.
