I Forked “Asyncpg” — And It Parses Database Information to Numpy 20x Sooner | by Vadim Markovtsev | Jun, 2022

Supposed use instances and limitations of the venture:

• The consumer ought to care concerning the efficiency. In most situations, the potential speedup just isn’t ground-shaking, so it’s in all probability not definitely worth the trouble.
• SELECT a minimum of ten rows. In any other case, the profit is minuscule.
• The consumer is aware of the varieties of the returned fields upfront. Any information modeling ought to come in useful, e.g., SQLAlchemy.
• These sorts are primarily primitive and map to NumPy: integers, floating factors, timestamps, bytea, textual content, uuid, fixed-size geometry.
• The consumer is aware of the utmost size of the returned bytes and strings to specify ample S# and U# sorts. In any other case, it’s attainable to fall again to things, however the efficiency will degrade.
• Both use asyncpg straight or via a skinny wrapper like morcilla. Async SQLAlchemy 1.4 and encode/databases are going to interrupt as a result of we return a tuple with a NumPy array and null indexes as a substitute of a single listing of File-s; even when these libs survive, they repack the outcome of their File-like Python objects and can miss the purpose.
• Conversion of the outcome to pandas DataFrame ought to fly.
• Don’t anticipate magic! This fork is not going to speed up the database server.

I in contrast the efficiency of 4 completely different execution modes:

1. min — executing SELECT 1. This mode units absolutely the minimal question execution time.
2. dummy— we learn information from PostgreSQL and instantly drop it. The shopper doesn’t do any actual work, fetch() all the time returns None. This mode approximates the server processing time along with the client-server communication. It serves as the perfect, unreachable goal.
3. report — legacy question execution, we return the listing of asyncpg.File objects as standard.
4. numpy— mode. We return the structured NumPy array and the listing of null indexes.

My Postgres server was operating regionally, and I linked to localhost:5432. The question was:

SELECT ... FROM generate_series(1, size)

• the place size is the variety of similar rows to be returned
• ... are hardcoded two booleans (5 bigints, one float4, 4 timestamps, two instances, two bytea-s of size 16, and two texts of size 5 and 10)

I measured numerous size-s from 100 to 50,000. The benchmark closely makes use of the superb pytest-benchmark plugin for pytest. I ensured a number of warmup iterations as well the ready assertion correctly.

The tip-to-end question execution is as much as 3x sooner in “numpy” mode versus “report.” If we subtract the immutable “dummy” time, the pure CPU time speedup reaches 22x. The usual deviation of the “numpy” execution time is identical as “dummy”, whereas “report” jumps significantly increased. We win about 120ms on 50k rows, as much as 250ms when it fluctuates most violently.

After all, the real-world speedup can be lower than 3x as a result of any sensible question spends extra time within the PostgreSQL server. I don’t promote snake oil. Absolutely the delta ought to stay the identical, although.

Beneath are thepy-spy --native profiles for comparability. The primary is “report”, the second is “numpy”. The profile exhibits that parsing pgproto to Python’s datetime is a major bottleneck.

The supply code of the benchmark is on GitHub.

“Question augmentation” works by prepending the pickled dtype. The backend code in native asyncpg unpickles again. That’s not as environment friendly as passing a reference straight, however I didn’t need to change any public interfaces, even backward-compatible. If asyncpg will get known as via a wrapper library, no one is keen to ahead the reference passing there, too.

The worth to pay is a few deserialization overhead. Given the supposed common variety of fetched rows, it ought to be negligible.

The unique Cython code is already fairly quick. It taught me a number of intelligent tips with extension lessons like preallocation — @cython.freelist or disable the rubbish assortment — @cython.no_gc. I discovered just one place for enchancment: the frb_check() buffer overrun checks ought to be inlined straight as a substitute of hoping that Cython will do “the precise factor.”

The slowdown is the consequence of Cython’s shenanigans of exception forwarding. The pure Python counterpart is twisted in some locations. I smile each time I’ve to hint the question circulation in asyncpg.Connection and step in via:

• execute()
• _execute()
• __execute()
• _do_execute()
• executor()

asyncpg- depends on NumPy’s structured arrays. They’re fixed-shape C constructions with non-compulsory alignment. NumPy exposes the construction fields each by index and by title. Internally, NumPy retains the plain listing and the dictionary mappings to the nested dtypes and offsets.

PostgreSQL streams rows to the shopper, and we don’t know what number of we’ll fetch till we fetch all of them. It’s unimaginable to resize a NumPy array dynamically, equally to appending File-s to an inventory, and therefore we’ve got to allocate page-aligned evenly sized chunks and concatenate them in the long run.

Sadly, these additional allocations result in double peak reminiscence consumption and tons of memcpy() from the chunks to the ultimate vacation spot.

PostgreSQL sends timestamps and timedeltas as 64-bit integers with microsecond precision. NumPy permits numerous models in datetime64 and timedelta64, e.g., days, seconds, nanoseconds.

Subsequently we should carry out the unit conversion by integer multiplication or division. Profiling indicated that the integer division is just too gradual. It’s nothing new, in fact. Fortunately, there’s a trick from the sequence of Doom’s quick inverse sq. root and the C header-only libdivide that’s bundled along with numpy≥1.21.

I wrapped numpy/libdivide/libdivide.h in Cython, and it labored like a “magic” allure. One other essential step is adjusting zero, aka the epoch. PostgreSQL’s zero is 2000–01–01, and NumPy’s zero is 1970–01–01, so we should add 30 years to every worth.

PostgreSQL sends strings in UTF-8. NumPy, on the opposite finish, expects UCS-4 for the sake of simpler vectorization, I suppose. We’ve to recode. CPython exports related features, however all of them anticipate Python string or byte objects, which might kill the efficiency.

I tailored the implementation from MIT-licensed fontconfig: FcUtf8ToUcs4. It really works moderately quick; the algorithm is easy. The fork has an possibility to repeat strings on to S# dtype — nice for ASCII-restricted textual content.

I needed to resolve the difficulty with nulls. We’ve to assist them with out sacrificing efficiency. Every time we encounter a null, we append the flat index to an inventory and write a NaN surrogate to the array chunk.

• For float32 and float64, that may be a real NaN.
• For datetime64 and timedelta64, that may be a NaT that behaves equally.
• For objects, we write None.
• For integers, we write the minimal worth given the variety of bits.
• For S#, we fill with 0xFF.
• For U#, we fill with 0x00.

Clearly, any of the talked about values may be official, so the one assured test for nulls is to scan the returned null indexes.

What occurs if the consumer units the unsuitable dtypes? They need to catch a useful exception:

asyncpg.pgproto.pgproto.DTypeError: dtype[10] = <U20 doesn’t match PostgreSQL textual content of measurement 21

Staying on the kind matter, I’m utilizing an undocumented constructor argument of np.dtype to specify the column output mode: