When Victor Ibarbo edged the Sardinians ahead midway through the first half, it looked like Napoli were finally thwarted in their attempt to catch leaders Juventus over the remaining five games.
A double salvo from Marek Hamsik and Edinson Cavani just after the interval briefly made amends until Cagliari's Marco Sau made it 2-2 with 20 minutes remaining.
The single point would have left Napoli 10 points shy of Juve before the champions' evening game against AC Milan and so Insigne dutifully climbed off the bench to salvage all three at the death.
Napoli's players knew this was a game they could not afford to lose and so the Azzurri started in determined fashion at the San Paolo
Davide Astori blocked Christian Maggio's early shot before Goran Pandev blasted over from close range.
Cagliari were eager to consolidate a place in the top 10, though, and Radja Nainggolan bared his teeth in the eighth minute with a shot to rattle the bar.
Thiago Ribeiro's missile gave stand-in Napoli goalkeeper Antonio Rosati more to worry about and, in the 18th minute, Cagliari made the breakthrough.
A corner flicked clear by Rosati landed at Ibarbo's feet and the Colombian stepped across the D to rifle the ball into the back of the net.
So often this season Cavani has rescued his team-mates but the Uruguayan would struggle before the break.
After fluffing an easy chance in front of goal he drove Pandev's cute pass over the bar before flicking Hamsik's cross high and wide.
Napoli were nearly punished for such wastefulness as Nene missed the mark by a hair's breadth at the end of a counter-attack.
Cavani took the hint but after letting Maggio's through ball run out of his reach, he failed to convert a one-on-one with Michael Agazzi moments before the break.
The second half began with a disallowed goal as Cavani put a foot offside while Hamsik was slotting home.
It did not matter, however, as moments later the Slovakian surprised Agazzi with a deflected shot off Astori to drag Napoli back into contention.
Visiting coach Ivo Pulga was banished from the touchline for his overzealous protests and therefore watched from the stands as his side went behind in the 64th minute.
This time Cavani made no mistake as he bore down on Agazzi to power home his 23rd league goal of the season.
His joy was shortlived, however, as Cagliari equalised inside seven minutes.
It was a wonderful moment for Sau who twisted his body on the edge of the area to fire past Rosati with aplomb.
A desperate Napoli turned the screw for the last 10 minutes but it looked like the task was beyond them when Hamsik screwed Pablo Armero's cross wide in the 79th minute.
But Insigne, who had been quiet since replacing Blerim Dzemaili, inspired jubilation at the San Paolo when his magnificent effort fizzed past Agazzi four minutes into stoppage time.