Insulation isn't really thought of as a product but more of a design.

I live in Raleigh which is a bit hotter but overall pretty close.

We did zip walls with a small amount of rigid foam attached, 2x6 walls with advanced framing, blown fiberglass in walls primarily with some foam in tough areas (and in mechanical portion of attic), and thick blown glass in the attic. We have a slab with some rigid foam. Overhangs on southern windows and few west windows.

Is this perfect? Not at all. Is it pretty good - sure. I found it a good compromise for our modest climate. Our HERS score with solar is negative 7 which means we produce more energy than the house uses. The HERS score was 58 before solar which means 42% energy use below reference house.

Spray foam is an expensive product that makes sense in certain situations but is overkill in a modest climate to use everywhere. It is a simple answer to a complex question.

So the first question is - have you started building yet?

And second - "special" that needs to be done with venting or HVAC? - for what? To save on energy? Not really - most important thing is house design. We have basic Seer 15 heat pumps.

The "best" insulation for an application would consider climate zone, construction methods, occupant needs, and the budget. I'm a big proponent of spray foam due to the superior air sealing it provides, but it's darned expensive. We use it for insulating the rim joists in all of the homes we build, but not so much elsewhere. I do advocate for the use of continuous exterior insulation and Huber's Zip-R system is one we've used.

If you suffer from severe allergies, spray foam is the way to go.

You will need to consider a fresh air ventilation system for a home with good air sealing in order to maintain a healthy indoor environment. You'll also need to consider a make-up air system for your cooktop/range hood. Both of these topics are great to discuss with your HVAC contractor. If he/she responds with a puzzled look, then you need to keep looking for another contractor.

Spray foam in ceilings is great because it puts attic ductwork inside your building envelope and is an air seal unlike most other insulations. It is expensive so walls which are sealed on all six sides(top and bottom plate, studs, ext. sheathing, and sheetrock) are good with a more cost effective product like cellulose.

Be careful with cellulose. You may not be able to get the code-required R-value in a 2" x 4" stud wall without supplementing it with continuous exterior insulation or going to 2" x 6" framing.