What is the best caching solution for website with a lot of content? We are building a website with over a million nodes. Those nodes also have paragraphs which increases the number of entities. Therefore, the cache tables are getting bigger and bigger, especially the cache_entity and cache_render tables.

What is the best way to avoid the database to get too big (we're talking hundred of gigabytes) ? Is memcache a solution here or is it just deporting the problem of disk space ?

The basic cron task doesn't clear the tables. We were also thinking about setting a cron task to execute "drush cr".

According to that post and that issue, Drupal 8.4 will have a better cache table management.
Part of the solution could be to compress those cache tables where the typical values to cache are quite big and allow for a high compress ratio. I think render caches fit that description very well.

See e.g. issue [#1281408] and a comment on another issue mentioned in comment #23.