Re: Potential "AIO / io workers" inter-worker locking issue in PG18?

Hi Thom,

I now also witnessed this issue with "io_method = sync", so it may not
have relation with the number of workers set. I initially thought it did
not occur with 'sync', as two runs successfully completed without
delays, however, the last did show the issue. Unfortunately, this is a
very complex multi-stage geoprocessing workflow, that cannot easily be
cut down to a simple one SQL statement reproducible case. And for the
specific Italy extract it takes about 7 hours to complete if the run is
successful and without the delays observed, so each test run costs
considerable time if I adjust anything.

There is also a PostGIS upgrade in the mix (3.5.2 to 3.6.0) that may or
may not be involved, as that version of PostGIS is the minimum for PG18.
I see a 3.6.1 is already planned and will need to re-test with that
version once released. I definitely do use PostGIS functions at the
stage the processing gets heavily delayed.

As to the question about the pg_aios view I wasn't aware off: it appears
to be empty at that point, but I will need to confirm that observation,
as with my last run, the moment I looked at the view, some of the very
delayed multi-threaded jobs (> 6.5 hours instead of 10 seconds!) started
slowly returning one by one, although some were still in wait / stuck
for some time before all had returned, so the pg_aios view being empty
probably is still representative of the stuck situation.

Also note that I also adjust the storage parameters of the tables
involved to force a more aggressive vacuuming to avoid transaction ID
wraparound (which shouldn't be an issue anyway with the small test Italy
extract). This has all proven pretty reliable in the past and with
previous PostgreSQL / PostGIS releases, up to the Facebook Daylight
multi-billion record tables as noted in the previous post. There also is
no PostgreSQL partitioning involved in any of this, these are ordinary
tables.

Marco

Op 5-10-2025 om 12:51 schreef Thom Brown:
> On Sun, 5 Oct 2025, 10:52 Marco Boeringa, <marco(at)boeringa(dot)demon(dot)nl> wrote:
>
> Hi,
>
> I currently run PG18 + PostGIS 3.6.0 on an Ubuntu 24.04 VM guest as
> Windows 10 Hyper-V virtual machine.
>
> The machine is a dedicated refurbished HP Z840 local workstation with
> 2x22 cores (E5-2699 v4) with 512 GB RAM and a 10 TB NVMe raid-0, with
> the Ubuntu guest having 400 GB RAM available.
>
> On this machine, which is dedicated to just one custom written
> geoprocessing workflow involving OpenStreetMap data, I have
> successfully
> processed up to global OpenStreetMap Facebook Daylight distribution
> data, with up to > 2.4 B record Polygon table for all Facebook
> Daylight
> buildings. So this has proven a very capable system.
>
> However, after upgrading to PG18 and the switch to the "io_method =
> worker" setting (tested with 3, 5, 16 and 22 workers), I am seeing an
> issue where it appears there may be a major issue with io workers
> potentially getting into some sort of locking conflict, that takes
> hours
> to resolve.
>
> The custom written geoprocessing workflow uses Python multi-threading
> based on the Python 'concurrent.futures' framework in combination
> with
> either pyodbc or psycopg2 as database connector to implement a
> powerful
> parallel processing solution to speed up some of the computationally
> intensive tasks (which use UPDATEs), which I generally use with up
> to 44
> threads to fully saturate the dual CPU 44 core system. The custom
> code
> creates a pool of jobs to process for the threads, with the code
> being
> designed to minimize inter-thread locking issues by taking into
> account
> PostgreSQL page locality (although the actual records to process
> are not
> assigned by pages but by unique IDs in the tables). Basically, the
> code
> is designed such that different threads never attempt to access
> the same
> database pages, as each thread gets it own unique pages assigned,
> thus
> avoiding inter-thread locking conflicts. This has worked really
> well in
> the past, with system usage maximized over all cores and
> significantly
> speeding up processing. Jobs are implemented as database VIEWs, that
> point to the records to process via the unique ID of each. These
> views
> must of course be read by each thread, which is probably where the
> PG18
> io workers kick-in.
>
> This has worked really well in previous versions of PostgreSQL
> (tested
> up to PG17). However, in PG18, during the multi-threaded
> processing, I
> see some of my submitted jobs that in this case were run against a
> small
> OpenStreetMap Italy extract of Geofabrik, all of a sudden take > 1
> hour
> to finish (up to 6 hours for this small extract), even though similar
> jobs from the same processing step, finish in less than 10 seconds
> (and
> the other jobs should as well). This seems to happen kind of
> "random".
> Many multi-threading tasks before and after the affected processing
> steps, do finish normally.
>
> When this happens, I observe the following things:
>
> - High processor activity, even though the jobs that should finish in
> seconds, take hours, all the while showing the high core usage.
>
> - PgAdmin shows all sessions created by the Python threads as
> 'active',
> with *no* wait events attached.
>
> - The pg_locks table does not show locking conflicts, all locks are
> granted. I did notice however, that the relation / table locks
> were not
> "fastpath" locks, but ordinary ones. All other locks taken, e.g. on
> indexes related to the same table, were fastpath. I don't know if
> this
> has any relevance though, as from what I read about the
> difference, this
> shouldn't cause such a big difference, not seconds to hours.
>
> - Please note that the processing DOES eventually proceed, so it
> is not
> an infinite dead-lock or something where I need to kill my Python
> code.
> It just takes hours to resolve.
>
> - Switching to "io_method = sync" seems to resolve this issue, and
> I do
> not observe some jobs of the same batch getting "stuck". This is the
> behavior I was used to seeing in <=PG17.
>
> I am not to familiar with all the internals of PostgreSQL and the new
> AIO framework and its "io workers". However, it seems there may be
> some
> sort of locking issue between io workers that can occasionally
> happen in
> PG18 with "io_method = worker"? Is there anyone else observing
> similar
> issues in high multi-threaded processing worklflows?
>
>
> So, to confirm, you get the issue with as little as 3 io_workers?
>
> Also, what is pg_aios telling you during this time?
>
> Thom
>