

The Sovereign Class Enterprise-E exceeded the D in length, if not overall size, and the Odyssey Class Enterprise-F clocked in at a huge 3478 feet in length. The Excelsior Class Enterprise-B and the Ambassador Class Enterprise-C gave way to the massive, Galaxy Class Enterprise-D in Star Trek: The Next Generation. The original Constitution Class Enterprise in the 23rd century was significantly larger than the 22nd century NX-01 in Star Trek: Enterprise.

It's true that the Enterprise has always trended larger throughout its history. The Titan-A - now Enterprise-G - was designed as a deep-space exploratory vessel and is capable of exploring new worlds without being titanic in size. It wasn't clarified in Picard's finale, but it's also assumed that the Enterprise-G's mission is the same as its predecessors: "to seek out new life and new civilizations" and "to boldly go where no one has gone before." Why would the newest Enterprise need to be larger than the Enterprise-F in order to accomplish this mission? Unlike the Enterprise-D, the G doesn't need to house 1000 people, and it won't have families aboard like Captain Picard's (Patrick Stewart) TNG starship. Presumably, upon its launch in 2402, a year after Star Trek: Picard season 3's Frontier Day, the USS Enterprise-G inherited the mantle of the flagship of the United Federation of Planets.
