What software would you use to store the data? It's not "a lot", maybe a few hundred gigs per year
I meant specifically I would re-think it.
If It were me, at this point I would just fragment it. Not shard, but instead; given the need to poll this data, I would remove it from the main DBs, and instead spin up another that contains these rows. If nothing else, I would run exports/live syncs of those rows at regular intervals (if I didnt remove them from the main DBs) so I could query that data at will without putting the strain on the main data bases. It sounds like thats really what you were trying to do to begin with, but I think like you said, you got impatient, because, well you needed the data now. I would split off (the first sync is gonna take a bit) and then just keep those specific tables sync'd.
This is a lot cheaper of an idea then implementing sharding, or having some software store and recall this data at will. Like you said, I would keep the backups ofc, but move these specific rows to its own box in its own aux DB so I could touch it when I want. Since there is a biz need. You could realistically make it even more simple and leave those rows included in the main DB because %reasons% (legacy API calls, peace of mind w/e) and simply sync it. Then you arent even modifying the integrity of the main DBs, you are just duplicating a specific subset of data you want to be able to hit whenever you want. With the added benefit of not touching prod but still reletively fresh data.
At the point of having that broken off DB then you could use w/e software you see fit. You could open it in Access if you really wanted to.
