Some more anecdata - from this it looks like you could `VACUUM INTO` + `zstd --long -12` using 19.1s and get 109% of the size you'd get from `dump` + `zstd --long -5` using 32.8s. Saves 13.7s at the cost of 76M. YMMV, obvs.

sqlite3 3.49.1, zstd 64bit 1.5.7, gzip (Apple) 457.120.3

Original file (3.3G)

    3264290816 Photos.sqlite

VACUUM INTO (10.3s, 3.1G, 94.3%)

    3078881280 test.sqlite

gzip -k (76s, 1.1G, 33.1%)

    1080119337 test.sqlite.gz

zstd --long (3.2s, 987M, 30.2%)

     986252298 test.sqlite.zst

zstd --long -9 (8.8s, 903M, 27.6%)

     902282663 test.sqlite.9.zst

zstd --long -12 (21.5s, 885M, 27.1%)

     884863443 test.sqlite.12.zst

.dump (27.6s, 4.7G)

    4693437307 photos.dump

gzip -k (72s, 942M, 28.8%)

     941018021 photos.dump.gz

zstd --long (5.2s, 860M, 26.3%)

     859204016 photos.dump.zst

zstd --long -12 (31.7s, 827M, 25.3%)

     826776415 photos.dump.12.zst

(edited to fix a typo in a size and a conclusion that came from that)