Ruby On Rails High Volume API response Logging

Question

Scenario: I would call a third party api and get response , considering the response size to be 40000 lines of xml. I want to store these response in case of any third party api failures , i can reuse the last recieved/stored response.

I have a confusion on what's the best way to store this response

My analyzed options are

1. Store in the error responses table in our db.With the following db maintenance

• rake to clear record older than 30 days
• Using an text column, my only concern is if the string is too large will it be an issue ? I won't read the logs that frequently , only for the case of recovery which is very rare.

2. Upload the responses to an an s3 bucket (Involves cost )
3. Cloud watch logs (Involves cost)

What db column type should be considered ? ( Is text good , what kind of index if any?) Provided it is could be mysql or postgresql db.

From a cost perspective application db is best . I want some suggestions on this.

Answer 1

The scenario you've described is more accurately referred to as caching rather than logging. Of the options you've mentioned, I recommend storing the responses in the DB. Among the advantages of this approach over the others are:

• No significant additional cost.
• No additional external service dependencies.
• Less complex implementation.

Storing the response in a column of type text should be adequate; however, I recommend using the XML data type since it offers additional capabilities and could help protect against malformed responses. The PostgreSQL text and XML data types can store values up to ~1GB in size, so it is unlikely that the response size will be an issue.

There should be a unique index on the attributes that identify matching past responses. When a new response that matches a past response is received, the past response row is updated instead of inserting a new row. An efficient way of handling this in PostgreSQL is to use the INSERT command's ON CONFLICT clause. (If using PostgreSQL v15 or later, the MERGE command offers a more SQL standard approach than INSERT with ON CONFLICT.) While updating in this manner reduces the number of old rows, it doesn't eliminate them, so a mechanism to remove old (expired) rows will still be desirable.