Hey there I am working on a site that collects a lot of data via scraping. For each data created i'm adding it to WP as a CPT. The problem is I'm going to reach around 1 billion rows eventually, and i'm worried it will slow down the site.

Most of these data will only be displayed on category type pages. With about 1000-10,000 posts per page.

My solution right now is to store the posts data as text in the post meta, then parse it into json to create HTML elements. The problem with this is that it makes it hard to do analysis charts since i can't query all the posts as they are stored as huge text strings in meta data.

The other idea i had was to make a table for each category post, and then add the above data as posts to the table. This way i would have about 10,000 tables and it would be faster to iterate?

Is there a name for this technique where you make a lot of tables? Is there a best practice for adding it to WP?

Thanks for any help!