Spring Boot JPA操作PostgreSQL性能缓慢问题排查求助

我在Spring Boot中集成了Oracle与PostgreSQL 42.6双数据库，每次从REST API获取500条数据（以更新操作为主，少量插入）存入PostgreSQL，当前耗时约22秒。Hibernate统计显示虽创建了70个批次，但每条SQL仍单独执行，批量操作未生效，移除batch_size配置后耗时无变化，请求排查配置遗漏点。

现有配置与代码 #

application.properties配置 #

spring.datasource.secondary.jndi-name=java:/datasources/IntegratorMasterdataDS
spring.datasource.secondary.driver-class-name=oracle.jdbc.OracleDriver

spring.jpa.secondary.hibernate.dialect = org.hibernate.dialect.OracleDialect


spring.datasource.primary.jndi-name=java:/datasources/ItghPostgresXADS
spring.datasource.primary.driver-class-name=org.postgresql.Driver

PostgreSQL配置类 #

@Primary
@Configuration
@EnableJpaRepositories(basePackages = "com.tietoevry.absolutesoftware.itgh", entityManagerFactoryRef = "itghEntityManager", transactionManagerRef = "itghTransactionManager")
public class ItghAutoConfiguration {

    @Value("${spring.datasource.primary.jndi-name}")
    private String itghJndiDatasourceName;

    @Primary
    @Bean
    public DataSource itghDataSource() {
        JndiDataSourceLookup jndiDataSourceLookup = new JndiDataSourceLookup();
        return jndiDataSourceLookup.getDataSource(itghJndiDatasourceName);
    }

    @Primary
    @Bean
    public PlatformTransactionManager itghTransactionManager() {
        JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
        jpaTransactionManager.setEntityManagerFactory(itghEntityManager().getObject());
        
        return jpaTransactionManager;
    }

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean itghEntityManager() {
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
        factoryBean.setDataSource(itghDataSource());
        factoryBean.setPackagesToScan("com.tietoevry.absolutesoftware.itgh");
        factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        
        Map<String,Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.jdbc.batch_size", 500);
        jpaProperties.put("hibernate.show_sql", true);
        jpaProperties.put("hibernate.generate_statistics", true);

        factoryBean.setJpaPropertyMap(jpaProperties);
        return factoryBean;
    }
}

Oracle数据库配置方式类似，作为secondary数据库且未配置jpaProperties。

Repository定义 #

@Repository
public interface ApplicationsRepository extends JpaRepository<Applications, String> {}

数据保存代码 #

appRepository.saveAll(applications);
appRepository.flush();

Hibernate统计信息 #

Metrics {
    45644216 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    950436647 nanoseconds spent preparing 18711 JDBC statements;
    851450529369 nanoseconds spent executing 18641 JDBC statements;
    14912223124 nanoseconds spent executing 70 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    18845894086 nanoseconds spent executing 39 flushes (flushing a total of 388757 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

实体类定义 #

@Data
@Entity
@Table(schema = "PD_Owner", name = "CIM_ABSOLUTE_APPLICATIONS_TAB")
public class Applications implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "Device_Id")
    private String deviceAppId;

    @Column(name = "DeviceUid")
    private String deviceUid;

}

问题排查与修复方案 #

• 开启批量更新支持：Hibernate默认仅对插入做批量处理，更新需额外配置。在PostgreSQL的jpaProperties中添加：

  jpaProperties.put("hibernate.jdbc.batch_versioned_data", true);
  

  该配置允许Hibernate对主键更新场景进行批量处理。

• 补充PostgreSQL方言配置：在application.properties中添加PostgreSQL的Hibernate方言，确保生成适配的批量SQL：

  spring.jpa.primary.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
  

• 确保事务边界：在调用saveAll的方法上添加@Transactional注解，保证批量操作在同一事务中执行：

  @Transactional
  public void saveApplications(List<Applications> applications) {
      appRepository.saveAll(applications);
      appRepository.flush();
  }
  

• 优化数据源配置：给PostgreSQL数据源添加reWriteBatchedInserts=true参数（可在JNDI数据源配置或JDBC URL中拼接），驱动会将批量插入SQL合并为单条请求：

  jdbc:postgresql://host:port/dbname?reWriteBatchedInserts=true
  

• 避免实体提前被管理：确保从REST API获取的applications是全新的实体对象，若已被EntityManager管理，需调用entityManager.detach(entity)解除管理后再保存。

• 生产环境关闭调试配置：关闭hibernate.show_sql和hibernate.generate_statistics，减少性能损耗，同时避免批量SQL被拆分打印。

内容的提问来源于stack exchange，提问作者Gagan Noor Singh