Can SQLAlchemy benefit from PEP 703 (making the GIL optional)?

[gabrieldemarmiesse]

Hi!

I'm Gabriel de Marmiesse and I'm helping Sam Gross to find out if maintainers of selected Python packages would benefit from PEP703.

If you don't know what is PEP 703, it's about making the global interpreter lock (GIL) optional.
Long story short, there would be a ~10% performance impact on single threaded programs, but would allow multithreaded programms to take full advantage of multiple cores.
You can play with it here.

If you have the time, we would like to know is if the maintainers of SQLAlchemy would benefit from PEP 703 or not. There are multiple situation SQLAlchemy could be in.

1. SQLAlchemy already uses threads and PEP 703 will likely make some features faster by allowing parallelization of cpu computation without any change.
2. SQLAlchemy doesn't use threads, but PEP 703 would allow you to rewrite some features with multithreading, thus making it faster.
3. SQLAlchemy uses multithreading with a low-level language (rust, C, C++...) or multiprocessing and using PEP 703 would allow a rewrite and lower the maintenance burden.
4. There is no computation that can be parallelized.
5. SQLAlchemy wouldn't use multithreading, but it's very likely that users will call this package functions and classes from multiple threads because the workload can often be parralelized.

The Python language could never use multiple cores with multithreading, so it's hard to imagine what the future of the language would look like if we were to enable it. Feel free to tell us if you see new potential use cases with this PEP. Thanks for your time!

If you want to know more about PEP 703:

• The PEP
• Python 3.9 implementation with install instructions
• Python 3.12 implementation
• Discussion about PEP 703
• Discussion about the multicore future of Python

[zzzeek]

hi yes, had some back and forth on fosstodon about this, have only skimmed the spec but for SQLAlchemy directly, this pep would be a lose for us:

1. sqlalchemy does not spawn any threads directly, so for us the 10% performance impact only hurts
2. sqlalchemy is of course used in concurrent applications, with all of: a. threads b. gevent/eventlet monkeypatch approaches c. asyncio d. multiprocessing. All of these approaches introduce new and novel issues specific to each approach. The issues are almost always non-deterministic, random, often not well identified or handled for months as users struggle to help us create reproduction cases, with maturity of each approach taking literally years to achieve. Adding an entirely new mode of operation to cPython that's optional would be an enormous burden for us as far as ensuring we use APIs appropriately, adding support, testing, we would have to spin up new test workers to test SQLAlchemy in both modes of operation, we would be getting strange new race condition related issues reported (which are usually surprising behaviors in the database drivers that we would need to accommodate).
3. SQLAlchemy uses cython and it's not clear what impact this pep has on cython

Basically it's one thing to "remove the GIL" from python, OK that would be challenging, but to make it an option, that would be basically really bad for us as far as testing / CI / support (and also might be seen as a bit of a red flag; by being an option only, it's a given that GIL-less mode is free to break existing code, perhaps it would be better to move Python forward first to a fully compliant language first before adding this mode. will there be a warnings mode for code that does non-GIL-less unsafe things? ).

We'd have users demanding compatibility for this new mode immediately and it's unknown the extent of changes which would be needed in SQLAlchemy to accommodate them. I see there's a test branch I can "try out" but our test suite has only a relatively small number of thread-related tests, so it's unknown how many issues our current test suite could even find.

Basically for the moment the GIL-less idea would likely be burdensome for us and the fact that it's only an "option" seems to strongly imply major compatibility issues that we would not prefer.

[ivoflipse]

The current design has a different ABI (see https://peps.python.org/pep-0703/#python-build-modes)

[zzzeek]

yah that would be really unpleasant, and id want us to do better.

will try to see if I can refine my response above...

[CaselIT]

cc @CaselIT in case you have interest

I don't have much to add here.
Since sqlalchemy generally interfaces with the network, threads already provide an useful speed increase also with the gil.

There may be some places in the internal sqlalchemy code that could maybe make use of threads if they were to use multiple cores, like result rows generation and/or cache key generation, but I'm not sure how impactful this change would be.

Overall it's probably not worth the single thread perf tradeoff if only considering sqlalchemy

[gpshead]

FWIW it is not possible to create a free-threaded CPython without changing the ABI because reference counting and the reference count itself is exposed as struct data and we have C APIs that return borrowed references. Both are existential problems.

(binary interfaces are horrible)

[CaselIT]

After reading a bit more about this pep my 2c is that it seems to cause a lot of complexity in many parts (packaging, maintainers, cpython developers, etc) for questionable usefulness, and the conclusion I got is that it may not really worth it, or at least not now (given that a similar feature, subinterpreters, to improve the multicore story has just been added to 3.12).

Either way for sqlalchemy it should not change much, since it's already used in multi threaded code (hopefully)