CODEWITHSUNDEEP
postgresqlperformancepostgresql-12
Gabriele D'Onufrio
Gabriele D'Onufrio

Reputation: 424

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

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

Upvotes: 2

Views: 2474

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247225

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.

Upvotes: 4

Related Questions