max_files_per_process is not a limit in the sense most people mean, where going over it produces an error. It is the size of a cache. Every backend keeps a pool of open kernel file descriptors, and when the pool is full and the backend needs one more, it closes the least recently used one and carries on. Nothing fails. The query just spends a couple of extra system calls on a file it had open thirty seconds ago.
The default is 1000, the range is 64 to 2,147,483,647, and the context is postmaster, so changing it means a restart. What it actually sets is a ceiling on a number the postmaster works out for itself at startup: it duplicates a file descriptor over and over until either the kernel refuses or it reaches max_files_per_process, takes the smaller of the two, subtracts ten as slop for code that opens files without telling fd.c, and stores the result as max_safe_fds. Every child process inherits that number. If it comes out below 48 the postmaster refuses to start (insufficient file descriptors available to start server process, with a detail line telling you how many the system allowed and how many it wanted), which is what ulimit -n 60 gets you.
Everything a backend touches on disk goes through that pool: every fork of every relation, every 1GB segment of anything larger than that, every temporary file. A table with three indexes is four files before you count its free space map and visibility map; a partitioned table with 1,500 partitions and one index each is 3,000 files before you count anything. That is the situation the default is too small for, and the symptom is not an error message. I built a 3,000-partition table (6,000 relation files) on 18.6 and traced a backend running repeated sequential scans of it, everything in shared_buffers. At the default, every scan re-opened every partition, two or three times over: the backend never read the file at all, it opened it to ask the kernel how long it was, closed it because the pool had cycled through the other 2,999 in the meantime, and a moment later opened it again to ask the same question. At max_files_per_process = 8192 the backend held 6,054 descriptors, made no open() calls, and the scan ran about 8% faster (165ms against 180ms). An open() on Linux costs a couple of microseconds. Six to nine thousand of them per query is where it adds up.
The limit under the limit
Here is the part the internet gets wrong. The postmaster’s probe stops at the process’s soft RLIMIT_NOFILE, the number ulimit -n reports. systemd starts services with a soft limit of 1024 (the hard limit is 524,288), and neither Debian’s postgresql@.service nor the PGDG RPM unit changes it. So on a stock package install the kernel’s number and the parameter’s number are almost the same by coincidence, and raising the parameter does nothing. I set max_files_per_process = 4096 under a 1024 soft limit and got max_safe_fds = 1010; under a 20,000 soft limit the same setting gave 4086. If you raise this parameter, raise LimitNOFILE= in a systemd drop-in first (or whatever your init system’s equivalent is), or you have edited a config file and changed nothing. To see what you actually got, start once with log_min_messages = debug2 and look for max_safe_fds = N, usable_fds = N, already_open = N in the log; /proc/<postmaster pid>/limits tells you what the postmaster was allowed.
The docs’ own advice runs the other direction: “If you find yourself seeing ‘Too many open files’ failures, try reducing this setting.” That is about the other limit, the system-wide one, on kernels (the docs say most BSDs) that will let each process open far more files than the machine can hold if enough processes try at once. On Linux under systemd 240 or later, fs.file-max is bumped to its maximum at boot and this is mostly a historical concern, but the tell is in the log either way: out of file descriptors: Too many open files in system; release and retry means the kernel said no, PostgreSQL evicted a descriptor and tried again, and you are running the whole box out of file handles. Each open file costs the kernel a couple of hundred bytes and the worst case is max_connections times this parameter, so it can be done. That is the case for lowering it, and it’s rare.
What 18 changed
Two things, both consequences of asynchronous I/O (the tour is in io_max_concurrency). The first is the definition. Through 17, files the postmaster already had open, and every child therefore inherited, were subtracted from max_files_per_process before the probe ran. That was harmless when the postmaster held nine descriptors. With io_method = io_uring, the postmaster creates one ring per process slot at startup, and each ring is a file descriptor: with max_connections = 300 mine held 342 of them, and under the old arithmetic you would have been left with a pool of 644 without touching anything. In 18 the parameter counts files a process opens beyond what it inherited, and the same configuration gives max_safe_fds = 990. The rings still count against the OS limit, though. Same configuration under a 1024 soft limit: 668. Push max_connections to 1000 under that limit and the postmaster won’t start at all (could not setup io_uring queue: Too many open files, with a hint telling you exactly what to set ulimit -n to). io_uring makes the drop-in mandatory rather than advisable.
The second is quieter. The default io_method in 18 is worker, and the three (by default) I/O workers perform the asynchronous reads (sequential scans, bitmap heap scans, VACUUM, ANALYZE) on behalf of every backend. Each worker has its own descriptor pool of the same size, and its working set is the union of every backend’s. Tracing a cold-cache scan of the same table, worker 0 opened about 2,900 files on the way through; at the default it finished holding 993 of them, which is the pool plus its own plumbing, and at 8192 it held all 2,888. A pool that is comfortably larger than any one session’s working set can still be smaller than the workers’. On 18 the number to size for is not what one session touches. It’s what the server touches.
For completeness: exactly one error in the server mentions this parameter by name, and it isn’t about relation files. Backend-side libpq connections (postgres_fdw, dblink, logical replication workers) each reserve a descriptor from the same pool, capped at a third of it, so at the default one backend gets about 330 of them before could not establish connection with a hint to raise max_files_per_process and/or ulimit -n. If you have hit that, you have my sympathy and a different problem.
So: count the files, not the tables. ls $PGDATA/base/<dboid> | wc -l is the honest number, and on 18 it’s the number for the whole database rather than one session’s slice of it. If it’s in the low thousands, the default is fine and you have now spent more time on this parameter than the descriptors are worth. If it’s in the tens of thousands, set LimitNOFILE=65536, set max_files_per_process to cover the working set with room to spare, restart, and read the debug2 line to confirm you got what you asked for. The only reason to lower it is a log full of Too many open files in system, and on a Linux box built this decade that means something else went wrong first.