A related question (opinion based) is are there practical cases where a very long file name is useful (e.g. file paths above a thousand characters)

Name or path? Kind of a big difference in this context :)

For names I don't think it really matters, but also I don't see much reason for a de jure limit (as opposed de facto limits imposed by implementation, which come and go without impacting the official standard).

For paths, it can be very useful to have long names. A classic example is when some program generates a deep file tree that approaches the length limit. You then want to move this to some subfolder, but now you can't because that would put you over the limit.

Another one is you try to rename an innocent folder, and get errors about length because it happens to contain some deep folders.

Some encryption/data storage systems generate a lot of small files. For example git has its own peculiar object store. It normally is rather wide than deep, but you can see how some people see a use case for arbitrary path length.

Not to say it is a good practice to have very long filenames, though.