Building Web Apps with Laravel Module 6 – Advanced database work
Zero-downtime migrations and database specifics
Move a Pathshala column on MySQL in four deploys with no outage, index a busy PostgreSQL table, and see where SQLite, MySQL and PostgreSQL answer differently.
What you will learn
- Plan an expand-and-contract migration that renames or retypes a column across deploys
- Build an index on a busy PostgreSQL or MySQL table without blocking its writes
- Query JSON columns, generated columns and full-text indexes through Laravel
- Predict where SQLite, MySQL and PostgreSQL answer the same query differently
- Decide when MongoDB with laravel-mongodb fits better than a JSON column
Before you start
What you’ll learn · Before you start
What you will learn
- Plan an expand-and-contract migration that renames or retypes a column across deploys
- Build an index on a busy PostgreSQL or MySQL table without blocking its writes
- Query JSON columns, generated columns and full-text indexes through Laravel
- Predict where SQLite, MySQL and PostgreSQL answer the same query differently
- Decide when MongoDB with laravel-mongodb fits better than a JSON column
Before you start
On this page
Pathshala renamed guardians.phone to phone_e164 in the same deploy as the code that uses the new name. The
rename itself took a moment, and the next save on a server still running the old code failed with table guardians has no column named phone.
This lesson moves the same column without that outage: an expand migration that 1,200 rows and three versions of
the code all survive, a backfill in queued jobs of 500 ids, and a contract migration that stopped safely on 3
numbers no code could convert. Then you will build a PostgreSQL index while the table keeps taking writes, run the
same JSON, generated-column, full-text, LIKE and sort queries on SQLite, MySQL 8.4 and PostgreSQL 18 to compare
their answers, and weigh a JSON column against MongoDB.
A rename in one deploy breaks the servers still on the old code
A deploy is not one instant. With two servers behind a load balancer, a rolling deploy: A deploy that gives the new code to the servers one at a time, so the site keeps answering while it runs and, for a while, old and new code serve requests side by side. gives them the new code one at a time, each queue worker: A long-running process, started with php artisan queue:work, that takes jobs from one or more queues, runs each job's handle() method and then deletes, releases or fails the job. More in “Queues and jobs: work after the response” keeps the old code until it restarts, and the migrations run once, somewhere in between. For that stretch, old and new code serve requests against the same schema. Step through what a one-step rename does then:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- Two servers, one column. Both servers run the code that saves a guardian's number in phone. The deploy replaces the code one server at a time, so the site never stops answering.
- Server 2 gets the new code. The deploy updates Server 2 first and runs the migrations once, from there. The migration renames phone to phone_e164.
- The rename itself is quick. MySQL renames a column by changing only the table's definition, so the database is busy for a moment, not for minutes. The trouble is in the code that still runs.
- Server 1 saves as before. Server 1 has not received the new code yet. Its next save inserts into phone, a column that no longer exists, and the request fails with an error.
- Server 2 saves fine. Server 2 writes phone_e164, so its saves work. Until the deploy reaches Server 1, every request that lands there fails.
- A rollback fails as well. Rolling back the code to fix the errors makes both servers write phone again, and now every save fails. Replacing code does not undo a migration.
The session reproduces it on the project’s SQLite database, with the first version of the code (v1, which writes
phone) and the new one (v3, which writes phone_e164):
rename_session.py
from session import run
# The one-step rename, in the project's SQLite database: the column is renamed in the same deploy as the
# code that uses the new name, and every server still running the old code fails at once.
run(
"""
php artisan migrate --force
php deploy_probe.php v1
php artisan migrate --force --path=database/migrations/rename
!php deploy_probe.php v1
php deploy_probe.php v3
""",
app=True,
overlay=("pathshala-guardians",),
)Output
$ php artisan migrate --force INFO Running migrations. 2026_05_01_000000_create_guardians_table .............................. DONE $ php deploy_probe.php v1 v1 code saved guardian 1 $ php artisan migrate --force --path=database/migrations/rename INFO Running migrations. 2026_05_02_000000_rename_phone_on_guardians ........................... DONE $ php deploy_probe.php v1 v1 code failed: SQLSTATE[HY000]: General error: 1 table guardians has no column named phone [exit status 1] $ php deploy_probe.php v3 v3 code saved guardian 2
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 rename_session.py
rename migration · pathshala-guardians/database/migrations/rename/2026_05_02_000000_rename_phone_on_guardians.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// The one-step version: rename the column in the same deploy as the code that uses the new name. Every
// server still running the old code fails from the moment this migration finishes.
return new class extends Migration
{
public function up(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->renameColumn('phone', 'phone_e164');
});
}
public function down(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->renameColumn('phone_e164', 'phone');
});
}
};pathshala-guardians/deploy_probe.php
<?php
// deploy_probe.php <step>: one step of moving guardians.phone to guardians.phone_e164 without downtime.
// seed 1,200 guardians saved by the first version of the app; 3 of them typed a number that is not a mobile
// v1, v2, v3 one guardian saved the way that version of the code saves it: v1 writes phone only, v2 writes
// phone and phone_e164, v3 writes phone_e164 only
// status how many guardians have phone_e164, and how many still lack it
// fix the 3 numbers that could not be converted, corrected by the office after a phone call
require __DIR__.'/vendor/autoload.php';
$app = require_once __DIR__.'/bootstrap/app.php';
$app->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();
use App\Support\IndianMobile;
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\DB;
$step = $argv[1] ?? 'status';
$now = now();
$save = function (string $version, array $columns) use ($now) {
try {
$id = DB::table('guardians')->insertGetId($columns + ['created_at' => $now, 'updated_at' => $now]);
echo "{$version} code saved guardian {$id}\n";
} catch (QueryException $e) {
echo "{$version} code failed: ".preg_replace('/ \(Connection: .*$/s', '', $e->getMessage())."\n";
exit(1);
}
};
match ($step) {
'seed' => (function () use ($now) {
$rows = [];
foreach (range(1, 1200) as $i) {
$local = sprintf('9%04d%05d', $i, ($i * 7919) % 100000);
$typed = match ($i % 3) {
0 => substr($local, 0, 5).' '.substr($local, 5), // 98123 45678
1 => '0'.substr($local, 0, 5).'-'.substr($local, 5), // 098123-45678
2 => '+91 '.$local, // +91 9812345678
};
$rows[] = ['name' => "Guardian {$i}", 'phone' => $i % 400 === 0 ? '12345' : $typed,
'created_at' => $now, 'updated_at' => $now];
}
DB::table('guardians')->insert($rows);
echo 'v1 code saved '.number_format(count($rows))." guardians\n";
})(),
'v1' => $save('v1', ['name' => 'Guardian v1', 'phone' => '97000 12345']),
'v2' => $save('v2', ['name' => 'Guardian v2', 'phone' => '97000 23456', 'phone_e164' => IndianMobile::e164('97000 23456')]),
'v3' => $save('v3', ['name' => 'Guardian v3', 'phone_e164' => IndianMobile::e164('97000 34567')]),
'status' => printf("guardians: %s, with phone_e164: %s, without: %s\n",
number_format(DB::table('guardians')->count()),
number_format(DB::table('guardians')->whereNotNull('phone_e164')->count()),
number_format(DB::table('guardians')->whereNull('phone_e164')->count())),
'fix' => printf("corrected by hand: %d guardians\n", DB::table('guardians')->whereNull('phone_e164')
->update(['phone_e164' => DB::raw("concat('+9198', lpad(id, 8, '0'))")])),
};v1 saved guardian 1 before the migration and failed right after it; v3 saved guardian 2. Nothing was wrong with
the migration. The outage came from code that was still running when it finished, and a rollback of the code would
not help, because the column stays renamed.
A change of type has the same problem and a second one. MySQL 8.4 can rename a column instantly, changing only the
table’s definition, but its manual supports a change of data type only with ALGORITHM=COPY: the whole table is
rebuilt, and writes to it wait until the copy is done. On a table of millions of rows that is minutes of failed
saves even with perfect code.
The way out is a rule for every deploy: a migration may only add what the running code ignores, and may only remove what no running code uses any more. A rename or a type change then becomes a new column and a few deploys.
Checkpoint
Check: Old and new code during a deploy
Expand and backfill: the new column arrives empty and fills in jobs
expand and contract: A way to change a schema without downtime: first add what the new code needs while the old code keeps working, move the data, switch the code, and only then remove what no running code uses. moves the data in steps that each work with the code
before and after them. Walk through the plan for phone:
- Deploy 1
Expand, and write both columns migration and code
A migration adds phone_e164 as a nullable column and stops requiring phone. The new code writes both columns on every save. Servers that still run the old code keep inserting with phone only, which works because the new column may stay empty.
- Backfill
Fill the old rows in small jobs queued jobs
Once every server writes both columns, a command queues one job per 500 ids. Each job fills only rows that are still empty, so a retried job changes nothing. Rows that cannot be converted stay empty and are counted.
- Deploy 2
Read from the new column code
The code now reads phone_e164 and still writes both columns, so rolling back to the code of deploy 1 loses nothing. The numbers that could not be converted are corrected by hand.
- Deploy 3
Stop writing the old column code
The code no longer touches phone. The expand migration made phone optional, so these saves work while the column still exists, and nothing that runs reads it or writes it any more.
- Deploy 4
Contract migration
A migration makes phone_e164 required and then drops phone. In the recorded session it first stopped on 3 rows without a number, before anything was dropped, and passed once they were fixed.
The timeline has 5 events, from Deploy 1 to Deploy 4.
The session runs the plan on MySQL 8.4 with 1,200 guardians saved by the first version of the app, 3 of whom typed a number that is not a mobile number. After each migration it saves a guardian the way each version of the code would:
expand_contract_session.py
from session import run
# Moving guardians.phone to guardians.phone_e164 on MySQL 8.4 without downtime, with each version of the
# code tried against the schema of each step: the expand migration (a new nullable column, the old one no
# longer required), after which code that writes the old column (v1), both (v2) or only the new one (v3)
# all save; a queued backfill in jobs of 500 ids; and the contract migration, which first meets three
# numbers no code could convert and drops the old column once they are fixed.
run(
"""
php artisan migrate --force
php deploy_probe.php seed
# Deploy 1: expand. Old (v1), dual-write (v2) and new-only (v3) code all save
php artisan migrate --force --path=database/migrations/expand
php deploy_probe.php v1
php deploy_probe.php v2
php deploy_probe.php v3
php deploy_probe.php status
# Backfill, once every server runs v2
php artisan guardians:backfill-phones
php artisan queue:work --stop-when-empty
php deploy_probe.php status
# Contract, once no running code reads or writes phone
!php artisan migrate --force --path=database/migrations/contract
php deploy_probe.php fix
php artisan migrate --force --path=database/migrations/contract
!php deploy_probe.php v2
php deploy_probe.php v3
""",
app=True,
overlay=("pathshala-guardians",),
servers=("mysql",),
)Output
$ php artisan migrate --force
INFO Preparing database.
Creating migration table .............................................. DONE
INFO Running migrations.
0001_01_01_000000_create_users_table .................................. DONE
0001_01_01_000001_create_cache_table .................................. DONE
0001_01_01_000002_create_jobs_table ................................... DONE
2026_05_01_000000_create_guardians_table .............................. DONE
$ php deploy_probe.php seed
v1 code saved 1,200 guardians
# Deploy 1: expand. Old (v1), dual-write (v2) and new-only (v3) code all save
$ php artisan migrate --force --path=database/migrations/expand
INFO Running migrations.
2026_05_02_000000_add_phone_e164_to_guardians ......................... DONE
$ php deploy_probe.php v1
v1 code saved guardian 1201
$ php deploy_probe.php v2
v2 code saved guardian 1202
$ php deploy_probe.php v3
v3 code saved guardian 1203
$ php deploy_probe.php status
guardians: 1,203, with phone_e164: 2, without: 1,201
# Backfill, once every server runs v2
$ php artisan guardians:backfill-phones
Queued 3 jobs of 500 ids for 1203 guardians.
$ php artisan queue:work --stop-when-empty
… App\Jobs\BackfillGuardianPhones ................ RUNNING
… App\Jobs\BackfillGuardianPhones ................... DONE
… App\Jobs\BackfillGuardianPhones ................ RUNNING
… App\Jobs\BackfillGuardianPhones ................... DONE
… App\Jobs\BackfillGuardianPhones ................ RUNNING
… App\Jobs\BackfillGuardianPhones ................... DONE
… Worker STOPPED Queue empty
$ php deploy_probe.php status
guardians: 1,203, with phone_e164: 1,200, without: 3
# Contract, once no running code reads or writes phone
$ php artisan migrate --force --path=database/migrations/contract
INFO Running migrations.
2026_05_03_000000_drop_phone_from_guardians ........................... FAIL
Illuminate\Database\QueryException
SQLSTATE[22004]: Null value not allowed: 1138 Invalid use of NULL value (Connection: mysql, Host: 127.0.0.1, Port: 3306, Database: pathshala, SQL: alter table `guardians` modify `phone_e164` varchar(16) not null)
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:857
853▕ $exceptionType = ($isUniqueConstraintError = $this->isUniqueConstraintError($e))
854▕ ? UniqueConstraintViolationException::class
855▕ : QueryException::class;
856▕
➜ 857▕ $exception = new $exceptionType(
858▕ $this->getNameWithReadWriteType(),
859▕ $query,
860▕ $this->prepareBindings($bindings),
861▕ $e,
+9 vendor frames
10 database/migrations/contract/2026_05_03_000000_drop_phone_from_guardians.php:14
Illuminate\Support\Facades\Facade::__callStatic("table")
+26 vendor frames
37 artisan:16
Illuminate\Foundation\Application::handleCommand(Object(Symfony\Component\Console\Input\ArgvInput))
[exit status 1]
$ php deploy_probe.php fix
corrected by hand: 3 guardians
$ php artisan migrate --force --path=database/migrations/contract
INFO Running migrations.
2026_05_03_000000_drop_phone_from_guardians ........................... DONE
$ php deploy_probe.php v2
v2 code failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'phone' in 'field list'
[exit status 1]
$ php deploy_probe.php v3
v3 code saved guardian 1204
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 expand_contract_session.py
expand migration · pathshala-guardians/database/migrations/expand/2026_05_02_000000_add_phone_e164_to_guardians.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// Deploy 1, expand: the new column arrives empty and nullable, so code that knows nothing about it keeps
// inserting rows exactly as before. The old column stops being required, so a later version of the code
// may stop writing it while the column still exists.
return new class extends Migration
{
public function up(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->string('phone_e164', 16)->nullable()->after('phone');
});
Schema::table('guardians', function (Blueprint $table) {
$table->string('phone', 20)->nullable()->change();
});
}
public function down(): void
{
// phone stays nullable: rows saved by code that wrote phone_e164 only have no phone to restore.
Schema::table('guardians', function (Blueprint $table) {
$table->dropColumn('phone_e164');
});
}
};pathshala-guardians/app/Jobs/BackfillGuardianPhones.php
<?php
namespace App\Jobs;
use App\Support\IndianMobile;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Queue\Queueable;
use Illuminate\Support\Facades\DB;
// Fills phone_e164 for the guardians with ids from $fromId to $toId that do not have it yet. Running it
// twice changes nothing the second time, so a retried or duplicated job is harmless.
class BackfillGuardianPhones implements ShouldQueue
{
use Queueable;
public function __construct(public int $fromId, public int $toId) {}
public function handle(): void
{
DB::table('guardians')
->whereBetween('id', [$this->fromId, $this->toId])
->whereNull('phone_e164')
->orderBy('id')
->get(['id', 'phone'])
->each(fn ($row) => DB::table('guardians')->where('id', $row->id)->whereNull('phone_e164')
->update(['phone_e164' => IndianMobile::e164($row->phone)]));
}
}backfill command · pathshala-guardians/app/Console/Commands/BackfillGuardianPhonesCommand.php
<?php
namespace App\Console\Commands;
use App\Jobs\BackfillGuardianPhones;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
// Queues one BackfillGuardianPhones job for every 500 ids, so the backfill runs in small transactions
// that never hold the table for long, and a worker can be stopped and started again at any time.
class BackfillGuardianPhonesCommand extends Command
{
protected $signature = 'guardians:backfill-phones {--size=500}';
protected $description = 'Queue jobs that fill guardians.phone_e164 from guardians.phone';
public function handle(): int
{
$size = (int) $this->option('size');
$max = (int) DB::table('guardians')->max('id');
$jobs = 0;
for ($from = 1; $from <= $max; $from += $size) {
BackfillGuardianPhones::dispatch($from, $from + $size - 1);
$jobs++;
}
$this->info("Queued {$jobs} jobs of {$size} ids for {$max} guardians.");
return self::SUCCESS;
}
}pathshala-guardians/app/Support/IndianMobile.php
<?php
namespace App\Support;
// Turns an Indian mobile number as a parent typed it ("98123 45678", "098123-45678", "+91 98123 45678")
// into the international E.164 form "+919812345678". Returns null for anything that is not a 10-digit
// mobile number, so a bad row is counted and looked at instead of guessed.
class IndianMobile
{
public static function e164(?string $typed): ?string
{
$digits = preg_replace('/\D/', '', (string) $typed);
$digits = preg_replace('/^(?:91|0)(?=\d{10}$)/', '', $digits);
return preg_match('/^[6-9]\d{9}$/', $digits) ? '+91'.$digits : null;
}
}contract migration · pathshala-guardians/database/migrations/contract/2026_05_03_000000_drop_phone_from_guardians.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// Deploy 3, contract: only once no running code reads or writes phone does the column go. phone_e164
// becomes required first, in a statement of its own: MySQL cannot roll back a schema change, so if a row
// still lacks a number the migration stops here, before anything has been dropped.
return new class extends Migration
{
public function up(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->string('phone_e164', 16)->nullable(false)->change();
});
Schema::table('guardians', function (Blueprint $table) {
$table->dropColumn('phone');
});
}
public function down(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->string('phone', 20)->nullable()->after('name');
});
Schema::table('guardians', function (Blueprint $table) {
$table->string('phone_e164', 16)->nullable()->change();
});
}
};After the expand migration all three versions saved: v1 writing only phone, v2 writing both columns, v3
writing only phone_e164. That is what the step is for. The new column is nullable, so code that has never heard
of it inserts as before, and phone is no longer required, so a later version may stop writing it while the column
still exists. MySQL 8.4 picks its INSTANT algorithm for a new column unless told otherwise, which touches only
the table’s metadata; making phone nullable rebuilds the table in place, with writes allowed while it runs.
The backfill: Filling a new column or table with values for the rows that existed before it, usually in small batches so that the table keeps serving requests meanwhile. then went through the 1,201 rows saved without the new column.
guardians:backfill-phones queued 3 jobs of 500 ids, and each BackfillGuardianPhones job converts only the rows
of its id range whose phone_e164 is still empty. Three choices keep it safe on a large table. Small id ranges
keep each statement short. A queue lets the work stop, resume and spread over workers. And the
whereNull('phone_e164') condition makes a job that runs twice change nothing the second time, the
idempotent job: A job whose second run with the same input changes nothing more than the first did, so a retry or a repeat after a crash does no harm, for example because it checks what is already done. More in “Queues and jobs: work after the response” of the queues lesson.
IndianMobile::e164() returned null for the 3 numbers it could not read instead of guessing, so the status line
showed 1,200 guardians with the new column and 3 without.
Key idea
Every deploy has to work with the schema as it is before its migration and as it is after: add before you use, and stop using before you remove.
Checkpoint
Check: Expanding and backfilling a column
Contract last, and let it fail before it drops anything
The contract migration has two jobs: make phone_e164 required, then drop phone. Step through its two runs in
the session above:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- Three rows without a number. After the backfill, 1,200 guardians have phone_e164 and 3 do not, because the numbers their parents typed are not mobile numbers. The old column phone still holds what they typed.
- Statement 1 makes it required. The migration first asks MySQL to make phone_e164 NOT NULL, in a Schema::table() call of its own. MySQL refuses with error 1138, because 3 rows hold NULL.
- Nothing was dropped. The drop of phone was the second statement, and it never ran. MySQL commits each schema change as it runs it, so this order is what kept the 3 typed numbers. Laravel did not record the migration as run.
- The office fixes three. Someone calls the 3 families and enters their numbers. Every guardian now has phone_e164.
- The second run passes. The same migration runs again. The NOT NULL change succeeds, then phone is dropped, and Laravel records the migration as run.
- Old code breaks, new code saves. Code that still writes phone, such as v2, now fails with Unknown column. Code that writes only phone_e164 saves. That is why the contract waits until every server and worker runs the new code.
The first time it ran, MySQL
refused the first statement with 1138 Invalid use of NULL value, because 3 rows still had no number. The
migration puts that statement first on purpose, in a Schema::table() call of its own. MySQL commits every schema
change as it runs it and cannot roll one back, so a migration that dropped phone first and then failed would
have left the table half changed, with the 3 typed numbers gone for good. As written, it stopped before anything
was lost.
Laravel records a migration as run only after it finishes, so the failed one stayed pending. Once the office had
corrected the 3 numbers (deploy_probe.php fix), the same migration ran to the end. On PostgreSQL, Laravel runs
each migration inside a transaction and the failure would have rolled the whole migration back; MySQL’s schema
grammar has no such transaction, which is why the order of the statements matters there.
After the drop, v2 failed with Unknown column 'phone'. A server or worker still writing both columns would have
broken at that moment, which is why the contract waits until the code that stops using phone runs everywhere.
Queue workers are long-lived processes that keep the code they started with, so the deploy before the contract
ends with php artisan queue:restart. v3 saved guardian 1204.
The drop in the same deploy as the code change
Shipping the contract migration together with the code that stops using the column brings the rename’s outage back: the migration runs while old servers still write the column. The drop belongs to a later deploy, after the last old server and worker is gone.
Checkpoint
Check: The contract migration
Indexes on busy tables are built without blocking writes
A new index is the most common migration on a large table. PostgreSQL’s standard CREATE INDEX locks out inserts,
updates and deletes on the table until the build is done. CREATE INDEX CONCURRENTLY builds it while they go on,
at the price of two scans of the table and a longer build, and it cannot run inside a transaction block. Laravel
writes it for you when an index ends in ->online(), but Laravel also runs every PostgreSQL migration inside a
transaction. The session builds the same index both ways on PostgreSQL 18, on the 1,200 guardians:
concurrent_session.py
from session import run
# PostgreSQL 18.6: an index on guardians.name built with $table->index('name')->online(), which Laravel
# sends as CREATE INDEX CONCURRENTLY, once from a normal migration (which Laravel runs inside a transaction
# on PostgreSQL) and once from a migration with $withinTransaction = false; pg_indexes then lists the index
# the second one built.
run(
"""
php artisan migrate --force
php deploy_probe.php seed
!php artisan migrate --force --path=database/migrations/in-transaction
php artisan migrate --force --path=database/migrations/outside-transaction
psql -c "select indexname, indexdef from pg_indexes where tablename = 'guardians' order by indexname"
""",
app=True,
overlay=("pathshala-guardians", "concurrent"),
servers=("pgsql",),
)Output
$ php artisan migrate --force
INFO Preparing database.
Creating migration table .............................................. DONE
INFO Running migrations.
0001_01_01_000000_create_users_table .................................. DONE
0001_01_01_000001_create_cache_table .................................. DONE
0001_01_01_000002_create_jobs_table ................................... DONE
2026_05_01_000000_create_guardians_table .............................. DONE
$ php deploy_probe.php seed
v1 code saved 1,200 guardians
$ php artisan migrate --force --path=database/migrations/in-transaction
INFO Running migrations.
2026_05_04_000000_add_guardians_name_index ............................ FAIL
Illuminate\Database\QueryException
SQLSTATE[25001]: Active sql transaction: 7 ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block (Connection: pgsql, Host: 127.0.0.1, Port: 5432, Database: pathshala, SQL: create index concurrently "guardians_name_index" on "guardians" ("name"))
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:857
853▕ $exceptionType = ($isUniqueConstraintError = $this->isUniqueConstraintError($e))
854▕ ? UniqueConstraintViolationException::class
855▕ : QueryException::class;
856▕
➜ 857▕ $exception = new $exceptionType(
858▕ $this->getNameWithReadWriteType(),
859▕ $query,
860▕ $this->prepareBindings($bindings),
861▕ $e,
+9 vendor frames
10 database/migrations/in-transaction/2026_05_04_000000_add_guardians_name_index.php:14
Illuminate\Support\Facades\Facade::__callStatic("table")
+27 vendor frames
38 artisan:16
Illuminate\Foundation\Application::handleCommand(Object(Symfony\Component\Console\Input\ArgvInput))
[exit status 1]
$ php artisan migrate --force --path=database/migrations/outside-transaction
INFO Running migrations.
2026_05_04_000000_add_guardians_name_index ............................ DONE
$ psql -c "select indexname, indexdef from pg_indexes where tablename = 'guardians' order by indexname"
indexname | indexdef
----------------------+--------------------------------------------------------------------------
guardians_name_index | CREATE INDEX guardians_name_index ON public.guardians USING btree (name)
guardians_pkey | CREATE UNIQUE INDEX guardians_pkey ON public.guardians USING btree (id)
(2 rows)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 concurrent_session.py
in-transaction migration · concurrent/database/migrations/in-transaction/2026_05_04_000000_add_guardians_name_index.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// An index built without blocking writes, written as a normal migration: online() makes Laravel send
// CREATE INDEX CONCURRENTLY to PostgreSQL. But Laravel runs each PostgreSQL migration inside a
// transaction, and CREATE INDEX CONCURRENTLY refuses to run inside one.
return new class extends Migration
{
public function up(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->index('name')->online();
});
}
public function down(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->dropIndex(['name']);
});
}
};outside-transaction migration · concurrent/database/migrations/outside-transaction/2026_05_04_000000_add_guardians_name_index.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// The same index, with the migration's transaction switched off, so PostgreSQL builds it while the
// table keeps taking inserts and updates.
return new class extends Migration
{
public $withinTransaction = false;
public function up(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->index('name')->online();
});
}
public function down(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->dropIndex(['name']);
});
}
};The normal migration failed with CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and the SQL in
the error shows the concurrently that online() added. The second migration sets
public $withinTransaction = false;, so Laravel sends the statement on its own, and pg_indexes then lists
guardians_name_index. A concurrent build that fails part-way, on a deadlock or a duplicate in a unique index,
leaves an invalid index behind that is still updated on every write; PostgreSQL’s documentation says to drop it
and build it again.
MySQL 8.4 builds a secondary index in place while writes continue, and its manual lists how each kind of change
runs. A migration can ask for the way it expects: ->instant() adds ALGORITHM=INSTANT, and
->inplace()->lock('none') asks for an in-place change that keeps writes going. MySQL then refuses a statement it
cannot run that way, instead of quietly locking the table:
online_mysql_session.py
from session import run
# MySQL 8.4.11 with the 1,200 guardians: a column added with instant() and an index on phone built with
# inplace()->lock('none') while the table stays open for writes, then a change of type asked for with
# lock('none'), which MySQL refuses because it can change a type only by copying the table. The mysql
# client then lists the columns and indexes the table has.
run(
"""
php artisan migrate --force
php deploy_probe.php seed
php artisan migrate --force --path=database/migrations/online
!php artisan migrate --force --path=database/migrations/retype
mysql -e "select column_name, column_type from information_schema.columns where table_schema = 'pathshala' and table_name = 'guardians' order by ordinal_position"
mysql -e "select index_name, column_name from information_schema.statistics where table_schema = 'pathshala' and table_name = 'guardians' order by index_name"
""",
app=True,
overlay=("pathshala-guardians",),
servers=("mysql",),
)Output
$ php artisan migrate --force
INFO Preparing database.
Creating migration table .............................................. DONE
INFO Running migrations.
0001_01_01_000000_create_users_table .................................. DONE
0001_01_01_000001_create_cache_table .................................. DONE
0001_01_01_000002_create_jobs_table ................................... DONE
2026_05_01_000000_create_guardians_table .............................. DONE
$ php deploy_probe.php seed
v1 code saved 1,200 guardians
$ php artisan migrate --force --path=database/migrations/online
INFO Running migrations.
2026_05_05_000000_add_relation_and_phone_index ........................ DONE
$ php artisan migrate --force --path=database/migrations/retype
INFO Running migrations.
2026_05_06_000000_widen_guardian_names ................................ FAIL
Illuminate\Database\QueryException
SQLSTATE[0A000]: Feature not supported: 1846 LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED. (Connection: mysql, Host: 127.0.0.1, Port: 3306, Database: pathshala, SQL: alter table `guardians` modify `name` text not null, lock=none)
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:857
853▕ $exceptionType = ($isUniqueConstraintError = $this->isUniqueConstraintError($e))
854▕ ? UniqueConstraintViolationException::class
855▕ : QueryException::class;
856▕
➜ 857▕ $exception = new $exceptionType(
858▕ $this->getNameWithReadWriteType(),
859▕ $query,
860▕ $this->prepareBindings($bindings),
861▕ $e,
+9 vendor frames
10 database/migrations/retype/2026_05_06_000000_widen_guardian_names.php:14
Illuminate\Support\Facades\Facade::__callStatic("table")
+26 vendor frames
37 artisan:16
Illuminate\Foundation\Application::handleCommand(Object(Symfony\Component\Console\Input\ArgvInput))
[exit status 1]
$ mysql -e "select column_name, column_type from information_schema.columns where table_schema = 'pathshala' and table_name = 'guardians' order by ordinal_position"
COLUMN_NAME COLUMN_TYPE
id bigint unsigned
name varchar(80)
phone varchar(20)
created_at timestamp
updated_at timestamp
relation varchar(20)
$ mysql -e "select index_name, column_name from information_schema.statistics where table_schema = 'pathshala' and table_name = 'guardians' order by index_name"
INDEX_NAME COLUMN_NAME
guardians_phone_index phone
PRIMARY id
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 online_mysql_session.py
online migration · pathshala-guardians/database/migrations/online/2026_05_05_000000_add_relation_and_phone_index.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// Two changes MySQL 8.4 makes while the table keeps taking writes, each asked for explicitly: a column for
// the guardian's relation to the student, and an index to find a guardian by the number that called. instant()
// adds ALGORITHM=INSTANT and inplace()->lock('none') adds ALGORITHM=INPLACE, LOCK=NONE. If MySQL could not
// do a change that way, it would refuse the statement instead of quietly copying or locking the table.
return new class extends Migration
{
public function up(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->string('relation', 20)->nullable()->instant();
});
Schema::table('guardians', function (Blueprint $table) {
$table->index('phone')->inplace()->lock('none');
});
}
public function down(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->dropIndex(['phone']);
});
Schema::table('guardians', function (Blueprint $table) {
$table->dropColumn('relation');
});
}
};retype migration · pathshala-guardians/database/migrations/retype/2026_05_06_000000_widen_guardian_names.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// Longer names: name from varchar(80) to text. MySQL changes a column's type only by copying the whole
// table, and a copy cannot let writes through, so with lock('none') MySQL refuses the change at once
// instead of holding the table's writes for as long as the copy takes.
return new class extends Migration
{
public function up(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->text('name')->lock('none')->change();
});
}
public function down(): void
{
Schema::table('guardians', function (Blueprint $table) {
$table->string('name', 80)->change();
});
}
};The relation column and the index on phone were added while the table stayed open for writes. The change of
name from varchar(80) to text was refused with error 1846, LOCK=NONE is not supported. Reason: Cannot change column type INPLACE, and the column kept its type. Without lock('none') the same migration would have copied the
whole table, with every write waiting until the copy was done.
| Migration on MySQL 8.4 | How it runs | Writes meanwhile |
|---|---|---|
| add a nullable column | instant, metadata only | continue |
renameColumn() |
instant, metadata only | continue |
$table->index() |
in place | continue |
nullable(false)->change() |
table rebuilt in place, fails on NULLs | continue |
$table->fullText() |
in place, the first one rebuilds the table | wait |
change() to another type |
table copied; refused with lock('none') |
wait |
Timing a migration on 50 rows
A migration that took a second on a laptop’s 50 rows can hold a table of millions for many minutes. Run it on a copy of production’s size first and time it, and plan any change that waits for writes as an expand and contract.
Checkpoint
Check: Schema changes on busy tables
JSON columns and generated columns work on all three databases
Pathshala keeps each profile’s notification settings in a json column: whether the parent wants SMS, the language
of messages, and the subjects followed. Laravel’s -> syntax reads a key inside it, whereJsonContains() searches
an array in it, and a generated column: A column whose value the database computes from other columns of the same row, either when the row is read (virtual) or when it is written (stored). It cannot be written to directly. turns one key into a column of its own. The
probe creates the same table on each connection, saves the same 3 profiles and prints the SQL each driver
generated:
specifics_mysql_session.py
from session import run
# The same table, rows and queries on SQLite (the project's database/database.sqlite file) and on a
# MySQL 8.4 server started for the session, each with the SQL its driver generates and the rows it found, in the order it sorted them.
run(
"""
php specifics_probe.php sqlite mysql
""",
app=True,
overlay=("specifics",),
servers=("mysql",),
)Output
$ php specifics_probe.php sqlite mysql
== sqlite
where('settings->sms', true) select * from "profiles" where json_extract("settings", '$."sms"') = true
-> 2 rows
whereJsonContains select * from "profiles" where exists (select 1 from json_each("settings", '$."subjects"') where "json_each"."value" is ?)
-> 2 rows
generated column = 'hi' select * from "profiles" where "language" = ?
-> 1 rows
name like 'asha' select * from "profiles" where "name" like ?
-> 1 rows
whereLike('name', 'asha') select * from "profiles" where "name" like ?
-> 1 rows
order by name select * from "profiles" order by "name" asc
-> NULL, Asha, Ravi
nulls last, any database select * from "profiles" order by name is null, name
-> Asha, Ravi, NULL
fullText('body') index This database driver does not support fulltext index creation.
whereFullText('body', 'exam') This database engine does not support fulltext search operations.
16 letters in string(10) stored as Aishwaryalakshmi
== mysql
where('settings->sms', true) select * from `profiles` where json_extract(`settings`, '$."sms"') = true
-> 2 rows
whereJsonContains select * from `profiles` where json_contains(`settings`, ?, '$."subjects"')
-> 2 rows
generated column = 'hi' select * from `profiles` where `language` = ?
-> 1 rows
name like 'asha' select * from `profiles` where `name` like ?
-> 1 rows
whereLike('name', 'asha') select * from `profiles` where `name` like ?
-> 1 rows
order by name select * from `profiles` order by `name` asc
-> NULL, Asha, Ravi
nulls last, any database select * from `profiles` order by name is null, name
-> Asha, Ravi, NULL
fullText('body') index created
whereFullText('body', 'exam') select * from `notices` where match (`body`) against (? in natural language mode)
-> 1 rows
16 letters in string(10) refused: SQLSTATE 22001 (String data, right truncated)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 specifics_mysql_session.py
specifics/specifics_probe.php
<?php
// specifics_probe.php <connection> ...: the same table, rows and queries on each connection named, with the
// SQL each driver generates and what each database answered: JSON columns, a generated column, a full-text
// search, a LIKE with other letter case (and Laravel's whereLike(), which spells the same match for each
// driver), the place of NULL in a sort (and a sort that puts NULL last on every database), and a name
// longer than its column.
require __DIR__.'/vendor/autoload.php';
$app = require_once __DIR__.'/bootstrap/app.php';
$app->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
// DB_DATABASE names the server's database (pathshala), so the sqlite connection is given its file here.
config(['database.connections.sqlite.database' => database_path('database.sqlite')]);
foreach (array_slice($argv, 1) as $name) {
$db = DB::connection($name);
$driver = $db->getDriverName();
$schema = Schema::connection($name);
$schema->dropIfExists('profiles');
$schema->dropIfExists('notices');
// The generated column reads settings.language; each database spells the JSON access its own way.
$language = match ($driver) {
'mysql' => "json_unquote(json_extract(`settings`, '$.language'))",
'pgsql' => "(settings->>'language')",
'sqlite' => "json_extract(settings, '$.language')",
};
$schema->create('profiles', function (Blueprint $table) use ($language) {
$table->id();
$table->string('name', 10)->nullable();
$table->json('settings');
$table->string('language', 5)->virtualAs($language);
});
$schema->create('notices', function (Blueprint $table) {
$table->id();
$table->text('body');
});
try {
$schema->table('notices', fn (Blueprint $table) => $table->fullText('body'));
$fullText = 'created';
} catch (RuntimeException $e) {
$fullText = $e->getMessage();
}
$db->table('profiles')->insert([
['name' => 'Asha', 'settings' => json_encode(['sms' => true, 'language' => 'hi', 'subjects' => ['maths', 'physics']])],
['name' => 'Ravi', 'settings' => json_encode(['sms' => false, 'language' => 'en', 'subjects' => ['chemistry']])],
['name' => null, 'settings' => json_encode(['sms' => true, 'language' => 'ta', 'subjects' => ['physics']])],
]);
$db->table('notices')->insert([
['body' => 'The physics exam moves to the main hall'],
['body' => 'Fee receipts are ready at the front desk'],
]);
echo "== {$driver}\n";
$show = function (string $label, $query, Closure $answer) {
printf("%-28s %s\n%-28s -> %s\n", $label, $query->toSql(), '', $answer($query));
};
$show("where('settings->sms', true)", $db->table('profiles')->where('settings->sms', true), fn ($q) => $q->count().' rows');
$show('whereJsonContains', $db->table('profiles')->whereJsonContains('settings->subjects', 'physics'), fn ($q) => $q->count().' rows');
$show("generated column = 'hi'", $db->table('profiles')->where('language', 'hi'), fn ($q) => $q->count().' rows');
$show("name like 'asha'", $db->table('profiles')->where('name', 'like', 'asha'), fn ($q) => $q->count().' rows');
$show("whereLike('name', 'asha')", $db->table('profiles')->whereLike('name', 'asha'), fn ($q) => $q->count().' rows');
$show('order by name', $db->table('profiles')->orderBy('name'),
fn ($q) => $q->pluck('name')->map(fn ($n) => $n ?? 'NULL')->implode(', '));
$show('nulls last, any database', $db->table('profiles')->orderByRaw('name is null, name'),
fn ($q) => $q->pluck('name')->map(fn ($n) => $n ?? 'NULL')->implode(', '));
printf("%-28s %s\n", "fullText('body') index", $fullText);
try {
$show("whereFullText('body', 'exam')", $db->table('notices')->whereFullText('body', 'exam'), fn ($q) => $q->count().' rows');
} catch (RuntimeException $e) {
printf("%-28s %s\n", "whereFullText('body', 'exam')", $e->getMessage());
}
try {
$db->table('profiles')->insert(['name' => 'Aishwaryalakshmi', 'settings' => '{}']);
printf("%-28s stored as %s\n", '16 letters in string(10)', $db->table('profiles')->where('id', 4)->value('name'));
} catch (Illuminate\Database\QueryException $e) {
preg_match('/SQLSTATE\[(\w+)\]: ([^:]+)/', $e->getMessage(), $m);
printf("%-28s refused: SQLSTATE %s (%s)\n", '16 letters in string(10)', $m[1], $m[2]);
}
echo "\n";
}from session import run
# PostgreSQL 18.6: the same table, rows and queries as specifics_mysql_session.py runs on SQLite and on
# MySQL 8.4, with the SQL the PostgreSQL driver generates and what the server answered, in its sort order.
run(
"""
php specifics_probe.php pgsql
""",
app=True,
overlay=("specifics",),
servers=("pgsql",),
)Output
$ php specifics_probe.php pgsql
== pgsql
where('settings->sms', true) select * from "profiles" where ("settings"->'sms')::jsonb = 'true'::jsonb
-> 2 rows
whereJsonContains select * from "profiles" where ("settings"->'subjects')::jsonb @> ?
-> 2 rows
generated column = 'hi' select * from "profiles" where "language" = ?
-> 1 rows
name like 'asha' select * from "profiles" where "name"::text like ?
-> 0 rows
whereLike('name', 'asha') select * from "profiles" where "name"::text ilike ?
-> 1 rows
order by name select * from "profiles" order by "name" asc
-> Asha, Ravi, NULL
nulls last, any database select * from "profiles" order by name is null, name
-> Asha, Ravi, NULL
fullText('body') index created
whereFullText('body', 'exam') select * from "notices" where (to_tsvector('english', "body")) @@ plainto_tsquery('english', ?)
-> 1 rows
16 letters in string(10) refused: SQLSTATE 22001 (String data, right truncated)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 specifics_pg_session.py
All three found the 2 profiles with settings->sms true and the 2 that follow physics, each with its own SQL:
json_extract() on SQLite and MySQL and ("settings"->'sms')::jsonb = 'true'::jsonb on PostgreSQL; json_each()
on SQLite, json_contains() on MySQL and the @> containment operator on PostgreSQL. You write one Laravel query
and the driver writes its database’s SQL. Laravel’s documentation lists JSON where clauses for MySQL 8.0+,
PostgreSQL 12.0+ and SQLite 3.39.0+, among others.
->virtualAs(...) made language a virtual generated column, computed from settings when a row is read;
->storedAs(...) computes the value on write and keeps it on disk. The expression inside is raw SQL, so it is the
one place where you write each database’s JSON syntax yourself, as the probe’s match shows. InnoDB can index a
virtual generated column, which is how a JSON key you filter on often gets an index on MySQL. PostgreSQL gained
virtual generated columns in version 18, where Laravel 13.34 generated generated always as (...) virtual; on older
PostgreSQL servers only storedAs() works.
Checkpoint
Check: JSON and generated columns
SQLite, MySQL and PostgreSQL disagree on case, NULLs, lengths and full text
The rest of the probe’s output is where the three databases parted ways, on the same rows and the same Laravel code:
| Query | SQLite | MySQL 8.4 | PostgreSQL 18 |
|---|---|---|---|
where('name', 'like', 'asha') |
1 row | 1 row | 0 rows |
whereLike('name', 'asha') |
1 row | 1 row | 1 row |
orderBy('name') |
NULL first | NULL first | NULL last |
orderByRaw('name is null, name') |
NULL last | NULL last | NULL last |
16 letters into string('name', 10) |
stored all 16 | refused, 22001 | refused, 22001 |
fullText('body') and whereFullText() |
not supported | 1 row | 1 row |
LIKE matches ASCII letters of either case in SQLite, MySQL’s default collation utf8mb4_0900_ai_ci compares
text without regard to case, and PostgreSQL’s LIKE is case-sensitive, with ILIKE as its case-insensitive form.
Laravel’s whereLike() writes the right one for each driver, ilike on PostgreSQL, and it found Asha on all three.
For sorting, SQLite treats NULL as smaller than any value and MySQL puts NULL first in an ascending sort, while
PostgreSQL sorts NULL as larger than any value. PostgreSQL and SQLite accept nulls last in a sort, but MySQL’s
ORDER BY has no such clause; sorting on name is null first works everywhere, and
orderByRaw('name is null, name') put the NULL last on all three databases.
SQLite does not enforce the length of a VARCHAR, so it kept all 16 letters of Aishwaryalakshmi in a column
declared for 10. MySQL in its default strict mode and PostgreSQL both refused the row. Full text is the widest gap:
Laravel’s SQLite driver refused to create a fullText() index at all, MySQL built a FULLTEXT index searched with
match ... against, and PostgreSQL a GIN index searched with to_tsvector ... @@ plainto_tsquery.
Two changes of the Laravel 13 upgrade guide belong to the same list. On MySQL and MariaDB, upsert() ignores its
uniqueBy argument and matches on the table’s primary and unique indexes, yet Laravel now refuses an empty
uniqueBy. A DELETE with a join, orderBy() and limit() is now compiled in full, so MySQL can throw a
QueryException where earlier versions ran a delete without its limit.
Test on the database you deploy
A test suite on SQLite passes for the 16-letter name that production refuses. Run the tests that touch JSON, text search, sorting and lengths against the same database and version as production, as in the database tests lesson.
Checkpoint
Check: Where the three databases differ
A JSON column or MongoDB depends on how the data is used
mongodb/laravel-mongodb, maintained by MongoDB, stores Eloquent models as documents in MongoDB collections and
adds embedded relationships; Laravel’s documentation recommends it, and most packages that use Eloquent work with
it. Version 5.11.0 requires Laravel 12 or 13 and the mongodb PHP extension, and Composer refuses to install it
without that extension. The PHP build these lessons run on has no mongodb extension, so this section has no
recorded session; the choice it teaches is about the data, not the code. Answer the questions for one kind of
Pathshala data:
Answer the questions above: the table then marks what fits your case and says why the rest does not.
| Where it lives | Filter on fields | Joins and transactions | Fields that vary | One database to run |
|---|---|---|---|---|
| Columns or a child table Stored as: one column per field | Good every field is a column with its own index | Good joins, foreign keys and transactions with every other table | Poor every new field is a migration; rare fields leave many empty columns | Good the database the application already has |
| A JSON column Stored as: one document per row of a SQL table | Mixed settings->sms works; an index on a key needs a generated column or an expression index | Good the row joins and commits with its table like any other | Good each row may hold different keys, with no migration | Good the same MySQL or PostgreSQL server |
| MongoDB with laravel-mongodb Stored as: documents in collections | Good indexes on any field or nested field of a document | Poor no joins with the SQL tables; a second database to keep consistent | Good documents of any shape, nested to any depth | Poor a second server, its backups, and the mongodb PHP extension |
How the answers decide
- “Does this data need joins, foreign keys or one transaction with your other tables?” Yes, it belongs to students or payments: rules out MongoDB with laravel-mongodb. Joins, foreign keys and a shared transaction exist only inside one SQL database; across two databases the application has to keep them consistent itself.
- “Do the fields differ a lot from one record to the next?” No, the same few fields every time: adds a caveat to A JSON column. When every record has the same few fields, plain columns are simpler to validate, index and query than keys inside a JSON document.
- “Do the fields differ a lot from one record to the next?” Yes, many optional or nested fields: adds a caveat to Columns or a child table. A field that most rows lack becomes a mostly empty column and a migration for every new one; a JSON column or documents fit better.
- “Does this data need joins, foreign keys or one transaction with your other tables?” No, it stands on its own and “Do the fields differ a lot from one record to the next?” Yes, many optional or nested fields and “Is this most of your data, read and written far more than the rest?” Yes, it is the main data of the product: adds a caveat to A JSON column. For document-shaped data that is most of the workload, a document database may index and scale it more simply; measure both with your real queries first.
- “Is this most of your data, read and written far more than the rest?” No, it sits beside the main tables: adds a caveat to MongoDB with laravel-mongodb. A second database for a small side of the data adds a server, backups and an extension to run for little gain.
A A database that stores each record as a self-contained document, such as JSON or BSON, whose fields may differ from one record to the next, instead of as a row of fixed columns. fits data that is shaped like documents: fields that vary a lot from one record to the next, nesting, and records that stand on their own. Pathshala’s attendance taps, saved by the million and read by student and day, could be such data. Fees and bookings could not: they need joins, foreign keys and one transaction with students and payments, which exist only inside one SQL database.
A JSON column sits between the two. It takes fields that vary, such as notification settings, while the row still joins and commits with its table, and a generated column gives a key an index when a filter needs one. Avoiding migrations is not a reason to choose MongoDB: the code that reads documents still has a schema, and it must then handle every shape ever saved, which is the expand-and-contract problem without the database’s help.
Checkpoint
Check: A JSON column or MongoDB
Practise: split a column without stopping the app
Copy this lesson’s pathshala-guardians folder into your own Pathshala project, start a MySQL or PostgreSQL
database, and split students.name into first_name and last_name the way the lesson moved phone:
- Write the expand migration: two nullable columns, and
nameno longer required. Write adeploy_probe.phpwith three versions of the save code and run all three after the migration. - Write a job that fills both columns for one range of 500 ids, splitting at the last space and skipping rows that are already filled. Queue it from a command, run the worker, then run the command and the worker again and show that the second pass changed no row.
- Write the contract migration:
first_namerequired first, in its ownSchema::table()call, then the drop ofname. Leave one student with an empty name and watch it stop before the drop. - On PostgreSQL, add an index on
last_namewithCREATE INDEX CONCURRENTLYin a migration with$withinTransaction = false, and checkpg_indexesafterwards. - Run
specifics_probe.phpagainst your production database’s engine and version, and write down one answer that differs from SQLite.
Then test yourself on the deploys, the contract, online indexes, JSON columns, the three databases and MongoDB. The questions start with whatever you answered least well and explain each choice:
Practice that adapts to you
Practise this lesson
15 questions about 7 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
Warm-up (fresher): why is renaming a column in the same deploy as the code that uses it unsafe, and what do you do instead?
During a deploy, old and new code run side by side: servers receive the new code one at a time, and queue workers keep the old code until they restart. The migration runs once, in the middle of that, so every server still on the old code fails its next query on the old name, and rolling the code back fails too, because the column stays renamed. In the recorded run, the old code’s save failed with “no column named phone” right after the rename. Instead, expand and contract: add the new column as nullable, deploy code that writes both, backfill the old rows in small queued jobs, switch reads, stop writing the old column, and drop it in a later deploy once nothing uses it.
The two questions below are about a type change on a large table and a test suite that passes on the wrong database. Decide your first move for each, then compare it with the suggested first step:
Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.
-
Mid to senior
Pathshala stores fee amounts in fees.amount as decimal(10,2) rupees on a MySQL 8.4 table of 25 million rows that takes writes all day. Finance wants whole paise in a bigint column instead. How would you make the change without downtime or lost writes?
-
Fresher to mid
The test suite runs on in-memory SQLite and is green. After a release, production on PostgreSQL shows three bugs: a guardian search finds nobody for "asha", a list shows students without a name at the bottom instead of the top, and a 300-character note is refused. How do you explain them, and what do you change?
Flashcards · 6 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
Why is renaming a column in the same deploy as the code that uses it unsafe?
While a deploy rolls out, some servers still run the previous code, and queue workers keep it until they restart, so each of those processes fails its next query on the old name. Rolling the code back leaves the migration in place, so that fails too.
-
What are the steps of expand and contract for replacing a column?
Add a nullable column the old code ignores, write both columns, backfill old rows in small idempotent jobs, switch reads to the new column, end the writes to the old column, and remove it only in a deploy after that.
-
Why does a contract migration put its statement that can fail first?
MySQL cannot roll back a schema change, so everything before a failure stays done. The recorded contract stopped on 3 rows without a number before it had dropped anything.
-
How do you add an index to a busy PostgreSQL table without blocking writes?
With CREATE INDEX CONCURRENTLY, which cannot run inside a transaction, so the migration sets withinTransaction to false. MySQL 8.4 adds a normal index online but copies the table for a change of column type.
-
Why should tests of LIKE, NULL ordering and long values run on the production database?
The same Laravel code got different answers from SQLite, MySQL and PostgreSQL for LIKE, the order of NULLs, a value longer than its column, and full-text search.
-
When does a JSON column fit, and when does MongoDB?
A JSON column keeps varying fields inside a table that still joins and commits with the rest. MongoDB fits data that is document-shaped and stands on its own; it is not a way to keep data out of migrations.
Key takeaways
- A rename or type change in the same deploy as its code breaks every server and worker still on the old code, and a rollback of the code does not undo the migration.
- Expand and contract adds a nullable column that old code ignores, writes both columns, backfills old rows in small idempotent jobs, switches reads, stops writing the old column, and drops it in a later deploy.
- A contract migration puts the statement that can fail first, because MySQL cannot roll back a schema change; the recorded one stopped on 3 rows without a number before anything was dropped.
- PostgreSQL builds an index without blocking writes only with
CREATE INDEX CONCURRENTLY, outside the migration’s transaction ($withinTransaction = false); MySQL 8.4 adds a normal index online but copies the table for a change of type. - The same Laravel code got different answers from SQLite, MySQL and PostgreSQL for
LIKE, NULL order, a value longer than its column and full-text search, so tests for these belong on the production database. - A JSON column keeps varying fields inside a table that still joins and commits with the rest; MongoDB fits data that is document-shaped and stands on its own, not data you want to keep out of migrations.
Terms in this lesson
- backfill
- Filling a new column or table with values for the rows that existed before it, usually in small batches so that the table keeps serving requests meanwhile.
- expand and contract
- A way to change a schema without downtime: first add what the new code needs while the old code keeps working, move the data, switch the code, and only then remove what no running code uses.
- generated column
- A column whose value the database computes from other columns of the same row, either when the row is read (virtual) or when it is written (stored). It cannot be written to directly.
- rolling deploy
- A deploy that gives the new code to the servers one at a time, so the site keeps answering while it runs and, for a while, old and new code serve requests side by side.
References
- Database: Migrations (column modifiers, renaming, indexes) (Laravel (laravel.com))
- Database: Query Builder (JSON and full text where clauses) (Laravel (laravel.com))
- Queues (restarting workers after a deploy) (Laravel (laravel.com))
- Upgrade Guide (MySQL upsert and DELETE with JOIN changes) (Laravel (laravel.com))
- MongoDB (Laravel (laravel.com))
- mongodb/laravel-mongodb (Packagist (packagist.org))
- MySQL 8.4 Reference Manual: Online DDL Operations (Oracle (docs.oracle.com))
- MySQL 8.4 Reference Manual: Statements That Cause an Implicit Commit (Oracle (docs.oracle.com))
- MySQL 8.4 Reference Manual: CREATE TABLE and Generated Columns (Oracle (docs.oracle.com))
- MySQL 8.4 Reference Manual: Problems with NULL Values (Oracle (docs.oracle.com))
- MySQL 8.4 Reference Manual: Case Sensitivity in String Searches (Oracle (docs.oracle.com))
- MySQL 8.4 Reference Manual: SELECT Statement (ORDER BY) (Oracle (docs.oracle.com))
- PostgreSQL 18 Documentation: CREATE INDEX (building indexes concurrently) (The PostgreSQL Global Development Group (postgresql.org))
- PostgreSQL 18 Release Notes (virtual generated columns) (The PostgreSQL Global Development Group (postgresql.org))
- PostgreSQL 18 Documentation: Sorting Rows (ORDER BY) (The PostgreSQL Global Development Group (postgresql.org))
- PostgreSQL 18 Documentation: Pattern Matching (LIKE and ILIKE) (The PostgreSQL Global Development Group (postgresql.org))
- PostgreSQL 18 Documentation: Character Types (The PostgreSQL Global Development Group (postgresql.org))
- SQLite: SQL Language Expressions (the LIKE operator) (SQLite (sqlite.org))
- SQLite Frequently Asked Questions (VARCHAR lengths) (SQLite (sqlite.org))
- SQLite: SELECT (the ORDER BY clause) (SQLite (sqlite.org))
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress