chore(postgres): Optimize the database.

[Ivansete-status]

Background

When nwaku has the "store/archive" protocol mounted it can store and retrieve historical messages. All this information is kept in a single table, messages. We need to optimize this.

Details

We need to get the maximum performance possible with regard to insert/select operations.
We need a rapid response when duplicate msgs happens. For that, we may need to adapt how the message id is generated so that we achieve high selectivity.

Tasks

• [ ] Rename the messages table to MESSAGE. (that renaming caused issues in the current existing shards.test fleet.
  - [ ] Apply @Menduist 's enhancement suggestions for a more appropriate asynchronous handling: feat(common): added postgress async pool wrapper #1631 (comment)
• Perform tests in a standalone database. This is a DB analyst task where operations are checked in a table with hundreds of millions of rows.
• Apply "integration" performance tests.
  ℹ️ We understand that a query performance is acceptable in Grafana by checking that "Waku Archive Query Duration" is <50ms.
  ℹ️ For that, we will use the next repo: https://github.com/waku-org/test-waku-query (cc - @richard-ramos )

Related issue

#1604

[jm-clius]

• add an index column with the consistent, deterministic message hash as message ID.

edited: being considered in #2112

[Ivansete-status]

<<Apply "integration" performance tests.>> This is done in the https://github.com/waku-org/test-waku-query repo

[Ivansete-status]

I'm reluctant to perform the @Menduist enhancement. We might still have a bottleneck in while pqIsBusy(db) == 1: for concurrent Store queries (see TODO comment in

nwaku/waku/common/databases/db_postgres/dbconn.nim

Line 124 in 6da1aee

while db.pqisBusy() == 1:

)

[Ivansete-status]

Tests had been performed in the database itself with ~12 million rows. On the other hand, the bottleneck is within the db.pqisBusy() as mentioned above. See also https://www.notion.so/Postgres-e33d8e64fa204c4b9dcb1514baf9c582

[Ivansete-status]

• add an index column with the consistent, deterministic message hash as message ID.

Thanks for adding this point @jm-clius!
I'll check it in this issue because it is properly being tackled in #2112 by @ABresting
Cheers

[Ivansete-status]

We conclude the Postgres optimization for now.
Find more details in https://github.com/waku-org/nwaku/wiki/Adoption-of-Postgres-as-an-archive-system