OHDSI Home | Forums | Wiki | Github

Optimizing postgreSQL database

I have never dealt with data with this size (mainly form a lesser web dev experience), when joinning tables, the query seems to run very long

Does not seem there is a lot of dead tuples either (vacuum is on the way on the only 2 tables that have dead tuples)

We have done optimizations suggested on CommonDataModel/PostgreSQL at master · OHDSI/CommonDataModel · GitHub

Is there any further optimization we can do? Or if there is any test to see why our SQL database is slow? Thanks

New users are only allowed to have one embedded picture per post. So there is part of the dead tuple table if one is interested.

Queries sometime can last for a few days, and indexing queries from the above github can take a few days too. I am already out of idea if it is normal.


CLUSTER observation USING idx_observation_person_id ;

takes a few days.