How to get all post_id and meta_value using meta_key in wp_postmeta table

WordPress does not include any functionality to directly acquire a list of Post IDs and meta values by meta-key. The most direct solution is to query for all posts which have the meta key, then access their respective meta values afterwards. While it’s possible to execute a SQL query to produce such a list, the need to do so may be a red flag indicating suboptimal design – you should consider other ways in which you might leverage WordPress’s systems to achieve your desired outcome before settling on such a solution.

Alternate solutions include caching the collective meta values in a single transient or site option and modifying your update operation to affect both stores, or using a dedicated custom table to store the data. The “best tool for the job” is as always dependent on the specifics of your use-case.

In any case, if you need to account for large numbers of entries, you should consider incorporating pagination in order to mitigate memory and execution timeout limitations.

---

Post Query

The most simple solution is to query for posts which have the meta:

$query = new WP_Query( [
  'meta_key'     => 'restriction_settings',
  'meta_compare' => 'EXISTS',
] );

foreach( $query->posts as $post ) {
  $post_id  = $post->ID;
  $settings = get_post_meta( $post_id, 'restriction_settings', true );

  // ...
}

While you might think to set 'fields' => 'ids' in the query in order to improve performance by only retrieving post IDs, doing so will disable the post meta cache for the query, which may result in each subsequent call to get_post_meta() incurring an additional database hit and penalizing overall performance instead of improving it.

---

Custom SQL Query

The code below is only for demonstrative purposes and circumvents all of WordPress’s conventional meta-data routines. This includes ignoring the actions and filters which would typically run when accessing post meta-data, as well as any interactions with the meta caches which improve the efficiency of the operation and provide interoperability with third-party caching solutions.

If you choose to go this route, it would be highly advisable to examine the implementation of get_metadata() and replicate it’s hook and caching functionality in order to best ensure compatibility with both core behaviors and third-party plugins, as well as performance optimization.

function wpse409692_get_meta_post_values(
  $meta_key,
  $single   = false,
  $per_page = 100,
  $page     = 1
) {
  global $wpdb;

  $query  = "
    SELECT `post_id`, `meta_value`
    FROM {$wpdb->postmeta}
    WHERE `meta_key` = %s
  ";
  $query_values = [ $meta_key ];

  if( $per_page == 0 || $per_page < -1 || $page < 1 )
    return [];

  if( $per_page > 0 ) {
    $query  .= ' LIMIT %d, %d';
    $offset = ( $page - 1 ) * $per_page;

    array_push( $query_values, $offset, $per_page );
  }

  $results = $wpdb->get_results( 
    $wpdb->prepare( $query, $query_values ),
    ARRAY_A
  );

  if( $wpdb->last_error )
    throw new WP_Error( 'wpse409692-db-error', $wpdb->last_error );

  return array_reduce(
    $results,
    function( $post_values, $result ) {
      $post_id    = $result[ 'post_id' ];
      $meta_value = maybe_unserialize( $result['meta_value'] );

      if( $single ) {
        $post_values[ $post_id ] = $meta_value;
      }
      else {
        if( !isset( $post_values[ $post_id ] ) )
          $post_values[ $post_id ] = [ $meta_value ];
        else
          $post_values[ $post_id ][] = $meta_value;
      }

      return $post_values;
    },
    []
  );
}

Usage (retrieve the first 100 post ID/meta-value pairs):

$post_restriction_settings = wpse409692_get_meta_post_values( 'restriction_settings', true );

foreach( $post_restriction_settings as $post_id => $settings ) {
  // ...
}