We are trying to create a new index on one of the tables we have in SQL Forge, but we are encountering some timeout errors during execution. These errors are intermittent; sometimes they occur and sometimes they don’t. They are unrelated to the amount of data in the database; the table can be completely empty and still return the same error.
The error is essentially a timeout during the execution of the index creation. This completely halts the execution of the remaining elements of the Migration Runner and also locks the database for a long time. We would like to know if this is normal and what is causing it, as if it were due to the amount of data in the database, it would be normal, but given that it occurs even in a completely empty installation, it is a cause for concern.
Thanks in advances for the help!
Hi @DiegoCernadasSexto,
The issue you’re seeing is likely because DDL operations in Forge SQL (such as CREATE TABLE and CREATE INDEX) can be quite slow. This may also be related to the logical isolation model of the database and the fact that Forge SQL runs on a distributed system with multiple nodes.
To resolve this properly, you can move migration operations into an Async Event:
- Longer Runtime: Async jobs allow for up to 900 seconds of execution, which is usually more than enough for Forge SQL.
- Retries: You can implement retry logic within the job to handle transient failures gracefully.
Ensure Idempotency (idempotent Migrations)
Since timeouts can happen after the migration is executed but before the runner marks the task as finished, your SQL must be idempotent. If you don’t use IF NOT EXISTS, the next retry will fail because the index already exists, causing a permanent “stuck” state for that customer.
Example:
CREATE INDEX IF NOT EXISTS your_index_name ON your_table (column_name);
1 Like