This is valid. However, many production databases won't scale to zero often. The serverless proposition is still valuable if you factor in the development, test, and staging environments scaling to zero plus our autoscaling that doesn't require downtime or dropped connections.
For guaranteed message delivery, you're probably best of using a messaging system designed with that in mind instead of listen/notify.
(Neon PM) This is true and very often production applications need access to their database 24/7, so they benefit from the serverless nature of autoscaling.
The only issue I've found with Neon is that to use listen/notify the DB needs to be awake 24/7 which defeats the purpose of serverless.