6 to 10 feet, gives you lots of flexibility, especially if there are two of you playing. Many of my bags have original hoses, which can be a bit short, so I do one of three things, get a connector and add a few feet, add a pump bulb, or, my favorite, use a flow meter as a coupler.
Or a combination of bulb pump and flow meter.
I have found that sometimes when my colon says no, and won't fill with the pressure of the bag, making me feel I have to go right now, a squeeze or two of the bulb gets things moving again. Remember, even held very high, the pressure is not that great, every foot above your anus is another 0.43 psi... So at 4 feet above the colon is about 1.72 psi.... Your colon can decide to say no to that pressure. A colonic usually tops out about 3 psi, and a quick squeeze of the bulb can exceed that, and at that point my colon usually backs down and says you win, I'll take more.
Obviously there can be real risk from a Shower Shot with mains pressure exceeding 30 psi... For instance where I am right now in Belgrave, Victoria, Australia mains pressure is 120psi+.... But for the risk, I haven't heard many stories of people blowing up their colon... Maybe they don't survive to write about it!