Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19622: io_method=worker retains file descriptors on dropped
 relations
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: sarangsai@gmail.com
Reply-To: sarangsai@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Sun, 16 Aug 2026 18:00:13 +0000
Message-ID: <19622-639a4ba94c5a53d7@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19622-639a4ba94c5a53d7%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

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

SUMMARY
=3D=3D=3D=3D=3D=3D=3D

With io_method=3Dworker (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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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=3D2
    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=3Dworker
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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Deterministic, about one minute:

  docker run -d --name pg18repro -e POSTGRES_PASSWORD=3Dpw postgres:18 \
      -c io_method=3Dworker -c shared_buffers=3D16MB \
      -c effective_io_concurrency=3D32

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=3D20
  pid=3D67 (postgres: io worker 0): deleted_relation_fds=3D20
  pid=3D68 (postgres: io worker 1): deleted_relation_fds=3D20
  pid=3D69 (postgres: io worker 2): deleted_relation_fds=3D20

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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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=3Doff);
  INSERT INTO leak_big
    SELECT g, repeat(md5(g::text),4) FROM generate_series(1,17000000) g;
  -- pg_relation_size =3D 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=3D3 (default):

  pid=3D66 (io worker 0):  base/5/16388   (deleted)
                         base/5/16388.1 (deleted)
                         base/5/16388.2 (deleted)
  pid=3D67 (io worker 1):  same three
  pid=3D68 (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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

100 drops each, io_workers varied, everything else default:

  io_workers=3D1: total=3D100  (100)
  io_workers=3D2: total=3D200  (100/100)
  io_workers=3D3: total=3D300  (100/100/100)
  io_workers=3D4: total=3D399  (100/100/99/100)

The 399 rather than 400 with io_workers=3D4 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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D

1200 drops of a 4MB UNLOGGED table with shared_buffers=3D1MB, 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=3D1000, container
RLIMIT_NOFILE=3D1024):

  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=3D8192, 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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D

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

  io_method=3Dworker
    iter  total  holders
    25    93     io worker 0=3D25, 1=3D25, 2=3D23; background writer=3D20
    50    163    io worker 0=3D50, 1=3D50, 2=3D48; background writer=3D15
    75    235    io worker 0=3D75, 1=3D75, 2=3D73; background writer=3D12
    100   310    io worker 0=3D100, 1=3D100, 2=3D98; background writer=3D12

  io_method=3Dio_uring
    25    16     background writer=3D16
    50    10     background writer=3D10
    75    11     background writer=3D11
    100   17     background writer=3D17

  io_method=3Dsync
    25    0      (none)
    50    13     background writer=3D13
    75    13     background writer=3D13
    100   9      background writer=3D9

Two things follow:

  1. The growth is specific to io_method=3Dworker.  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)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D

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
=3D=3D=3D=3D=3D=3D

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=3Dworker (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=3D1542995 (io worker 0): 402
  pid=3D1542996 (io worker 1): 439
  pid=3D1542997 (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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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.1076007946@sss.pgh.pa.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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

io_method=3Dio_uring or io_method=3Dsync.  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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

#!/bin/sh
# Reproducer: PG18 io_method=3Dworker 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=3D"psql -U postgres -X -q -v ON_ERROR_STOP=3D1"
PGDATA=3D$(psql -U postgres -Atc "SHOW data_directory;")
ITER=3D${1:-20}

i=3D0
while [ $i -lt $ITER ]; do
    i=3D$((i+1))
    $PSQL -c "CREATE TABLE leak_t(i int, pad text) WITH
(autovacuum_enabled=3Doff)" \
          -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=3D$ITER"
echo "--- fds held on unlinked relation files, per io worker ---"
for p in /proc/[0-9]*; do
    cmd=3D$(tr '\0' ' ' 2>/dev/null < "$p/cmdline" || true)
    case "$cmd" in
        *"io worker"*)
            pid=3D${p#/proc/}
            del=3D$(ls -l "$p/fd" 2>/dev/null | grep -F "$PGDATA/base" \
                  | grep -c '(deleted)' || true)
            echo "pid=3D$pid ($cmd): deleted_relation_fds=3D$del"
            ;;
    esac
done
echo "--- sample ---"
ls -l /proc/$(pgrep -f 'io worker 0' | head -1)/fd 2>/dev/null \
    | grep '(deleted)' | head -5