如何搭建数据库与缓存架构以处理第三方API的海量销售数据？

Hey there, let's break down how to tackle this scaling challenge step by step—you’ve got a solid tech stack already, so we can lean into each tool’s strengths to handle 100k+ historical records and daily updates smoothly.

Right now, you’re hitting the third-party API on every app load, which is unsustainable for large datasets. Let’s fix that by persisting the sales data in your existing MongoDB instance:

• Design a dedicated collection (e.g., business_sales) with fields like companyId (to link to your user data), externalSaleId (the third-party’s unique ID for deduplication), saleDate, amount, productDetails, and any other fields you need. Add indexes on companyId and saleDate—these will make queries for a specific business’s historical or recent data lightning fast.
• Decouple from the third-party schema: Map the API’s response fields to your own structure instead of storing raw API data. This way, if the third-party changes their API format later, you only have to update the mapping layer, not every place you use the data.

Trying to pull 100k records in one go will trigger API rate limits and leave users waiting forever. Instead, build an async batch sync workflow:

• Add a backend endpoint to kick off the initial sync when a user first connects their business. Use a job queue like BullMQ (it integrates seamlessly with your Redis instance, which you already use for sessions) to split the sync into chunks—say 1,000 records per batch.
• Track sync progress: Store the current sync state (e.g., last fetched record ID, percentage complete) in MongoDB or Redis. Let your frontend poll this state or use WebSockets to show users a progress bar (e.g., "Syncing historical data: 45% complete") so they know what’s happening.
• Retry on failures: Configure the queue to retry failed batches with exponential backoff (1s, 2s, 4s, etc.) to handle temporary API outages. Log any persistent failures to a sync_errors collection so you can debug or manually retrigger them later.

For daily incremental data, use a scheduled task to pull and update your database without user intervention:

• Use Heroku Scheduler: It’s built for this exact use case—set it to run a Node.js script once per day (pick a low-traffic time like 2 AM UTC). The script should:
  1. Fetch new sales data from the third-party API (use a date filter to only get records from the past 24 hours; if the API doesn’t support filtering, pull all recent records and deduplicate using externalSaleId).
  2. Upsert the records into MongoDB: use updateOne({ externalSaleId: ... }, { $set: ... }, { upsert: true }) to avoid duplicates and update any records that might have changed in the third-party system.
• Cache invalidation: After the daily sync finishes, clear any Redis cache entries related to the updated business’s sales data to ensure users get fresh results.

Combine server-side and client-side caching to balance speed and freshness:

• Redis (Server-Side Caching):
  • Cache aggregated data (e.g., company:123:monthly_sales_total) with a short TTL (like 1 hour) since this data doesn’t change minute-to-minute.
  • Cache paginated results (e.g., company:123:sales:page:3) to avoid hitting MongoDB for every page load. Invalidate these caches when the daily sync runs or when a user triggers a manual refresh.
• Apollo Client (Client-Side Caching):
  • Don’t try to cache all 100k records locally—instead, cache only the data the user is actively viewing (e.g., the current page of sales records).
  • Use Apollo’s refetchQueries or update methods to refresh the client cache when new data is synced, or set up a GraphQL Subscription to push real-time updates if you need instant visibility into new sales.

100k records are too much to load at once—use these tricks to keep the frontend responsive:

• Pagination or infinite scroll: Implement offset-based or Relay-style pagination in your GraphQL API, and use Apollo’s useInfiniteQuery to load data as the user scrolls.
• Prioritize recent data: When the app loads, first fetch the last 30 days of sales data (most users care about recent activity first). Let users manually trigger a load of older historical data if they need it.
• Precompute aggregates: In MongoDB, use aggregation pipelines to precompute daily/weekly/monthly sales totals and store them in a sales_aggregates collection. This lets the frontend load summary data instantly instead of calculating it on the fly.

• Dyno sleep: Heroku free/hobby dynos sleep after 30 minutes of inactivity. For your sync jobs, use Heroku Scheduler (it wakes the dyno to run tasks) or upgrade to a professional dyno if you need 24/7 uptime for queues.
• Resource limits: Make sure your MongoDB Atlas (or Heroku MongoDB add-on) plan has enough storage and read/write capacity for 100k+ records. Similarly, pick a Redis add-on (like Redis Cloud) with enough memory for your cache entries.
• Logging: Enable Heroku’s log drains or use a tool like Papertrail to monitor sync jobs—this helps you catch API rate limits, connection issues, or failed batches early.

No matter how careful you are, sync jobs might run twice. To avoid duplicate records:

• Use the third-party’s unique externalSaleId as the _id in your MongoDB collection, or add a unique index on externalSaleId.
• Always use upsert operations when inserting data—this way, if a record already exists, it gets updated instead of duplicated.

内容的提问来源于stack exchange，提问作者localproof