Thanks for the answers. So, H2, like PostgreSQL, also internally has (a) an MVCC engine and (b) LOBs existing as a on-the-side extra thing.
On Fri, 24 Feb 2023 at 17:39, Tom Lane <t...@sss.pgh.pa.us> wrote: > Postgres doesn't really do LOB in the same sense that some other DBs > have, so you'd need to specify what you have in mind in Postgres > terms to get a useful answer. > So, specifically, the primary problem we have is this: (1) A typical small query returns all of its data in a stream to the client (2) which means that, from the server's perspective, the transaction is closed the moment the last record in the stream is pushed to the client. (3) which means that, in the face of concurrent updates, the underlying MVCC data in the query might be long-gone from the server by the time the client has finished reading the result set. (4) However, with LOBs, the client doesn't get the LOB in the result set data stream, it gets a special identifier (a hash), which it uses to fetch LOB data from the server in chunks (5) Which means that the lifetime of an individual LOB is just horrible At the moment the implementation I have satisfies the needs of clients in terms of correctness (crosses fingers), but is horrible in terms of performance because of how long it has to keep LOB data around.
