So in this scenario I'd need to double-up all fields of this type?

Yes, but there's no performance impact to _comprofiler table when using Code or Query fields as they have no storage behavior. The only performance impact would be the efficiency of whatever code you write for the display.

Are custom field types still a 'thing'? Is there any documentation around how to create these? I couldn't see anything obvious.

Custom HTML fields? Yes. Or do you mean field types you code yourself from the ground up? Of course that's how CB Query Field and CB Code Field are able to add new field types.

I've noticed that for editing, Query Tag uses select lists rather than autocomplete and the lookups for these could include up to 8000 items, which presents a problem as these are all loaded into the DOM.

I don't have a solution for you to workaround that. We don't have query autocomplete for tag fields only text fields, but text fields would only be able to accept a single value. There are plans to add support for this though. This would allow filtering down the list of options as they type. I do not know when this will be implemented though.

forge.joomlapolis.com/issues/8888

That's a field type someone else developed. I can't help you with that. You can try updating its code to work with CB 2.x though I suppose.

We do not provide coding assistance outside of simple examples. Best I can suggest is reviewing other field type plugins. Depending on how clean the existing code is it shouldn't be too difficult to get the plugin updated to CB 2.x.