How to Efficiently Expire Millions of Coupons with Temp Tables & Scheduled Jobs

Massive coupon expiration is a common scenario for platforms that distribute large numbers of coupons daily, such as food‑delivery services.

Each coupon has an expiration time (e.g., midnight of the same day). For big platforms, the number of coupons expiring each day can reach tens of millions, making a direct scan of the main coupon table impractical. The article proposes a solution that combines a lightweight temporary table with a scheduled task.

Solution Overview

The core idea is to insert coupons that are about to expire into a temporary table and let a periodic scheduled job scan this table to process expiration logic, thereby avoiding heavy scans on the main coupon table.

Table Design

The main coupon table stores the complete coupon data. An additional expiration‑task table holds the minimal information required for the scheduled job to identify expiring coupons. The design of the expiration‑task table is shown below:

Because the expiration‑task table can also become large, a composite index on expire_time and status is created to accelerate queries.

Coupon Claim Flow

When a user claims a coupon, the system writes the full coupon record into the main table and simultaneously inserts a corresponding record into the expiration‑task table. The claim process is illustrated below:

Coupon Usage and Expiration Handling

When a user redeems a coupon, the main table status is updated to “used” and the related record in the expiration‑task table is either deleted or marked as “processed”. If a coupon is claimed but never used, the scheduled job processes it after expiration.

The scheduled job runs at a fixed interval (e.g., every minute), queries the expiration‑task table using expire_time and status as conditions, and processes a limited batch size (e.g., 2000 rows) to avoid overload.

After fetching the expiring coupons, the job bulk‑updates the main table status to “expired” and marks the corresponding task records as “processed”. Processed task records can be physically deleted later by another cleanup job that removes entries whose status is “processed” and whose creation time exceeds a certain threshold, keeping the task table lightweight.

Key Benefits

The temporary‑table + scheduled‑task approach keeps performance under control because the job scans only the much smaller task table, which has efficient indexes, reducing database pressure.

The design offers high reliability and idempotency; if a batch fails, the task status remains unchanged, so the next run will retry, ensuring eventual consistency.

By decoupling state‑change logic from core business logic, the architecture simplifies maintenance and future feature extensions.