Adding a column to all rows of a large Postgres database

I have a PostgreSQL database where I need to update a specific column based on some calculations performed in Python. This is running very slow and seems to slow down the longer the code runs (it got through about 3% of the rows in 12 hours).

I don't think I'm resource constrained based on Windows task manager. I have have RAM and CPU available. Disk active is 100%, but is performing below NVME read/write speeds.

The database has about 1.6 billion rows. id is the primary key. I'm interacting with it using psycopg2 as follows:

import psycopg2


def retrieve_raw_content_batch(batch_size):
    with db_connect() as conn:
        with conn.cursor('testit') as cursor:
            cursor.execute("SELECT id, columnoftext FROM table;")
            while True:
                rows = cursor.fetchmany(batch_size)
                if not rows:
                    break

                yield rows


def update_database(upload_list):
    with db_connect() as conn:
        with conn.cursor() as cursor:
            update_query = "UPDATE table SET col1 = %s, col2 = %s WHERE id = %s"
            psycopg2.extras.execute_batch(cursor, update_query, upload_list)


def do_stuff(row_batch): 
    for rows in row_batch:
        upload_list = []
        for row in rows:
            #calculate to get id, col1, col2
            upload_list.append((id, col1, col2))
        update_database(upload_list)


def main(batch_size):
    rows_batch = retrieve_raw_content_batch(batch_size)
    do_stuff(rows_batch)

I tried to modify the postgresql.conf file by increasing max_wal_size to 10GB, but I am relatively new to Postgres. I'm not sure how to optimize my database configuration or if this is even the issue.

I am also wondering whether it makes more sense to create a new table with COPY and use JOIN after, instead of UPDATE each row individually.

The reason it is taking so long is that you are using an UPDATE for every single row, meaning you have 1.6 billion transactions - this is gonna take forever.

If you split the UPDATEs into batches, you could greatly reduce the number of transactions. The safest and most efficient way in postgres would be to make a temporary table that batch UPDATEs and transfers. The solution is relatively simple as long as this works for your usecase:

def update_database_in_batches(upload_list, batch_size):
    with db_connect() as conn:
        with conn.cursor() as cursor:
            for i in range(0, len(upload_list), batch_size):
                temp_table_query = """
                CREATE TEMP TABLE temp_table (id INTEGER, col1 INTEGER, col2 INTEGER)
                ON COMMIT DROP;
                """
                cursor.execute(temp_table_query)

                # Batch insert to the temp_table
                insert_query = "INSERT INTO temp_table (id, col1, col2) VALUES %s"
                psycopg2.extras.execute_values(cursor, insert_query, upload_list[i:i+batch_size])

                # Update the main table from the temporary table
                update_query = """
                UPDATE table
                SET col1 = temp_table.col1, col2 = temp_table.col2
                FROM temp_table
                WHERE table.id = temp_table.id;
                """
                cursor.execute(update_query)

You're gonna have to specify batch_size, etc and maybe small modifications, but this is the general structure