Thread: Column type modification in big tables

Hello all,

We have a postgres table which is a range partitions on a timestamp column having total size ~3TB holding a total ~5billion rows spanning across ~150 daily partitions and there are ~140+columns in the table. Also this table is a child to another partition table. And we have partition creation handled through pg_partman extension on this table.

We have a requirement of modifying the existing column lengths as below . So doing it directly through a single alter command will probably scan and rewrite the whole table which may take hours of run time.

So trying to understand from experts what is the best possible way to tackle such changes in postgres database? And if any side effects we may see considering this table being child to another one and also dependency with pg_partman extension.

two of the columns from varchar(20) to numeric(3)
one of the columns from varchar(20) to varchar(2)
one of the columns from Number(10,2) to Numeric(8,2)

Others may correct but i think, If you don't have the FK defined on these columns you can do below.


--Alter table add column which will be very fast within seconds as it will just add it to the data dictionary.

ALTER TABLE tab1 ADD COLUMN new_column1 NUMERIC(3),   new_column2 varchar2(3);


-- Back populate the data partition wise and commit, if it's really needed

UPDATE tab1_part1 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
UPDATE tab1_part2 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
UPDATE tab1_part3 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
.....

--Alter table drop old columns which will be very fast within seconds as it will just drop it from the data dictionary.

ALTER TABLE your_table DROP COLUMN old_column1, DROP COLUMN old_column2; 

On Wed, Aug 7, 2024 at 4:51 PM sud <suds1434@gmail.com> wrote:

Others may correct but i think, If you don't have the FK defined on these columns you can do below.


--Alter table add column which will be very fast within seconds as it will just add it to the data dictionary.

ALTER TABLE tab1 ADD COLUMN new_column1 NUMERIC(3),   new_column2 varchar2(3);


-- Back populate the data partition wise and commit, if it's really needed

UPDATE tab1_part1 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
UPDATE tab1_part2 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
UPDATE tab1_part3 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
.....

--Alter table drop old columns which will be very fast within seconds as it will just drop it from the data dictionary.

ALTER TABLE your_table DROP COLUMN old_column1, DROP COLUMN old_column2; 

Thank you so much.

I understand this will be the fastest possible way to achieve the column modification.

But talking about the dropped column which will be sitting in the table and consuming storage space, Is it fine to leave as is or auto vacuum will remove the column values behind the scene and also anyway , once those partitions will be purged they will be by default purged. Is this understanding correct?

 And also will this have any impact on the partition maintenance which is currently done by pg_partman as because the template table is now different internally(not from outside though). Will it cause conflict because of those dropped columns from the main table?

On Wed, Aug 7, 2024 at 5:00 PM Lok P <loknath.73@gmail.com> wrote:

On Wed, Aug 7, 2024 at 4:51 PM sud <suds1434@gmail.com> wrote:

Others may correct but i think, If you don't have the FK defined on these columns you can do below.


--Alter table add column which will be very fast within seconds as it will just add it to the data dictionary.

ALTER TABLE tab1 ADD COLUMN new_column1 NUMERIC(3),   new_column2 varchar2(3);


-- Back populate the data partition wise and commit, if it's really needed

UPDATE tab1_part1 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
UPDATE tab1_part2 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
UPDATE tab1_part3 SET new_column1 = CAST(old_column1 AS NUMERIC(3)), new_column2 = CAST(old_column2 AS varchar2(3)) ;
commit;
.....

--Alter table drop old columns which will be very fast within seconds as it will just drop it from the data dictionary.

ALTER TABLE your_table DROP COLUMN old_column1, DROP COLUMN old_column2; 

Thank you so much.

I understand this will be the fastest possible way to achieve the column modification.

But talking about the dropped column which will be sitting in the table and consuming storage space, Is it fine to leave as is or auto vacuum will remove the column values behind the scene and also anyway , once those partitions will be purged they will be by default purged. Is this understanding correct?

 And also will this have any impact on the partition maintenance which is currently done by pg_partman as because the template table is now different internally(not from outside though). Will it cause conflict because of those dropped columns from the main table?

I think leaving the table as is after the dropping column will be fine for you because your regular partition maintenance/drop will slowly purge the historical partitions and eventually they will be removed. But if you update those new columns with the old column values, then autovacuum should also take care of removing the rows with older column values (which are dead actually) .

Not sure if pg_partman will cause any issue ,as because the table now has the column data type/length changed. Others may confirm.

Can anybody suggest any other possible way here.

On Thu, Aug 8, 2024 at 2:39 PM Lok P <loknath.73@gmail.com> wrote:

Can anybody suggest any other possible way here.

Sure - how about not changing the column type at all?

> one of the columns from varchar(20) to varchar(2)

ALTER TABLE foobar ADD CONSTRAINT twocharplease CHECK (length(mycol) <= 2) NOT VALID;

> one of the columns from Number(10,2) to Numeric(8,2)

ALTER TABLE foobar ADD CONSTRAINT eightprecision CHECK (mycol <= 10^8) NOT VALID;

> two of the columns from varchar(20) to numeric(3)

This one is trickier, as we don't know the contents, nor why it is going to numeric(3) - not a terribly useful data type, but let's roll with it and assume the stuff in the varchar is a number of some sort, and that we don't allow nulls:

ALTER TABLE foobar ADD CONSTRAINT onekorless CHECK (mycol::numeric(3) is not null) NOT VALID;

You probably want to check on the validity of the existing rows: see the docs on VALIDATE CONSTRAINT here:

https://www.postgresql.org/docs/current/sql-altertable.html

Thank you so much. Will definitely try to evaluate this approach. The Only concern I have is , as this data is moving downstream with exactly the same data type and length , so will it cause the downstream code to break while using this column in the join or filter criteria. Also I believe the optimizer won't be able to utilize this information while preparing the execution plan.

 Another thing , correct me if wrong, My understanding is  , if we want to run the "validate constraint" command after running this "check constraint with not valid" command, this will do a full table scan across all the partitions , but it's still beneficial as compared to updating the columns values for each rows. Correct me if I'm wrong. 

On Fri, Aug 9, 2024 at 6:39 AM Lok P <loknath.73@gmail.com> wrote:

Thank you so much. Will definitely try to evaluate this approach. The Only concern I have is , as this data is moving downstream with exactly the same data type and length , so will it cause the downstream code to break while using this column in the join or filter criteria. Also I believe the optimizer won't be able to utilize this information while preparing the execution plan.

Yes, this is not as ideal as rewriting the table, but you asked for another approaches :) As to the impact of your downstream stuff, I think you have to try and see. Not clear what you mean by the optimizer, it's not going to really care about numeric(10) versus numeric(8) or varchar(20) vs varchar(2). It's possible the varchar -> numeric could cause issues, but without real-world queries and data we cannot say.

 

 Another thing , correct me if wrong, My understanding is  , if we want to run the "validate constraint" command after running this "check constraint with not valid" command, this will do a full table scan across all the partitions , but it's still beneficial as compared to updating the columns values for each rows. Correct me if I'm wrong. 

Yes, it needs to scan the entire table, but it's a lightweight lock, won't block concurrent access, will not need to detoast, and makes no table or index updates. Versus an entire table rewrite which will do heavy locking, take up tons of I/O, update all the indexes, and generate quite a lot of WAL. 

On Fri, Aug 9, 2024 at 9:19 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:

On Fri, Aug 9, 2024 at 6:39 AM Lok P <loknath.73@gmail.com> wrote:

Thank you so much. Will definitely try to evaluate this approach. The Only concern I have is , as this data is moving downstream with exactly the same data type and length , so will it cause the downstream code to break while using this column in the join or filter criteria. Also I believe the optimizer won't be able to utilize this information while preparing the execution plan.

Yes, this is not as ideal as rewriting the table, but you asked for another approaches :) As to the impact of your downstream stuff, I think you have to try and see. Not clear what you mean by the optimizer, it's not going to really care about numeric(10) versus numeric(8) or varchar(20) vs varchar(2). It's possible the varchar -> numeric could cause issues, but without real-world queries and data we cannot say.

 

 Another thing , correct me if wrong, My understanding is  , if we want to run the "validate constraint" command after running this "check constraint with not valid" command, this will do a full table scan across all the partitions , but it's still beneficial as compared to updating the columns values for each rows. Correct me if I'm wrong. 

Yes, it needs to scan the entire table, but it's a lightweight lock, won't block concurrent access, will not need to detoast, and makes no table or index updates. Versus an entire table rewrite which will do heavy locking, take up tons of I/O, update all the indexes, and generate quite a lot of WAL. 

Thank you so much Greg.

Considering the option, if we are able to get large down time to get this activity done.

Some teammates suggested altering the column with "USING" Clause. I am not really able to understand the difference,  also when i tested on a simple table, it seems the "USING" clause takes more time as compared to normal ALTER. But again I don't see any way to see the progress and estimated completion time. Can you share your thoughts on this? 

ALTER TABLE foobar ALTER COLUMN mycol TYPE NUMERIC(3) USING mycol::NUMERIC(3);
VS
ALTER TABLE foobar ALTER COLUMN mycol TYPE NUMERIC(3) ;

*****

Another thing also comes to my mind whether we should just create a new partition table(say new_part_table) from scratch from the DDL of the existing table(say old_part_table) and then load the data into it using command (insert into new_part_table.. select..from old_part_table). Then create indexes and constraints etc, something as below.

Will this approach be faster/better as compared to the simple "alter table alter column approach" as above, considering we will have 4-6 hours of downtime for altering three different columns on this ~5TB table?

-- Steps

Create table exactly same as existing partition table but with the modified column types/lengths.

drop indexes ;  (Except PK and FK indexes may be..)
drop constraints;

insert into new_part_table (...) select (...) from old_part_table;

create indexes concurrently ;
create constraints; (But this table is also a child table to another partition table, so creating the foreign key may be resource consuming here too).

drop the old_part_table;
rename the new_part_table to old_part_table;
rename all the partitions;

VACUUM  old_part_table  ;

ANALYZE  old_part_table  ; 

My 2cents. 

If you have enough time then from a simplicity point of view, your single line alter command may look good, but how are you going to see  the amount of progress it has made so far and how much time it's going to take to finish. And you got ~6hrs of down time but if it fails at 5th hour then you will be in a bad position. 

Can someone through some light , in case we get 5-6hrs downtime for this change , then what method should we choose for this Alter operation?

it seems the "USING" clause takes more time as compared to normal ALTER. But again I don't see any way to see the progress and estimated completion time. Can you share your thoughts on this?

 Will this approach be faster/better as compared to the simple "alter table alter column approach" as above

On Sat, Aug 10, 2024 at 5:06 PM Lok P <loknath.73@gmail.com> wrote:

 

Can someone through some light , in case we get 5-6hrs downtime for this change , then what method should we choose for this Alter operation?

We can't really answer that. Only you know what resources you have, what risk/reward you are willing to handle, and how long things may take. For that latter item, your best bet is to try this out on the same/similar hardware and see how long it takes. Do a smaller table and extrapolate if you need to. Or promote one of your replicas offline and modify that. I've given you a low-risk / medium-reward option with check constraints, but for the ALTER TABLE options you really need to try it and see (on non-prod).

On Sat, Aug 10, 2024 at 5:06 PM Lok P <loknath.73@gmail.com> wrote:

 

Can someone through some light , in case we get 5-6hrs downtime for this change , then what method should we choose for this Alter operation?

We can't really answer that. Only you know what resources you have, what risk/reward you are willing to handle, and how long things may take. For that latter item, your best bet is to try this out on the same/similar hardware and see how long it takes. Do a smaller table and extrapolate if you need to. Or promote one of your replicas offline and modify that. I've given you a low-risk / medium-reward option with check constraints, but for the ALTER TABLE options you really need to try it and see (on non-prod).

On Sat, Aug 10, 2024 at 5:06 PM Lok P <loknath.73@gmail.com> wrote:

 

Can someone through some light , in case we get 5-6hrs downtime for this change , then what method should we choose for this Alter operation?

We can't really answer that. Only you know what resources you have, what risk/reward you are willing to handle, and how long things may take. For that latter item, your best bet is to try this out on the same/similar hardware and see how long it takes. Do a smaller table and extrapolate if you need to. 

> On 15 Aug 2024, at 14:15, Lok P <loknath.73@gmail.com> wrote:

(…)
Are all those partitions critical, or only a relative few?

If that’s the case, you could:
        1) detach the non-critical partitions
        2) take the system down for maintenance
        3) update the critical partitions
        4) take the system up again
        5) update the non-critical partitions
        6) re-attach the non-critical partitions

That could shave a significant amount of time off your down-time. I would script the detach and re-attach processes first, to save some extra.

On Thu, Aug 15, 2024 at 9:18 PM Alban Hertroys <haramrae@gmail.com> wrote:

> On 15 Aug 2024, at 14:15, Lok P <loknath.73@gmail.com> wrote:

(…)
Are all those partitions critical, or only a relative few?

If that’s the case, you could:
        1) detach the non-critical partitions
        2) take the system down for maintenance
        3) update the critical partitions
        4) take the system up again
        5) update the non-critical partitions
        6) re-attach the non-critical partitions

That could shave a significant amount of time off your down-time. I would script the detach and re-attach processes first, to save some extra.

Thank you so much. 

The partition table which we are planning to apply the ALTER script is a child table to another big partition table. And we have foreign key defined on table level but not partition to partition. So will detaching the partitions and then altering column of each detached partition and then re-attaching will revalidate the foreign key again? If that is the case then the re-attaching partition step might consume a lot of time. Is my understanding correct here?

Additionally , if we are okay with the 7.5hrs of down time , is my calculation/extrapolation of total time consumption based on a sample table,  for direct alter, accurate? Because, in that case , I was thinking it's less complex and also less error prone to just do it in a single alter command rather than going for multiple steps of detach, alter, attach partition.