slow inserts of bytea column in PostgreSQL

11 views
Skip to first unread message

tod...@googlemail.com

unread,
Jul 16, 2021, 4:23:45 AMJul 16
to jOOQ User Group

Hi,

I have a table defined by

CREATE TABLE  document_storage (
        id INTEGER NOT NULL,
        content BYTEA,
        PRIMARY KEY (id)
)

The DB Type BYTEA is used by the generated source files as byte[].

The insert of 70MB content needs >40s via JOOQ records, which sems to be way too long.

DocumentStorageRecord storageRecord = jooq.newRecord(DOCUMENT_STORAGE);
storageRecord.setId(documentRecord.getId());   
storageRecord.setContent(content);
storageRecord.store();

If I use the example from the postgres jdbc docs, than the row is writen in <2s

ByteArrayInputStream is = new ByteArrayInputStream(content)
PreparedStatement ps = conn.prepareStatement("insert into document_storage (id, content) values (?, ?)");
ps.setInt(1, id);
ps.setBinaryStream(2, is, content.length);
ps.executeUpdate();

I've try to configure a <forcedType> BINARY for that column but the result was the -expected- same type byte[] within the generated sources.

What is needed to have the same performance as in the plain jdbc example ?

Kind reagds
Dominik
How can I

Lukas Eder

unread,
Jul 16, 2021, 4:29:38 AMJul 16
to jOOQ User Group
Hi Dominik,

That's curious. jOOQ uses setBytes() behind the scenes, assuming the blobs are "reasonably" small. For large LOBs, you generally have to either bypass jOOQ or use your own custom data type binding. Though, since you already have a byte[] in memory, I don't really see why binding the byte[] should be so much slower than binding the stream. The pgjdbc driver could implement the same logic. Have you profiled it, e.g. using VisualVM? Might be a pgjdbc bug.

The solution is to use a data type binding. Starting from jOOQ 3.15, you can also attach an ad-hoc binding to your DOCUMENT_STORAGE.CONTENT column, by calling Field.convert(binding)

I hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1022010e-3f01-40af-8210-2ea4e9b2876an%40googlegroups.com.

tod...@googlemail.com

unread,
Jul 19, 2021, 4:17:12 AMJul 19
to jOOQ User Group
Hi Lukas,

thank you for response.
In order to try the ad-hoc binding, I updated the jooq.version in my pom from 3.14.11 to 3.15.1
After that, my Spring Boot application does not start due to

    Field dataSource in de.hub28.steel42.appconfig.JooqConfig required a bean of type 'javax.sql.DataSource' that could not be found.

The Spring Boot CONDITIONS EVALUATION REPORT contains the following Negative match which may lead to the reason for that situation:

   DataSourceAutoConfiguration:
      Did not match:
         - @ConditionalOnMissingBean (types: io.r2dbc.spi.ConnectionFactory; SearchStrategy: all) found beans of type 'io.r2dbc.spi.ConnectionFactory' connectionFactory (OnBeanCondition)
      Matched:
         - @ConditionalOnClass found required classes 'javax.sql.DataSource', 'org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType' (OnClassCondition)

If I switch back to JOOQ 3.14.11 there is the following Positive match and the application starts successfully.

DataSourceAutoConfiguration matched:
      - @ConditionalOnClass found required classes 'javax.sql.DataSource', 'org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType' (OnClassCondition)
      - @ConditionalOnMissingBean (types: io.r2dbc.spi.ConnectionFactory; SearchStrategy: all) did not find any beans (OnBeanCondition)


Kind regards
Dominik

Lukas Eder

unread,
Jul 19, 2021, 4:32:14 AMJul 19
to jooq...@googlegroups.com
See https://stackoverflow.com/q/68297295/521799
--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/34db56da-6059-48bc-9eb0-e62f2a1d678dn%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages