[SQL] Need advice for Postgres DB Optimization or evaluating other DBs

I currently have a 32 core Postgres server with ~240GB RAM and 500GB network mount. We have a simple postgres table with the following schema:

class_name, method_name, test_name

This table essentially describes a mapping between a method of a class to a particular test. We use this table as part of our CI/CD pipelines, where for a given set of changed methods of corresponding classes, we identify all the tests to be executed as part of the pipeline. All columns in this table are strings. The table has more than 900 million rows.

In this main table, we have a primary key on (class_name, test_name, method_name). We also have two indexes on class_name and method_name respectively. The way we identify the tests to execute is we first create a temporary/buffer table for all the modified classes. And from this temporary/buffer table, we query for tests against the class_name and method_name.

Sometimes, there are more than 20,000 changed classes and methods. Querying for tests against this large changeset is very slow and impacting our pipeline turnaround time. We want to find ways to speed up querying of tests against such large changesets and having them as part of the where clause significantly affects the query performance. I want to optimize the DB to give results within 10-15 seconds for a changeset that could contain more than 35,000 methods and classes.

As part of optimizing our jobs, we want to evaluate other techniques or databases. I have already tried all the suggested postgres tuning values in the server. Could you suggest any other optimization steps/design in this use-case? Or should we try to evaluate other NoSQL DBs like MongoDB/Cassandra?

Any input/advice is highly appreciated. Thanks!

Continue reading...