Is there any other approach to update or chunk to populate a new column with millions rows? [closed]

Question

Closed. This question is opinion-based. It is not currently accepting answers.

Want to improve this question? Because this question may lead to opinionated discussion, debate, and answers, it has been closed. You may edit the question if you feel you can improve it so that it requires answers that include facts and citations or a detailed explanation of the proposed solution. If edited, the question will be reviewed and might be reopened.

Closed 2 months ago.

Improve this question

My query is taking too long to run when I use general filters, but it's fine with specific filters. I suspect it's an optimization issue.

To fix it, I'm trying to create a non-clustered index on a new column. This column will extract a specific value from a JSON attributes column.

The table has over 2 million rows. I ran the migration and it created the new column instantly, but it's hanging indefinitely while trying to populate the data. I'm also planning to create a composite index on three columns, including this new one.

What is the best way to backfill a column on a large table without causing the migration to fail or hang?

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('answer_tests', function (Blueprint $table) {
            $table->BigInteger('unity_id')->after('institution_id')->nullable();
        });

        DB::table('answer_tests')->orderBy('id')->chunkById(100, function ($rows) {
            foreach ($rows as $row) {
                DB::table('answer_tests')
                    ->where('id', $row->id)
                    ->update([
                        'unity_id' => DB::raw("JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.class_school.unity.id'))"),
                    ]);
            }
        });

        Schema::table('answer_tests', function (Blueprint $table) {
            $table->index(['institution_id', 'unity_id', 'created_at'], 'idx_unity_institution_created_at');
        });

    }

    public function down(): void
    {
        Schema::table('answer_tests', function (Blueprint $table) {
            $table->dropIndex('idx_unity_institution_created_at');
            $table->dropColumn('unity_id');
        });
    }
};

Oscar Ekeyekwu · Accepted Answer · 2025-09-09 16:49:01Z

3

running the backfill logic inside the migration file will most likely lock your db, or cause it to hang because you are running multiple db updates.
Try these steps:
1. Maintain the migration to just add the column keeping it simple and fast.
2. Create an artisan command with raw SQL to run the backfill function in chunks of 5k-10k or your preferred size
3. Create a new migration to create the index after backfill command runs

See sample code for the artisan command to backfill your table in chunks.

$batchSize = 5000;
$lastId = 0;

do {
    $updated = DB::update("
        UPDATE answer_tests
        SET unity_id = JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.class_school.unity.id'))
        WHERE id > ? AND id <= ? AND unity_id IS NULL
    ", [$lastId, $lastId + $batchSize]);

    $lastId += $batchSize;
} while ($updated > 0);

answered Sep 9 at 16:49

Oscar Ekeyekwu

463 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is there any other approach to update or chunk to populate a new column with millions rows? [closed]

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related