The database and check is on your device, and triggered when you try to upload. There is not much point to moving scanning to the device-side, unless this technology is intended to be extended so that in the future it doesn't check for an iCloud upload trigger.

I wonder how much local storage space is used to support this self-incrimination application?

Not much actually since it's just strings of hashes. It will however use CPU cycles when it hashes your device's photos.

If you use an Apple phone as intended every photo you take is automatically uploaded to iCloud. It takes no usr action and does not imply an intent to share or distribute the photo.