As you know, the spire is very vulnerable to long range weapons due to its long profile making it easy to hit. One hades is enough to render a spire useless. Mercs and lumbers are also very effective. Close range dps or carronades are also deadly.
The spire does however have good turning and vertical. A well timed hydro can dodge a Pyramidion even with hydro and a buffed balloon.
The classic loadout is merc artemis flak. For flak I use a buff engi with spanner mallet buff and charged (sometimes loch). Double gat can work but it's very risky because you have no defence. Hades artemis works, you can even do double hades.
For a time I used to do a hades + lesmok gat flak spire. Two buff engi downstairs and I was a spanner mallet buff engi with a pilot tool. The hades engi had a main kit and a different pilot tool. This allowed me to buff the top deck and do hull repairs. I usually had a mine up top too (both us up top had lesmok) and the flak was spanner mallet buff lochnagar. This was mostly against charging ships.
The meta lumber is hades art lumber. Light flak is good for a quick kill, but artemis is invaluable in good hands. I used to use a light flak on top right and shoot it when their armor breaks. Now I use a mine to put in front of charging ships (my pilot ammo is always lesmok). In an emergency the gunner can move up to use the mine (lumber ammo = mine ammo). This is quite effective against slower ships like Junkers. With a good gunner you can lock down any ship except squids.
My favorite is hwatcha double art gat. Hwatcha and art on bottom, art and gat top (gat left). For longer range two art and hwatcha reliably disable and do armor damage. For closer the top engi moves to gat, or against a charging ship, I jump on gat for quadfecta. I have buff heavy for hwatcha but a gunner also works. I often see hades art hwatcha or even merc art hwatcha. Hwatcha gat mortar also works well.
I have found double art gat hwatcha to be very effective against most threats including fish and squids who can really give a spire a bad time. I've had a lot of success with this loadout and the double art hwatcha does significant damage to weaker armors while forcing lots of repairs.
My real favorite is heavy carronade double gat. Gats use heavy clip and aim for guns. A gat will break a light gun in under 2 seconds, however gat tracers don't line up exactly with where they hit. The goal is to charge fast and hard. Usually it's a good tactic to reload gat immediately when their armor breaks, but for this the gats constantly shoot. It only takes about 3 gat clips to kill a pyra. I have two main kit engineers with a gunner. The reason for the gunner is shooting charged or heatsink into guns/engines for additional shatter damage. I like using it against metamidions. It can be highly effective against poor Junkers.
Blender fish can be quite effective against spires due to their ability to take damage while charging fast, popping balloon, and ramming. Ships fall at a rate determined by their vertical speed (not sure the exact), so spires fall fast and the bottom spike makes them very vulnerable to hitting the ground. Carronade flame pyras are similarly very effective.
A tactic I love to counter this with is both hydro and drogue chute. Hydro before they pop your balloon, then drogue and keep shooting. The decision whether to bring kero or claw is situational but I usually prefer claw.
Another tactic that is useful on ships like mobulas and spires is to disregard repairs and keep shooting to overwhelm the target, even if your armor is down.
"No repairs! No cowards!"
To sum up I love spires but they are easily countered by range. They aren't seen is competitive due to this. Flash a spire and the other team will bring a hades.