Big PostgreSQL Problems: ID exhaustion

If you’re a PostgreSQL user, you may have encountered some issues with ID exhaustion already. This is a common problem, especially for databases that handle a large amount of data and/or have a high volume of insertions and deletions.

How does this happen?

In my case, it was a web app backend. There was a heavily used many-to-many relationship between the two tables. The application operated in such a way that the associations between those two tables were created and removed quite often.

One day I found that the app had crashed. On production! After a quick investigation, I found the cause of the problem - IDs in the associative table have been exhausted. It turns out that heavy associations and disassociations consumed all the available IDs.

Other causes of ID exhaustion that I met

Simply, tons of data.
INSERT ... ON CONFLICT ... - so, upserts most of the time.

Simply put, each insert, successful or not, will bump the ID’s sequence. You can even finish with a very small number of rows in the table, like a hundred or something, and yet the IDs will be exhausted.

But they said in the docs that the number of rows in the table is unlimited!

As I mentioned in the first post of this series, the maximum number of rows in a single PostgreSQL table is unlimited. So why did we end up with a broken app in the middle of the night?

Types!

To be exact, the type of the primary key field. The “default” way of creating a new table that can be spotted in many tutorials looks like this:

CREATE TABLE user (
  id serial PRIMARY KEY,
  username VARCHAR (50) UNIQUE NOT NULL,
  ...
);

As you can see in the highlighted line, the primary key (id) column, is gonna be of type serial. Also, as far as I know, most of the frameworks/ORMs choose serial as the default type for the primary key column. But what serial is?

In reality, it is just four bytes signed integer. Or, rather, a positive part of it. So, a table with a primary key column of type serial can hold up to 2^31 (a bit more than two billion) rows.

How to fix this?

The easiest fix is to migrate both the primary key column and sequence for that column to another, bigger type. If there are any tables that have a relation to this column, they need to be migrated too. The most used, bigger than serial, types for primary key columns are bigserial and UUID.

Bigserial

As you probably guessed, the bigserial type is in reality a bigint - an eight bytes signed integer. A table with a primary key of that type can hold up to 2^63 rows - it’s more than four billion times more than the serial.

Migration of both the column and sequence from serial to bigserial is rather easy and fast. But what if it’s still not enough?

UUID

UUID is an even bigger type - it’s 128 bits long! But it’s not numeric like the previous ones, so it has certain consequences.

The most important one, in this case, is that the migration of the primary key column from serial or bigserial to UUID is not that simple. The clue of the problem is that UUIDs aren’t generated in series, but randomly. The representation of the UUIDs also is different than numeric types. It all together causes that it’ll be needed to:

Rewrite all the existing primary keys - both in the problematic table and in the tables that relate to it. And this isn’t a cheap (in terms of execution time) operation at all.
Change the way they are generated - as they’re no more serial, you’ll need to generate it either on the app side or the DB side. On the DB side, the UUID-OSSP module is recommended.
Pay attention to collisions - as they’re randomly generated, the more rows you’ll have, the more likely the collisions will appear.

But besides those downsides, there are some good sides to using UUID as the primary key:

Obviously, it’s size.
As UUIDs aren’t serial, you’ll have an extra layer of “security by obscurity” for free - one can not simply guess the ID of the next or previous row in the table anymore.
As they can be generated on the app side, it enables the development of the app in a more DDD way, as you can have a known entity ID even before it’s stored in DB and push it down through the layers.

But hey, they said in the docs that the number of rows in the table is unlimited!

And obviously, that’s true, who would lie in the documentation :) As long as you have a primary key in the table, you’re limited by it. If you want to get rid of that limit, you’ll have to get rid of the primary key - and that is, too, a fix to ID exhaustion.

Exercise!

If you want to try it on your own, below you can find a very simple showcase of ID exhaustion:

CREATE TABLE tbl (
  id smallserial PRIMARY KEY
);

INSERT INTO tbl SELECT FROM generate_series(1, 32767);

INSERT INTO tbl DEFAULT VALUES;

In lines 1-3, you can see the preparation of the table that we will test on. As you can see, I used type even smaller than serial, a smallserial - two bytes signed integer.

Then in line 5, the table is filled to the brim.

And finally, in line 7 with the last one insert, we’re causing the following error:error: nextval: reached maximum value of sequence "tbl_id_seq" (32767)which means that the IDs were exhausted and this row (and the following rows) will not be inserted.