Prometheus without any supporting tooling isn't really designed for long term storage as I understand it, however it is built to support long term storage and querying via its remote read/write protocol. Prometheus will write data to remote storage, and can delegate queries to that storage, rather than using its own local storage as it does by default.

Of the various tools that expose the remote read/write APIs, I like the looks of Promscale/TimescaleDB the most so far, but other options like Thanos might make more sense if you need to collect metrics from a bunch of Prometheuses. That said, maybe you can still use Promscale/TimescaleDB with Thanos as the storage backend, I can't recall the details on its requirements though, so it might not be suitable for that case. For my own use cases though, Promscale is a great solution.