Why is it useful?
Generally it is all about thread models.
And there are several examples in the past, where it would have helped. E.g. gitea introduced it after their bot account has been compromised.
It is useful to prevent modification of your source code on your git hosting service/server (GitHub, GitLab, Gogs/Gitea etc.) and at the connections to them.
What to sign?
Now for users of your software it may be useful to e.g. download signed release tags and verify them, so they can be sure you created the source code and stuff like that.
Keep in mind though that signing release tags helps nothing if you then distribute your binaries unsigned. In most cases users prefer the ready binaries and thus would not be able to verify their authenticity.
It may also make sense to sign git commits if users may regularly use the master branch directly for downloading/using the software. They may e.g. be more common when it's just a PHP project you can clone and it works out of the box than when you have to compile it, where many users may rather use compiled binaries.
However signed git commits may also make sense for developers, as you they can verify nothing malicious got introduced in a git commit (at least not without a committer/contributor doing that deliberately or signing off something accidentally). As such, if there is something wrong with a commit you can be sure it's the one who committed it that has introduced that. (as you can otherwise just set your git username and mail arbitrarily)
However, this is really only useful if all committers to the main branch do sign their commits and thus may be cumbersome.
Attention: Remember when signing git commits you have to do it for each commit. Some workflows accept that you create a feature branch and then sign only the merge commit, but this has to fit your workflow.
In any case this also means that you often have to use your private key and have it (unlocked) in your development environment. This may not be ideal from a security perspective.
In contrast to that when you make a release with a signed git tag, you likely do that not so often, so you can use a separate device/VM/sandbox/user/…, where you do not have so much untrusted development code running, but just clone/pull the repo (with
git pull --verify-signatures obviously if you've signed your commits to make sure nothing has been mangled with it) or transfer the whole git repo somehow else (in an "integrity-preserving" way) where you can sign the tag with a signing key kept in a more secure place.
This way your signing key is at least not at risk of getting compromised if your development machine should get compromised.
Signing git tags is (IMHO) more useful than signing commits, as the first is what users of your software will mainly use, while the latter is mostly good for developers and has it's risks (as discussed above). Thus it makes also totally sense to use different git signing keys, one for your releases that is kept in a securer place/way and one you use to sign all your commits. It makes sense to use a personal git signing key for signing commits and, especially in bigger projects, have the release/tag signing key separated and shared among the project lead/some trusted people, who are authorized to make releases. This way users also don't have to have 50 keys of your developers in their key store, but just the one of your project. Especially as users likely won't recognize all your developers and may not be able to verify whether this if a real developer of your's or an attacker.
So if you can sign git tags. It already helps very much for distributing your software securely to your users. Signing git commits may also be useful, but is likely not as important as signing git tags.