PostgreSQL resource consumption, segregation and scheduling

Question

How does PostgreSQL protect sessions from each other from the resource consumption perspective?

For example, I write some stored procedures:

1. a stored procedure that executes a highly cpu-bound tight loop, how does PostgreSQL keep it from sucking up a big portion of the available cpu?
2. a stored procedure that triggers a lot of IO, how does PostgreSQL keep it from sucking up most of the IO bandwidth?
3. a stored procedure that reads widely scattered pages that no other session references, how does PostgreSQL keep it from filling up the buffer pool?

Also, as I understand it that each PostgreSQL session corresponds to a different OS process, so I also wonder what resource consumption segregation that PostgreSQL deals with explicitly and what it relies on for the OS to perform (as part of the OS's scheduling mechanisms).

Thanks much.

piaka

Answer 1

There is no resource throttling for processes in PostgreSQL, each process will consume as much CPU and I/O as it can.

This is somewhat mitigated by the fact that PostgreSQL backends run single-threaded, so a single backend cannot consume all the resources of the database server. Note, however, that PostgreSQL has parallel query, so (with the default configuration) up to three processes can work on a single statement. You can reduce that by setting max_parallel_workers_per_gather to 0.

There is also no limit of how many pages a statement can evict from shared buffers. But unless the statement touches a single page multiple times, the usage count of the pages read in by the statement will remain low, and the buffers can get evicted from the cache again. There is also an optimization for large sequential scans: if the table is estimated to blow out more than a quarter of shared buffers, it is scanned using a "ring buffer" consisting of only a small part of shared buffers.