Replacing UPDATE SQL vs Drop & Add COLUMN with def

问题:

I have a requirement to update a text column for all rows of a RDBMS table (PostgresSQL) with a fixed text. Currently, table has around 700k records but that is expected to grow. SpringJDBC batch update is slow with following query,

UPDATE TABLE TABLENAME SET columnname="FIXED VALUE"

This columns is NULLABLE. Is it advisable to replace this single UPDATE statement with these two steps?

1. First drop this column from table

2.Readd the column to table specifying default value to be "FIXED VALUE"

I tested code and its very faster than UPDATE statement.

I just wanted to ask you folks if there is any negative side to the approach of dropping and adding column again?

I am running these SQLs in Java via SpringJDBC

回答1:

The problem with postgresql is every update require a delete and an insert operation. Do that 700k times and will be very slow and if that field have one index can be even worst.

I dont see any problem with your aproach. I even recreate the whole table for some updates. But you have to be carefull no one else is using that table or are any fk relationship.

CREATE table_backup AS 
   SELECT function(field1), function(field2) ....

DROP table_current;

RENAME table_backup to table_current;

CREATE INDEX and CONSTRAINS TO table_current;