Shared Database Schema Migrations: The Cross-Service Gap

To add a database column when several services share the database, you must ensure that all services querying the table have deployed the new code before the migration that adds the column, or implement a backward-compatible migration strategy.

What went wrong

We experienced an UndefinedColumnError when a new column was added to our AI usage log table. The main bot service, which owned the migration, successfully deployed its release command, adding the new column. However, the marketing service, which also queries this table, had not yet deployed its updated code. When the marketing service's code executed, it attempted to read the newly added column, which it had no knowledge of, causing the system to halt.

Why it happens

Our deployment process correctly orders migrations and code for a single service. The release command runs migrations before new code starts executing for that specific service. This works perfectly when a service is the sole consumer of a table or when all consumers deploy in lockstep. However, when multiple services share a database, this internal ordering guarantee does not extend across service boundaries. A migration executed by Service A can break Service B if Service B's code is not yet aware of the schema change and attempts to access the new column.

What we changed

We adopted a two-pronged approach to mitigate this cross-service dependency:

1. Deployment Ordering: The most straightforward solution is to ensure the service that owns the migration deploys its updated code first. This means the new column is added to the database only after all services that will read it have already deployed code that can handle its absence (e.g., by treating it as nullable or ignoring it).

2. Backward-Compatible Migrations (Expand and Contract): For more complex or critical changes, we employ the "expand and contract" pattern. This involves a multi-step migration process:

   • Expand (Add Nullable): First, we add the new column as nullable. This ensures that existing code, which doesn't expect the column, will not error out. We then deploy this change to all services that read the table. This step is crucial: every reader must be updated to tolerate the column's existence, even if it's not yet populated.
   • Backfill (Optional but Recommended): If historical data needs to be populated into the new column, this is the stage to do it. This can be a separate, potentially long-running, process.
   • Write: Once all readers are updated and any backfilling is complete, we can safely update the services to start writing data to the new column.
   • Contract (Make Required/Remove): Finally, after a sufficient period has passed and we are confident that all services have been updated and are actively using the new column, we can make the column non-nullable or, if it was a temporary addition, remove it in a subsequent migration.

We also have a startup routine that dynamically adds missing columns as a safety net. However, this only benefits the service running that routine. It does not solve the fundamental problem of cross-service dependencies where one service's migration can break another service's runtime if not coordinated properly.

How to check your own system

Before deploying any schema migration that adds a column to a shared database table, ask yourself these questions:

• Which services read from this table? Identify every service that has a SELECT statement targeting the table in question.
• What is the current deployment status of each of those services? Are they all on code versions that can tolerate the absence of the new column? If not, they must be deployed before the migration.
• Can the new column be added as nullable? If yes, this is the preferred first step in a backward-compatible migration.
• Is there a fallback mechanism in place for queries? Consider wrapping queries that access the new column in a try-catch block that specifically handles UndefinedColumnError (or its equivalent in your database driver) until all services have successfully migrated.

This disciplined approach to schema migration in a microservices environment, especially with shared databases, is essential for maintaining production stability and avoiding costly outages.

Disclaimer

This article describes engineering decisions in a trading system. It is not investment advice. Trading involves substantial risk of loss and is not suitable for all investors. Past performance does not guarantee future results. Always do your own research and consider your financial situation before trading.