Hi, streaming doesn’t make a difference with transaction isolation and locking behavior, but yes readers don’t block writers and your query results will be a consistent snapshot from the beginning of the query. This is just standard Postgres behavior, you can refer to their docs.
SELECT statements take an “ACCESS SHARED” lock. The only transactions that acquire locks exclusive of “ACCESS SHARED” are ALTER TABLE, DROP TABLE a few other less common commands.
I also believe that Postgres.stream/4 will use a cursor underneath (which makes sense). So:
There will be at least 2 extra DB round trips - one to declare/open the cursor and one to close it. Probably not a big overhead on a large DB
I believe the cursor is opened for READ (not READ and UPDATE) so in Postgres that means MVCC would result in you having consistent results and not messing with other users