search

waku-org / pm

Project management, admin, misc
3 stars 1 forks source link

[Deliverable] PostgreSQL Maintenance #119

Closed chair28980 closed 3 days ago

chair28980 commented 8 months ago

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

chair28980 commented 8 months ago

Add to scope: https://github.com/waku-org/nwaku/issues/1885, legacy from 2023 milestone.

fryorcraken commented 8 months ago

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

Ivansete-status commented 8 months ago

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 commented 7 months ago

@chair28980 @fryorcraken - this can be closed as per the work made in https://github.com/waku-org/nwaku/pull/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 commented 5 months ago

@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 commented 5 months ago

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 :heart: ! :)

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

fryorcraken commented 4 months ago

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 commented 4 months ago

We should do shards.staging first, right?

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

fryorcraken commented 3 days ago

This is done.