32-bit integer primary keys cause processing delays

  • Published on Sep 24, 2024
  • 1 minute(s) read
Prev Next

Issue

Historically, application database tables were created with 32-bit integers as primary keys. This approach has proved insufficient for large volumes of submissions and may cause the application to become unresponsive.

This issue was addressed in v33.1 and v34, so any new installations have 64-bit keys by default. However, the issue still exists in previous versions of Hyperscience. 

Affected versions

Versions prior to v33.1 

Also, if your instance’s original version is earlier than v33.1, and you upgraded to a more recent release, your instance is still affected.

Affected configurations

All configurations

Fixed versions

  • v33.1 and subsequent versions of v33

  • v34 and later

Cause

Hyperscience used 32-bit integers as primary keys in older versions.

Solution / Workaround

We’ve implemented a new utility called migrate_to_bigint to migrate primary keys from 32-bit to 64-bit integers.

1. Check whether migration is necessary.

Run the following command:

run.sh manage migrate_to_bigint –dry-run

The command outputs an SQL migration plan. If the plan is empty, your database is already migrated, and no further steps are required.

An example of an empty plan appears below.

-- Stage: DROP_INTERTABLE_CONSTRAINTS
-- Stage: ALTER_TABLES
-- Stage: ADD_INTERTABLE_CONSTRAINTS

2. Run the migration using run.sh.

  1. Stop all Hyperscience instances sharing the same database.

  2. Run run.sh manage migrate_to_bigint 

Note that, depending on database size, the migration may take multiple hours to complete. We encourage you to plan appropriate maintenance windows ahead of time.

We recommend that no other resource-intensive tasks, including backups, occur at the same time as the migration. To ensure faster operations, provision additional IOPS, and allocate an adequate disk-space buffer.