Unlocking PostgreSQL 18 File Descriptors: The Hidden Performance Bottleneck Every DBA Must Know
Database administration is a constant balancing act between hardware resources and software configurations. Among the myriad settings inside the postgresql.conf file, few are as misunderstood as max_files_per_process. Often mistaken for a hard operational ceiling that triggers catastrophic errors when breached, this parameter actually governs an internal caching mechanism designed to manage kernel file descriptors gracefully.
However, with the release of PostgreSQL 18 and the architectural integration of modern asynchronous I/O frameworks like io_uring, understanding how PostgreSQL handles file descriptors has shifted from an obscure optimization trick to a critical requirement for maintaining database performance and stability.
Main Facts: Decoding max_files_per_process
To understand max_files_per_process, one must first dispel the myth of the error message. Unlike limits that instantly halt execution when crossed, max_files_per_process defines the size of an internal cache. Every PostgreSQL backend process maintains a pool of open kernel file descriptors. When this pool reaches capacity and a backend requires a new descriptor, it does not crash or throw an exception. Instead, it identifies the least recently used (LRU) descriptor, closes it, and opens the new file.
This eviction process incurs a minor performance penalty—costing a couple of extra system calls to open a file that may have been accessed mere moments prior.
The Mechanics of max_safe_fds
Out of the box, PostgreSQL sets max_files_per_process to a default of 1,000, with an allowable range spanning from 64 to over 2.1 billion. Because it is a postmaster context parameter, modifying it requires a server restart.
At startup, the postmaster calculates a safe ceiling known as max_safe_fds. It attempts to duplicate file descriptors repeatedly until the kernel refuses or it hits the max_files_per_process threshold. It then takes the smaller of the two values, subtracts a buffer of ten descriptors for internal operations (fd.c), and assigns the resulting number to every child process.
If this calculation results in a number below 48, the postmaster refuses to start entirely, throwing the following critical message:
insufficient file descriptors available to start server process
The Partitioning Problem
Everything a backend touches on disk—every fork of every relation, every 1GB segment of a large table, and every temporary file—must flow through this descriptor pool.
Consider a standard table with three indexes: it instantly accounts for four distinct files, before even factoring in the free space map and visibility map. Now, scale that to modern enterprise architectures. A single partitioned table featuring 1,500 partitions with one index each accounts for a staggering 3,000 files.
At default settings, large tables suffer from silent inefficiencies. When running repeated sequential scans on a heavy partitioned schema, backends often repeatedly re-open partitions. Because the pool constantly cycles through thousands of files, a backend might open a file to query its length, have it evicted from the pool, and be forced to re-open it moments later.
By raising max_files_per_process to values like 8192 on tables with thousands of partitions, benchmarks show that backends can hold their necessary working sets entirely in memory, eliminating redundant open() system calls and accelerating sequential scans by roughly 8%.
Chronology: The Evolution of File Management up to PostgreSQL 18
File descriptor management in PostgreSQL has evolved alongside operating system capabilities. For decades, database administrators adjusted max_files_per_process based on the number of relations a single database session might touch concurrently. However, the path to PostgreSQL 18 introduced radical shifts in how I/O operations are scheduled and executed.
The Era of Synchronous Limitations
Historically, database systems relied heavily on synchronous I/O models. The operating system limits (governed by ulimit -n) dictated the maximum files a process could open. PostgreSQL treated the max_files_per_process parameter as a straightforward cap on backend file handles. If administrators encountered "Too many open files" errors, they were often advised to lower the setting to prevent exhausting system-wide resources—a concern largely alleviated on modern Linux kernels utilizing systemd 240 or later, where fs.file-max is automatically maximized at boot.
The Arrival of PostgreSQL 18 and Asynchronous I/O
The release of PostgreSQL 18 fundamentally rewrote the rules, primarily due to the introduction of advanced asynchronous I/O capabilities driven by parameters like io_max_concurrency and the implementation of io_method = io_uring.
This architectural leap altered file descriptor calculations in two profound ways:
-
Redefining the Arithmetic: In versions up through PostgreSQL 17, files the postmaster already had open (which children inherited) were subtracted from
max_files_per_processbefore the startup probe ran. When the postmaster held only a handful of descriptors, this was negligible. However, underio_method = io_uring, the postmaster creates an I/O ring for every process slot at startup. Withmax_connections = 300, the postmaster can hold hundreds of descriptor rings. Under older arithmetic, this would severely bottleneck the available pool. PostgreSQL 18 corrected this by counting only files a process opens beyond what it inherits, keepingmax_safe_fdsstable while still counting rings against operating system limits. -
The Rise of I/O Workers: The default
io_methodin PostgreSQL 18 isworker. Background I/O workers handle asynchronous reads—such as sequential scans, bitmap heap scans,VACUUM, andANALYZE—on behalf of all backends. Each worker maintains its own descriptor pool matching the configured size, but its working set represents the union of every backend’s active files. Consequently, a pool sized comfortably for a single session may prove entirely inadequate for the aggregate demands of I/O workers.
Supporting Data: The Systemd Trap and Configuration Realities
One of the most persistent pitfalls for database administrators involves the invisible hand of modern init systems. Raising max_files_per_process inside postgresql.conf without verifying system-level limits yields zero results.
The Soft Limit Bottleneck
The postmaster’s initialization probe strictly halts at the process’s soft RLIMIT_NOFILE boundary—the value reported by ulimit -n.
Modern Linux distributions leveraging systemd typically initialize services with a modest soft file descriptor limit of 1,000 (while the hard limit sits safely at 524,288). Standard packaging from Debian ([email protected]) or PGDG RPM units do not automatically alter this default.
As a result, a stock installation features a kernel limit and a parameter setting that coincidentally mirror one another. If an administrator edits postgresql.conf to set max_files_per_process = 4096 while leaving the systemd soft limit at 1024, the resulting max_safe_fds will clip at roughly 1010. The configuration change is effectively ignored.
Diagnostic Verification
To ensure that configuration changes take effect, DBAs must inspect server startup logs rather than relying on assumptions. By temporarily adjusting the logging verbosity:
log_min_messages = debug2Administrators can examine the startup logs for explicit confirmation lines:
max_safe_fds = N, usable_fds = N, already_open = N
Additionally, inspecting /proc/<postmaster_pid>/limits reveals the exact operational constraints imposed on the postmaster process by the operating system.
Official Responses and Documentation Guidance
Official PostgreSQL documentation has historically cautioned administrators against blindly inflating file descriptor settings, warning that excessive allocations can exhaust system-wide resources. However, modern infrastructure context requires a nuanced interpretation of these warnings.
Balancing System-Wide vs. Process-Level Limits
The documentation traditionally advises: "If you find yourself seeing ‘Too many open files’ failures, try reducing this setting."
This guidance addresses rare edge cases on operating systems where individual processes could request more files than the total machine capacity could support if multiple sessions peaked simultaneously. On modern Linux distributions running contemporary kernels, fs.file-max mitigates this concern.
When a genuine system-wide exhaustion occurs, logs will explicitly display:
out of file descriptors: Too many open files in system; release and retry
This indicates that the kernel rejected a request, forcing PostgreSQL to evict descriptors and retry. In such scenarios, the entire host is running dry on file handles—an issue typically rooted outside PostgreSQL proper.
Implications: Best Practices for Database Administrators
Navigating file descriptor optimization in PostgreSQL 18 requires moving away from table-centric assumptions toward comprehensive database-wide auditing.
1. Count the Files, Not the Tables
To determine whether default settings suffice, administrators should query the filesystem directly rather than counting logical tables:
ls $PGDATA/base/<dboid> | wc -l- Low Thousands: If the file count remains in the low thousands, the default settings (
max_files_per_process = 1000) are more than adequate. Further tuning offers negligible returns. - Tens of Thousands: If the database contains tens of thousands of relation files, partitions, and indexes, immediate configuration adjustments are mandatory.
2. The Modern Tuning Checklist
For high-density database environments running PostgreSQL 18, administrators must execute the following protocol:
- Adjust Systemd Limits: Modify the systemd service configuration (via a drop-in file) to elevate the soft and hard limits:
[Service] LimitNOFILE=65536 - Tune the Parameter: Set
max_files_per_processhigh enough to comfortably encompass the aggregate working set of background I/O workers and concurrent backends (e.g.,8192or higher depending on partition scale). - Restart and Verify: Restart the PostgreSQL service and inspect the logs for
debug2output confirming thatmax_safe_fdssuccessfully scaled to the requested level.
By aligning operating system constraints with PostgreSQL 18’s advanced asynchronous architecture, database administrators can eliminate hidden I/O bottlenecks, ensure seamless query execution, and future-proof their data infrastructure against scale-induced performance degradation.
