Will PostgreSQL switch from multi-process to multi-threaded architecture?

postgresql.org

31 points·sh_tomer··2 comments

I don't know enough about the internal architecture of threads to know if they are necessary for this, but one of the very few things about postgres that always struck me as nuts is that any one query (you could easily write one yourself) can grab as much memory or CPU as it wants, the point of stalling out thousands of other queries (serving thousands of other users in the context of a webapp).

There is a statement_max_duration which is a great start, but if I want anything from postgres it's an ability to say "allocate X cpu/memory per thread and let it do as much as it can within those limits"

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.

Reply on news.ycombinator.com