iMessage Size Discrepancy

[tonyflo]

I read through the previous discussions on this topic, but it's still not clear to me why the size that iMessage reports is different than imessage-exporter.

iCloud says my Messages take up 59.6 GB.

My filesystem closely reflects that size at 59.92 GB.

As expected, most of that size is from Attachments.

However, diagnostics only detects around 30 GB of attachments.

[ReagentX]

Attachment diagnostic data is explained here.

[tonyflo]

Thank you for linking the diagnostic data explanation. You mention in there:

The sum of the total_bytes column in the attachments table. I don't know why they are different, but the former is the actual storage taken up by iMessage attachments.

The actual storage taken up by the Attachments folder is 51.49 GB, not 32.89 GB as reported by diagnostics.

Based on the diagnostics and export size, I am not confident that this tool is exporting the entire iMessage database due to the mismatch in what I seeing on the filesystem (~60 GB) and the size of the export (~30 GB).

[ReagentX]

imessage-exporter only looks at attachments actually referenced by the attachment table, it doesn't look at the disk directly–if there are files not referenced in the table, they cannot be exported in the first place. You can read the logic here.

[tonyflo]

That's very helpful. I just learned a lot about the filesystem storage of the iMessage database.

What I did was fire up sqlite3:
sqlite3 chat.db

And listed all the attachments in my database:
sqlite> SELECT filename FROM ATTACHMENT;

I picked a random one:

~/Library/Messages/Attachments/76/06/5F4D844E-F2BA-47E9-A938-E0F719401D71/IMG_6432.jpeg

And looked in that directory with Finder:

As you can see there is a JPEG (the still image), MOV (the live photo) and another file format called PVT. I found out that the PVT file contains both the live photo and still image and just so happens that the file sizes equal each other: JPEP + MOV = PVT (2.2 MB + 4.5 MB = 6.7 MB)

Now I guess because the chat.db is only referencing IMG_6432.jpeg then MOV and PVT files are not considered in the export. Is that true, and if so I think this could explain why iCloud and my filesystem are approximately 2x larger than the export size?

[ReagentX]

It's possible, but that may not be the only reason.