Dropping primary key column from large Postgres table

Question

I have a table that currently has less than 2 billion records, and its primary key is set as "Serial" data type, which can handle up to 2 billion records. However, I anticipate that the limit will be reached soon.

Since I don't use the primary key anywhere in the query and it doesn't have any relation with any other table, I am considering dropping the primary key column itself.

My concern is how much time it will take to drop the column from the table, and whether it will cause any lock or downtime issues for the other queries accessing the table.

Could you please explain the process of dropping a column in PostgreSQL?

Answer 1

ALTER TABLE tab DROP id;

will get rid of the column in a matter of milliseconds unless you have long-running transactions that access the table. The statement will fail if the primary key is referenced anywhere else.

What happens is just a simple metadata operation that marks the column as "deleted". The table itself is not touched, and the column values are still there. If you update a row, these "dead" data get removed.

You should not have a table without a primary key. For one, it prevents you from having foreign keys, and then you can end up with duplicate rows. Look at answers like this and this for alternatives.