bug: message retention policy not applied

[apentori]

Problem

nwaku doesn't remove old message from the database psql

Impact

The database become to big

To reproduce

1. Run a nwaku node with the image harbor.status.im/wakuorg/nwaku:deploy-shards-test and a psql database
2. Configure a retention policy
3. Send message
4. Wait for the retention time to be over

Expected behavior

Message should be removed after the retention policy period

Screenshots/logs

Node configuration

$ sudo cat /docker/nim-waku-store/conf/config.toml| grep store-message
store-message-db-vacuum = false
store-message-retention-policy = 'time:2592000'

Database:

nim-waku=> select version, id, timestamp, storedat from messages order by timestamp limit 5;
 version |                                id                                |      timestamp      |      storedat
---------+------------------------------------------------------------------+---------------------+---------------------
       1 | 1bd8438dffc9bd76273f6a1001d89d855509e065d1bef8bf356071e5772de4f2 | 1711914289283505255 | 1711914289283505255
       1 | 1bd8438dffc9bd76273f6a1001d89d855509e065d1bef8bf356071e5772de4f2 | 1711914289283505255 | 1711914289283505255
       1 | b437db6b71604cbf194c0b85e2d7f178407e17e00396ccea1bd8d19ff0f9e840 | 1711914289289262019 | 1711914289289262019
       1 | b437db6b71604cbf194c0b85e2d7f178407e17e00396ccea1bd8d19ff0f9e840 | 1711914289289262019 | 1711914289289262019
       1 | 621126883dd68d10b15ae4856130cf988fcb3cef155d7a2bef41fd79968e2693 | 1711914289296294165 | 1711914289296294165

nwaku version/commit hash

version : https://harbor.status.im/harbor/projects/9/repositories/nwaku/artifacts-tab/artifacts/sha256:a149122c9a54b7da1a9b4fa66f83d803c3af27e3f6acfbc46010e15e2f3f751c

Additional context

Problem appeared in fleet shard.testfor the node store-db-01.gc-us-central1-a.shards.test

[NagyZoltanPeter]

@apentori: I don't think currently we have automation on decreasing pgsql db size automatically in case of time retention policy in use, yet. We do have it for size retention policy. As much as I know its on the desk to extend auto partition management for config with time retention policy which will result in automatic space management out of the box for psql DB.

@Ivansete-status can you please confirm that? Do we have separate task for that work? If so we may can close this bug. Thank you.

[apentori]

Well the main problem here is the messages are not deleted after the retention policy time is over. That cause the database to get bigger over time. Also from what @Ivansete-status told me, for this version, the size retention policy wont reduce the db size if we are past the limit.

Please advice on what to do with the node store-db-01.gc-us-central1-a.shards.test to ensure that it work correctly for the status application (Drop db, manually remove message,...)

[Ivansete-status]

Thanks for submitting that @apentori ! This is something we need to fix. I will tackle it asap so that we can apply the fix in the current waku.test fleet where we have 45GiB databases.

@NagyZoltanPeter - you are absolutely right with all your comments. Given that we don't have a specific task for such a feature we will use that bug to tackle that.