Finding 90% similar photos in a Photos library

Five takes of every pose

A phone library accumulates retakes. Someone blinks, the dog moves, the horizon tilts, and the result is three to five frames of the same scene a few seconds apart, of which one is worth keeping. Photos has a Duplicates utility, and in this library it found four items. That is not a bug. It finds identical and near-identical files, the kind produced by saving a picture twice, and it is deliberately strict about it.

"90% similar" is a different question. It wants the retakes, which share framing and subject but differ in every pixel. Two things are needed to answer it: a per-photo signature that changes a little when the picture changes a little, and a threshold with evidence behind it rather than a guess. Both came from the library's own database, and the story of finding them is most of this article. The smaller cleanups the same database allowed, review albums for screenshots and receipts and a list of the biggest videos, come after.

Where Photos keeps its answers

The library in ~/Pictures/Photos Library.photoslibrary is a package, which is a folder Finder shows as a file. Three parts of it matter here:

• database/Photos.sqlite, a Core Data store. Every table starts with Z, every primary key is Z_PK, and relationships are plain integer columns. ZASSET has one row per photo or video. ZADDITIONALASSETATTRIBUTES has one row per asset with the original file name, size and importing app. ZSCENEPRINT holds the fingerprints. This library's copy was 1.25 GB.
• database/search/leo.sqlite, the index behind the search field. It contains the human words: scene names, place names, people, the Utilities categories, and the apps that imported each item. Older Photos versions called this file psi.sqlite with a different layout.
• resources/derivatives/masters/, a small JPEG preview for every item, named UUID_4_5005_c.jpeg and sorted into folders by the first character of the UUID. They stay on disk even when Optimize Mac Storage has moved the originals to iCloud, which is what makes visual checks possible without downloading anything.

Photos keeps both databases open in write-ahead logging mode. The main file is only part of the state; recent changes live in the -wal file next to it until a checkpoint folds them in. Opening the live file can block on Photos' locks, and copying only the main file silently loses those recent changes. So the first rule is: copy the database together with its -wal and -shm sidecars, and query the copy. The copy replays the log on first open and then behaves like an ordinary database. The whole pipeline, from library to album, looks like this:

+----------------------------------------+
| Photos library: Photos.sqlite, -wal,   |
| -shm                                   |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| copy all three to a scratch folder     |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| sqlite3 -json on the copy; blobs       |
| through hex()                          |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| Ruby: decode, compare, group           |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| text file of asset UUIDs               |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| osascript: media item id UUID/L0/001   |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| album inside a folder in Photos        |
+----------------------------------------+

The second rule is to avoid a dependency. The sqlite3 gem is not installed by default, but the sqlite3 command is, and since version 3.33 it has a -json mode that prints rows as a JSON array. Ruby parses that with the standard library. Binary columns need hex() in the SQL, because JSON mode has no way to emit raw bytes, and .to_i(16) or [hex].pack("H*") turns them back into a number or a byte string on the Ruby side. Every script below uses this helper. Dates in Photos count seconds from the first of January 2001, Apple's Cocoa epoch, which is why cocoa_time exists.

# photos_library.rb
# Shared helpers: locate the Photos library, snapshot its databases,
# and run read-only SQL through the sqlite3 command-line tool.
# No gems required.
require "json"
require "fileutils"
require "open3"
require "tmpdir"

module PhotosLibrary
  COCOA_EPOCH = Time.utc(2001, 1, 1)

  module_function

  def default_path
    ENV.fetch("PHOTOS_LIBRARY", File.expand_path("~/Pictures/Photos Library.photoslibrary"))
  end

  # Photos keeps the databases in WAL mode and holds them open. Querying the
  # live files can block or see a torn state, so copy the database together
  # with its -wal and -shm sidecars and query the copy.
  def snapshot(library = default_path, into: File.join(Dir.tmpdir, "photos-snapshot"))
    FileUtils.mkdir_p(into)
    {
      photos: copy_db(File.join(library, "database", "Photos.sqlite"), into),
      search: copy_db(File.join(library, "database", "search", "leo.sqlite"), into)
    }
  end

  def copy_db(path, into)
    [path, "#{path}-wal", "#{path}-shm"].each do |file|
      FileUtils.cp(file, into) if File.exist?(file)
    end
    File.join(into, File.basename(path))
  end

  # Returns an array of hashes, one per row. Binary columns must be wrapped in
  # hex() in the SQL; sqlite3 -json cannot emit raw blobs. The copy is ours,
  # so no -readonly flag: sqlite needs to replay the -wal file on first open.
  def query(db, sql)
    out, err, status = Open3.capture3("sqlite3", "-json", db, sql)
    raise "sqlite3 failed: #{err}" unless status.success?
    out.empty? ? [] : JSON.parse(out)
  end

  def cocoa_time(seconds)
    seconds ? COCOA_EPOCH + seconds : nil
  end

  def human_size(bytes)
    return "0 B" if bytes.nil? || bytes.zero?
    units = %w[B KB MB GB TB]
    exp = [(Math.log(bytes) / Math.log(1000)).floor, units.size - 1].min
    format("%.2f %s", bytes.to_f / 1000**exp, units[exp])
  end

  # Path of the small JPEG preview Photos keeps locally for every item, even
  # when the original lives only in iCloud.
  def thumbnail_path(uuid, library = default_path)
    File.join(library, "resources", "derivatives", "masters", uuid[0], "#{uuid}_4_5005_c.jpeg")
  end
end

Reading the library from a terminal may also need Full Disk Access for the terminal app, under Privacy and Security in System Settings. Without it the copy will usually fail with a permission error. None of the schema is documented by Apple, and the layout changes between releases. The column names here are from Photos 12.0 on macOS 27.2; the osxphotos project tracks how they have moved over the years.

A fingerprint Photos never shows

The ZSCENEPRINT table has one row per asset and three binary columns. ZDATA is 3,618 bytes and starts with bplist00: a keyed archive of a VNSceneprint, the Vision framework's scene embedding, whose payload is 768 little-endian floats forming a unit vector. It describes what is in the picture. ZDUPLICATEMATCHINGDATA is 24 bytes, present for 23,429 of the 24,550 items, with a 25-byte sibling called ZDUPLICATEMATCHINGALTERNATEDATA. The name says what it is for. Nothing says what is in it.

Twenty-four bytes is 192 bits, which is the size of a perceptual hash: a signature where each bit encodes a coarse comparison of the image, such as whether one cell of a downscaled grey version is brighter than its neighbour. If that is what it is, two properties follow. The same picture gives the same bits, and the number of bits that differ, the Hamming distance, grows with how different two pictures look. A 192-bit blob could also be an opaque token, so this had to be tested rather than assumed:

• The two pairs Photos had already marked as duplicates had a distance of exactly 0. Same picture, same bits.
• Five thousand random pairs of photos had a median distance of 96 bits, which is what independent coin flips over 192 bits give, and the first percentile was 75. Unrelated photos never got close.
• Pairs of photos taken within two minutes of each other had a long tail toward zero: 274 pairs under 8 bits, 528 between 8 and 15, 587 between 16 and 23, then a slow rise to the same peak at 90 bits as the random pairs. Retakes of a scene are in that tail; two different photos shot a minute apart look as unrelated as any other pair.

That is the behaviour of a perceptual hash and nothing else, so the column was trusted. Two things remain unknown and do not matter: which algorithm produced it, and what the alternate 25-byte version encodes. The known-duplicate check is the one that cannot be skipped on another library or Photos version, because it costs one query and would catch a format change immediately.

What 90% means in bits

With the distances measured, "90% similar" stops being a feeling. Ninety percent of 192 bits identical means at most 19 bits different. The calibration shows why that exact number is comfortable: everything below 24 bits was retakes, everything above 75 was unrelated, and the band in between was almost empty.

bits different  0        19  24                        75       96     192
                |--------|---|-------------------------|--------|--------|
                | retakes|   |      nearly empty       |  unrelated photos
                |  of a  |   |                         |  (random pairs
                | scene  |   |                         |   peak at 96)
                +--------+---+-------------------------+-----------------+
identical ------^        ^
             90% similar: at most 19 of 192 bits differ

bits   0    19 24        75   96   192
       |-----|--|---------|----|-----|
       retakes  nearly    unrelated
                empty     (peak 96)
       ^
       identical; 90% = at most 19

A threshold in the middle of an empty band is a forgiving one. Moving it to 15 or 23 changes the group count by a few percent, not by a factor. If the band were full, the number would be a judgement call and the article would need to say so.

Not 200 million comparisons

The direct approach compares every photo with every other. For 20,440 photos that is 209 million pair comparisons, each a 192-bit XOR and a bit count. It is minutes in Python with NumPy and a long coffee in plain Ruby, and it was the first version. The second version uses multi-index hashing, which is the standard way to search a Hamming space and happens to be a few lines.

Split every 192-bit hash into 20 chunks: twelve of 10 bits and eight of 9 bits. If two hashes differ in at most 19 bits, those 19 differing bits can fall into at most 19 chunks, so at least one chunk is identical in both. That is the pigeonhole principle doing all the work. The search becomes: put every photo into a bucket for each of its 20 chunk values, then compare only photos that share a bucket. A 10-bit chunk has 1,024 possible values, so each bucket holds a few dozen photos instead of twenty thousand.

hash A   | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | ...  | c19 | c20 |
hash B   | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | ...  | c19 | c20 |
differs    x    x         x    x    x         x    x       x     x
                                     ^
19 differing bits touch at most 19 of 20 chunks, so one chunk matches
exactly. Bucket every photo by (chunk number, chunk value); compare in
full only the photos that share a bucket.

hash A  |c1|c2|c3|..|c20|
hash B  |c1|c2|c3|..|c20|
differs  x  x     ..  x
19 differing bits cannot
touch all 20 chunks: one
chunk matches exactly, so
bucket by chunk value and
compare only bucket mates.

+----------------------------------------+
| 20,440 hashes of 192 bits              |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| split each into 20 chunks              |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| bucket by (chunk number, chunk value)  |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| candidate pairs: photos sharing a      |
| bucket                                 |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| full compare: popcount(A xor B) at     |
| most 19                                |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| 1,099 matching pairs                   |
+----------------------------------------+

The Ruby version keeps each hash as a 192-bit Integer, which Ruby handles natively, and counts differing bits with (a ^ b).to_s(2).count("1"). That is not the fastest popcount imaginable, but it only runs on candidates, and the whole script finishes in three seconds. The candidate step is exact: it can only miss a pair if that pair differs in 20 or more bits, which is precisely the pair that should be missed.

Pairs into groups, and a second opinion

Matching pairs are not yet groups. If A matches B and B matches C, all three belong together even when A and C differ in 25 bits, because they are three takes of one moment. Union-find does that merge: every photo starts as its own set, each matching pair joins two sets, and the groups fall out at the end. Twelve lines of Ruby.

 matching pairs: A-B B-C D-E F-G G-H H-F
                     |
                     v
+----------------------------------------+
| each pair joins the two sets its       |
| photos are in                          |
+----------------------------------------+
                     |
                     v
    groups: {A, B, C} {D, E} {F, G, H}

#!/usr/bin/env ruby
# similar_photos.rb [MAX_DIFFERING_BITS] [OUTPUT_FILE]
# Groups photos whose perceptual fingerprints differ in at most N of 192 bits.
# Photos computes the fingerprint for its own duplicate detection and stores
# it in ZSCENEPRINT.ZDUPLICATEMATCHINGDATA. 19 bits is 90% identical.
require_relative "photos_library"

threshold = (ARGV[0] || 19).to_i
out_file  = ARGV[1] || "similar.txt"
db = PhotosLibrary.snapshot[:photos]

rows = PhotosLibrary.query(db, <<~SQL)
  SELECT a.ZUUID AS uuid, a.ZDATECREATED AS created,
         hex(sp.ZDUPLICATEMATCHINGDATA) AS hash
  FROM ZASSET a
  JOIN ZADDITIONALASSETATTRIBUTES aa ON aa.ZASSET = a.Z_PK
  JOIN ZSCENEPRINT sp ON sp.ZADDITIONALASSETATTRIBUTES = aa.Z_PK
  WHERE a.ZKIND = 0 AND a.ZTRASHEDSTATE = 0 AND a.ZHIDDEN = 0
    AND a.ZKINDSUBTYPE != 10              -- leave screenshots out
    AND sp.ZDUPLICATEMATCHINGDATA IS NOT NULL
  ORDER BY a.ZDATECREATED
SQL
hashes = rows.map { |r| r["hash"].to_i(16) } # 192-bit Integers
puts "photos: #{hashes.size}"

# Comparing every pair directly is 200 million comparisons. Instead split
# each 192-bit hash into 20 chunks (12 of 10 bits, 8 of 9 bits). Two hashes
# that differ in at most 19 bits must agree exactly on at least one chunk, so
# only photos sharing a chunk value need the full comparison.
CHUNKS = ([10] * 12 + [9] * 8).freeze
raise "threshold must be below #{CHUNKS.size}" unless threshold < CHUNKS.size

def chunks_of(hash)
  shift = 0
  CHUNKS.map do |bits|
    value = (hash >> shift) & ((1 << bits) - 1)
    shift += bits
    value
  end
end

buckets = Hash.new { |h, k| h[k] = [] }
hashes.each_with_index do |hash, i|
  chunks_of(hash).each_with_index { |value, c| buckets[[c, value]] << i }
end

pairs = []
buckets.each_value do |members|
  next if members.size < 2
  members.combination(2) do |i, j|
    next if (hashes[i] ^ hashes[j]).to_s(2).count("1") > threshold
    pairs << [i, j]
  end
end
pairs.uniq!
puts "pairs within #{threshold} bits: #{pairs.size}"

# Union-find turns pairs into groups (A~B and B~C puts A, B, C together).
parent = (0...hashes.size).to_a
find = ->(x) { x = parent[x] while parent[x] != x; x }
pairs.each { |i, j| parent[find.call(i)] = find.call(j) }

groups = Hash.new { |h, k| h[k] = [] }
pairs.flatten.uniq.each { |i| groups[find.call(i)] << i }
sizes = groups.values.map(&:size).tally.sort
puts "groups: #{groups.size}, photos in groups: #{groups.values.sum(&:size)}, sizes: #{sizes.to_h}"

members = groups.values.flatten.sort_by { |i| rows[i]["created"] || 0 }
File.write(out_file, members.map { |i| rows[i]["uuid"] }.join("\n") + "\n")
# One line per group, for the contact sheet script.
File.write("#{out_file}.groups", groups.values.map { |g| g.map { |i| rows[i]["uuid"] }.join(" ") }.join("\n") + "\n")
puts "wrote #{out_file} and #{out_file}.groups"

photos: 20440
pairs within 19 bits: 1099
groups: 660, photos in groups: 1547, sizes: {2 => 522, 3 => 88, 4 => 31, 5 => 10, 6 => 4, 7 => 3, 8 => 1, 12 => 1}

Screenshots are excluded on purpose. Thirty screenshots of the same app differ by a few lines of text and would flood the album with matches that are correct and useless. Favorites stay in, because when choosing which of four takes to keep, the one already marked as a favorite is the one to see.

Then two cross-checks, one numeric and one human. The numeric one used the other fingerprint. The scene embedding in ZDATA knows nothing about pixels and everything about content, so if the hash groups were real, those vectors should agree. On the 1,099 matched pairs the cosine similarity had a median of 0.975 and a fifth percentile of 0.854, and 94% of the pairs were taken within an hour of each other. Two independent signals pointing the same way is as close to proof as a private database allows.

The human check is a contact sheet. The local previews make it cheap, one group per row, and ImageMagick's montage does the layout. Eight random groups showed exactly what the numbers promised: the same pose, the same room, the same mountain view, two to four frames apart.

#!/usr/bin/env ruby
# contact_sheet.rb similar.txt.groups [ROWS] [OUTPUT.jpg]
# Renders a few random groups side by side from the previews Photos keeps
# locally, one group per row, so the threshold can be judged by eye.
# Needs ImageMagick's montage command (brew install imagemagick).
require_relative "photos_library"

groups_file = ARGV[0] || "similar.txt.groups"
rows = (ARGV[1] || 8).to_i
output = ARGV[2] || "contact-sheet.jpg"
per_row = 6

groups = File.readlines(groups_file, chomp: true).map(&:split).reject { |g| g.size < 2 }
sample = groups.sample(rows, random: Random.new(1))

tiles = sample.flat_map do |group|
  paths = group.first(per_row).map { |uuid| PhotosLibrary.thumbnail_path(uuid) }
  paths += ["null:"] * (per_row - paths.size) # pad short rows with blanks
  paths
end

system("montage", *tiles, "-tile", "#{per_row}x#{rows}", "-geometry", "180x180+2+2",
       "-background", "white", output) or abort "montage failed"
puts "wrote #{output}: #{rows} groups of up to #{per_row}"

Getting the groups into Photos

Photos has a usable AppleScript dictionary. media item id "UUID/L0/001" resolves an asset, where the UUID is ZASSET.ZUUID and the suffix is fixed; make new album named X at folder Y creates an album in a folder; add {items} to album fills it. Albums are references. Adding an item changes nothing else about it, so this step is fully reversible, and deleting later is done in Photos by a person. Three details cost time:

• An id that no longer resolves is an item deleted since the database snapshot. Wrap each lookup in try and count what actually landed. The first run lost one video that had been deleted in the ten minutes between the query and the album.
• When a script adds a large batch, Photos shows a dialog: "Add 300 Photos to Album. Are you sure?" It did not appear for 58 items and did for 300. Twenty batches means twenty dialogs, so the script runs a thread that presses Apply through System Events, and only on a dialog whose text names the album it is filling.
• Send batches one after another. Apple Events to a single app are handled in series, so parallel osascript calls cannot make it faster and only risk timeouts.

#!/usr/bin/env ruby
# add_to_album.rb "Folder name" "Album name" uuids.txt
# Adds the listed items to an album inside a folder in Photos, creating both
# when needed. Photos asks for confirmation when a script adds more than a
# few hundred items at once, so a background thread presses Apply on exactly
# that dialog while the batches run.
require "open3"

folder, album, list = ARGV
abort "usage: add_to_album.rb FOLDER ALBUM UUIDS_FILE" unless list
uuids = File.readlines(list, chomp: true).reject(&:empty?)
BATCH = 300

def osascript(script)
  out, err, status = Open3.capture3("osascript", "-", stdin_data: script)
  raise err unless status.success?
  out.strip
end

def quote(text) = text.gsub("\\", "\\\\\\\\").gsub('"', '\"')

osascript(<<~APPLESCRIPT)
  tell application "Photos"
    if not (exists folder "#{quote(folder)}") then make new folder named "#{quote(folder)}"
    if not (exists album "#{quote(album)}" of folder "#{quote(folder)}") then
      make new album named "#{quote(album)}" at folder "#{quote(folder)}"
    end if
  end tell
APPLESCRIPT

# Photos' AppleScript ids are the asset UUID followed by "/L0/001".
watcher = Thread.new do
  loop do
    clicked = osascript(<<~APPLESCRIPT) rescue "no"
      tell application "System Events" to tell process "Photos"
        repeat with w in (every window whose subrole is "AXDialog")
          set txt to ""
          try
            set txt to (value of static text 1 of w) as text
          end try
          if txt starts with "Add " and txt contains "to Album “#{quote(album)}”" then
            click button "Apply" of w
            return "yes"
          end if
        end repeat
      end tell
      return "no"
    APPLESCRIPT
    sleep(clicked == "yes" ? 2 : 1)
  end
end

added = 0
uuids.each_slice(BATCH) do |slice|
  ids = slice.map { |u| %("#{u}/L0/001") }.join(", ")
  added += osascript(<<~APPLESCRIPT).to_i
    tell application "Photos"
      set theAlbum to album "#{quote(album)}" of folder "#{quote(folder)}"
      set theItems to {}
      repeat with theId in {#{ids}}
        try
          set end of theItems to media item id theId
        end try
      end repeat
      if (count of theItems) > 0 then add theItems to theAlbum
      return count of theItems
    end tell
  APPLESCRIPT
  $stderr.print "\r#{added} of #{uuids.size}"
end
watcher.kill
puts "\nadded #{added} of #{uuids.size} to \"#{folder}\" > \"#{album}\""

ruby similar_photos.rb 19 similar.txt
ruby add_to_album.rb "Review for deletion" "09 Similar photos (90%+)" similar.txt

The album is best viewed sorted by oldest first. Retakes were shot seconds apart, so each group lands side by side without any further work, and the choice of which frame to keep is a glance per row.

What else the database knows

The similarity search was the hard part. The same survey of ZASSET and its neighbours turned up far more ready-made classification than expected, enough to sort a library without writing a classifier:

The subtype names are the ones osxphotos documents. The scene classification table is the richest and the least usable on its own: ZSCENEIDENTIFIER is a number such as 1494, and nothing in Photos.sqlite says that 1494 means whiteboard. The names are in the other database.

Names for the numbers

The search index has two tables that matter. lexicon holds every searchable term with a category and an identifier; items holds one row per asset with the terms that apply to it. The categories map cleanly onto what the Photos search field offers:

The link from an item to its terms is a blob called lexeme_ids. The first row examined had 304 bytes there and 152 bytes in lexeme_scores. 304 divided by 4 is 76, and 152 divided by 2 is also 76, which is the whole decoding: an array of 32-bit lexeme ids and a parallel array of 16-bit scores. In Ruby that is unpack("V*") on the bytes. Checking that every decoded id existed in lexicon confirmed it.

+----------------------------------------+
| items row: asset UUID + lexeme_ids     |
| blob (304 bytes)                       |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| unpack as 76 little-endian uint32      |
+----------------------------------------+
                     | join on lexeme_id
                     v
+----------------------------------------+
| lexicon rows: lexeme_id, category,     |
| content, identifier                    |
+----------------------------------------+
                     |
                     v
+----------------------------------------+
| utilityCollection/2 = Receipts;        |
| scene/1494 = Whiteboard                |
+----------------------------------------+

With that, the Utilities counts came straight out: 935 Documents, 47 Receipts, 1,684 Illustrations and 815 Handwriting, and separately 65 items Photos classified as an ID card, passport, licence or visa. The second list is the one to be careful with. Those are not unimportant pictures, they are sensitive ones, and they were excluded from every bucket rather than offered for deletion. The scene route works too, with a confidence threshold, and gives finer classes the Utilities section does not: menu (28 items above 0.5), chart (18), whiteboard (35). The two routes overlap heavily and were simply unioned.

Review albums for the obvious junk

The goal was not a script that deletes. It was a set of albums, each holding one kind of probably-unimportant item, that a person can flick through in Photos and delete from. One album of 6,000 mixed items is useless for that, so there are eight, and every item appears in at most one, assigned in the order listed so screenshots are never counted twice as illustrations.

#!/usr/bin/env ruby
# review_buckets.rb [OUTPUT_DIR]
# Sorts the library into review buckets using Photos' own classifications:
# media subtypes, the Utilities labels from the search index, scene classes,
# the app that imported each item, and simple size and duration rules.
# Writes one UUID list per bucket. Favorites and anything Photos labels as an
# identity document are excluded from every bucket.
require "set"
require_relative "photos_library"

out_dir = ARGV[0] || "review"
dbs = PhotosLibrary.snapshot

# --- 1. Labels from the search index (leo.sqlite) ---------------------------
# lexicon: one row per searchable term. category 4080 is the Utilities
# section (Documents, Receipts, Illustrations, Handwriting); 4130 is content
# classification (Document, ID card, passport, licence, visa, barcode).
# items: one row per asset; lexeme_ids is a packed array of little-endian
# uint32 lexeme ids, so String#unpack("V*") decodes it.
LABELS = {
  "utilityCollection/1" => :documents,
  "utilityCollection/2" => :receipts,
  "utilityCollection/3" => :illustrations,
  "utilityCollection/4" => :handwriting,
  "contentClassification/2" => :sensitive, # driving licence
  "contentClassification/3" => :sensitive, # ID card
  "contentClassification/5" => :sensitive, # passport
  "contentClassification/6" => :sensitive  # visa
}.freeze

lexeme_to_label = {}
PhotosLibrary.query(dbs[:search], <<~SQL).each { |r| lexeme_to_label[r["lexeme_id"]] = LABELS[r["identifier"]] }
  SELECT lexeme_id, identifier FROM lexicon
  WHERE identifier IN (#{LABELS.keys.map { |k| "'#{k}'" }.join(", ")})
SQL

labelled = Hash.new { |h, k| h[k] = [] }
PhotosLibrary.query(dbs[:search], <<~SQL).each do |r|
  SELECT identifier AS uuid, hex(lexeme_ids) AS ids FROM items
  WHERE type = 1 AND lexeme_ids IS NOT NULL
SQL
  [r["ids"]].pack("H*").unpack("V*").each do |lexeme|
    label = lexeme_to_label[lexeme]
    labelled[label] << r["uuid"] if label
  end
end
labelled.transform_values!(&:to_set)

# --- 2. Facts from the main database (Photos.sqlite) ------------------------
BASE = <<~SQL
  FROM ZASSET a
  JOIN ZADDITIONALASSETATTRIBUTES aa ON aa.ZASSET = a.Z_PK
  WHERE a.ZTRASHEDSTATE = 0 AND a.ZHIDDEN = 0
SQL

uuids = ->(condition) {
  PhotosLibrary.query(dbs[:photos], "SELECT a.ZUUID AS uuid #{BASE} AND #{condition}")
    .map { |r| r["uuid"] }.to_set
}

# Assets carrying a scene class above a confidence, by numeric scene id.
# The ids come from the lexicon too: identifier 'scene/1494' is Whiteboard.
scene = ->(id, confidence = 0.5) {
  uuids.call(<<~SQL)
    aa.Z_PK IN (SELECT ZASSETATTRIBUTES FROM ZSCENECLASSIFICATION
                WHERE ZSCENEIDENTIFIER = #{id} AND ZCONFIDENCE > #{confidence})
  SQL
}

# Every item records the app that imported it. Camera, Photos and the lock
# screen are the normal sources. Add any app that only re-imports your own
# pictures (compressors, converters, film filters) so its output is not
# mistaken for something saved from a chat. The report below shows the apps.
OWN_PHOTO_IMPORTERS = %w[
  com.apple.camera com.apple.Photos com.apple.springboard com.apple.mobileslideshow
  com.newmarketinglab.ImageCompressor com.newmarketinglab.CompressVideos
  com.johnRehbein.LivePhotoConverter RNI.Rni-Films
].freeze

puts "items by importing app:"
PhotosLibrary.query(dbs[:photos], <<~SQL).each { |r| puts format("  %6d  %s", r["n"], r["app"] || "(unknown)") }
  SELECT aa.ZIMPORTEDBYBUNDLEIDENTIFIER AS app, COUNT(*) AS n #{BASE}
  GROUP BY 1 ORDER BY 2 DESC LIMIT 15
SQL
puts

alive       = uuids.call("1 = 1")
favorites   = uuids.call("a.ZFAVORITE = 1")
screenshots = uuids.call("a.ZKINDSUBTYPE = 10")
recordings  = uuids.call("a.ZKINDSUBTYPE = 103")
short_video = uuids.call("a.ZKIND = 1 AND a.ZDURATION < 3")
tiny        = uuids.call("a.ZKIND = 0 AND MAX(a.ZWIDTH, a.ZHEIGHT) < 600")
from_apps   = uuids.call(<<~SQL)
  aa.ZIMPORTEDBYBUNDLEIDENTIFIER IS NOT NULL
  AND aa.ZIMPORTEDBYBUNDLEIDENTIFIER NOT IN (#{OWN_PHOTO_IMPORTERS.map { |b| "'#{b}'" }.join(", ")})
SQL

# --- 3. Buckets, made disjoint in this order ----------------------------------
buckets = {
  "01 Screenshots"                        => screenshots,
  "02 Screen recordings"                  => recordings,
  "03 Receipts"                           => labelled[:receipts] | scene.call(1086),
  "04 Documents, handwriting, whiteboards" => labelled[:documents] | labelled[:handwriting] |
                                             scene.call(1494) | scene.call(14852) | scene.call(340),
  "05 Illustrations, memes, stickers"     => labelled[:illustrations],
  "06 Saved from apps"                    => from_apps,
  "07 Tiny images under 600px"            => tiny,
  "08 Videos under 3 seconds"             => short_video
}

excluded = favorites | labelled[:sensitive]
seen = Set.new
FileUtils.mkdir_p(out_dir)
buckets.each do |name, set|
  final = (set & alive) - excluded - seen
  seen |= final
  File.write(File.join(out_dir, "#{name}.txt"), final.sort.join("\n") + "\n")
  puts format("%-40s %5d", name, final.size)
end
puts
puts "excluded: #{favorites.size} favorites, #{(labelled[:sensitive] & alive).size} identity documents"
puts "in review: #{seen.size} of #{alive.size} items"

The importer report at the top matters more than it looks. In this library a compression app had re-imported 15,000 photos, so "saved from an app" would have swallowed most of the library until that app joined the ignore list. Run the script once, read the report, extend the list, run it again. The result:

That is 5,961 of 24,558 items, with 451 favorites and the 65 identity documents left out. Two of the albums need an actual review rather than a bulk delete. "Documents" means "looks like paper" and catches contracts and notes as readily as parking tickets, and "Illustrations" means "not a photograph", which covers memes but also drawings worth keeping. The screenshot and tiny-image albums were as junk as they sound. Scores were surveyed and left out: the aesthetic and curation scores describe how Photos would rank a picture for a memory, not whether it matters to its owner, and a blurry photo of a newborn scores badly on every one of them.

And the biggest videos

The question that started the evening is one query. ZKIND is 0 for a photo and 1 for a video, ZORIGINALFILESIZE is the size of the original even when it is only in iCloud, and ZTRASHEDSTATE marks items waiting in Recently Deleted.

#!/usr/bin/env ruby
# largest_videos.rb [N]
# Lists the N largest videos in the Photos library by original file size.
require_relative "photos_library"

limit = (ARGV[0] || 25).to_i
db = PhotosLibrary.snapshot[:photos]

rows = PhotosLibrary.query(db, <<~SQL)
  SELECT a.ZUUID              AS uuid,
         aa.ZORIGINALFILENAME AS name,
         aa.ZORIGINALFILESIZE AS bytes,
         a.ZDURATION          AS seconds,
         a.ZDATECREATED       AS created,
         a.ZTRASHEDSTATE      AS trashed
  FROM ZASSET a
  JOIN ZADDITIONALASSETATTRIBUTES aa ON aa.ZASSET = a.Z_PK
  WHERE a.ZKIND = 1
  ORDER BY aa.ZORIGINALFILESIZE DESC
  LIMIT #{limit}
SQL

totals = PhotosLibrary.query(db, <<~SQL).first
  SELECT SUM(CASE WHEN a.ZKIND = 1 THEN aa.ZORIGINALFILESIZE END) AS video_bytes,
         SUM(CASE WHEN a.ZKIND = 1 THEN 1 END)                     AS videos,
         SUM(CASE WHEN a.ZKIND = 0 THEN aa.ZORIGINALFILESIZE END) AS photo_bytes,
         SUM(CASE WHEN a.ZKIND = 0 THEN 1 END)                     AS photos
  FROM ZASSET a
  JOIN ZADDITIONALASSETATTRIBUTES aa ON aa.ZASSET = a.Z_PK
  WHERE a.ZTRASHEDSTATE = 0
SQL

puts format("%-9s %7s  %-10s  %s", "size", "length", "shot", "original name")
rows.each do |r|
  mins, secs = r["seconds"].to_i.divmod(60)
  date = PhotosLibrary.cocoa_time(r["created"])&.strftime("%Y-%m-%d")
  flag = r["trashed"] == 1 ? "  (in Recently Deleted)" : ""
  puts format("%-9s %4d:%02d  %-10s  %s%s",
              PhotosLibrary.human_size(r["bytes"]), mins, secs, date, r["name"], flag)
end
puts
puts "videos: #{totals["videos"]} (#{PhotosLibrary.human_size(totals["video_bytes"])}), " \
     "photos: #{totals["photos"]} (#{PhotosLibrary.human_size(totals["photo_bytes"])})"

size       length  shot        original name
1.02 GB      4:49  2020-08-27  IMG_6833-compressed.MOV
760.78 MB    6:46  2026-07-30  ScreenRecording_07-30-2026 21-08-28_1.mp4
491.08 MB    3:38  2020-01-25  IMG_5506-compressed.MOV
463.85 MB    1:08  2019-07-13  IMG_3559.MOV
428.45 MB    1:03  2021-06-09  IMG_9700.MOV

videos: 1067 (89.01 GB), photos: 23494 (21.89 GB)

Videos were four times the size of all the photos together, and the one-minute clips at 0.4 GB were not outliers but ordinary 4K 60 fps recordings, so the bulk of the 89 GB was hundreds of clips like them. AppleScript can jump straight to any of them with the same id format the album script uses:

osascript -e 'tell application "Photos" to spotlight media item id "UUID/L0/001"'

What to keep in mind

• Nothing here writes to the library. The scripts read a copy and talk to Photos only through its scripting interface. Writing to Photos.sqlite directly is the one way to break a library that iCloud will then faithfully replicate.
• The schema is private. Column names, subtype numbers and the search index format have all changed before and will again. Treat the queries as a worked example for one version, and repeat the known-duplicate check before trusting the hash on another.
• The 19-bit threshold is calibrated on one library's distance distribution. The shape should be the same elsewhere, because it comes from the hash rather than from the pictures, but the two-minute histogram is cheap to reproduce and worth a look.
• A review album is a list of candidates, not a verdict. The identity documents were excluded because a classifier cannot know what matters; the same applies, more weakly, to everything in the Documents and Illustrations albums.
• Deleting inside an album only removes the item from the album. To delete from the library, select the items and press Command and Delete, and they move to Recently Deleted for 30 days.
• The previews in resources/derivatives exist only for items that have synced. A freshly imported batch may have none yet, and the contact sheet will show blanks for it.

Photos had already done the hard part, a perceptual hash for every photo, and simply never offered it as a feature beyond exact duplicates. Reading it cost an evening. Reviewing 660 groups to pick the keeper in each will take longer, which is the right way round.