Laravel SQL database user posts tags interection (post suggestions)

This is a Laravel 10 project and, I have a table posts – (title(varchar), category (varchar), tags (json), postImg, created_at , delete_at).
And tablet user_post_interactions where I am storing (user_id, post_id, introduction_type).
And I want to show new posts to user based on there post information like tags, category.
How I can do it without any memory overflow (only by using SQL)?

$interactedPostIds = UserPostInteractions::where('user_id', Auth::user()->id)
            ->pluck('post_id');
$page = FacadesRequest::get('page', 1);
$perPage = 10;
//Get hashtags from those posts
$interactedHashtags = Posts::whereIn('id', $interactedPostIds)
            ->pluck('tags')
            ->map(function ($tags) {
                return json_decode($tags); // Convert JSON string to array
            })
            ->flatten()->countBy()->sortDesc()->keys()->toArray();
 $userId = Auth::user()->id;
        $posts =  Posts::where('is_approved', 1)
            ->leftJoin('user_post_interactions', function ($join) use ($userId) {
                $join->on('posts.id', '=', 'user_post_interactions.post_id')
                    ->where('user_post_interactions.user_id', '=', $userId);
            })
            ->selectRaw('posts.*, GROUP_CONCAT(DISTINCT user_post_interactions.interaction_type) as interaction_types')
            ->groupBy('posts.id') // Ensure unique posts
            ->get();
 // Sort using tags match
        $sorted = $posts->sortByDesc(function ($post) use ($interactedHashtags) {
            $postTags = json_decode($post->tags, true) ?? [];
            return count(array_intersect($postTags, $interactedHashtags));
        });
        $posts = new LengthAwarePaginator(
            $sorted->forPage($page, $perPage),
            $sorted->count(),
            $perPage,
            $page,
            ['path' => FacadesRequest::url(), 'query' => FacadesRequest::query()]
        );

Currently, I just pluck post_id from user_post_interactions and by using it I pluck ‘tags’ from post table and after that match these tags for rank posts by tags and take 10 posts to show to user, but when there is more than 1k+ posts it’s going to crash (because of PHP memory limit), is there any other way (using SQL or something else) to do without going to crash?