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.)

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.

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?