Duplicate data in external storage

[SimonMisencik]

Hi,
One of the weaknesses of external storage is that it stores a lot of duplicate data, so it would be good to save the data only once to save storage space.

I implemented this functionality here:

• feature: Add option to add payloadBytes into getLocation method #3383
• feature: Not storing duplicate data in external storage conductor-community#153

This implementation calculates a digest(sha-256 in this case) from payloadBytes and uses it as the id of the record in the database. If data with the same digest already exists in the database, only created_on is updated to avoid deletion during the cleaning of old data.

[MikeTangoEcho]

@SimonMisencik Do you have a reason to not hash the payload on upload , and manage a look up table with the hashes with a foreign key on the table with the files ? The download will then join both tables to retrieve the result.

Pros	Cons
no update of createdOn	+1 table, +1 insert for each upload
on delete, remove all hashes behind ttl, then async delete all payloads that are not linked to any hash	one row of hash for each upload with a different path
reconcile payloads, on race condition

classDiagram
    class Payload
    Payload: +Id id
    Payload: +Blob data
    Payload: +Timestamp createdOn
    class PayloadHash
    PayloadHash: +Id id
    PayloadHash: +String hash
    PayloadHash: +String path
    PayloadHash: +Payload payload
    PayloadHash: +Timestamp createdOn
    Payload "1" <.. "0..n" PayloadHash

Loading

[SimonMisencik]

Hi @MikeTangoEcho, can you please more explain the second:
on delete, remove all hashes behind ttl, then async delete all payloads that are not linked to any hash
and third:
reconcile payloads, on race condition
rows in the table? I don't understand them.

[MikeTangoEcho]

From only created_on is updated to avoid deletion during the cleaning of old data and the fact that you use the postgres modules in your PR.

I guessed your query to clean old data to be like DELETE FROM payload WHERE created_on < :TTL;.
With a look up table, it will be in two steps:

• first deletion of hashes DELETE FROM payload_hash WHERE created_on < :TTL;,
• then deletion of payloads that are not referred by any hash (ones with a different path) DELETE FROM payload WHERE id IN (SELECT p.id FROM payload p LEFT JOIN payload_hash ON payload_hash.payload_id = payload.id WHERE payload_hash.id IS NULL);

For the race condition: the upload is not an atomic operation and there is no redis lock set around.

• Your solution INSERT ... ON CONFLICT (id) DO UPDATE works since the id/key/path holds the hash,
• but for my suggestion i ends up with duplicate. In this case, if the growth is not acceptable until deletion, there is a need to reconcile payloads, by discarding duplicate references from the same hash to different payload id: UPDATE payload_hash ph SET ph.payload_id = (SELECT p.id FROM payload p LEFT JOIN payload_hash ph2 ON ph2.payload_id = p.id WHERE ph2.hash = ph.hash LIMIT 1); then delete all payload with no referrer like the second step of the previous cleanup procedure.

[SimonMisencik]

Thanks for the explanation and looking at the PRs. I still don't quite understand how you could get a race condition in my implementation, but I will try to think about it. But anyway, even if that happens, the worst thing that can happen is that the created_at time will differ by a few milliseconds, and that's not such a big deal, or am I wrong? So far, my implementation seems simpler and more effective, in my opinion.