When using DAML-on-SQL, is it viable to have the query store as a database in the same postgres instance as the ledger itself? Is that recommended or should it be avoided?
There are trade-offs to consider here. If you don’t expect heavy traffic, having a single database can simplify operating your deployment. I would probably go for this in a first stage. Still, separating the two has advantages for which I would probably consider it the way to go: having two separate databases allows you two isolate the resources across components and possibly allow you to upgrade or perform maintenance on the JSON API server without ever shutting down the participant server.
Another argument in favor of separation is backups. You probably want to backup the ledger db but not the query store as it is just a cache.