slopfront
preferences

Which library for postgresql access?

1 points · submitted Sep 2019 by u/spotta · archived

I know there are a few of them. psycopg2 seems to be the most popular, but asyncpg looks to be the fastest. There are also the standard sqlalchemy type sql connections that aren't specific to postgres.

I haven't looked at the postgresql python library ecosystem in a while, and I'm curious if anyone has any recent experience with these? My concerns:

  • The last time I looked, asyncpg was fairly new. Has it reached a pretty stable state? It hasn't had a release since Jan, so it seems to have slowed down... anyone know if that is due to a loss of resources or that it has reached a more stable form.

  • When asyncpg came out, it was outrageously faster than psycopg2, is this still true? A factor of three is nothing to sneeze at.

  • asyncpg in their introductory blogpost had a headline feature of supporting generic container types, obviating the need for typecasters which are apparently prevalent in psycopg2. Is this still something that needs work with psycopg2?

  • asyncpg also makes extensive use of prepared statements, which pycopg2 didn't. Has this changed?

Any opinions about postgresql python libraries is also welcome: I didn't use either of these a lot when I last looked, so if there is some nasty API designs somewhere lurking I would love to know.

4 comments · 60% upvoted · other discussions

comments

tunisia3507 2 points · Sep 2019

If you are already waist-deep in async stuff, asyncpg would probably be fine. If you're currently in sync-world, adding async is a big hassle which pervades every level of your stack.

I have no issues believing that asyncpg is 3x faster than psycopg2, if their benchmark was testing a lot of very small calls. Shockingly, concurrent code can be faster than serial code. In specific situations where you had to make a lot of very small calls, you could handle your own concurrency with a threadpool and it would still be more worth your time than adding asyncio. But with complex calls, the time on the SQL side would increase (so the difference between python libraries would be smaller). I doubt there's any speed significant speed difference when it comes to formatting the query, or parsing the result into python types.

SoCalLongboard 2 points · Sep 2019

I always used PsychoPig (psycopg2).

PiBaker 2 points · Sep 2019

I've used PeeWee and SQL Alchemy, both of which are ORMs.

In the end, I went with psycopg2 because PeeWee/SQL Alchemy felt like I was being forced to learn a new module just to run SQL on the database when I already knew SQL.

aes110 1 points · Sep 2019

Depends on your need, for something simple psycopg2, however if you are fine with the development overhead of async, asyncpg is great, and very easy to use.

Its just a matter of making your project async vs sync.