Best practices for schema migrations and jobs in a cluster setting

[Kaushik Tadikonda]

Hello,
I’m looking for best practices and guidance on managing schema migrations and specialized tasks across multiple FHIR server instances pointing to one database.

Setup
I run multiple HAPI FHIR server instances, all pointing to a single shared database. Among these, there is a single dedicated FHIR server instance designed for specialized tasks such as schema migrations and reindexing. This instance has a more substantial CPU allocation due to the CPU-intensive nature of reindexing jobs, and the setMarkResourcesForReindexingUponSearchParameterChange setting is enabled.

Issue Observed
Upon startup, these multiple FHIR server instances attempted concurrent schema migrations because they point to the same database, leading to conflicts. We also noticed high CPU utilization across all instances. For example, here’s a failure from one of the instances:
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create index IDX_RL_TGT_v2 on hfj_res_link (target_resource_id, src_path, src_resource_id, target_resource_type, partition_id)" via JDBC Statement...

The error correlates with the migration task defined in https://github.com/hapifhir/hapi-fhir/blob/master/hapi-fhir-jpaserver-base/src/main/java/ca/uhn/fhir/jpa/migrate/tasks/HapiFhirJpaMigrationTasks.java#L525

While HAPI FHIR’s official documentation recommends using the hapi-fhir-cli migrate-database command to perform schema migrations when moving from one version to the next, I encountered these auto-migrations upon startup.

I’m seeking clarity on a few points:

1. How can I ensure that my instances do not initiate automatic schema migrations on startup?
2. Given the auto-migrations observed on startup, what is the intended use of the hapi-fhir-cli migrate-database tool? How should it be effectively utilized in a production-like setup? I was planning on running the command manually in the dedicated instance before realizing the migrations ran automatically.
3. Concerning the dedicated FHIR server instance focused on reindexing, jobs, perhaps also migrations, what configurations or settings should be unique to ensure primary instance activities are unaffected?
4. Are there any other particular precautions to maintain database consistency and integrity during upgrades when operating in a multi-instance setup?

Any insights and recommendations would be highly appreciated.

Relevant properties from application.yaml
server:
  max-http-header-size: 4MB

management:
  endpoints:
    web:
      exposure:
        include: "health,prometheus"
spring:
  main:
    allow-circular-references: true
    #allow-bean-definition-overriding: true
  flyway:
    enabled: false
  jpa:
    properties:
      hibernate.format_sql: false
      hibernate.show_sql: false
      hibernate.dialect: ca.uhn.fhir.jpa.model.dialect.HapiFhirPostgres94Dialect
      # we don't use any advanced text searching (yet)
      hibernate.search.enabled: false
  batch:
    job:
      enabled: false

Thanks,
Kaush

[James Agnew]

I'm hoping someone more familiar with Boot can chime in - but that sounds to me like hibernate is probably executing a schema update on startup. Hibernate has no mechanism itself for locking the DB before making schema updates, which can cause this kind of issue. There is some discussion on how this works here, you want this to be in mode "none" or "validate".

Cheers,

James

--
You received this message because you are subscribed to the Google Groups "HAPI FHIR" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hapi-fhir+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/78078ab6-935d-499d-bd15-21e73e8c0475n%40googlegroups.com.

[Kaushik Tadikonda]

Thank you for your insights!

I find it puzzling that Spring Boot defaults to such a potentially disruptive setting. According to it's documentation, the default setting should be "none" unless using an embedded database. As I'm utilizing PostgreSQL and have programmatically configured the data source via a bean, could this be influencing the observed behavior?

With the schema updates disabled, I assume the hapi-fhir-cli migrate-database command should be employed to manually manage schema migrations. Would you concur?

Lastly, considering our clustered setup, do you have additional recommendations around reindexing jobs or any other relevant configurations?

Thank you for your time and assistance,
Kaush