Postgres is a way better fit than Kafka if you want a large number of durable streams. But a flexible OLTP database like PG is bound to require more resources and polling loops (not even long poll!) are not a great answer for following live updates.
Plug: If you need granular, durable streams in a serverless context, check out s2.dev
s2.dev looks cool... I jumped around the home page a bit and couldn't perfectly grasp what it is quickly though. But if it is about decoupling the Kafka approach and client side libraries from the use of Kafka specifically I am cheering for you.
Could you see using the s2.dev protocol on top of services using SQL in the way of the article, assigning event sequence numbers, as a good fit? Or is s2 fundamentally the component that assigns event numbers?
I feel like we tried to do something similar to you, but for SQL DBs, but am not sure:
https://github.com/vippsas/feedapi-spec