Alter Table Rename Column Postgres: A Solution for When Life Isn't Simple

We've been in this situation before when dealing with similar issues on another table. In such cases, it acquires an exclusive lock on the table, making it inaccessible to others until the task is completed. However, altering a column in PostgreSQL can be a bit more complicated due to its Multi-Version Concurrency Control (MVCC) model.

In most cases, changing a column will require rewriting the entire table, with few exceptions. The straightforward answer is to run an ALTER command:

ALTER TABLE sem_id ALTER COLUMN id BIGINT

Of course, it would be wise to ensure that this field is initially set as bigint, which is an 8-byte integer suitable for most use cases unless you're dealing with extremely large datasets.

One of our tables, sem_id, used to store unique identifiable data about our ads, is approaching its limit and needs attention. Once we reach the maximum value (around 2.1 billion), it will no longer be possible to add new rows to the table. For commercial use cases, this may not be sufficient.

By default, this is an integer field with a value range of up to around 2.1 billion in PostgreSQL. You can override this behavior by specifying a primary key explicitly; however, this isn't possible if you have a composite primary key or no primary key at all.

The Django model automatically adds an id column to your tables, which is an auto-increment integer field. At the time of our migration, we were running PostgreSQL version 9.4 (now upgraded to 11), and this content should be applicable to later versions as well, regardless of whether you're using a self-hosted database.

We maintain a copy of all ads in our database, which is hosted on an Amazon RDS-PostgreSQL architecture. As part of the marketing tech team at Lyst, we spend a significant portion of our day-to-day job dealing with various online advertising platforms.

Alter table rename column postgres