Improve database accesses

[hanoii]

I am setting up a VPS where multiple instances of strapi will be run. These instances connects to a DigitalOcean postgre managed database.

I started about the the lowest tier for the managed database (1GB of RAM) which gives me small amount of concurrent connections (22) something I just learnt. While this still in the PoC phase and the servers will be bigger I found it odd that with a handful of no traffic strapi instances I was quickly getting errors out of postgre when running config-sync import.

I wondered by and while looking at database activity I saw that one run of config-sync import was creating several databases connections at the same time.

I'd assume this is not needed and whatever this is doing it can be done using 1 (or as fewer as possible) connections or at least to have the option to limit it if this is because things are done in parallel.

Any advice/comment is appreciated.

[boazpoolman]

Hi @hanoii,

Thanks for raising this issue.

As of now, preforming the import action will do the following:
For each config type:

• Query all the entries with findMany in sets of 100
• Query all the corresponding JSON files (No DB requests)
• Calculate the difference between the database entries and the JSON files. (No DB requests)
• For each entry that has been changed run the import.

And in that last step is probably where most DB requests happen. As for each single config that is imported we do the following:

• Query the entry from the DB.
• Compare to the corresponding file to determine wheter it should be created, updated or deleted.
• Once determined, we will run the create, update or delete query for the entry.
• Also we will query all the relations of the entry.
• Compare then to the corresponding file to determine wheter it should be created, updated or deleted.
• And once determined we will loop over them and run a single update, create or delete query for each individual relation.

It goes without saying that there is much room for improvement here. Some things more obvious then others.
As I'm currently not in a position where I have the time to contribute these performance updates I won't be listing all the possible improvements.

If you, or anyone else looking at this, is looking to contribute to these improvements please reach out and we can discuss the best way to go about this. Where we'll probably start by writing tests to make sure the changes we make don't break any functionality.