How can I add a column/s to wp_posts table?

I am trying to sync WP with another service that offers an API that allows me to hook up their table’s Update, Create, Delete to my sites.

So when they create, update, or delete a row on their server, I get details about that update, including the row ID.

The way I am thinking of syncing is by using custom post types, and either add a new separate table with post -> api relation like post_id:1 = api_id:53.

Or alter wp_posts table by adding a column containing the api_id.

I know I can add metabox which will add that as post_meta but I am not sure how effective will that be and I am pretty sure if I alter wp_posts, querying will be a lot faster.

So how can I alter wp_posts in the WordPress way? If that’s not possible, should I add meta boxes or create an entirely new table just for the relation ?

You can technically add a post column with SQL but I’d caution against it as backup scripts, exports etc. would likely ignore it.

Instead, I would add the content as a post_meta, either using a Custom Field or through PHP with the update_post_meta() function.

To fetch a post based on the meta simply use:

$args = array(
        'post_type' => 'custom_post_type',
        'meta_key' => 'api',
        'meta_value' => $api_value,
      );
$posts = get_posts( $args )

or, to fetch the value of the API key

get_post_meta( $post_ID, 'api', true );