Optimizing retry intervals for fetching transaction status from a payment gateway

Question

I have a system where transactions are initially marked as "Pending" in a relational database, if a payment transaction is not immediately successful by payment gateway, I need to fetch the latest status from the payment gateway. Currently, I have a cron that runs ever 15 min that scans the table for pending transactions and fetches latest status from payment gateway, and I want to optimize how we poll the gateway for these statuses.

Instead of polling the same transaction every 15 min, I want to implement a retry strategy where:

• If the transaction is still pending, it should retry after increasing intervals (e.g., first after X minutes, then 5X minutes, then 15X minutes, etc.).
• This will help avoid scanning the entire "Pending" transactions table and minimize redundant checks.

What is the best way to implement this kind of retry strategy in an efficient and scalable manner? Specifically, how can I structure the polling mechanism so that it doesn't repeatedly check every pending transaction too frequently while still ensuring that status updates are fetched in a timely manner?

Answer 1

A common approach is to run the job more frequently, such as every minute, and add two fields to the pending records: not_before (a timestamp) and number_of_attempts (an integer).

The job selects all records where not_before is less than or equal to the current timestamp, retries them, and if necessary, calculates a new not_before based on the available data.

This approach requires minimal changes and no additional infrastructure. Alternatively, you could deploy a streaming or messaging service that supports delayed messages, similar to https://docs.aws.amazon.com/AWSSimpleQueueService/latest/SQSDeveloperGuide/sqs-message-timers.html