Adding a Column to a PostgreSQL Table with Millions of Records Takes Several Minutes - How Can I Improve Performance?

Question

Question:

I'm working with a PostgreSQL database, and I need to add an additional column to a table that contains millions of records. The problem is that this operation is taking several minutes to complete, impacting the overall performance of my application. Are there ways to speed up this process and minimize downtime, especially when dealing with PostgreSQL?

Problem Description:

• PostgreSQL version: PostgreSQL 12.11
• The table contains approximately 4.000.000 records.
• The SQL query I'm using looks like this:

ALTER TABLE my_table ADD COLUMN new_column INT;

Answer 1

Adding a column to a larger table is time consuming generally, but in general, it is less time consuming when adding a column with a default value.

ALTER TABLE my_table ADD COLUMN new_column INT DEFAULT 0;

If you are using the varchar, you can use,

ALTER TABLE my_table ADD COLUMN new_column VARCHAR(255) DEFAULT '';

and there is another option, like concurrently. This option allows you to have the operation running while the query execute. Example like,

ALTER TABLE my_table ADD COLUMN new_column INT CONCURRENTLY;

And we can use the batch the update method to add the column in batches to reduce the time consumption for whole process and it splits to small processes.

DO $$ 
DECLARE
   chunk_size INT := 10000; -- Adjust as needed
   start_index INT := 0;
BEGIN
   LOOP
      EXIT WHEN start_index >= 4000000;
      EXECUTE 'UPDATE my_table SET new_column = DEFAULT WHERE id >= ' || start_index || ' AND id < ' || start_index + chunk_size;
      start_index := start_index + chunk_size;
   END LOOP;
END $$;

Hope this is helpful..