feat: Migrate deployments to PostgreSQL

[chair28980]

Ensure that new and existing Waku fleets use PostgreSQL by default.

Priority: Critical for launch

[Ivansete-status]

Hi @chair28980 !
May kindly elaborate a bit more on this point? I can't quite remember why we need that and I was assuming that the PostgreSQL was specifically interesting from the Status point of view.
( cc @jm-clius to shed some light on this .)

[jm-clius]

was specifically interesting from the Status point of view

This is true, so as long as the Status fleets are working as expected with PostgreSQL, migrating other deployments is not critical. I would say though that the new fleets (for the Waku Network, for example) should use postgresql to simplify what we maintain and support, but I think this is already the case.

[fryorcraken]

migrating other deployments is not critical.

I believe that infra team has been complaining that SQLite is not as easy to manage and hence would prefer a migration to PostgreSQL.
I understand their grievance is mainly around the fact that SQLite needs double the space to reduce the watermarks/refragment/vacuum.

Is this statement still valid? I think infra may consider migrating other deployment as critical.

edit: clarified my comment, original comment below

---

Is this comment still valid considering the complete from infra regarding SQLite database sizes? @jm-clius @Ivansete-status

[Ivansete-status]

Weekly Update

• achieved: Preparing environment to stress one single database with multiple Postgres clients writing and reading simultaneously.
• next: Extend Postgres benchmarking report from previous results and start analyzing the performance of status.test fleet where three nodes will use one single database.

[Ivansete-status]

Is this comment still valid considering the complete from infra regarding SQLite database sizes? @jm-clius @Ivansete-status

I think it is still valid. We have the "size" retention policy for SQLite database which can be helpful to simplify the maintenance on fleets != status.*

[fryorcraken]

I think it is still valid. We have the "size" retention policy for SQLite database which can be helpful to simplify the maintenance on fleets != status.*

As long as infra is happy.

[Ivansete-status]

I will close this because I don't see a clear need to change the current wakuv2.* archive system, for instance.
On the other hand, Jakub doesn't consider this migration (SQLite -> Postgres) very interesting. Instead, he considers we need to reduce the number of fleets ( @jakubgs - correct me if I said something inaccurate .)

( cc @chair28980 )

[chair28980]

Closing in favor of work covered in 2024 Milestone scope.