Best way to store 1 billion+ posts?

Question

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!

Answer 1

I don't know about how long it will take to create that many tables, especially if you do it in one go, but you can use $wpdb to handle the creation. There's an article on the Codex regarding creating custom tables, https://codex.wordpress.org/Creating_Tables_with_Plugins

And it crossed my mind that perhaps using some external tool to handle the database querying could potentially make the queries run faster. Elasticsearch (I'm not an affiliate), in tandem with ElasticPress, is the only one I've heard of, but I'm sure there's plenty of similar tools. Maybe that could be worth checking out?