[Deliverable] PostgreSQL Maintenance

[chair28980]

Work on how to best handle PostgreSQL database growth and pruning is in progress and must completed to enable node operators to handle database growth in an easy and non-disruptive manner. Also, the PostgreSQL should bring an acceptable QoS from the Status PoV.

Milestone:

Summary

Provide clear strategy and tooling on how a node operator should prune/reduce the PostgreSQL DB size while minimizing service disruption.

Perform the required optimizations so that the PostgreSQL works smoothly and without blocking. The optimization is related to analyzing the `EXPLAIN PLAN' of the most usual queries and adding required indexes or prepared statements to enhance those queries.

We need to perform this optimization in StoreV3.

We will consider this as completed after one week of intense QA performed around Status with no new Postgres-related issues.

Justification

Store database grows linearly with the number of messages on the network. Regular deletion is needed, deletion should be done on older messages.

Operators need to keep Postgres database size controlled and it to support standard queries in the most possible performant way.

Epics

• [Epic: nwaku] PostgreSQL Maintenance nwaku#3072
• [Epic:dogfooding] PostgreSQL Maintenance #211

[chair28980]

Add to scope: waku-org/nwaku#1885, legacy from 2023 milestone.

[fryorcraken]

Note that waku-org/nwaku#1885 may not make sense to do, which is the whole point of this milestone.

[Ivansete-status]

Note that waku-org/nwaku#1885 may not make sense to do, which is the whole point of this milestone.

Sorry, I'm a bit puzzled now. I think is important to allow the size constraint.

Regarding scope, this falls under nwaku team umbrella.

[Ivansete-status]

@chair28980 @fryorcraken - this can be closed as per the work made in waku-org/nwaku#2506
It suffice to establish a retention policy to maintain the database size bounded:
e.g.:
store-message-retention-policy = "size:100MB"
store-message-retention-policy = "size:10GB"

[fryorcraken]

@chair28980 @fryorcraken - this can be closed as per the work made in waku-org/nwaku#2506 It suffice to establish a retention policy to maintain the database size bounded: e.g.: store-message-retention-policy = "size:100MB" store-message-retention-policy = "size:10GB"

We need some dogfooding to happen. Has it happen? Can we make it happen in our fleets?
Also, can you clarify from what version are we using the new strategy with nwaku-compose?

[Ivansete-status]

We need some dogfooding to happen. Has it happen? Can we make it happen in our fleets? Also, can you clarify from what version are we using the new strategy with nwaku-compose?

Yes, I agree with that. That already happened in waku.test and waku.sandbox.

The "size" retention policy is properly working, freeing disk space when applied, from version v0.26.0 (March 2024.)

On the other hand, we completed the "time" retention policy in v0.28.0 (May 2024.) With that, the oldest partitions get removed up to the time derived from the applied "time" retention policy. And this is interesting from the Status PoV.

Components running v0.28.0 ( thanks @gabrielmer for that version and the deployments ❤️ ! :)

• waku.test
• waku.sandbox
• nwaku-compose

We are aiming to update the shards.test (used by Status) the next 29th of May.

[fryorcraken]

We are aiming to update the shards.test (used by Status) the next 29th of May.

We should do shards.staging first, right?

[Ivansete-status]

We should do shards.staging first, right?

Yes, you are right! I didn't mention it properly.