0

Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be added in DB or updated in old data if row already existed matching on certain conditions.

To keep it short and to the point, query structure seems something like:

WITH values_cte(a, b, c, d, e, f, g, h, i) AS (
  VALUES  (2, 4, 4, '{17, 8, 99}', '2017-05-35 05:04:10', TRUE, 15, 1, 1, 1)
          (3, 4, 4, '{27, 1, 101, 11, 12}', '2017-06-00 10:04:10', TRUE, 10, NULL, NULL, NULL),
          (2, 4, 4, '{17, 3, 11, 1, 2, 5}', '2017-03-10 11:04:10', TRUE, 15, NULL, NULL, NULL),
          (2, 4, 4, '{2, 5, 8}', '2016-05-20 14:04:10', TRUE, 1, NULL, NULL, NULL),
          (3, 4, 4, '{45, 18, 6}', '2017-12-12 12:04:10', TRUE, 2, NULL, NULL, NULL),
          (2, 4, 4, '{39, 81, 9}', '2017-05-30 15:04:10', TRUE, 13, NULL, NULL, NULL),
          ...
)
SELECT f_key,
       a::INT AS a,
       b::INT AS b,
       c::varchar[] AS c,
       to_timestamp(d, 'YYYY-MM-DD hh24:mi:ss') AS d,
       e,
       f::INT AS f,
       g::INT AS g,
       h::INT AS h,
       i::INT AS i
INTO TEMP TABLE temp_dynamic_uuid
FROM values_cte;

UPDATE table_a_s
 SET g = temp_dynamic_uuid.g,
     h = temp_dynamic_uuid.h,
     i = temp_dynamic_uuid.i
 FROM temp_dynamic_uuid
 WHERE table_a_s.f_key = temp_dynamic_uuid.f_key
 AND table_a_s.a IS NOT DISTINCT FROM temp_dynamic_uuid.a
 AND table_a_s.b IS NOT DISTINCT FROM temp_dynamic_uuid.b
 AND table_a_s.d = temp_dynamic_uuid.d
 AND table_a_s.e IS NOT DISTINCT FROM temp_dynamic_uuid.e
 AND table_a_s.f IS NOT DISTINCT FROM temp_dynamic_uuid.f;

INSERT INTO table_a_s (f_key, a, b, d, e, f, g, h, i, created_at, updated_at)
SELECT temp_dynamic_uuid.f_key,
       temp_dynamic_uuid.a,
       temp_dynamic_uuid.b,
       temp_dynamic_uuid.d,
       temp_dynamic_uuid.e,
       temp_dynamic_uuid.f,
       temp_dynamic_uuid.g,
       temp_dynamic_uuid.h,
       temp_dynamic_uuid.i,
       now(),
       now()
FROM temp_dynamic_uuid
LEFT JOIN table_a_s a_s ON a_s.f_key  = temp_dynamic_uuid.f_key
                                AND a_s.a IS NOT DISTINCT FROM temp_dynamic_uuid.a
                                AND a_s.b IS NOT DISTINCT FROM temp_dynamic_uuid.b
                                AND a_s.d = temp_dynamic_uuid.d
                                AND a_s.e IS NOT DISTINCT FROM temp_dynamic_uuid.e
                                AND a_s.f IS NOT DISTINCT FROM temp_dynamic_uuid.f
WHERE a_s.id IS NULL;

INSERT INTO table_e_a_t (a1, a2)
SELECT table_a_t.id AS a1,
       a_s.id AS a2
FROM appointment_types AS table_a_t
JOIN temp_dynamic_uuid ON table_a_t.external_id = ANY(temp_dynamic_uuid.c)
                    AND table_a_t.f_key  = temp_dynamic_uuid.f_key
JOIN table_a_s a_s ON a_s.f_key  IS NOT DISTINCT FROM temp_dynamic_uuid.f_key
                           AND a_s.a IS NOT DISTINCT FROM temp_dynamic_uuid.a
                           AND a_s.b IS NOT DISTINCT FROM temp_dynamic_uuid.b
                           AND a_s.d = temp_dynamic_uuid.d
                           AND a_s.e IS NOT DISTINCT FROM temp_dynamic_uuid.e
                           AND a_s.f IS NOT DISTINCT FROM temp_dynamic_uuid.f
LEFT JOIN table_e_a_t e_a_t ON e_a_t.a1 = table_a_t.id
                                          AND e_a_t.a2 = a_s.id
WHERE e_a_t.id IS NULL;
DROP TABLE temp_dynamic_uuid;

Explanation:

I know this is quiet long query, but gist is we are creating as STE with values and then doing some type castings and inserting in a TEMP TABLE named temp_dynamic_uuid here dynamic_uuid is a random number/uuid kind of thing i had an edge case in mind with respect to concurrency its for that. I had to do type casting because sometimes i have all NULL in some columns, in which case auto type assigned is text also some issues in defining arrays etc.

After we have temp_dynamic_uuid, we do multiple DML queries using this temp table.

Problem:

Our DB instance has 8GB of RAM, this above query is generated with batch size of 25k values/rows. And we have ~25 threads in our workers which do this query simultaneously. Jobs keep running for some time smoothly, after like 2-3 hours of processing OOM kill is triggered and we start seeing errors like: FATAL: the database system is in recovery mode and server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

It seems like these queries are causing some memory to not get freed, accumulating this behavior by running lot of these queries for some time causes out of memory kill.

PS: We are on a managed hosting provider, they are running DB through amazon RDS. We don't have direct control over RDS but we can request any tweaks as needed.

UPDATE1: It might be our DB connections were being reused so these temp tables didn't got destroyed for long time. I am trying forcing a connection closure, will post updates. See this answer.

  • instead of an UPDATE and INSERT, you could merge both statements into an insert into table_a_s select ... from temp_dynamic_uuid on conflict update set ... statement. – a_horse_with_no_name Jan 25 at 11:55
  • @a_horse_with_no_name Yes i agree, there was a reason not using this earlier. But now we can. But, issue is if this will fix our OOM kills? I don't think so, i think issue is not from these statements but rather from the cte and temp table usage somehow. But i am not sure what exactly is causing it and how would i go about fixing it. – Zia Ul Rehman Mughal Jan 25 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.