2

Is there a method to do an ALTER COLUMN in postgres 12 on an huge table without waiting a lifetime?

I try to convert a field from bigint to smallint :

ALTER TABLE huge ALTER COLUMN result_code TYPE SMALLINT;

It takes 28 hours, is there a smarter method? The table has sequences, keys and foreign keys

Gabriele D'Onufrio
  • 405
  • 1
  • 5
  • 17
  • 1
    What is the original type? If you hope to reduce storage space by changing `integer` to `smallint` it's probably not going to make a difference. –  Jul 08 '20 at 16:07
  • 1
    The smarter alternatives would depend on why you are doing it in the first place. – jjanes Jul 08 '20 at 16:14
  • the original type is bigint, and I found many fields of type bigint that can be easily smallint, and there is a 10 -15 % space gain which is necessary to me – Gabriele D'Onufrio Jul 08 '20 at 17:04

1 Answers1

3

The table has to be rewritten, and you have to wait.

If you have several columns whose data type you want to change, you can use several ALTER COLUMN clauses in a single ALTER TABLE statement and save time that way.

An alternative idea would be to use logical replication: set up an empty copy of the database (pg_dump -s), where your large table is defined with smallint columns. Replicate your database to that database, and switch over as soon as replication has caught up.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263