PostgreSQL 8.3 – How to Limit Resource Consumption Per Query

performancepostgresql

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy.

This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes the query to take more and more memory until the entire machine gets stuck.

I'm looking for a PostgreSQL mechanism that can:

  • Limit the memory consumption (and perhaps other resources) used by a specific query.
  • Automatically stop queries whose execution time exceeds a certain threshold.

Any ideas?

Best Answer

To limit memory consumption, the main configuration parameter is work_mem. Since this applies per operation, not per query, you cannot simply set it to N if you want to spend N amount of memory, but you have to tweak and tune it a bit to get the desired result.

This doesn't help, however, in case of bugs and other memory leaks in the server code or extensions. You could control that with the process-specific resource limits controlled by ulimit. But if you reach the limit and memory allocation fails, what do you want to have happen? It probably won't behave too nicely. Better fix those bugs or use a different version.

The stop queries whose execution time passed a threshold, use the parameter `statement_timeout', e.g.,

SET statement_timeout TO '10min';
Related Question