Database Design for Caching

The Rate-Limit Problem

An API is a service shared by many users. A provider cannot allow every application to make unlimited requests, so it restricts how many requests one application can make during a time window. This restriction is called a rate limit. Rate limits protect server resources and help provide fair access across users.

The problem becomes visible when a dataset contains thousands of locations that must be sent to a geocoding API. The application may eventually reach the API's request ceiling. At that point, requests fail and the process stalls. A caching design does not remove the limit; it reduces the number of requests that the application needs to make.

Two Phases, One Dataset

The central strategy is to divide the work into two phases. Phase 1 collects data from the API. Phase 2 analyzes the data that has already been collected. Keeping these activities separate means that analysis can continue locally even when the API is temporarily unavailable or its request quota has been reached.

Cache Lookup Before Request

A local cache changes the decision made for each item. Before requesting data, the application checks whether that item's data already exists locally. If it exists, the application uses the cached data and avoids another API call. If it does not exist, the application makes the API request and stores the result locally. This lookup is the key database behavior in the caching design.

When the Quota Stops Work

Reaching the rate limit does not mean that all collected progress has disappeared. It means phase 1 must pause because further requests fail. The application can wait for the quota to reset and then resume phase 1. Previously cached data remains useful, so subsequent runs need requests only for data that is not already cached.

Resetting Stale Data

A cache is useful when its stored data is the data you want to keep using. You may instead need to start over after discovering an error in the input data or deciding to geocode a subset of locations again with updated parameters.

For this workflow, reset the cached dataset by deleting the geodata.sqlite file. When phase 1 runs again, it finds no cached data and makes fresh API calls for every location. Resetting therefore restores the complete-miss condition: no previous results are available to prevent requests.

Common Caching Mistakes

• Treating the rate limit as a reason to abandon the entire process.

  The two-phase workflow is designed to pause phase 1, wait for the quota to reset, and resume without losing cached progress.

  Fix: Keep the local cache, pause phase 1, and resume after the quota resets.

• Making an API request every time an item is processed.

  This creates duplicate API calls and consumes requests that the cache could have avoided.

  Fix: Check whether the data exists locally before making the request.

• Deleting the cache when only a temporary rate-limit pause is needed.

  Deleting the file removes the cached dataset and causes the next phase 1 run to make fresh requests for every location.

  Fix: Delete the cache only when the stored dataset should be replaced, such as after an input error or a change requiring re-geocoding.

• Assuming that a reset will reduce API usage.

  After the reset, no cached data exists, so phase 1 makes fresh API calls for every location.

  Fix: Reset deliberately, understanding that it creates a complete cache miss on the next run.

Check Your Reasoning

Design Summary

1. Rate limits protect server resources and help ensure fair access among API users.
2. A two-phase workflow separates API data collection from local analysis, so collection can pause and resume.
3. A local cache prevents duplicate API calls by checking for existing data before requesting new data.
4. When the rate limit is reached, pause phase 1, wait for the quota to reset, and resume with the cache intact.
5. Delete geodata.sqlite only when the cached dataset should be replaced; after deletion, the next phase 1 run makes fresh requests for every location.