Resource Usage / Disk

max_notify_queue_pages

• What it does: Sets the maximum number of allocated pages (in shared memory) for the NOTIFY/LISTEN message queue, controlling the amount of memory reserved for asynchronous notification messages.
• Why it matters: This parameter directly impacts the scalability and reliability of PostgreSQL's NOTIFY/LISTEN system. When applications use asynchronous notifications extensively, the queue can fill up if messages are produced faster than they are consumed. Setting this too low may cause notification messages to be lost when the queue fills up, while setting it too high may waste shared memory resources that could be used for other purposes like caching.
• Ideal value & Best Practice: Default 4096 pages (32MB with 8KB pages) is reasonable for most applications. For systems with heavy NOTIFY/LISTEN usage, monitor the pg_notification_queue_usage metric and increase if you see frequent queue full conditions. For minimal usage, you can decrease to conserve shared memory. Balance based on your actual notification patterns and volume.

temp_file_limit

• What it does: Sets the maximum total size of temporary files that each database process can use before the operation is cancelled, preventing runaway disk usage by queries that require large temporary spaces for sorting or aggregation.
• Why it matters: This parameter is crucial for preventing disk space exhaustion caused by poorly optimized queries that require excessive temporary file usage. Without this limit, a single problematic query could potentially fill up the disk, affecting the entire database system. This is particularly important in shared environments where disk space is a limited resource and needs to be protected from accidental or malicious abuse.
• Ideal value & Best Practice: Default -1 (unlimited) is not recommended for production. Set to a reasonable value based on your available disk space and workload characteristics, such as 10GB or 50GB. Consider your largest expected legitimate temporary file usage and set a limit slightly above that. Monitor for queries hitting this limit as they may need optimization.

---

Try pghealth Free Today 🚀

Start your journey toward a healthier PostgreSQL with pghealth.
You can explore all features immediately with a free trial — no installation required.

👉 Start Free Trial