The Creation Engine, the game engine with which Fallout 4 runs, allows for 255 standard plugins, which includes the master plugin and all DLC. Creation Club Content, while official "mini-DLC" allows for up to 4096 plugins to load. For console users, these values were limited by Bethesda in 2017. It has not been officially stated why the limitation was imposed; however, this is a good thing for console users.
Enhance, Don't Overload
The objective while using mods is to use others' fanworks to enhance your gameplay. Overloading your load order and using too many mods that conflict can be a frustrating experience. A conflict-free load order is achievable. There are mods that will work with others, provided you do your diligence and do not overload your console with too many mods that don't work on your console.
Unfortunately, Fallout 4 was the test bed for Xbox mods, and the mods that first came out were not all optimized for the experience since very few of the mod authors who were publishing had even played on Xbox before.
Read all documentation carefully and observe the date the mod was created on Nexus. Just because the page was updated on the Bethesda site does not mean there was an update.
You may download up to a limit of 150 mods and 2 GB of space.
You may download up to 100 mods and 900 MB.
You may have up to 253 data files with the ESM and ESP extensions (including the game files and the 6 DLC) and 4096 data files with the ESL extension. You may have more with the ESL extension as they load differently than 'normal' data files. ESL data files can have a maximum of 2048 records and should not contain interior cells. The ESP extension can be flagged as an ESL extension if the plugin is smaller than 2048 records.
PC players have the advantage of being able to repack texture archives to keep from hitting the limit. Players can use Wrye Bash and the archiver that comes with the Creation Kit for an easy way to pack and unpack outside the data folder.
Load Order Worksheet
The Fallout 4 Load Order Framework Basic Template has a built-in calculator for your planning needs. You have full control over your load order and how you play the game.
Creation Kit Technical: Data Files