DEV Community

Mangesh
Mangesh

Posted on

# PostgreSQL Tutorial: “Too Many Open Files” Error: Diagnosis and Resolution Guide

PostgreSQL is a powerful, production-grade relational database, but even the best systems encounter limits. One of the more frustrating and cryptic issues you might face in high-concurrency or large-data workloads is the dreaded:

FATAL: could not open file: Too many open files

This guide walks you through the why, how, and most importantly, the fixes for the “Too Many Open Files” error in PostgreSQL on Linux systems. Whether you're a system administrator, DevOps engineer, or backend developer, you'll walk away with a working solution.

🧠 Understanding the Problem

PostgreSQL uses file descriptors for a variety of internal operations:

  • Opening tables, indexes, and system catalogs
  • Handling client connections via sockets
  • Managing WAL (Write-Ahead Log) files
  • Working with temporary files for sorting or hashing

When PostgreSQL or the underlying operating system exceeds its file descriptor limits, you get errors like:

FATAL: could not open file "base/16384/2600": Too many open files
Enter fullscreen mode Exit fullscreen mode

This is a resource exhaustion issue, not a bug.

🔍 Step 1: Verify the Current Limits

OS-Level File Descriptor Limit

Check the shell limit for open files:

ulimit -n
Enter fullscreen mode Exit fullscreen mode

For PostgreSQL’s active process:

cat /proc/$(pidof postgres | awk '{print $1}')/limits | grep "Max open files"
Enter fullscreen mode Exit fullscreen mode

PostgreSQL Internal Limit

Check the database-side maximum files per backend:

SHOW max_files_per_process;
Enter fullscreen mode Exit fullscreen mode

⚙️ Step 2: Increase File Descriptor Limits

If the current limits are too low (commonly 1024), it’s time to raise them.

A. Modify System-Wide Limits (Linux)

Edit /etc/security/limits.conf:

postgres    soft    nofile  4096
postgres    hard    nofile  65535
Enter fullscreen mode Exit fullscreen mode

Ensure PAM uses these settings:

echo "session required pam_limits.so" | sudo tee -a /etc/pam.d/common-session
Enter fullscreen mode Exit fullscreen mode

B. Configure systemd for PostgreSQL

If PostgreSQL runs as a systemd service (default on modern Linux distros):

sudo systemctl edit postgresql
Enter fullscreen mode Exit fullscreen mode

Add or modify the override file:

[Service]
LimitNOFILE=65535
Enter fullscreen mode Exit fullscreen mode

Reload systemd and restart PostgreSQL:

sudo systemctl daemon-reexec
sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

🛠 Step 3: Tune PostgreSQL Settings

Update the following parameters in postgresql.conf if applicable:

max_files_per_process = 4096
max_connections = 200
Enter fullscreen mode Exit fullscreen mode

⚠️ Note: A higher max_connections leads to more simultaneous file handles. Use with care and monitor performance.

After changes, restart the PostgreSQL service:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

🔬 Step 4: Analyze and Monitor Open File Usage

Use lsof to examine open files:

sudo lsof -u postgres | wc -l
Enter fullscreen mode Exit fullscreen mode

Or target a specific PostgreSQL process:

sudo lsof -p $(pidof postgres | awk '{print $1}')
Enter fullscreen mode Exit fullscreen mode

Check for excessive use of:

  • Temp files (/tmp or PostgreSQL temp dirs)
  • Frequent or long-running queries
  • Poorly tuned connection pools

🧰 Step 5: Best Practices to Prevent Recurrence

  • Use a connection pooler (e.g., PgBouncer) to reduce concurrent connections
  • Analyze slow queries to prevent unnecessary temp file usage
  • Audit extensions or custom code that might open too many handles
  • Monitor file usage via pg_stat_activity and pg_stat_file

✅ Summary

Action Command / Config
Check file limits ulimit -n, cat /proc/*/limits
Raise limits in Linux /etc/security/limits.conf
Adjust systemd settings LimitNOFILE=65535
PostgreSQL file limit max_files_per_process = 4096
Restart PostgreSQL sudo systemctl restart postgresql

🧩 Final Thought

The “Too many open files” error is PostgreSQL telling you it needs more breathing room. With just a few configuration updates on both the OS and database sides, you can lift this ceiling and allow PostgreSQL to scale comfortably with your workload.

Got thousands of connections or huge data volume? Consider connection pooling and query optimization as your next frontier.

Top comments (0)