Resolve "ActiveRecord::QueryCanceled: PG::QueryCanceled"

What does this MR do and why?

Issue #357422 (closed) reports failing Security::Finding cleanup jobs in Sidekiq as a result of queries batched Security::Finding queries failing to respond in Postgres within a reasonable threshold.

While the underlying issue has since ceased to be reported by Sentry due to an index added to the database, the queries executed by this service can stand to be improved due to a strange issue pushing build_id subqueries into the relevant deletion and update queries when it is no longer necessary.

This MR modifies the Security::Findings::CleanupService to:

• remove the unnecessary propagation of the build_id subquery down to the security_finding deletion queries
• minimise subquerying
• ensure use of appropriate indices for the cleanup operation.
• Set batch deletion size is set to 100 to prevent Queries with very large query strings.

Database Review

Before Changes

SELECT
    "security_scans"."id"
FROM
    "security_scans"
WHERE
    "security_scans"."build_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 664, 681, 665, 666, 680, 684, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 696, 697, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 682, 683, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 69, 70)
ORDER BY
    "security_scans"."id" ASC
LIMIT 1000

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9696/commands/34380

SELECT
    "security_findings"."id"
FROM
    "security_findings"
WHERE
    "security_findings"."scan_id" IN (
        SELECT
            "security_scans"."id"
        FROM
            "security_scans"
        WHERE
            "security_scans"."build_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 664, 681, 665, 666, 680, 684, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 696, 697, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 682, 683, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 69, 70)
            AND "security_scans"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14))
ORDER BY
    "security_findings"."id" ASC
LIMIT 1

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9696/commands/34381

SELECT
    "security_findings"."id"
FROM
    "security_findings"
WHERE
    "security_findings"."scan_id" IN (
        SELECT
            "security_scans"."id"
        FROM
            "security_scans"
        WHERE
            "security_scans"."build_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 664, 681, 665, 666, 680, 684, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 696, 697, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 682, 683, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 69, 70)
            AND "security_scans"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14))
    AND "security_findings"."id" >= 1
ORDER BY
    "security_findings"."id" ASC
LIMIT 1 OFFSET 10000

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9696/commands/34382

DELETE FROM "security_findings"
WHERE "security_findings"."scan_id" IN (
        SELECT
            "security_scans"."id"
        FROM
            "security_scans"
        WHERE
            "security_scans"."build_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 664, 681, 665, 666, 680, 684, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 696, 697, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 682, 683, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 69, 70)
            AND "security_scans"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14))
    AND "security_findings"."id" >= 1

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9696/commands/34383

UPDATE
    "security_scans"
SET
    "status" = 6
WHERE
    "security_scans"."build_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 664, 681, 665, 666, 680, 684, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 696, 697, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 682, 683, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 69, 70)
    AND "security_scans"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9696/commands/34384

After Changes

SELECT
    "security_scans"."id"
FROM
    "security_scans"
WHERE
    "security_scans"."build_id" IN (2324655813, 2317206183, 2315027659, 2313845496, 2309892526, 2303689638, 2297860044, 2296299068, 2296299067, 2296299066, 2296299065, 2296299064, 2296299063, 2296298792, 2296298789, 2296298787, 2296298784, 2296298783, 2296298781, 2296298779, 2296298778, 2296298777, 2296298776, 2296298775, 2296298774, 2296298773, 2296298772, 2296298770, 2296298769, 2292506426, 2287399971, 2285191596, 2284309237, 2284309236, 2284309235, 2284309234, 2284309233, 2284309232, 2283960723, 2282803033, 2282803032, 2282803031, 2282803030, 2282803029, 2282803028, 2282803026, 2282803025, 2282803024, 2282803023, 2282803022, 2282803020, 2282803019, 2282803018, 2282803017, 2282803016, 2282801917, 2282801916, 2282801915, 2282801913, 2282801912, 2282801911, 2282801909, 2282801907, 2282801906, 2282801904, 2282801903, 2282801901, 2282801899, 2282801898, 2282801896, 2282801895, 2279756548, 2274560240, 2273237175, 2273237173, 2273237172, 2273237170, 2273237167, 2273237165, 2273237164, 2273237161, 2273237160, 2273237159, 2273237158, 2273237157, 2273237156, 2273237155, 2273237154, 2273237153, 2273207983, 2273207979, 2273207976, 2273207973, 2273207970, 2273207968, 2273207965, 2273207963, 2273207958, 2273207955, 2273207952)
ORDER BY
    "security_scans"."id" ASC
LIMIT 1000

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9696/commands/34407

DELETE FROM "security_findings"
WHERE "security_findings"."id" IN (
        SELECT
            "security_findings"."id"
        FROM
            "security_findings"
        WHERE
            "security_findings"."scan_id" IN (19195974, 19195975, 19195976, 19195977, 19195978, 19195979, 19195980, 19195985, 19195986, 19195987, 19195988, 19195989, 19195990, 19195994, 19195997, 19195998, 19195999, 19196000, 19196001, 19196002, 19196003, 19196004, 19196005, 19196006, 19196007, 19196008, 19196009, 19196010, 19196011, 19196012, 19196013, 19196014, 19196015, 19196020, 19196021, 19196022, 19196023, 19196024, 19196025, 19196026, 19196027, 19196028, 19196029, 19196030, 19196031, 19196032, 19196033, 19196034, 19196035, 19196036, 19196037, 19196038, 19196039, 19196040, 19196042, 19196044, 19196045, 19196048, 19196049, 19196050, 19196051, 19196052, 19196053, 19196054, 19196055, 19196056, 19196057, 19196058, 19196059, 19196060, 19196061, 19196062, 19196063, 19196064, 19196065, 19196066, 19196067, 19196068, 19196069, 19196070, 19196071, 19196072, 19196073, 19196074, 19196075, 19196076, 19196077, 19196078, 19196079, 19196080, 19196081, 19196082, 19196083, 19196084, 19196085, 19196086, 19196087, 19196088, 19196089, 19196090)
        LIMIT 100)

This query will be executed as many times as it takes for it to cease deleting records.

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9696/commands/34394

UPDATE
    "security_scans"
SET
    "status" = 6
WHERE
    "security_scans"."id" IN (19195974, 19195975, 19195976, 19195977, 19195978, 19195979, 19195980, 19195985, 19195986, 19195987, 19195988, 19195989, 19195990, 19195994, 19195997, 19195998, 19195999, 19196000, 19196001, 19196002, 19196003, 19196004, 19196005, 19196006, 19196007, 19196008, 19196009, 19196010, 19196011, 19196012, 19196013, 19196014, 19196015, 19196020, 19196021, 19196022, 19196023, 19196024, 19196025, 19196026, 19196027, 19196028, 19196029, 19196030, 19196031, 19196032, 19196033, 19196034, 19196035, 19196036, 19196037, 19196038, 19196039, 19196040, 19196042, 19196044, 19196045, 19196048, 19196049, 19196050, 19196051, 19196052, 19196053, 19196054, 19196055, 19196056, 19196057, 19196058, 19196059, 19196060, 19196061, 19196062, 19196063, 19196064, 19196065, 19196066, 19196067, 19196068, 19196069, 19196070, 19196071, 19196072, 19196073, 19196074, 19196075, 19196076, 19196077, 19196078, 19196079, 19196080, 19196081, 19196082, 19196083, 19196084, 19196085, 19196086, 19196087, 19196088, 19196089, 19196090)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9696/commands/34405

MR acceptance checklist

This checklist encourages us to confirm any changes have been analysed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.