INT → BIGINT, a SQL Story

November 22, 2024

Read Time: ~12 minutes

Running out of range on a primary key in SQL Server is a ticking time bomb, especially for high-traffic production tables. This article dives deep into the process of migrating a primary key column from

INT

BIGINT , tackling challenges like constraints, clustered indexes, and data synchronization. Learn the tested strategies, performance tweaks, and lessons from a real-world scenario involving 650 million rows—ensuring scalability and reliability for your database. Perfect for engineers navigating similar database dilemmas.

“Hey, the primary key in one of our hottest tables is almost out of range in production…”

My initial thoughts when presented with the above problem was, “Oh, no big deal, we’ll just update it, right? WRONG!

Introduction

In SQL Server, the INT data type ranges from -2,147,483,648 to 2,147,483,647. When a primary key (PK) column with the IDENTITY(1,1) property approaches this upper limit, it poses a significant risk to database operations. This article details the challenges and solutions encountered during the migration of a PK column from INT to BIGINT in a high-traffic production environment.

Understanding the problem

The primary key uniquely identifies each row in a table. An IDENTITY(1,1) property ensures that each new row receives a sequential integer value starting from 1. However, SQL Server does not reuse identity values from deleted rows, leading to potential exhaustion of the INT range. In our case, the PK value had reached approximately 1.9 billion, with the table containing around 650 million rows.

What happens when you run out of range on a PK w/ identity turned on?

Well, nothing good! Every single new insert will start to fail as the data type in the PK column can’t physically hold a number higher than the top of the range!

Here’s what our table looked like to begin with:

CREATE TABLE [dbo].[LargeTable](
    [LargeTableId] [int] IDENTITY(1,1) NOT NULL,
    [ErrorMessage] [nvarchar](max) NULL,
    [DateCreated] [datetime] 
    CONSTRAINT [PK_dbo.LargeTable] PRIMARY KEY CLUSTERED ([LargeTableId] ASC) 
    WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
    ON [PRIMARY]
)
ON [PRIMARY]

Research and Testing Begins…

Attempt 1: Direct Column Alteration

“Can we just ALTER TABLE LargeTable ALTER COLUMN LargeTableId BIGINT?”

Ok, let’s test this out in a local database and see what happens:

Well, that didn’t work, but ok, let’s work through the error. This approach failed due to the existing PK constraint, which prevented the alteration of the column's data type.

Attempt 2: Utilizing Row Compression

What about row compression?

I came across a blog post that mentioned the ability to update a column’s data type from INT to BIGINT by utilizing row compression. Ultimately, the same issue with the PK constraint stopped this approach from working but remains a great option for non-constrained columns.

Attempt 3: Dropping and Recreating the Primary Key

We can drop the constraint and then run the ALTER command.

That worked! Let’s confirm this will work though in a real production situation. If possible, the best way to do something like this is request a copy of the database (specifically, just the actual table and any related tables) be made temporarily somewhere you can test this out.

Now that we have a copy of what the table looks like in production, let’s attempt the above command.

When this was attempted, not only did the command never finish, but it was drawing down a ton of server resources while it was trying to apply these changes!

The reason this was occurring has to do with how data is structured in SQL server. If you look at the PK definition, you’ll notice that it’s CLUSTERED. This means that the way that data is stored in this table is dictated by the PK value. Unfortunately for us, this also means that if we were to drop the PK, the index would go away and the table would have to reorganize itself as a HEAP instead of the B-Tree structure that it was with the Clustered PK.

We’re now in a situation where we can’t realistically get rid of the PK or alter it in any way! Sounded like the only option was to migrate to a new table and backfill all the data over (remember this table is highly used and contains well over 650 million rows!)

Attempt 4: Creating a New Table and Data Migration

Create a copy of the table but with the PK being a BIGINT and come up with a syncing mechanism.

This approach is generally what other DBA experts recommend when dealing with a production table that needs this treatment but what wasn’t clear from further research was a consistent implementation of approach.

A few approaches that were considered:

Option 1: Backfill new table with rows from current table and create a TRIGGER to keep new rows added or updated in the current table in sync with the new one.
Option 2: Modify the application code to insert/update new records in both tables while backfilling. Then, once everything has been backfilled, cut over to using just the new table.
Option 3: Create a script to backfill and keep records in sync between the current and new tables. Once finished, take a brief downtime to swap the names of the tables, keys, and indexes (more details on this later!).

Ultimately, Option 3 was chosen as the most straightforward, least risky, and lowest amount of required downtime with no impact to application code necessary.

Lessons Learned!

Migrating a primary key column from INT to BIGINT is far from a straightforward task, especially in production environments with large, high-traffic tables. Here are the critical lessons we learned through this process:

1. Thorough Testing is Essential

Simulate the Production Environment: Ensure your test environment closely mirrors your production setup in terms of data volume, schema, and concurrent workloads. This helps identify challenges that may not surface in a simplified test.
Test Every Step: Validate each component of the migration strategy independently, such as backfill scripts, synchronization mechanisms, and cutover plans.
Iterate on Test Results: Incorporate findings from test runs to refine scripts and approaches. For example, adjusting batch sizes or indexing strategies can significantly improve performance.

2. Understand the Impact of Constraints

Primary Key Constraints: The clustered index associated with a primary key introduces complexity when altering a column. Dropping and recreating constraints can lead to resource-intensive operations and downtime.
Data Organization: A clustered index dictates the physical order of data in the table. Altering it often requires the table to reorganize, which is costly for large datasets.

3. Optimize for Performance

Monitor Resource Usage: Large-scale data migrations can consume significant server resources. Use tools like SQL Server Profiler or Extended Events to monitor performance and adjust as needed.
Batch Processing: Migrating data in manageable batches helps avoid excessive locking and reduces the strain on the database.
Optimize Query Plans: Variables in SQL scripts can sometimes lead to suboptimal execution plans. Hardcoding constants, where feasible, can improve performance significantly, as seen in our experience.

4. Plan for Synchronization and Downtime

Data Synchronization: Implementing a robust synchronization mechanism between old and new tables ensures no data is lost during the migration. Triggers, scripts, or dual-write strategies can help achieve this.
Downtime Management: While minimizing downtime is ideal, a brief period may be necessary for schema changes. Schedule this during low-traffic windows and communicate with stakeholders well in advance.

5. Document the Process

Detailed Records: Document every step, from initial research and testing to the final implementation. Include scripts, configurations, and decision rationale.
Reusable Templates: A well-documented migration process can serve as a template for future projects, saving time and effort.

About Matthew Preciado

With over 15 years of experience in software engineering, I have worked on various web development, cloud services, and e-commerce projects for leading companies in the travel, home services, and automotive industries.

As a Senior Software Engineer at Empower, I work on cross-cutting concerns on the Platform Team, enabling other developers throughout the organization.