I've built a system where I have 2 options - lifespawn of draft and lifespawn of published post (integer which marks days).

If post is being published, exact date-time of publish is saved as postmeta, also lifespawn is added to that time and that value is saved to database as expiration date-time, same thing with draft (a.k.a when post is being saved).

My current system is ran like this:

• I've set up a server cron which runs at night in every 24h

• All published posts are being queried
• Publish date-time metadata is retrieved from database in while loop
• That time is compared with current time in if statement
• If time has passed, change post status to draft

• All draft posts are being queried
• Draft date-time metadata is retrieved from database in while loop
• That time is compared with current time in if statement
• If time has passed, delete post
• All attached images to the post are also queried and deleted

Im super worried that this system / server fails hard if posts amount is 10 000 - 15 000 or even few thousand. It's important that posts (especially published) that has exceeded the time, are taken down.

What other options do I have?

Ideas:

• Trigger the date-time "check" for certain amount of older posts in random queries that users do anyway? - longer loading times for users..
• Trigger the date-time "check" if single-post is being visited and do it one-by-one? - queries still retrieve and echo posts if people are quering it in exact same time which leads to 404s and disappointment.
• Somehow split all posts and do it in chunks? - I could use 'offset' to split posts to groups and do it in smaller chunks but it doesn't work if page_per_page is set to -1

Update:

I've listened your advices and I've tried to query by "expiry date":

• It seems to work.. I changed the cronjob interval and give it a try for few rounds
• Is there a workarounds for formating date? if statement doesn't seem to work otherwise
• Almost same code for drafts, only that I delete draft if time is up
• I also imagine this would be better to run in every 1 hour to avoid big amounts, right?

//Current date-time
$now = date( 'Y-m-d H:i:s' ); //Let's say it's 2015-12-24 21:00:00 (9PM)

//Get all published posts that are over the deadline
$published_posts = new WP_Query( array(
    'posts_per_page'    => -1,
    'post_type'         => 'my-post',
    'post_status'       => 'publish',
    'meta_query' => array(
        array(
            'key'     => 'post_publish_deadline', //string e.g 2015-12-24 15:00:00 (3PM)
            'value'   => $now,
            'type'    => 'DATETIME',
            'compare' => '<=',
        )
    )
));

while( $published_posts->have_posts() ) {
    $published_posts->the_post();

//Get postmeta deadline and format it for if statement
$publish_deadline = get_post_meta( get_the_ID(), 'post_publish_deadline', true );
//If statement doesn't seem to work without this step
$format_publish_deadline = date( 'Y-m-d H:i:s', strtotime( $publish_deadline ) );

//Check if it exists just in case
if( !empty( $format_publish_deadline ) ) {

     //Additional check if time for that guy is over just in case
     if( $format_publish_deadline < $now ) {
       
         //Change post status to draft
         wp_update_post( array( 'ID' => get_the_ID(), 'post_status' => 'draft' ) );

         //Get lifespawn option
         $draft_to_delete_lifespawn = $my_option[ 'draft-lifespawn' ];   

         //Build a new draft expiration date-time -> now + lifespawn in hours
         $draft_deadline = date( 'Y-m-d H:i:s', strtotime( $now . ' + ' . $draft_to_delete_lifespawn . ' hours' ) );            

         //Save new draft expiration date-time
         update_post_meta( $post->ID, 'post_draft_deadline', $draft_deadline );
     }
}

} //END while
wp_reset_postdata();