slow inserts of bytea column in PostgreSQL

[tod...@googlemail.com]

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]

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]

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]

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.