Send blocked users to Arkose

This MR adds a daily job to send the blocked users to Arkose.

New Queries

SQL

SELECT
    "user_custom_attributes"."value"
FROM
    "user_custom_attributes"
WHERE
    "user_custom_attributes"."key" = 'arkose_session'
    AND "user_custom_attributes"."user_id" IN (11430729, 11430743, 11430749, 11430828, 11430938, 11431033, 11431174, 11431195, 11431217, 11431229, 11431258, 11431277, 11431301, 11431550, 11431582, 11431671, 11431748, 11431755, 11431894, 11431985, 11432039, 11432052, 11432178, 11432238, 11432429, 11432492, 11432544, 11432547, 11432579, 11432584, 11432673, 11432690, 11432696, 11432753, 11432757, 11432786, 11432808, 11432812, 11432880, 11432903, 11432916, 11433012, 11433016, 11433067, 11433140, 11433257, 11433288, 11433291, 11433299, 11433328, 11433397, 11433385, 11433468, 11433475, 11433500, 11433505, 11433514, 11433538, 11433562, 11433662, 11433687, 11433116, 11433882, 11433929, 11433945, 11433968, 11434152, 11434172, 11434207, 11434291, 11434385, 11434423, 11434432, 11434550, 11434597, 11434598, 11434727, 11434773, 11434776, 11434792, 11434836, 11434656, 11434909, 11435028, 11435059, 11435061, 10814099, 11435130, 11435176, 11435113, 11435242, 8691611, 11435259, 11435337, 10388569, 11179462, 11435306, 9660163, 8798496, 11435412, 11435422, 11435425, 8724565, 11435497, 11435552, 11435560, 8753895, 8617311, 8867759, 10830879, 9131278, 11435622, 11435658, 11435785, 11435842, 11435911, 11435913, 11436218, 11436399, 11436546, 11436718, 11436723, 11436880, 11436954, 11437096, 11437304, 11437318, 11437473, 11437523, 11437596, 11437613, 11437696, 11437775, 11437882, 11438077, 11438081, 11438134, 11438165, 11438352, 11438435, 11438559, 11438655, 11438722, 11438802, 11439016, 11439341, 11439396, 11439400, 11439432, 11439507, 11439847, 11439962, 11439981, 11440065, 11440222, 11440250)
 

https://explain.depesz.com/s/8MCC

Time

Time: 220.774 ms
  - planning: 0.928 ms
  - execution: 219.846 ms
    - I/O read: 140.281 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 382 (~3.00 MiB) from the buffer pool
  - reads: 180 (~1.40 MiB) from the OS file cache, including disk I/O
  - dirtied: 71 (~568.00 KiB)
  - writes: 0

SQL

SELECT
    "user_custom_attributes"."user_id"
FROM
    "user_custom_attributes"
WHERE
    "user_custom_attributes"."key" = 'blocked_at'
    AND "user_custom_attributes"."updated_at" BETWEEN '2022-04-25 00:00:00'
    AND '2022-04-25 23:59:59.999999'

Explain

https://explain.depesz.com/s/qxm9

The database-lab recommended a Specialized index.

Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query.

Time without index

Time: 15.268 s
  - planning: 0.779 ms
  - execution: 15.267 s
    - I/O read: 34.948 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 748263 (~5.70 GiB) from the buffer pool
  - reads: 59426 (~464.30 MiB) from the OS file cache, including disk I/O
  - dirtied: 1046 (~8.20 MiB)
  - writes: 0

This Merge Request adds a new index to address this issue as discussed here

Explain using the new index

https://explain.depesz.com/s/nuni

Time with index

Time: 1.387 ms
  - planning: 0.853 ms
  - execution: 0.534 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 156 (~1.20 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0