Seamless Transition: Handling Exhausted Auto-Increment IDs

Posted on In Views:

When we are using a relational database, we often use incremental ID as the primary key (e.g. AUTO_INCREMENT in MySQL or SERIAL in Postgres), have you ever thought about what will happen if the incremental ID is used up?

For example, Postgres raises the following error.
ERROR: nextval: reached maximum value of sequence "<table_name>_<pk>_seq".

Don’t feel hard of happening, it is possible to accidentally set the primary key to SERIAL instead of BIGSERIAL, and then a value larger than 2147483647 will overflow. For a database, it’s not a big number.

If such a problem unfortunately occurs, what should we do to solve this?

There are a lot of articles on how to migrate data from the original table to a new table, but in fact, I don’t recommend it. Of course, it’s easiest to migrate all the data to a new table at once, but there are several problems like the following.

  1. Migrating a large volume data consumes a lot of database resources, and will have a significant impact on the production environment.
  2. Data inconsistency is inevitable during the migration process. For example, if data is migrated to a new table and then a new version of application is deployed to read and write the new table, if there is data written to the old table during the migration period, then there will be inconsistency.
  3. If there is a downstream application that listens for updates to the source database (or CDC), then the downstream application will be flooded with events.

Therefore, I recommend that applications support both old and new tables. Creating new data is always written to the new table, but reading or updating is done according to whether the data is in the old table or the new table.

But how? For querying, should I look for the new table first and then the old table as in the example below?

1
2
3
4
5
6
if (ret := find_orig_table(params)) is not None:  
    return ret  
elif (ret := find_new_table(params)) is not None:  
    return ret  
else:  
    return None

Actually, there is an even simpler approach.

Solution Detail

To better describe the solution, let’s prepare the test environment with Postgres.

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE table_with_auto_increment (  
    id SERIAL PRIMARY KEY,  
    common_column1 VARCHAR(50),  
    common_column2 INTEGER,  
    common_column3 TEXT  
);  
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";  
CREATE TABLE table_with_uuid_primary_key (  
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,  
    common_column1 VARCHAR(50),  
    common_column2 INTEGER,  
    common_column3 TEXT  
);

Above is an old table (table_with_auto_increment) with SERIAL as primary key, it is easy to overflow, so we want to replace it with a new table (table_with_uuid_primary_key) with UUID as primary key.

There is a lot of debate on whether UUID as primary key is good or bad, but that’s not the point of this article, it’s just one of the examples.

Based on such a table structure, it will have a DTO (data transfer object) similar to the following.

1
2
3
4
5
6
7
from dataclasses import dataclass  
@dataclass  
class DTO:  
    id: ...  
    common_column1: ...  
    common_column2: ...  
    common_column3: ...

The DTO will be the same whether using the new table or the old table, so the business logic does not have to be modified during the migration process.

Then, what’s the problem of looking for the new table first and then the old one? The main problem is twice accessing the database is two round trips, which leads to one more expense, and secondly, the two functions also have maintenance complexity. Therefore, it would be great if there is a way to use one database access to assemble a DTO from two tables.

The first idea was to use UNION. However, it is worth mentioning that UNION requires both tables to have exactly the same datatype, and the id of our old and new tables are of different types.

Therefore, we need to convert the id type at the same time with UNION, and converting both of them to text is the simplest way. This is done in the following example.

1
2
3
4
5
6
7
SELECT id::text, common_column1, common_column2, common_column3  
FROM table_with_auto_increment  
WHERE common_column1 = 'test' AND common_column2 = 123  
UNION ALL  
SELECT id::text, common_column1, common_column2, common_column3  
FROM table_with_uuid_primary_key  
WHERE common_column1 = 'test' AND common_column2 = 123;

Our search criteria is common_column1 is test and common_column2 is 123, so we search both tables directly and combine the results. In general, only one of the two tables will have data ( with no bugs ), so it is very efficient to create the DTO in this way.

In this example, the purpose of creating two tables is to change ids seamlessly, so we can simplify the process through type conversion.

However, there are other scenarios where our functionality iteration leads to breaking change, so we make two tables to allow the old application to gradually transition to the new application, it may not be possible to simply do type conversion (maybe the types are not compatible at all).

So, we can consider to use OUTER JOIN method, and use the common field as the key of JOIN, so that the result will be the union of the fields of the two tables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  
    t1.id AS t1_id, t2.id AS t2_id,  
    COALESCE(t1.common_column1, t2.common_column1) AS common_column1,  
    COALESCE(t1.common_column2, t2.common_column2) AS common_column2,  
    COALESCE(t1.common_column3, t2.common_column3) AS common_column3  
FROM  
    table_with_auto_increment t1  
FULL OUTER JOIN  
    table_with_uuid_primary_key t2  
ON t1.common_column1 = t2.common_column1 AND t1.common_column2 = t2.common_column2  
WHERE  
    t1.common_column1 = 'test' AND t1.common_column2 = 123  
OR  
    t2.common_column1 = 'test' AND t2.common_column2 = 123;

The above example is very similar to UNION, we need to find out common_column1 is test and common_column2 is 123 in two tables, but we need to list the remaining columns.

Nevertheless, the whole SQL is a bit annoying, such as the series of COALESCE, so I suggest some template rendering to make the development more intuitive.

Here’s an example of using Python with jinja2.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
from jinja2 import Template  
columns = ['common_column1', 'common_column2', 'common_column3']  
template_str = """SELECT  
    t1.id AS t1_id, t2.id AS t2_id  
{% for column in columns %}  
    , COALESCE(t1.{{ column }}, t2.{{ column }}) AS {{ column }}  
{% endfor %}  
FROM  
    table_with_auto_increment t1  
FULL OUTER JOIN  
    table_with_uuid_primary_key t2  
ON t1.{{condition_column1}} = t2.{{condition_column1}} and t1.{{condition_column2}} = t2.{{condition_column2}}  
WHERE  
    t1.{{condition_column1}} = '{{condition_value1}}' AND t1.{{condition_column2}} = {{condition_value2}}  
OR  
    t2.{{condition_column1}} = '{{condition_value1}}' AND t2.{{condition_column2}} = {{condition_value2}};  
"""  
condition_column1 = 'common_column1'  
condition_column2 = 'common_column2'  
condition_value1 = 'test'  
condition_value2 = 123  
template = Template(template_str)  
query = template.render(  
    columns=columns,  
    condition_column1=condition_column1, condition_column2=condition_column2,  
    condition_value1=condition_value1, condition_value2=condition_value2  
)  
print(query)

At this point, we’ve been able to make the transition from the old table structure to the new one seamless for the application.

Conclusion

Although in this article we have introduced how to solve the problem of id exhaustion, it can be used in any scenario where we need to make a breaking change to the table structure of the database.

When the data volume is small, breaking change is not a big deal, we just need to migrate all the old tables to the new ones, but when the data volume is huge, such an approach will increase a lot of operation risks.

A complete table migration involves not only the data migration itself, but also how to keep the production environment unaffected. The most common problem we encountered is how to synchronize the data to the new table if there are still clients writing to the old table during the data migration period.

In addition, in order to avoid affecting the production environment, we usually have to choose the off-peak time to perform the migration, which inevitably requires many departments to work overtime together.

For instance, the development team needs to release a new version of the program, the operation team needs to migrate the data, the SRE team needs to monitor the risk of online, and the QA team needs to validate. It is not worth for me to work so hard just for the sake of table migration.

Therefore, it’s best to be able to upgrade your application to a new version seamlessly. This article introduces a simple but effective practice to avoid many unnecessary breaking changes. Of course, there are many different ways to achieve this, if you have some good solutions, please feel free to share them with me.

Stackademic

Thank you for reading until the end. Before you go:

  • Please consider clapping and following the writer! 👏
  • Follow us on Twitter(X), LinkedIn, and YouTube.
  • Visit Stackademic.com to find out more about how we are democratizing free programming education around the world.

Originally published on Medium