Insert in Parallel

[kderme]

Currently all db insertions are done by a single thread. We could try and use more. We have to be careful about shared resources

• Different threads should use different connections, as conenctions is not thread safe.
• The monad stack needs to change, as ReaderT SqlBackend .. means only a single connection is available.
• Two connections mean two differnt postgres transactions. We should make sure that if any of the transaction is rollbacked so is the other. We want to avoid cases where a block is partially inserted.

Parallelism provides more benefits when there is enough workload for each thread. So this can have more benefits after https://github.com/orgs/input-output-hk/projects/52/views/7

[erikd]

I tried this. It does not work, Postgres only allows inserts into the database from a single thread.

I tried this specifically for reward and epoch stake tables. Separate connections were used.

[kderme]

Do you remember the error? It should be possible to do parallel insertions, they just have to be from different connections.

[erikd]

It absolutely 100% was separate connections. The error message from Postgres was that parallel insertions were not allowed. This may have been due to the large number of foreign keys we were using at the time.