What is the best way to store +3 millions records in Firestore?

Question

I want to store +3 millions records in my Firestore database and I would like to know what is the best way, practice, to do that.

In fact, I want to store every prices of 30 cryptos every 15 minutes since 01/01/2020.

For example:

• ETH price at 01/01/2020 at 00h00 = xxx
• ETH price at 01/01/2020 at 00h15 = xxx
• ETH price at 01/01/2020 at 00h30 = xxx
• ...
• ETH price at 09/04/2022 at 14h15 = xxx

and this, for 30 cryptos (or more).

So, 120 prices per day multiplied by 829 days multiplied by 30 cryptos ~= 3M records

I thought of saving this like this:

[Collection of Crypto] [Document of crypto] [Collection of dates] [Document of hour] [Price]

I don't know if this is the right way, that's why I come here :)

Of course, the goal of this database will be to retrieve ALL the historical prices of a currency that I would have selected. This will allow me to make statistics etc later.

Thanks for your help

Answer 1

For the current structure, instead of creating a document every 15 minutes you can just create a "prices" document and store an array of format { time: "00:00", price: 100 } which will cost only 1 read to fetch prices of a given currency on a day instead of 96.

Alternatively, you can create a single collection "prices" and create a document everyday for each currency. A document in this collection can look like this:

{
  name: "BTC",
  date: "2022/04/09", // or Firestore timestamp
  prices: [
    { time: "00:05", price: 12.345 },
    { time: "00:10", price: 6.345 },
    { time: "00:15", price: 68.586 },
  ]
}

With this structure as well you can query rates a particular coin in a given date range. An example for this query:

const qSnap = await getDocs(
  query(
    collection(db, "prices"),
    where("name", "==", "BTC"),
    where("time", ">=", startDateTimestamp),
    where("time", "<", endDateTimestamp)
  )
);