Table sem_id must be locked at the beginning of the transaction, to prevent new rows being inserted for the duration.We need to move the sequence over, so that id_bigint can auto increment.id_bigint ALTER TABLE sem_id ALTER COLUMN id_bigint SET DEFAULT nextval ( sem_id_seq ) ALTER TABLE minion_semid DROP COLUMN id ALTER TABLE minion_semid RENAME COLUMN id_bigint TO id COMMIT The procedure will be something like this:ġ) Create a new id_bigint column, of type bigint:īEGIN LOCK TABLE sem_id IN SHARE ROW EXCLUSIVE MODE DROP TRIGGER bigint_id_update ON sem_id ALTER SEQUENCE sem_id_seq OWNED BY sem_id.

It allows us to replicate the id column into another of a different data type. Trigger is a Postgres functionality (and many other relational databases too) which executes queries automatically upon certain events. Replicating the column with triggerįortunately, triggers come to our salvation. However, there are live processes that depend on this table, which can be paused temporarily, but definitely not for two days. In other words, we are looking at a two-day downtime of the sem_id table. That table was one-fifth of sem_id’s size and the altering command took almost ten hours to complete. We did this to another table previously when it encountered the same issue. Meanwhile it acquires an exclusive lock of the table which means no-one else could access it. Only if life was so simple! Because of the MVCC model Postgres operates on, altering a column will usually cause a rewrite of the entire table, with few exceptions. The most straightforward answer is to run an ALTER command:ĪLTER TABLE sem_id ALTER COLUMN id BIGINT Of course, a precaution would be to make this field bigint in the first place, which is an 8-byte integer and well, probably suitable for your use case unless you are counting the stars in our universe. One of our tables, called sem_id (used to store uniquely identifiable data of our ads) is approaching this limit and we must do something before it is met, at which point it’s no longer possible to add new rows to the table.

Two billion may sound a lot, but unsurprisingly for commercial use cases it is not always enough. This is an integer field by default, whose value can be up to around 2.1 billion in Postgres. You can override this behaviour by specifying a primary key explicitly, however it is not possible if you have a composite primary key, or the table simply doesn’t have one.

The Django model automatically adds an id column to your tables, which is an auto-increment integer field. Our Postgres was on version 9.4 at that time (migrated to 11 since), however the content in this post should be applicable to later versions too, same if your database is self-hosted. We use Python Django database models to manage our data. Naturally, we maintain a copy of all the ads we run in our database, which is on an Amazon RDS-Postgres architecture. Working in the marketing tech team at Lyst, a great part of our day-to-day job is to deal with various online advertising platforms.

Rename column postgres