You can set work_mem to limit per-process memory and max_parallel_workers_per_gather to limit the number of CPUs, although I don't think there is a general way

You can set work_mem to limit per-process memory and max_parallel_workers_per_gather to limit the number of CPUs, although I don't think there is a general way to limit the CPU to less than 1 CPU. If you rewrite your query, you can add in pg_sleep calls to try to not hog an entire CPU. The buffer/page cache is shared, so it doesn't count towards worm_mem, so a query could replace a bunch of hot pages with cold pages, but in practice it will probably evict cold pages. In my experience, if you have a very busy database, running a big query doesn't even show up on the load metrics, so it probably doesn't matter unless your database isn't very busy or it's nearly exceeded some capacity threshold.