Pushed to: origin (beta) + production (live)

The Problem

We shipped a new Property model to normalize address and lot data across orders. Every order needed to be linked to a property — 70,810 orders across 5 tenants, with tenant 1 alone holding 66,549 records. The backfill ran during php artisan migrate --force on deploy. It timed out.

Approach v1: PHP Cursor Loop (Migration)

The first attempt used a PHP cursor() loop inside a Laravel migration — iterating over address groups one at a time, running ~3 queries per group (check if property exists, insert if not, update orders). At ~5 records/second, it timed out during the Forge deployment script before making a meaningful dent.

Approach v2: Eloquent Queued Job (Per-Row)

Next, we built a proper queued job (BackfillOrderProperties) dispatched per-tenant via an artisan command. Each order went through PropertyService::linkOrderToProperty(), which calls:

  • Order::find() — load the Eloquent model
  • findOrCreate() — query properties table by normalized address, insert if missing
  • updateQuietly() — set property_id on the order
  • syncPropertyFromOrder() — conditionally update property with better data

That's 3-5 database queries per order, or roughly 250,000 queries for tenant 1 alone.

After 10 minutes: 267 of 66,549 orders processed. That's 0.4 records/second. Projected completion: ~40 hours for a single tenant.

Approach v3: Bulk SQL (2 Statements)

We threw away the ORM and wrote two raw SQL statements:

Pass 1 — Create properties:

INSERT INTO properties (tenant_id, customer_id, address, ...)
SELECT o.tenant_id, o.customer_id, LOWER(TRIM(o.address)), ...
FROM orders o
INNER JOIN (
    SELECT MAX(id) as latest_id
    FROM orders
    WHERE tenant_id = ? AND address IS NOT NULL
    GROUP BY LOWER(TRIM(address))
) latest ON o.id = latest.latest_id
WHERE NOT EXISTS (
    SELECT 1 FROM properties p
    WHERE p.tenant_id = o.tenant_id
      AND p.address = LOWER(TRIM(o.address))
);

Pass 2 — Link orders:

UPDATE orders
SET property_id = p.id
FROM properties p
WHERE orders.property_id IS NULL
  AND p.tenant_id = orders.tenant_id
  AND p.address = LOWER(TRIM(orders.address));

Result: 68,551 properties created and 70,810 orders linked in seconds. We also added functional indexes on LOWER(TRIM(address)) for ongoing performance.

The Numbers

ApproachRecords/secTime for 70K ordersTotal Queries v1: PHP cursor loop~5/secTimed out on deploy~3 per address group v2: Eloquent per-row job~0.4/sec~40 hours projected3-5 per order (~250K) v3: Bulk SQL~70,000/secSeconds2 total

That's a 175,000x speedup from v2 to v3.

The Lesson

Eloquent's convenience costs ~3-5ms per row in overhead — invisible at 10 rows, fatal at 70K. The ORM is the right tool for request-response cycles where you're touching 1-50 records with complex business logic. For bulk data operations, drop down to raw SQL and let PostgreSQL do what it's good at: set operations on large datasets.

The progression tells the whole story:

  • 10 rows → Eloquent. Readable, testable, convenient.
  • 1,000 rows → Chunked jobs. Distributes work, survives timeouts.
  • 70,000 rows → Raw SQL. Two statements. Seconds.

Always start with the simplest approach. But know when to drop down a level.

Also Shipped Today

  • Dark theme on auth pages: Login, Register, and ForgotPassword now force .prime-dark regardless of user's theme preference.
  • Terms/Privacy link fix: Switched from Inertia <Link> to standard <a> tags pointing to the marketing domain — Inertia links can't cross subdomain boundaries.
  • Dashboard migration ticker: Real-time progress banner showing orders linked to properties, polls every 10 seconds, auto-hides when complete.