MIME-Version: 1.0
References: <013D63E2-5D75-492E-85FF-1D5CC0148C82@gmail.com>
 <CAJ7c6TMYvYBQpgv8k+4QnajqPcroOzrj7d3snBBTKX4tHQfVYw@mail.gmail.com>
 <499686.1759250489@sss.pgh.pa.us>
 <68dcd1f2.df0a0220.3300c0.f7af@mx.google.com>
 <CAKZiRmwHKY=KTjBEL3S2cVQpo1OjHyky4BdgJm4Hkv1-ig9PfQ@mail.gmail.com>
 <CAJ7c6TMCyhX4Z7FrS=ORCcKS1F_iZzfUuQM3shx64WKynoxjsA@mail.gmail.com>
 <CAKZiRmy0CK3m0-raCdTDELg0JjY7qAqzEN9P5n4N4wGw6ys4tw@mail.gmail.com>
 <68dd1b79.170a0220.3c4175.198f@mx.google.com>
In-Reply-To: <68dd1b79.170a0220.3c4175.198f@mx.google.com>
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Mon, 6 Oct 2025 11:19:41 +0200
Message-ID: 
 <CAKZiRmzGgOY5iQ=CFHJVE2df2Bz599bACqoWP3V_e-3Rw+XJhA@mail.gmail.com>
Subject: Re: The ability of postgres to determine loss of files of the main
 fork
To: Michael Banck <mbanck@gmx.net>
Cc: Aleksander Alekseev <aleksander@tigerdata.com>,
 pgsql-hackers@lists.postgresql.org,
	Tom Lane <tgl@sss.pgh.pa.us>, Frits Hoogland <frits.hoogland@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAKZiRmzGgOY5iQ%3DCFHJVE2df2Bz599bACqoWP3V_e-3Rw%2BXJhA%40mail.gmail.com>
Precedence: bulk

On Wed, Oct 1, 2025 at 2:15=E2=80=AFPM Michael Banck <mbanck@gmx.net> wrote=
:

> > > That might be a lot of files to open.
> >
> > I was afraid of that, but let's say modern high-end is 200TB big DB,
> > that's like 200*1024 1GB files, but I'm getting such time(1) timings
> > for 204k files on ext4:
> >
> > $ time ./createfiles                      # real    0m2.157s, it's
> > open(O_CREAT)+close()
> > $ time ls -l many_files_dir/ > /dev/null # real    0m0.734s
> > $ time ./openfiles                          # real    0m0.297s , for
> > already existing ones (hot)
> > $ time ./openfiles                          # real    0m1.456s , for
> > already existing ones (cold, echo 3 > drop_caches sysctl)
> >
> > Not bad in my book as a one time activity. It could pose a problem
> > potentially with some high latency open() calls, maybe NFS or
> > something remote I guess.
>
> Yeah, did you try on SAN as well? I am doubtful that will be performant.


OK you I guess you wanted to have some latency + big fs size, so I do
not have real SAN here, but I've tried that on noisy Azure's Cloud 4x
disk Standard __HDD__ (probably SSD/flash but just throttled way
down). Each LUN of size: 32TB (max), giving me in total 128TB striped
fs:
- lvcreate -L 128G -n lv -i 4 -I 1M vgbig + mkfs.xfs
- ioping reports there the following latencies for Direct IO:
min/avg/max/mdev =3D 3.25 ms / 18.0 ms / 193.8 ms / 39.5 ms (yay!)
- so as per above it's doesn't have low latency at all
- simple fio (fio --name=3Drand_read_iops
--filename=3D/xfs/many_files_dir/test1 --rw=3Drandread --bs=3D4k --direct=
=3D1
--ioengine=3Dio_uring --iodepth=3D64 --size=3D4G --runtime=3D60 --time_base=
d
--group_reporting) reports just:  IOPS=3D959, BW=3D3837KiB/s

Yet, to create/open 204k empty files to simulate that 200TB cluster on
that 128TB fs:

$ time ./createfiles                            # real 0m2.140s
$ time ls -l /xfs/many_files_dir/ > /dev/null  # real 0m0.697s
$ time ./openfiles                             # real 0m0.440s (hot)
$ time ./openfiles                               # real 0m29.872s
(after 3 to drop_caches)
$ time ./openfiles                             # real 0m0.443s (hot)
$ time ./openfiles                               # real 0m31.050s
(after 3 to drop_caches)

$ echo 3 > /proc/sys/vm/drop_caches
$ time ls -l /xfs/many_files_dir/ > /dev/null  # real 1m2.345s
$ time ./openfiles                             # real 0m0.437s (hot)

Anyway, I do not know if opening all the files on startup (or just
crash-recovery?) is the proper way, personally I would take +30s to
open a 200TB database any day over the risk of silent data loss and
wrong results given the option. I'm sure however that ignoring missing
files on access is certainly not how things should be looking like and
I would speculate that you might even get some accidents that files
may go missing when up & running and you won't be notified in any way
(due to human error: imagine someone rsyncing the wrong [opposite]
direction he wanted by accident or something like that).

-J.