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

Hi Thom,

I realized that my observation of the pg_aios view being empty was 
likely with the "io_method = sync" option set, which I guess doesn't use 
or fill the pg_aios view? Can you confirm the pg_aios view is unused 
with "io_method = sync", this aspect is not documented in the PostgreSQL 
help? Anyway, I will need to re-test with 'worker' set.

I do see Tomas Vondra mentioning that even the 'sync' option in PG18 
still goes "through the AIO infrastructure", but what that exactly 
means, also in relation to the pg_aios view, IDK:

https://vondra.me/posts/tuning-aio-in-postgresql-18/

Marco

Op 5-10-2025 om 21:57 schreef Marco Boeringa:
>
> 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@boeringa.demon.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
>>