is there any drawback to having too many partitions?

Waste of space due to fragmentation is the first thing that comes to mind. I got tired of moving and symlinking stuff to make room inside partitions. Nowadays I only use the essentials (/ and /efi) + /home + 1 partition per physical device, with a filesystem that makes sense for the usage and device. That said, I never run more than one distro on bare metal.

I’m not an expert, but one drawback is the potential for one partition to become full when the others are empty… However, it looks like you have several different file systems, so a leaner approach might not be doable. May I also recommend something like proxmox to virtualize different OSes, although I’m not sure that would meet your needs.

A partition for each thing you might want to change the size of is my rule, or to be able to wipe independently of the others.

I usually prefer lvm over actual partitions, since it does a better job letting me think about volumes as opposed to devices.

Boot gets a partition because it's basically required. Home gets one so I can reinstall without mucking things up. The database directory gets one for similar reasons, a d because I might need to scale it up. The system itself gets one because it's most likely to get wiped or need more space.
Most of these are actually lvm volumes.

It's only a problem if your software can't address that many. Any modern software can handle up to 128 primary partitions on GPT.

Just deleting the excess ones and resizing the remainder is fine.

First off, the HLS+ partition is for MacOS, not Linux, and could be for OCLP if you use that (which I assume you do based on having MacOS 14 on a 2014 Macbook). I would highly recommend you try new distros in a VM instead of installing them. It's much faster and will reduce the work you need to do dramatically. You can just pick whatever distro you seem to like the most (or whichever is first in partition order because it doesn't matter which distro you use for this), extend that partition for more space, then try everything in a VM. It seems that 2014 Macbook Pros come with a dual core processor, so you can assign one core to the host OS and one to the VM, as well as about half your memory (so 4GB of the 8GB that comes by default). Once you settle on one, you can delete the partition with the OS you used for VMs and install that (or keep that OS if you decide you like that best and just delete the VMs). Installing too many operating systems could also bloat your EFI boot manager list (stored in NVRAM, not on disk), and you may have to manually delete EFI entries if it gets too full with something like efibootmgr.

I'd also like to clarify that Fedora can be configured for EXT4 during installation, but it uses BTRFS by default. The be clear, BTRFS has many advantages, and I'd recommend that over EXT4 any day of the week. Fedora adopts new technologies before most other major distros, and while BTRFS was initially released over 15 years ago, it wasn't very "proven" or ready for major distros until maybe 5 or so years ago, and Fedora 33 was among the first distros to start using BTRFS by default (late 2020). You were able to manually partition BTRFS before Fedora 33, but it was not the default. You can feel free to look up any additional info on the differences yourself, as it's fairly off topic to your question.

After you decide what you want, you can delete other partitions and move/resize partitions from a liveUSB (so you aren't running an OS from a partition that you're moving). It should be fine to use the liveUSB for whatever OS you plan to install, but be aware that you may have to install GParted since it doesn't always come by default on the liveUSB. By moving and resizing partitions, you can eliminate unallocated gaps. You don't generally have to delete all the partitions/reformat a drive to install a new OS unless you're having issues with the drive itself (which is likely to be a hardware issue anyway).

Technically, GPT is capable of up to 128 physical partitions, but you can also extend that with LVM to create virtual partitions (which I believe you can have a max of 65,536 virtual partitions in an LVM physical partition, so you can see that the numbers get pretty absurd). The main limiting factor is space and utilization. If you have 128 partitions on a 256GB drive, each partition would only be 2GB, so it would be fairly useless. Generally, you want to give each partition room to grow to a specified size that you believe is reasonable. For instance, it would be irrational to size your EFI partition at 64GB, because it could never be expected to grow to that size, and takes away space from partitions that could utilize it (such as your main operating system). Additionally, it would be unreasonable to size your OS partition at 20GB on a 256GB drive because it can be expected to grow far beyond that in normal use (downloading/storing media, for instance).

A little off topic, but I'd also generally recommend you use a single distro at a time if using Linux, as for the most part, you can do basically anything in one distro you can do in any others. If you use your laptop for work, you can use a different OS for work and personal use, but often it makes more sense to just set up multiple users. Managing and maintaining multiple installations takes much more time, and has very few benefits unless there's a very specific reason for doing so. Most of the differences you should care about will have to do with the package manager and repos, as your desktop environment can be changed if absolutely necessary. If you want to change your desktop environment though, I'd recommend seeing if there is a spin of the distro of your preferred DE available first, as that's going to be much smoother and less prone to breakage than changing it yourself. For instance, Fedora Workstation comes with GNOME by default, but has spins with KDE Plasma, XFCE, Cinnamon, MATE, i3, LXQt, LXDE, SOAS, Sway, Budgie, and soon will have COSMIC available (you can view the spins here). Since I'm already going off topic, Fedora is my #1 personal recommendation for distro, especially for new users who aren't sure what they want yet. It's fairly mainstream (so lots of forums and support available), has a semi-rolling release (so it gets updates and features quickly, but is still stable and has discrete releases), and generally has a good user experience. If one prefers the release schedule of Debian or Arch, then at the very least Fedora sits in between the two (fixed vs rolling) so it is generally agreeable with most.

TL;DR: You aren't going to be limited by the amount of partitions you have, but you can fill up your EFI boot manager list in NVRAM if you install too many operating systems (you have to manually clear that list, uninstalling/deleting partitions won't do it). Generally, the use of VMs is easier and involves much less work than installing distros to your disk, and allows you to distro hop much faster to find your preferred distro.

Theoretically you could put all the Linux installations on a single big btrfs partition under different subvolumes.

Don't get me wrong, but please start using LVM volumes, ZFS volumes or BTRFS subvolumes instead of partitions. We're not in 1995 anymore.

I know a lot of you come from a Windows background so you're used to juggling partitions with all the hassle that entails, but if you put a bit of time towards learning LVM you'll find that you can stretch and shrink volumes to your heart's content.

You can do neat tricks with mirroring, stripping and other RAID levels, mix and match disks and more.

Not really - sounds like you do need actual partitions since you're running different OSs. Typically you would use some other form of volume management (LVM, btrfs, zfs) for partitioning within a single OS. But for separate installs it's fine. gparted does a good job of letting you move things around and resize as needed. Just take care when shrinking partitions to shrink the filesystem first.

There used to be a time where there were obnoxious limits about the number and types of partitions you could have on disk (2 "physical partitions" and some number of "logical partitions" - I forget the details now). But if it works it works.

partitions are used for organizing, the downside is that more partitions make each one smaller

I end up running 1 btrfs partition sagred between all my installed Linux distros on one system

lvm is also awesome for resizing and moving partitions

my desktop right now has nvme0n1p1 as my efi partition and p2 as a lvm pv

inside that lvm I put everything else as it's very easy to resize and move them (I also have p2 encrypted with luks2)

Lvm could be the way to go. Start with the minimum amount of partitions (i.e. / and /boot and swap as lv, maybe efi as a real partition). Add additional lv later if/ when you need them. You can always re-size a partition and the wrapping lv when you want to re- distribute storage-space.

I never needed more than these partitions. But that is just my use case.

Edit: oh. Missed the Multi boot point. Forget what I wrote. :)