It’s quite well supported, and has been for years, on IBM POWER systems as well as workstations using motherboards produced by Raptor or Tyan for example. Debian (ppc64el) and Fedora (PPC64LE) installation images are available. RHEL is also available, and a number of systems from Hitachi, IBM, and Inspur are certified with that distribution.
Packages provided with the distribution should work fine; since the switch from big-endian to little-endian, you won’t run into endianness issues. Third-party support is less certain, especially for anything that can’t be rebuilt from source — the systems are fast enough to run x86 binaries using emulation, but that’s not ideal.
Performance is great, at least on the IBM systems I’ve used (in a logical partition). The cost/performance ratio however doesn’t compare favourably with x86 or ARM systems but I imagine you’re aware of that.
Whether all that qualifies POWER9 for “end-user usage” depends on the end-user in question — I don’t think the servers are usable by most end-users, and even POWER9 workstations aren’t quite like PCs, with two-stage BMC-based boots on Tanos II for example.