====== Attachments deduplication ======

Attachments are stored into 3 tables: ''attachments'', ''attachment_contents'' and the ''pg_largeobject'' system table.

At import time, the SHA1 fingerprint of attachments are computed and compared to contents already in the database. When a match is found, a new reference is created in attachment_contents to the already existing OID (large object reference) instead of importing again the same contents.

This can save significant amounts of disk space, especially when the mail database has accounts within the same organization where documents tend to be sent to multiple persons. Logo pictures in signatures are also good candidates for deduplication.

The following query shows how much total space in bytes is saved from attachments deduplication:
<code sql>
select sum(sz) from (select fingerprint,content_size*(count(*)-1) as sz
 from attachment_contents ac join attachments a using(attachment_id)
 group by fingerprint,content_size
 having count(*)>1) s1;
</code>

The following query retrieves, within the top 30 of the most deduplicated attachments (in number of occurrences), the count and sizes of attachments with file names and a significant size:
<code sql>
select filename,count(*),content_size
 from attachments a join attachment_contents ac using(attachment_id)
 join (select fingerprint,count(*) as cnt from attachment_contents
    group by fingerprint order by 2 desc limit 30) s1
     using(fingerprint)
  where content_size>50000
  group by filename,content_size;
</code>