The thing that clicked today: I have on the order of 91,000 photos and videos accumulated over twenty years, and I want to cull them systematically instead of by guilt and vibes. The first step is boring and everything else depends on it: read every file's metadata into a database so I can ask real questions before I delete anything. This was day one of building that ingest tool.
Built / shipped
A single ingest script, grown over four iterations and tested on real directories at each step before moving on. It walks the library, pulls the camera metadata and file metadata, computes a content hash (a fingerprint of the exact bytes, so identical files can be found), computes perceptual hashes (a looser fingerprint of what the image looks like) and a blur score for images, pairs up Live Photos, and writes it all to a SQLite database. The database lives on a local SSD, never on the network share, because SQLite and network filesystems do not get along over small writes and file locking.
Problems & fixes
The brief said the metadata tools were "already installed." They were not, and there's no passwordless sudo on the box, so I couldn't just install them. The fix was to bundle the standalone, no-install version of the EXIF tool alongside the script and call it directly. No root required, fully self-contained.
Then the big Python image libraries kept dying mid-download on a flaky TLS connection (DECRYPTION_FAILED_OR_BAD_RECORD_MAC, over and over). The fix was a small script that fetches each package with a resumable download, then installs from the local folder. One gotcha worth writing down: the computer-vision packages are published for a range of Python versions under one label rather than for my exact version, so a download selector keyed on the exact version silently misses them.
Decisions
- Create the full table schema (every column and index) up front, so later iterations only backfill columns and never have to migrate.
- Build resume in from the start: skip a file if it's already processed and its size and last-modified time are unchanged, and never re-hash a file that hasn't moved.
- Batch one EXIF invocation per directory instead of per file. The per-file cost was process startup, not work: starting the tool once per file costs about 256 ms before it reads anything, and batching one invocation per directory cut a fresh ingest by 61%. It only helps across many directories; a single directory has nothing to overlap.
- Cap workers at four rather than guessing higher. I tried 4, 6 and 8 workers on a 25 GB directory. All three finished within 0.6% of each other (~39.5 minutes), zero errors. Past about four workers you buy nothing but RAM, because reading whole files over the network share to hash them is the bottleneck. Effective throughput landed around 10.8 MB/s, which extrapolates the whole library to roughly two days of wall time.
- Read each image's bytes exactly once and feed them to both the hasher and the decoder, no second read of the same file over a slow mount, and compute blur on a grayscale image resized to a fixed longest edge so the scores are comparable across resolutions.
Learned
- When the environment contradicts the brief, bundle the tool yourself: a standalone, no-root binary beats waiting on sudo.
- A flaky TLS connection kills large package downloads; resumable per-file fetches plus a local install folder was the path that actually worked.
- Measure before you tune. I'd have happily thrown eight workers at this; the measurement said four, and said the real cost was the network, not the CPU.
Still open / next
The full multi-day ingest run, a duration backfill for videos once a media probe tool is available, and a decision about whether to keep full-file hashing every large video over the network or sample head/tail instead. And a couple of modern formats (webp, avif) aren't in the extension list yet, so they're being silently skipped, a thing to catch before it bites.