I think there is a large overlap with projects that market/focus on offline-first experiences.
AFAIK this problem can be solved by:
1) Considering a client-side copy of the database that gets synced with the remote DB. This is an approach [PowerSync](https://www.powersync.com/) and [ElectricSql](https://electric-sql.com/) and [rxdb](https://rxdb.info/) take! For the first two, they effectively use postgres CDC and then have a sync process that is provided for you. For rxdb I think you have to roll your own syncing but there is an interface for you to implement.
One difference between ElectricSQL and PowerSync (copied this from another [HN comment](https://news.ycombinator.com/item?id=37600917)) -> `ElectricSQL uses “shapes” (currently a WIP feature) defined on the client to partition data, while PowerSync uses server-side “sync rules”. The sync rules approach allows data to be transformed and filtered using basic SQL queries, which [PowerSync] expect to scale well (benchmarks pending). PowerSync guarantees consistency over all synced data, while it appears like ElectricSQL only guarantees consistency within each shape. PowerSync also automatically adds and removes sync buckets when changes are made to either the sync rules or data, while ElectricSQL likely requires you to subscribe and remove shapes yourself.`
^ Please note that comment faced rebuttal from ElectricSQL. On the same thread (6 months ago) , someone from ElectricSQL said this `Electric is designed to support partial sync, and so you don’t have to sync your whole dataset. (Note that this is feature is under development and not yet public)`
2) Another exciting entry is [Convex](https://www.convex.dev/) which supports reactive queries but it is not based on a well known existing DB like postgres, it's fully custom and new - but looks easy to get started with! They recently open-sourced their backend so you can give that a try.
3) [SurrealDB](https://surrealdb.com/features#connectivity) also supports realtime queries? but I haven't looked into this yet
4) [Triplit](https://www.triplit.dev/) Another option! Triplit is an open-source database that syncs data between server and browser in real-time but again, this not based on a well-known db like postgres, instead it uses another new solution. It is open-source, but I haven't tried it yet. Looks quick to set uo.
4) Yes, you can use supabase realtime/ postgres events to listen to changes in your db and refetch all your data as you said - not ideal but yep.
Other projects/technologies to look into include Debezium for CDC and also potentially using GraphQL subscriptions to keep your frontend up to date
See also: [Prisma Pulse](https://www.prisma.io/data-platform/pulse) which I looks something similar to Supabase Realtime. Supabase are in the midst of their announcement week. I have quietly hoping that they will launch an offline-first / realtime query feature soon. [This github issue](https://github.com/orgs/supabase/discussions/357) seems to tease it.
Best of luck with this! It's a super interesting problem and I'd like to know what you end up doing! :)