I think the saucer separation ability was an example of theoretical thinking. The Galaxy class designers clearly intended a use for it. In dangerous times, the saucer would separate leaving the stardrive section free to act without it.

But, via practical real world experience, Starfleet learned that separating the saucer section was seldom worth it. It was too slow, where danger tends to occurred suddenly, and, even when there was extra time, Galaxy captains learnt the extra power generation of the saucer proved more useful than the decrease in mass. I doubt the War Galaxys from the Dominion War even had the ability and, if they did, it was only because it would take too much work to remove.

It's possible that the saucer section worked better as a one-use 'super-lifepod' but the original plan of combining and transforming ships clearly didn't pan out. Same thing seems to have happened with the Prometheus with Starfleet deciding the 'wolf pack' attack mode innovations, while effective, are better implemented via separate ships. Like we see a trio of Texas class ships doing to take down a Sovereign.

(There's clearly some guy in the Starfleet bureau of ship design who keeps trying to make combining/separating ships happen and keeps getting disappointed when they don't pan out in practice.)

They highlighted the tactical disadvantages in The Best Of Both Worlds. In a battle situation, the saucer section’s phaser banks, impulse drive, and fusion generators are of high importance.

There are also alternatives. In DS9’s episode introducing the Jem’hadar, The Odyssey simply dropped off their families before heading to the Delta quadrant.

I’m the event a hostile force is encountered, it is likely the saucer section would just be pursued and destroyed, as it could only flee on impulse or sustain a low warp is released at warp.

So they really only used it the only times it was practical: facing Q, facing the arsenal of freedom (as the automated system only attacked ships in orbit), and as a life raft after the star drive was destroyed as in Generations.

It might also be noted that prior Enterprises had the ability to separate the saucer on an emergency. It was just never used on screen, and also would probably require a spacedock to reassemble.

I personally think it was just an experiment that didn’t work out. Perhaps they just reverted to emergency separations.

In universe, I don't know how useful saucer separation really was. Then again, I don't really know how useful a floating city really was, either. It really showcased the hubris of the galaxy class design and the naivete of Starfleet at that point in time.

At the beginning of TNG, the Federation seemed very idyllic. That started to change with the introduction to the Borg, and was completely shattered with the Dominion War (remember the Jem'Hadar kamikaze pilots against a galaxy class, for example). At the end of the TNG era, you don't really see many galaxy class ships flying around, but more ships that are more battle ready.

To your direct point about saucer separation, separating half the ship to leave vulnerable seemed like a bad idea. The saucer section (which had most of the population) didn't really have a warp drive but it did have phasers. Still, it was susceptible to hit and run tactics while the lower portion was away.

Additionally, the saucer had most of the phaser array - that could be handy in a fight! Why leave that behind?

Lastly, you mentioned Generations. The saucer section couldn't leave the lower section fast enough and was caught in the blast radius. The end result was the same as if traditional life boats were used - the destruction of the entire ship. In general, the separation procedure was slow. It made more sense to just take the saucer with you instead of wasting precious minutes in a separation procedure that could introduce the possibility of damage to the vessel before/after the time critical mission.

I'm not sure how useful saucer separation really was. Starfleet didn't seem to think it was useful, either, as no other ship had that feature moving forward, and the one ship that was shown on screen to have it rarely used it.

Because the special effects required to do it were too expensive for the production company? The Best of Both Worlds separation used footage from Farpoint, I believe.

From a real world perspective, I think they just found it slowed down storytelling too much. I think the idea originally came from Gene Roddenberry and they had always been trying to make it work.

Ironically in Best of Both Worlds, it's a lure to distract the Borg from the rescue mission, and it wasn't like they offloaded the kids and civilians first. Pretty high stakes gamble on Riker's part.

Do we know if it is really used infrequently, or does it just happen that it is not used during the events pictured on screen? There is no dialog where a character claims that it is used infrequently?