BUG #19622: io_method=worker retains file descriptors on dropped relations

The following bug has been logged on the website:

Bug reference: 19622
Logged by: SeongHan Jeong
Email address: sarangsai(at)gmail(dot)com
PostgreSQL version: 18.6
Operating system: Debian Linux aarch64 (official postgres:18 Docker)
Description:

SUMMARY
=======

With io_method=worker (the default in PostgreSQL 18), AIO I/O worker
processes retain open file descriptors on relation files that have been
dropped. After the files are unlinked -- for the first segment this
happens at the first checkpoint after DROP -- /proc/<pid>/fd shows io
workers still holding descriptors on them:

lrwx------ 1 postgres 64 ... 29 ->
/var/lib/postgresql/18/docker/base/5/16388 (deleted)
lrwx------ 1 postgres 64 ... 30 ->
/var/lib/postgresql/18/docker/base/5/16393 (deleted)

The retention unit is the relation *file*, not the relation: each io
worker keeps one descriptor for every segment it actually opened. A
single dropped 2826 MB table -- three main-fork segments -- left nine
descriptors behind on a default three-worker cluster, verified below.
It is not a leak in the malloc sense -- these are ordinary VFD cache
entries -- but unlike regular backends, io workers have no invalidation
path that drops them when the relation goes away, so the set grows
monotonically for the life of the worker.

Regular backends do not accumulate them: they receive SMGR invalidation
via the shared invalidation queue and call smgrclose(). io workers are
auxiliary processes and never register as invalidation receivers, so
that message never reaches them.

The two release paths that do exist for io workers are both incidental
to DROP TABLE:

a) the VFD LRU, once the worker reaches its effective descriptor
limit (fd.c max_safe_fds, enforced by ReleaseLruFiles()), and
b) PROCSIGNAL_BARRIER_SMGRRELEASE, which io workers do process --
but which is only emitted by tablespace- and database-level
operations, never by DROP TABLE.

So under a workload of repeated relation drops, retention is bounded
only by the worker's effective file-descriptor limit. In production we
observed ~1280
descriptors on unlinked files across 3 io workers after 61 days of
daily partition drops, still growing linearly. Details below.

VERSIONS AND ENVIRONMENT
========================

Primary reproduction:

PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2), aarch64, docker postgres:18
image defaults except the flags shown in each test below.

Also reproduced:

- macOS (Homebrew PostgreSQL 18.6). Same accumulation. Detection
there is via lsof plus a path-existence check, since darwin lsof
does not print a "(deleted)" suffix.
- PostgreSQL 19beta3 (Debian 19~beta3-1.pgdg13+1): same linear
accumulation under a continuous workload (300 drops -> 600
descriptors across 2 workers). Note that the new dynamic worker
pool does not mitigate this by default: the io_min_workers=2
workers stay resident regardless of io_worker_idle_timeout. After
the workload stopped, both resident workers still held 300 each
after 51 minutes of idleness.

We also observed it on 18.4 in production (see Impact). 18.6 and
19beta3 are the current releases of their branches at the time of
writing.

The behavior should apply to every 18.x, i.e. since the io_method=worker
introduction (commit 247ce06b883d7b3a40d08312dc03dfb37fbff212). On
REL_18_STABLE, method_worker.c has not been touched since 2025-08-21,
which predates the 18.0 release on 2025-09-25, so the worker main loop
and its pgaio_io_reopen() call are byte-identical from 18.0 through
18.6. smgr.c has had one post-18.0 change on that branch, 9ed411e
(2026-01-15, "Fix 'unexpected data beyond EOF' on replica restart"),
which adjusts a cached block count in the truncation path and does not
touch smgr_aio_reopen(), smgrfd(), or descriptor lifecycle.

REPRODUCTION
============

Deterministic, about one minute:

docker run -d --name pg18repro -e POSTGRES_PASSWORD=pw postgres:18 \
-c io_method=worker -c shared_buffers=16MB \
-c effective_io_concurrency=32

Then, inside the container as the postgres user, run the script at the
end of this mail (repro_worker_fd_leak.sh). It performs N iterations of
CREATE TABLE -> INSERT ~24MB -> seq scan -> DROP TABLE, issues a
CHECKPOINT, and counts descriptors on unlinked files per io worker.

Settings for the run below (image defaults plus the flags above):

SELECT version() -> PostgreSQL 18.6
(Debian 18.6-1.pgdg13+2), aarch64
SHOW io_method -> worker
SHOW io_workers -> 3
SHOW max_files_per_process -> 1000
SHOW shared_buffers -> 16MB
SHOW effective_io_concurrency-> 32
ulimit -n (soft) -> 1024 (docker default)

Result, after 20 drops:

dropped_tables=20
pid=67 (postgres: io worker 0): deleted_relation_fds=20
pid=68 (postgres: io worker 1): deleted_relation_fds=20
pid=69 (postgres: io worker 2): deleted_relation_fds=20

Two conditions matter:

- The table must exceed shared_buffers, so that the scan actually
issues physical AIO reads. Tables that fit in shared_buffers show
no accumulation, because the worker path never executes.
- Only relation files a given worker actually read are retained by
that worker. DROP itself does not create the descriptor.

The table used here is about 24 MB, i.e. a single segment, which is why
this test yields exactly one descriptor per drop per worker. See the
multi-segment section below for the general case.

Relations in user-defined tablespaces behave the same way. Note that
/proc fd targets resolve to the external directory
(/mnt/.../PG_18_*/dboid/relnode), not to the pg_tblspc symlink path, so
an audit has to follow the pg_tblspc/* symlinks.

Dropped range partitions reproduce it identically, including the
DETACH-then-DROP variant, which matches our production workload: with a
parent scanned via SELECT ... FROM parent, two DROP TABLE partition plus
one ALTER TABLE ... DETACH PARTITION followed by DROP TABLE added
exactly 3 descriptors per io worker (100 -> 103). Those partitions were
single-segment; larger ones scale as described next.

MULTI-SEGMENT RELATIONS
=======================

Because PostgreSQL splits a relation fork into 1 GB segment files, the
per-drop cost is proportional to the number of segments the worker
touched, not to the number of relations. Verified directly:

CREATE UNLOGGED TABLE leak_big(i bigint, pad text)
WITH (autovacuum_enabled=off);
INSERT INTO leak_big
SELECT g, repeat(md5(g::text),4) FROM generate_series(1,17000000) g;
-- pg_relation_size = 2826 MB, relfilenode 16388, database oid 5
SELECT count(*) FROM leak_big; -- 17000000
DROP TABLE leak_big;
CHECKPOINT;

On-disk before the drop:

1073741824 base/5/16388
1073741824 base/5/16388.1
815587328 base/5/16388.2
745472 base/5/16388_fsm
0 base/5/16388_init

After DROP plus CHECKPOINT, with io_workers=3 (default):

pid=66 (io worker 0): base/5/16388 (deleted)
base/5/16388.1 (deleted)
base/5/16388.2 (deleted)
pid=67 (io worker 1): same three
pid=68 (io worker 2): same three

So one dropped relation left 9 descriptors on unlinked files, not 3.
Only the main-fork segments were retained -- the _fsm and _init forks
were not read through AIO by the sequential scan in this test, so the
precise rule is one descriptor per relation file a worker actually
opened.

This matters for sizing the effect on real workloads: a daily drop of a
10 GB partition on a default three-worker cluster leaves on the order of
30 descriptors, not 3. It also makes our production numbers below
coherent -- roughly 7 new descriptors per worker per day there
corresponds to a small number of multi-GB partitions, not to seven
relations.

The zero-length property still holds at this scale. stat() through
/proc/<pid>/fd on all nine retained descriptors reports size 0, and the
2826 MB was fully returned to the filesystem, confirming that the
retained descriptors pin inodes but no data blocks.

SCALING WITH io_workers
=======================

100 drops each, io_workers varied, everything else default:

io_workers=1: total=100 (100)
io_workers=2: total=200 (100/100)
io_workers=3: total=300 (100/100/100)
io_workers=4: total=399 (100/100/99/100)

The 399 rather than 400 with io_workers=4 is consistent with one worker
not having serviced I/O for one of the relations. (The table used here
is single-segment, so relations and files are one-to-one in this test.)
Overall the totals match each io worker independently populating its own
smgr hash and VFD table for every relation file it reads.

PERSISTENCE WHILE IDLE
======================

On 18.6 the worker pool is static. After the workload stopped, io
workers held their descriptors unchanged for the full observation
window: 30 minutes idle, count constant at 1205 per worker. The worker
main loop waits on WaitLatch() with an infinite timeout
(method_worker.c), so there is no timer -- LRU or otherwise -- that
would close them while idle. The production observation below extends
this to 61 days.

BOUNDED VS UNBOUNDED GROWTH
===========================

1200 drops of a 4MB UNLOGGED table with shared_buffers=1MB, census every
200 drops. (The absolute counts below start from a residual of roughly
10-20 descriptors per worker left over from the setup phase of the same
container; the slope, not the intercept, is the point.)

With default limits (max_files_per_process=1000, container
RLIMIT_NOFILE=1024):

iter total per worker
900 2740 920/910/910 <- linear, +1 per drop per worker
1000 2902 958/976/968 <- VFD LRU cap reached
1100 2902 958/976/968 <- plateau; LRU now closes oldest 1:1

The per-worker plateau of roughly 960-980 is max_safe_fds, which fd.c
set_max_safe_fds() computes as
Min(usable_fds, max_files_per_process) - NUM_RESERVED_FDS
(fd.c lines 1061 and 1066), less the descriptors the worker is using for
other purposes.

With limits raised (max_files_per_process=8192, ulimit -n 65535), the
same workload shows no plateau:

iter total per worker
1000 3000 1000/1000/1000
1200 3600 1200/1200/1200

No EMFILE or other errors occurred in either configuration. The
effective ceiling is therefore max_safe_fds -- whichever of
RLIMIT_NOFILE and max_files_per_process yields the lower per-process
limit, less reserved descriptors. That is, retention is bounded by the
descriptor budget rather than by any invalidation.

COMPARISON ACROSS io_method
===========================

Identical environment and workload for all three methods: postgres:18,
shared_buffers=16MB, effective_io_concurrency=32,
max_files_per_process=8192, RLIMIT_NOFILE=65535, io_workers=3, 100
iterations of CREATE -> INSERT ~24MB -> seq scan -> DROP, with a
CHECKPOINT and a census across *all* postgres processes every 25
iterations:

io_method=worker
iter total holders
25 93 io worker 0=25, 1=25, 2=23; background writer=20
50 163 io worker 0=50, 1=50, 2=48; background writer=15
75 235 io worker 0=75, 1=75, 2=73; background writer=12
100 310 io worker 0=100, 1=100, 2=98; background writer=12

io_method=io_uring
25 16 background writer=16
50 10 background writer=10
75 11 background writer=11
100 17 background writer=17

io_method=sync
25 0 (none)
50 13 background writer=13
75 13 background writer=13
100 9 background writer=9

Two things follow:

1. The growth is specific to io_method=worker. io_uring performs the
I/O in the issuing backend, which does receive SMGR invalidation,
so no separate process accumulates anything.

2. The background writer holds a small, bounded, non-growing set in
all three methods -- this is long-standing behavior, unrelated to
AIO, and it stays flat. Backends and the checkpointer held none in
any of these runs. The qualitative difference is that the io
worker counts track the drops -- one per relation file, so
one-for-one here because this table is single-segment -- and do
not come back down.

ANALYSIS (REL_18_6 source)
==========================

1. src/backend/storage/aio/method_worker.c, IoWorkerMain(): every IO is
preceded by pgaio_io_reopen(ioh) (line 530).

2. src/backend/storage/smgr/smgr.c, smgr_aio_reopen() (line 1064) calls
smgropen() (line 1083) and smgrfd() (lines 1090/1094). This
populates the *worker process's own* smgr hash and VFD table, and the
descriptor stays in the worker's VFD pool after the IO completes.
smgrfd() reaches md.c mdfd() (line 1484), which calls _mdfd_getseg()
(line 1744); that opens and caches one VFD per segment in the
per-fork md_seg_fds array (see the comment at md.c line 68). This is
why the retention is per relation file rather than per relation.

3. io workers never receive SMGR invalidation. The only callers of
SharedInvalBackendInit() are src/backend/utils/init/postinit.c
line 750, for regular backends, and src/backend/storage/ipc/standby.c
line 125, for the startup process (send-only). io workers are
auxiliary processes initialized through
src/backend/postmaster/auxprocess.c, which does not call it, so they
are never entered into the sinval receiver set and messages are never
queued for them. (The "except for IO workers" comment at
src/backend/storage/ipc/sinvaladt.c line 202 concerns only the
one-instance-per-auxiliary-type sizing assumption, not delivery.)

4. io workers *do* handle PROCSIGNAL_BARRIER_SMGRRELEASE: they call
ProcSignalInit() (auxprocess.c line 69) and reach
CHECK_FOR_INTERRUPTS() in their main loop (method_worker.c line 576),
which dispatches through procsignal.c line 585 to
ProcessBarrierSmgrRelease() -> smgrreleaseall() (smgr.c line 1027).
That barrier is, however, only emitted for tablespace- and
database-level operations (commands/tablespace.c lines 515 and 1530,
commands/dbcommands.c lines 1876, 2127 and 3377). DROP TABLE does
not emit it.

5. The remaining release path is the VFD LRU: once the worker reaches
max_safe_fds, fd.c ReleaseLruFiles() (line 1404) closes the least
recently used descriptors -- which is what produces the plateau
above. Its loop condition is purely a count against max_safe_fds
(line 1410); it is oblivious to whether the underlying file still
exists, so stale and live entries compete on equal terms.

IMPACT
======

This is not a crash or a security issue, and it does not hold disk
space: mdunlink() truncates the first segment to zero length before
deferring the unlink (md.c line 382, do_truncate() plus
register_forget_request()), and additional segments are truncated before
being unlinked, so the retained descriptors all refer to zero-length
files. We confirmed this empirically at multi-GB scale in the
multi-segment test above: all nine retained descriptors reported size 0
and the full 2826 MB was returned to the filesystem.

What it does cause:

- Long-lived kernel references (open file descriptions, inodes) to
dropped relations, for the life of the io worker.
- Inflated file descriptor counts and persistent "lsof | grep
deleted" noise, which makes monitoring for genuine descriptor
problems harder.
- A concern we want to flag but have *not* measured: stale entries
occupy slots in the worker's VFD cache, and ReleaseLruFiles() does
not distinguish live from unlinked files, so on a busy system the
stale set can evict entries for live relations and cause additional
reopen syscalls. We have no benchmark for this and are not
claiming a measurable regression -- only that the eviction policy
makes it possible in principle.

Production observation (anonymized): PostgreSQL 18.4 on Ubuntu 24.04
x86_64, io_method=worker (default), 3 io workers up for 61 days,
workload is daily partition drops of multi-GB, multi-segment relations
across two user-defined tablespaces. Census taken as the postgres OS
user, following pg_tblspc symlinks:

pid=1542995 (io worker 0): 402
pid=1542996 (io worker 1): 439
pid=1542997 (io worker 2): 439

That is roughly 1280 descriptors on unlinked relation files after two
months, growing at about 7 per day per worker. Given the per-file
behavior established above, that rate corresponds to a small number of
multi-GB, multi-segment partitions per day rather than to seven
relations, which is consistent with the actual workload. No other
process class showed comparable accumulation.

PRIOR DISCUSSION
================

The closest precedent we found is Tom Lane's 2004-02-05 thread "It's
past time to redo the smgr API"
(message-id 5416(dot)1076007946(at)sss(dot)pgh(dot)pa(dot)us), which describes exactly this
class of problem for the then-new background writer:

"Because we don't smgrclose after a write, it is possible to have
'dangling' smgr entries that aren't useful any more, as well as open
file descriptors underneath them."

and proposes the two-part fix that is still the shape of the current
code:

"1. In the bgwriter, at each checkpoint do 'smgrcloseall' to close all
open files.
2. In regular backends, receipt of a relcache flush message will
result in smgrclose() [...]"

io workers currently get neither half of that: no periodic
smgrcloseall(), and no invalidation delivery. As far as we can tell
this specific case has not been reported before.

EXISTING TEST COVERAGE
======================

We looked at why this would not have been caught in-tree. As of
REL_18_6, nothing under src/test, src/tools or contrib/*/t inspects
descriptor state: there are no references to lsof, /proc/<pid>/fd,
RLIMIT_NOFILE or max_files_per_process anywhere in the test suite. That
seems reasonable rather than an oversight -- /proc is Linux-only and
lsof is not a build dependency, so a portable in-tree assertion about
open descriptors is awkward to write.

The nearest existing coverage is
src/test/modules/test_aio/t/001_aio.pl:

- test_invalidate() (line 899, run for all three io_methods via
test_generic() at line 1523) is documented as "Verify that we handle
a relation getting removed (due to a rollback or a DROP TABLE) while
IO is ongoing for that table." It asserts only that the statements
produce empty stdout and stderr, i.e. that nothing errors or
crashes, and never looks at descriptors afterwards. Its table is
about 2 MB and it forces a single low-level block read via
read_rel_block_ll() rather than running a scan, so the retention
described here is invisible to it by construction. We are pointing
this out only to note that the two are orthogonal, not to suggest
that test is wrong -- it covers correctness *during* the drop, while
this report is about retention *after* it.

- test_inject_worker() (line 861) does exercise the worker reopen
path, but only its failure case, through the existing
"aio-worker-after-reopen" injection point (method_worker.c line
536).

src/test/modules/test_aio/t/002_io_workers.pl covers only io_workers
count management and SIGINT termination.

If a regression test for this is wanted, the pieces are mostly there --
pg_stat_activity gives the io worker pids and the injection point above
already hooks the reopen path -- but a portable assertion would probably
need the worker's open-segment count exposed from inside the backend
rather than read from /proc. We are happy to help with that if it is
the direction people want.

POSSIBLE FIX DIRECTIONS (for discussion)
========================================

1. Have io workers call smgrcloseall() (or smgrreleaseall()) when they
go idle, or on some bounded schedule -- the direct analogue of the
bgwriter's checkpoint-time smgrcloseall(). Cheap and simple, at the
cost of some reopens after an idle period.

2. Extend PROCSIGNAL_BARRIER_SMGRRELEASE, or add a narrower relation-
scoped equivalent, so that relation drops also reach auxiliary
processes. io workers already handle the barrier, so the delivery
machinery exists; the question is the cost of a barrier per DROP.

3. Have io workers participate in shared invalidation. This is the most
precise fix but the largest change, since it means giving an
auxiliary process an invalidation slot and catchup handling.

4. Have smgr_aio_reopen() close the descriptor after the IO completes.
Correct but presumably too expensive, as it reopens on every IO.

We do not have a strong opinion on which is right; (1) looks like the
smallest change that removes the unbounded behavior. We are happy to
test any patch against both the reproducer and the production workload.

WORKAROUND
==========

io_method=io_uring or io_method=sync. Both were verified above to avoid
the growth under an identical workload. A small, bounded set of
descriptors on unlinked files remains in the background writer in every
mode, but it does not grow.

REPRODUCER SCRIPT
=================

#!/bin/sh
# Reproducer: PG18 io_method=worker retains fds on dropped relations.
# Verified on PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2), Linux aarch64.
# Run inside a postgres:18 container as the postgres user:
# sh repro_worker_fd_leak.sh [iterations]
set -e
PSQL="psql -U postgres -X -q -v ON_ERROR_STOP=1"
PGDATA=$(psql -U postgres -Atc "SHOW data_directory;")
ITER=${1:-20}

i=0
while [ $i -lt $ITER ]; do
i=$((i+1))
$PSQL -c "CREATE TABLE leak_t(i int, pad text) WITH
(autovacuum_enabled=off)" \
-c "INSERT INTO leak_t SELECT g, repeat(md5(g::text),4) FROM
generate_series(1,180000) g" \
-c "SELECT sum(length(pad)) FROM leak_t" \
-c "DROP TABLE leak_t" >/dev/null
done
$PSQL -c "CHECKPOINT"

echo "dropped_tables=$ITER"
echo "--- fds held on unlinked relation files, per io worker ---"
for p in /proc/[0-9]*; do
cmd=$(tr '\0' ' ' 2>/dev/null < "$p/cmdline" || true)
case "$cmd" in
*"io worker"*)
pid=${p#/proc/}
del=$(ls -l "$p/fd" 2>/dev/null | grep -F "$PGDATA/base" \
| grep -c '(deleted)' || true)
echo "pid=$pid ($cmd): deleted_relation_fds=$del"
;;
esac
done
echo "--- sample ---"
ls -l /proc/$(pgrep -f 'io worker 0' | head -1)/fd 2>/dev/null \
| grep '(deleted)' | head -5