Tenant data exports without breaking the DB

A tenant clicks "export all my data." Three minutes later, every other tenant on the same database is timing out. The naive query plan opened a transaction, scanned every relevant table, and held shared locks for the whole walk. The longer the export ran, the more queries piled up behind it.

Exports break shared databases more often than people think. Here is the structure that does not.

What "export everything" actually means#

GDPR-style "give the user all their data" requires:

1. Every row, across every tenant-bound table, that belongs to this tenant.
2. Joined or denormalised so the export is intelligible without schema knowledge.
3. Delivered as a downloadable file (CSV per table, often zipped, or a JSON bundle).
4. Within a "reasonable" wall-clock window (we promise customers 24 hours; in practice it finishes in under an hour for 99% of tenants).

What people miss is that points 1 and 4 conflict on a shared database. Point 1 is "lots of rows;" point 4 is "lots of rows, fast, without locking the table." That is the whole problem.

The pattern that broke#

The first export job we shipped did the obvious thing:

DB::transaction(function () use ($tenant) {
    $bundle = [];
    foreach (TENANT_TABLES as $table) {
        $bundle[$table] = DB::table($table)
            ->where('tenant_id', $tenant->id)
            ->get();
    }
    return $bundle;
});

Three problems, in order of severity:

1. One transaction. All queries run inside one transaction so the snapshot is consistent. That transaction holds locks on InnoDB row versions for the entire export. Long-running exports wedge other writers.
2. No chunking. Each get() materialises the entire table for that tenant in memory. A tenant with 200,000 invoice rows blows the worker's memory.
3. No throttle. The export reads as fast as the database can serve, competing with live tenant traffic for connection slots and disk IO.

The fix involves giving up on each of those three.

The shape we ship now#

class TenantExport implements ShouldQueue {
    public function handle(): void {
        $bundle = new ZipArchive();
        $bundle->open($this->path, ZipArchive::CREATE);

        foreach ($this->config->tables as $table => $cfg) {
            $this->dumpTable($table, $cfg, $bundle);
        }

        $bundle->close();
        Notification::route('mail', $this->tenant->email)
            ->notify(new ExportReady($this->path));
    }

    private function dumpTable(string $table, array $cfg, ZipArchive $zip): void {
        $stream = fopen('php://memory', 'r+');
        $writer = $this->csvWriter($stream, $cfg['columns']);

        DB::table($table)
            ->where('tenant_id', $this->tenant->id)
            ->orderBy('id')
            ->chunkById(2000, function ($rows) use ($writer) {
                foreach ($rows as $row) {
                    $writer->insertOne((array) $row);
                }
                usleep(200_000); // 200ms breath, every chunk
            });

        rewind($stream);
        $zip->addFromString("{$table}.csv", stream_get_contents($stream));
        fclose($stream);
    }
}

Five changes from the broken version:

No outer transaction#

The bundle is eventually consistent. A row created during the export might or might not be in it, depending on timing. We accept this. Customers care about export within 24 hours; they do not care that the snapshot is at second-precision. The legal text in our privacy policy says "current state at time of export, may not include operations from the last sixty seconds."

chunkById, not chunk#

Laravel's chunk uses OFFSET ... LIMIT, which gets quadratically slower on large tables. chunkById walks WHERE id > last_seen ORDER BY id LIMIT N. Linear time, predictable plan, uses the primary index.

Streaming CSV writes#

league/csv writing into a memory stream, then flushed to the zip. On very large tenants we swap to a disk file (tmpfile()); the rest of the code is unchanged.

Per-chunk usleep(200ms)#

This is the throttle. Without it, the export burns the database. With it, the export uses roughly 1/6th of available IO at peak. We arrived at 200ms by halving until other tenants stopped reporting slowdowns during exports.

A receipt at the end#

The export job writes its own row to a tenant_exports table: tenant_id, path, row_count, started_at, finished_at, requested_by. The receipt is what customer support shows when a tenant claims their export was incomplete. Without it, you are arguing from memory.

What we put in the bundle#

Tables in the bundle are not the raw schema. We project to a "stable export schema" maintained alongside the table:

const EXPORT_TABLES = [
    'invoices' => [
        'columns' => ['id', 'number', 'created_at', 'amount', 'status', 'notes'],
        'transform' => InvoiceExportTransform::class,
    ],
    // ...
];

The transform fills denormalised fields. For invoices it joins the contact name, the company name, the line items as a JSON column. For the customer downloading the bundle, "invoice 4729 to Acme Corp for 1450 SAR on 2026-05-08" is more useful than "row in invoices, row in contacts, row in line_items, you figure it out."

What we deliberately do not export#

Three categories:

1. Hashed and salted credentials. The user has them; we do not need to surface them.
2. Audit log of other users. A user export contains their audit trail, not the company's. Otherwise admin exports leak employees.
3. Computed reports. Those can be re-derived from the underlying tables; including them doubles the export size for no gain.

Document this in the privacy text. Customers who ask why their export does not have item X are easier to handle when X is on a published list.

What this bought us#

The export job, on a 50,000-invoice tenant, takes about 4 minutes, peaks at 18 % database CPU, and does not affect any other tenant's operations. We have run six concurrent exports across different tenants without measurable degradation on the live read path.

The next part of the multi-tenant series is on audit logs that actually scale, which is a closely related problem with very different constraints. Series page tracks the rest.