1

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?

1 Answer 1

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.

4
  • Could you explain how it works? Like does it create a separate table without the deleting-column and copying data over? OR, it removes, let's say, some pointer to the dropping column?
    – sujeet
    Commented Feb 17, 2023 at 7:01
  • 1
    I have added an explanation of how it works. Commented Feb 17, 2023 at 7:18
  • Is it safe to claim the space after deleting the column using VACUUM tab(id)?
    – sujeet
    Commented Feb 17, 2023 at 9:39
  • 1
    Neither VACUUM nor VACUUM (FULL) will reclaim the space from a dropped column. You have to update the row, like I wrote. Commented Feb 17, 2023 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.