#10373 Improve performance on system database Issue created by stevenlw-porpoise My company is using Meltano with a PostgreSQL system database. We are observing high resource usage for our Postgres instance on 2 separate query patterns: 1. Run start up queries There were 2 slow queries here:

---- stale-run check
SELECT
    runs.id AS runs_id,
    runs.job_name AS runs_job_name,
    runs.run_id AS runs_run_id,
    runs.state AS runs_state,
    runs.started_at AS runs_started_at,
    runs.last_heartbeat_at AS runs_last_heartbeat_at,
    runs.ended_at AS runs_ended_at,
    runs.payload AS runs_payload,
    runs.payload_flags AS runs_payload_flags,
    runs.trigger AS runs_trigger
FROM runs
WHERE runs.state = $1
  AND (
      (
          runs.last_heartbeat_at IS NOT NULL
          AND runs.last_heartbeat_at < $2::timestamp
      )
      OR (
          runs.last_heartbeat_at IS NULL
          AND runs.started_at < $3::timestamp
      )
  )
  AND runs.job_name = $4;

SELECT
    runs.id AS runs_id,
    runs.job_name AS runs_job_name,
    runs.run_id AS runs_run_id,
    runs.state AS runs_state,
    runs.started_at AS runs_started_at,
    runs.last_heartbeat_at AS runs_last_heartbeat_at,
    runs.ended_at AS runs_ended_at,
    runs.payload AS runs_payload,
    runs.payload_flags AS runs_payload_flags,
    runs.trigger AS runs_trigger
FROM runs
WHERE runs.job_name = $1
  AND runs.state = $2
ORDER BY runs.started_at DESC
LIMIT $3;

1. Heartbeat updates.

--- Update heartbeat
UPDATE runs
SET last_heartbeat_at = $1::timestamp
WHERE runs.id = $2;

--- additional query run before the heartbeat
SELECT
    runs.id AS runs_id,
    runs.job_name AS runs_job_name,
    runs.run_id AS runs_run_id,
    runs.state AS runs_state,
    runs.started_at AS runs_started_at,
    runs.last_heartbeat_at AS runs_last_heartbeat_at,
    runs.ended_at AS runs_ended_at,
    runs.payload AS runs_payload,
    runs.payload_flags AS runs_payload_flags,
    runs.trigger AS runs_trigger
FROM runs
WHERE runs.id = $1;

I've asked codex and it's suggested some optimizations. 1. The heartbeat pattern seems to use a SELECT → UPDATE → COMMIT pattern per each heartbeat but I believe the select query is redundant. 2. Add a heartbeat frequency configuration. Currently this is hard coded to 1 second. If I understand correctly, the main function of the heartbeat timestamp is to avoid simultaneous runs with the same state key, unless a run is stale because it has no heartbeat from the last 5 minutes. I think we can lower the heartbeat frequency to 30 seconds while safely keeping this functionality. 3. Add an index to the runs table on (job_name, state) to make the start up queries more efficient. I would like to work on changes that improve the performance. I'm happy to test these internally first, but wanted to check with the Meltano maintainers if they have any advice or experience in this area and if they are open to code contributions for this. meltano/meltano