0

I am under Spring Data JPA 2.5.5 / Hibernate 5.4.32 / PostgreSQL 12.7.0.

I have a database table 'A', and I have mainly 2 use-cases:

  1. Add a large number of new data (new in terms of datetime) into 'A' (at least 1 million per minute)
  2. Fill empty intervals of old data (old in terms of datetime), again potentially 1 million of records per minute: it means insert if not exists, there will be already existing data in the input data

I have the following schema:

@Embeddable
public class TableId implements Serializable {

    @Column(name = "datetime", nullable = false)
    private OffsetDateTime datetime;

    @Column(name = "data")
    private String data;

   (getters/setters...)
}

@Entity
@Table(name = "\"A\"")
public class AEntity implements Persistable<TableId> {

    @EmbeddedId
    private TableId id;

    @Column(name = "value")
    private Double value;

    @Override
    public boolean isNew() {
        return true;
    }

    (getters/setters...)
}

the isNew() method allows to fix the first case, which means not doing any select while inserting. Is this the best way ?

Now for the 2nd point, I do not know what is the best way to do it in terms of performance, any proposition ?

I found this but it is as expected damn slow:

@Query(nativeQuery = true, value = "select * from A a where CONCAT(cast(a.datetime as VARCHAR), '-', a.data) in (:columns)")
List<AEntity> findByIdIn(@Param("columns") List<String> columns);

Note that I cannot add an extra column in the database, and that the table contains billions of records.

Khalil Bouzekri
  • 210
  • 4
  • 12

0 Answers0