Postgres DB mysteriously has dropped important scheduler indexes

[kennethpgreen]

I use airflow and it's related postgres DB through k9s and I've been seeing a very high CPU utilization % for one of our postgres instances. For the instance with high CPU utilization %, I could identify several airflow queries that look related to normal scheduler tasks that have a higher than expected latency.

I use

• airflow 2.2.5
• postgres 14.5

For example:

SELECT job.id, job.dag_id, job.state, job.job_type, job.start_date, job.end_date, job.latest_heartbeat, job.executor_class, job.hostname, job.unixname 
FROM job 
WHERE job.id = ? AND job.job_type IN ( ? )

has a P50 latency of 139 ms in the problem pod and 2 ms in our regular pod.

Upon further investigation, it seems like this is related to dropped indexes in the problematic postgres.
When looking at

SELECT schemaname, indexname, tablename
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY indexname;

The problematic postgres DB is missing indexes on tables like job, dag and just has indexes on ab_permission, ap_permission_view, ab_user, ab_role and a couple of other ones.

Couple of questions:

• What is the best way to recover the default airflow indexes in the problematic postgres DB?
• Any idea how a postgres DB could end up in a funky state like this?

[kennethpgreen]

I suspect the indexes being dropped is totally unrelated to any airflow interaction...so main question is really is there a recommended approach for restoring the indexes that should be there?

[potiuk]

No there is not one. But I think the best approach you can take is to create an empty database for the same version and run some tools that compare two schemas and produce DDL that will synchronise them. There are likely a dosen of those available - likely some free but also some commercial ones.

[potiuk]

I cannot endorse any of those but google search I run produced immediately some apparently good results https://www.google.com/search?q=synchronize+Db+schemas&oq=synchronize+Db+schemas&aqs=chrome..69i57j0i22i30.7145j0j7&sourceid=chrome&ie=UTF-8