How to fetch fetch one column lazily with a jOOQ active record

Question

I have a PostgreSQL table that defines a couple of small simple columns and one column data that stores large binary data:

create table my_table
(
    id          serial primary key,
    data        bytea  not null,
    description text
);

When I'm fetching rows of this table through jOOQ (into a MyTableRecord), jOOQ fetches every column at once. I know jOOQ supports lazy fetching of records via fetchLazy() and stream(), but that applies only on the level of rows, not individual columns.

Thus, the resulting MyTableRecord class always has a simple getter for data stored in memory:

...
public byte[] getData() {
    return (byte[]) get(1);
}
...

This means that whenever I want to work with an active record instance of MyTableRecord I have to fetch all the data from DB, even when I don't need it. How can I change the behaviour of jOOQ/MyTableRecord to fetch the data column lazily, only when I first call .getData() on the record object?

I know this problem can be solved by a workaround where I offload the binary data to another table and introduce a 1:1 foreign key relationship, but that is a complication I'm not looking for, as it would (among other issues) require adding a join operation each time to my data queries.

Answer 1

You can work with your MyTableRecord even when you don't fetch all columns. Just make sure you include the primary key:

Result<MyTableRecord> result =
ctx.select(MY_TABLE.ID, MY_TABLE.DESCRIPTION)
   .from(MY_TABLE)
   .fetchInto(MY_TABLE);

Now, when you're ready, refresh the record in question:

myTableRecord.refresh(MY_TABLE.DATA);

You can also fetch some of those values up front, and others only later, like this:

Result<MyTableRecord> result =
ctx.select(
      MY_TABLE.ID, 
      MY_TABLE.DESCRIPTION,
      when(MY_TABLE.ID.in(1, 2, 3), MY_TABLE.HEAVY_COL1).as(MY_TABLE.DATA))
   .from(MY_TABLE)
   .fetchInto(MY_TABLE);

Obviously, this approach is prone to N+1 queries, but you seem to have measured the difference and are certain that N queries are faster/better for you, in this case.

A remark on other RDBMS

Since you're using PostgreSQL, there's probably no other way, but other RDBMS support JDBC's Clob and Blob APIs, which serve as pointers towards the actual LOB data. By default, jOOQ always fetches everything eagerly, but you can make jOOQ expose those JDBC Clob or Blob types instead, in order to read the binary data only later on without a second query:

https://www.jooq.org/doc/latest/manual/reference/reference-data-types/data-types-lobs/