Insertion speed slowing down a lot as Postgres db gets bigger

Dear Conquest dev & users,

I recently noticed that the data insertion speed (i.e. importing new DICOM images from C-STORE requests) slow down quite dramatically as my database size grows.

With an empty database the insertion speed is 10+ images per second.

With ~ 200GB of data the insertion speed is only 2-3 image per second.

I suspect this is because of database performance.

If I do "Re-initialize database" which seems to clean the metadata database, insertion speed will get back to 10+ images per second.

However as the re-initialization goes and the database entries get filled up again, the insertion speed decreases to 2-3 images per second again.

I'm using Conquest 1.5.0b on Windows Server 2019 + Postgres 9.6 running on the same machine.

I'm using the Native Postgres driver to connect my Postgres to Conquest.

Has anyone encountered similar issues?

Is there any way to fix this? e.g. database optimization, creating indices for certain database rows, etc.

If not, what would be the better database options for large scale production use (10-20 users, ~10TB of data in .v2 format)? MySQL?

Thank you very much for your assistance!

Andy

Thanks Marcel for the quick response. I'll test this out and report back!

To clarify - you meant a joint index on studyinsta and patientid for the dicomstudies table right?

Reporting back my finding:

I ended up creating indices for the following

dicomstudies.patientid

dicomstudies.patientid, dicomstudies.studyinsta

dicomseries.studyinsta, dicomseries.seriesinst

dicomimages.seriesinst, dicomimages.sopinstc

Right after I created the joint index for (dicomimages.seriesinst, dicomimages.sopinstc), the insertion speed recovered to normal (10+ per second).

I still need to observe it for a while, but it seems like the efficiency increase in joint indexing between SeriesInstanceUID and SOPInstanceUID was the key factor in my case, probably simply because I have way too many series and images in my database.

In case this is helpful to others with similar issues. Cheers.

And thanks Marcel for the hint!

That's quite interesting!

I have similar observations with sqlite in Conquest as well. No joint indices but its speed didn't really degrade at all as database grows bigger.

However I have seen sqlite's performance degrading in other software applications which can be recovered by the creation of joint indices - I guess it depends on each individual use case.

In case of help, the statements I used were:

CREATE INDEX idx_patientid ON dicomstudies(patientid);

CREATE INDEX idx_patientid_studyinsta ON dicomstudies(patientid, studyinsta);

CREATE INDEX idx_studyinsta_seriesinst ON dicomseries(studyinsta,seriesinst);

CREATE INDEX idx_seriesinst_sopinstanc ON dicomimages(seriesinst, sopinstanc);