Hacker News Reader

Hasura avoids this issue by having it’s own row level security model. In fact they go one step further in that for any subscriptions, they can run one query for all connected clients by building a temp table with a row per client using their session variables to join against. (They poll for subscription updates since it’s fast and scales well)

Pgbouncer

Does that help with fragmentation though? If the connecting user must be the same then it doesn't add much, except lower connection latency.

Row-level security is not at all dependent upon DB users/roles. Storing app user data in a table for lookup is sufficient. Storing the user info in a session config value as provided by JWT can be even better.

Basic database table replication will suffice for the former. No replication required for the latter.

I've previously seen this handled quite elegantly with SET ROLE/Authorization if I recall correctly, with a rollback at the end.