It simply uses the sqlite3_create_function API, so it’s as fast/slow as your Python function. You can even use Cython if pure Python isn’t fast enough. I use UDFs quite a bit and the performance is fine without extra tweaks most of the time, but for something like pow I’d use the math extension instead which would be way more performant.
Btw, user-defined scalar functions aren’t remotely as sexy as user-defined window functions.
Beware that only recent versions of Python have a sqlite3 recent enough to support window functions. This bit me recently; I had to scrap a prototype, pull all the data out, and use numpy instead. Still, in general, SQLite3 is amazing and it's my automatic first choice when working with 100GiB or less.
Python links to sqlite3 dynamically so you just need to sub in a more recent sqlite3 (>=3.25.0). I don’t think it has anything to do with the Python version.
