cockroachdb / cockroachdb/cockroach
backup: planning perf improvements at 100k descriptors
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Verbose tracing of a full-cluster `BACKUP INTO ... WITH DETACHED` against a 100k-descriptor cluster shows that planning takes **~8.7 seconds end-to-end**, of which **~8.4 s (~96%)** is spent in a single uninstrumented region inside `Collection.aggregateAllLayers` (`pkg/sql/catalog/descs/collection.go:1288`). Projecting linearly, this is expected to be ~90+ seconds at 1M descriptors.
Within `aggregateAllLayers` the cost decomposes (estimated) into descriptor proto decoding, six-layer cache walks in `getDescriptorsByID`, `finalizeDescriptors` validation, `hydrateDescriptors` UDT resolution, and a final `fullClusterTargets` filtering pass. Validation is likely the single biggest bucket.
### Why the work is wasted
For a full-cluster backup, the planner-side descriptor walk's result is **not stored** in the job payload — see the comment at `pkg/backup/backup_planning.go:609` (*"for full-cluster we can just recompute it during execution"*). The same `fullClusterTargetsBackup` call is re-issued by `createBackupManifest` at execution time (`pkg/backup/backup_job.go:1552-1567`), so the decode + validate + hydrate cost is paid **twice** per cluster backup.
The planner only uses the resolved descriptors for two things:
1. An external-table reject check (`backup_planning.go:565-572`)
2. Populating `Record.DescriptorIDs` for the job row (`backup_planning.go:671-676`)
Neither requires fully validated/hydrated descriptors. `checkPrivilegesForBackup` is passed `targetDescs` but short-circuits to a system-privilege check on the cluster-backup path and never reads the slice.
---
**Approach 1 — lightweight scan for cluster backups (preferred).** Replace `fullClusterTargetsBackup` at the planner with a path that does a partial-decode scan returning only descriptor IDs + an "is external table" bit, skipping validation, hydration, and the six-layer cache resolution. Prior art exists in `Collection.GetAllTableIDsInDatabaseFromStorage` (`collection.go:1052-1063`). Estimated savings: ~5.5–7 s of the 8.4 s gap at 100k → planning drops to ~1.5–3 s total. At 1M scale: ~15–30 s instead of ~90 s.
**Approach 2 — defer the external-table check to execution.** Removes the only planning-time use that requires per-descriptor inspection, allowing the planner to skip the descriptor scan entirely for cluster backups. Trade-off: external-table-containing clusters would surface the error as a failed job rather than a rejected statement. Less preferred — we want validation in planning when feasible — but kept on the table as a fallback if Approach 1's per-descriptor inspection turns out to dominate.
Epic CRDB-62562
Contributor guide
Assessment
This issue has not been assessed yet.