chore(jobsdb): latest job status query optimization

[atzoum]

Description

When jobsdb queries the database for jobs, it needs to get the latest job status for each job. The current way of achieving this is through a SELECT MAX(id) GROUP BY job_id query in the job status table.

However, a specialized index (job_id asc, id desc) along with using SELECT DISTINCT ON (job_id) yields significant performance improvements over the current strategy (25% - 100% faster), see some example query plans: old vs new. See also the relevant thread.

To simplify queries even further, a new database view is introduced for every job status table (e.g. table rt_job_status_1 has view v_last_rt_job_status_1) abstracting away the actual query that provides this information.

Note: This pull request shall be reviewed along with its counterpart in rudder-migrator: #32

Bonus!

Added an easy-to-use function unionjobsdb(prefix text, num int) for quickly querying multiple jobsdb datasets for jobs along with their latest status. You no longer have to keep track of constantly changing jobsdb indexes...

E.g. to query the first 5 rt tables for Amplitude jobs, all you need to do now is:

SELECT * FROM unionjobsdb('rt',5) WHERE custom_val = 'AM'

Notion Ticket

Link

Security

• The code changed/added as part of this pull request won't create any security issues with how the software is being used.

[codecov]

Codecov Report

Base: 45.43% // Head: 45.39% // Decreases project coverage by -0.03% ⚠️

Coverage data is based on head (329e635) compared to base (bca84b7).
Patch coverage: 63.26% of modified lines in pull request are covered.

Additional details and impacted files

@@            Coverage Diff             @@
##           master    #2693      +/-   ##
==========================================
- Coverage   45.43%   45.39%   -0.04%     
==========================================
  Files         290      290              
  Lines       48101    48066      -35     
==========================================
- Hits        21853    21821      -32     
+ Misses      24861    24857       -4     
- Partials     1387     1388       +1     

Impacted Files	Coverage Δ
jobsdb/readonly_jobsdb.go	34.79% <40.00%> (+0.31%)	⬆️
jobsdb/jobsdb.go	72.97% <68.42%> (-0.36%)	⬇️
jobsdb/unionQuery.go	84.13% <100.00%> (-0.62%)	⬇️
jobsdb/unionQueryLegacy.go	70.49% <0.00%> (-4.92%)	⬇️
config/backend-config/namespace_config.go	67.00% <0.00%> (-3.00%)	⬇️
processor/processor.go	85.99% <0.00%> (ø)
warehouse/schema.go	51.42% <0.00%> (+1.14%)	⬆️
services/db/recovery.go	53.01% <0.00%> (+2.40%)	⬆️

Help us with your feedback. Take ten seconds to tell us how you rate us. Have a feature suggestion? Share it here.

☔ View full report at Codecov.
📢 Do you have feedback about the report comment? Let us know in this issue.

[atzoum]

Note: all gateways will need to be upgraded to the latest version so that the necessary schema migration will be performed on gw datasets.