How to Upgrade Integer Sizes in Django and Postgres

The Problem

The other day my computer ran out of numbers.

I was pretty surprised. Well, wait. Let’s go back a bit to before I was surprised.

It was a calm, still morning. There I was, having a great day, enjoying a cup of joe, when all of a sudden my database started throwing errors.

Here we go again… I chased down the stacktrace and at the heart of it was this error message—

ERROR: nextval: reached maximum value of sequence "object_id_seq" (2147483647)

When I saw this error in the logs, my eyes immediately became affixed to the magic number—the ever elusive 2,147,483,647.

As someone who has been swimming around in the waters of software consultancy for a handful of years now, seeing this number in the wild was almost like seeing the Loch Ness Monster.

So what’s special about this number?

Well, if you’re a human, this is just a random number. But if you’re a computer, 2,147,483,647 is the highest number you can represent in the language in which you speak—binary. If you use 31 bits (a.k.a., 31 zeroes or ones), plus one extra bit to denote negative numbers, then 2,147,483,647 is as high as you can count.

As a textbook would put it, 2,147,483,647 is the maximum value of a signed 32-bit integer!

The Integer

Signed 32-bit integers are an important datatype to many systems.

My first encounter with this number was as a teenager. 2,147,483,647 is the maximum amount of gold you can have in Runescape, a popular online videogame, which was originally written in Java. It just so happens that a player’s gold is stored in an int datatype, which in Java is, you guessed it, a signed 32-bit integer.

32 bits is situated in a bit of a Goldilocks zone. It’s big enough to store most numbers relevant to most applications, while not being overly large and wasteful. That’s why it pops up so much in computer programming. In most high-level programming languages (Ruby, Python, Java, etc.), it’s the default for storing integers.

Django and Postgres

The application in question is written in Python, using the Django framework, and has a Postgres database attached to it.

By default, objects in Django have an id< field. Even if it’s not explicitly defined in the model, Django will assume you want this definition:

id = models.AutoField(primary_key=True)

Django’s ORM will translate this to SQL and dump it into your Postgres database. The id field is so ubiquitous in these modern frameworks (Django, Ruby on Rails, etc.) that you might take it for granted. It’s an example of boilerplate code that these frameworks do a great job of abstracting away, so you can focus on the core, bespoke parts of your application.

But under the hood, what Django’s ORM is really creating is something like—

-- Step 1: Create the sequence for the id column
CREATE SEQUENCE object_id_seq
  AS integer
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

-- Step 2: Create the table with id, name, and created_at columns
CREATE TABLE object (
  id integer NOT NULL DEFAULT nextval('object_id_seq') PRIMARY KEY,
  name varchar(255) NOT NULL,
  created_at timestamp with time zone NOT NULL
);

-- Step 3: Link the sequence to the id column
ALTER SEQUENCE object_id_seq OWNED BY object.id;

Here we can see the translation between Django and Postgres. I want to highlight the AS integer, and id integer parts of this SQL, as they reveal that the default identifier in Django actually translates to the signed 32-bit integer datatype in Postgres.

Now that I understood how identifiers are stored in Django and Postgres, I could start to address the issue presented in my application’s error message.

The Fix

Here again is the error message—

ERROR: nextval: reached maximum value of sequence "object_id_seq" (2147483647)

So there are two parts to our problem. Firstly, the datatype of our id field needs to be upgraded to allow for numbers larger than 2,147,483,647. Secondly, we need to similarly upgrade our sequence. Like the SQL code showed earlier, the id field in Django uses a Postgres sequence to automatically increment the value of the id with each new record in the table. We’ll need to change the datatype of the sequence as well.

So the first step to fixing this issue is to explicitly define our identifier in our Django model. Again, there probably isn’t an explicitly defined id field in your model, so we’ll add a new line to override the implicitly defined AutoField with BigAutoField.

from django.db import models

class YourModel(models.Model):
  id = models.BigAutoField(primary_key=True) # this line is new
  # other fields go here

After adding this to the model, we can run—

python manage.py makemigrations
python manage.py migrate

That should update the database column which is storing the object’s identifier to use a BIGINT type. A BIGINT is a signed 64-bit integer.

Our max value with the signed 32-bit integer was 2,147,483,647, but now with 64 bits, we can store up to 9,223,372,036,854,775,807. That should buy us some time.

And in most cases, this should update the sequence which is in charge of auto-incrementing the identifier as well.

However, it is possible that the sequence’s last value can become out of sync with the current highest value in your table. If this is the case, the migration might not go as seamlessly, and you might need to get out the more fine, delicate tools, and do some further investigation.

Manual Intervention

If we’re going to be performing database surgery, we’ll want to take the proper precautions. Make sure you have a database backup, or better yet, have access to a testing or staging environment where you can perform these changes before the real deal.

Now, the first step is to get CLI access to the database.

With Postgres, you’ll probably run something like—

psql -h -U -d

Once you’re in, you can explore a little bit.

\dt will list all of your database tables, which might look something like—

List of relations
Schema  | Name               | Type | Owner
--------+--------------------+-------+---------
public  | myapp_object       | table | postgres

\ds to list all of the sequences—

List of sequences
Schema  | Name                      | Type     | Owner
--------+---------------------------+----------+---------
public  | myapp_object_id_seq       | sequence | postgres

Now we’re getting somewhere.

You can list the details of the table with \d myapp_object.

> \d myapp_object

Table "public.myapp_object"
Column      | Type     | Collation | Nullable | Default
------------+----------+-----------+----------+----------------------------------
id | bigint |          | not null  | nextval('myapp_object_id_seq'::regclass)>/pre>

We can see the type is bigint. If you see this, then that means the migration worked at least up until this point.

Now let’s look at the sequence. We can see the details of the sequence with \d myapp_object_id_seq.

> \d myapp_object_id_seq

Sequence "public.myapp_object_id_seq"
Type      | Increment By   | Min Value     | Max Value     | Start Value     | Cache Value     | Cycle
----------+----------------+---------------+---------------+-----------------+-----------------+---------
integer   | 1              | 1             | 2147483647     | 1              | 1               | no

Let’s say this was my output when I inspected the sequence. Well, that’s no good. There is an inconsistency between the datatype of the object’s identifier and the sequence.

Some other things we can do—

SELECT MAX(id) FROM myapp_object;

This will give us the max id from the object table. In my case, this was 2,147,483,647, confirming the problem at hand.

Then, as a last resort, you can run—

ALTER SEQUENCE myapp_object_id_seq AS bigint;

—to manually set the datatype of the sequence to a bigint.

Once this is done, you can run—

SELECT setval('myapp_object_id_seq', (SELECT MAX(id) FROM myapp_object) + 1);

—in order to make sure that the sequence is aligned with the max id of the objects table.

These types of manual fixes are not always pretty, but can come in handy in a pinch.

Conclusion

Hopefully this post was helpful, whether it be in integer overflow prevention, or reminding you of a few psql commands that are available for deeper database inspection.

Maybe you yourself might run into 2,147,483,647, and have to upgrade to a signed 64-bit integer. But there’s no way, you’ll run into 9,223,372,036,854,775,807, right??